Working with Microsoft SQL Server AlwaysOn Availability Groups

The Microsoft SQL Server AlwaysOn Availability Groups feature is a high-availability, disaster-recovery solution that provides an enterprise-level alternative to database mirroring.

Prerequisites

• Credentials used for connecting to individual replicas should be identical to those used for connecting to the AlwaysOn Listener.

• Replicas should be configured to allow connections.

  To configure this, the DBA should run the following script:

  ALTER AVAILABILITY GROUP [{ag_name}]
  MODIFY REPLICA ON '{replica_name}'
  WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL, READ_ONLY_ROUTING_URL = 
  'TCP://{replica_name}:{Port}'))

Configuring Replicate to access the AlwaysOn Listener

When working with AlwaysOn Availability Groups, you need to specify the IP address/host name and access credentials of the AlwaysOn Listener in the Microsoft SQL Server source endpoint settings.

Limitations

The following limitations apply when working with AlwaysOn Availability Groups:

• DAG (Distributed Availability Groups) is not supported.
• An AlwaysOn Availability Group that requires Replicate to access the backup transaction logs (see below) is not supported when the Replicate has file-level access to the backup log files check box is enabled in the Microsoft SQL Server source endpoint settings.

Accessing backup logs in AlwaysOn Availability Groups

As opposed to active transaction logs which are synchronized across the AlwaysOn Availability Group, backup transaction logs are different for each individual replica. Consequently, when Replicate needs to access the backup transaction logs, it "asks" each Replica in turn if it has the required logs, until the backup transaction logs are located. Note that if one of the Replicas that Replicate is trying to reach is offline, Replicate will wait until that Replica comes back online and then query it for the backup transaction logs.

Replicate needs to access the backup transaction logs on each Replica (or until it finds the required logs) in the following scenarios:

• Working in backup only mode.

  For more information on this mode, see the Microsoft SQL Server source endpoint settings.

• Starting a task from a specific timestamp.

  For more information on this option, see the Tables are already loaded option in Advanced Run Options.

• Due to latency i.e. if there is a high rate of events (changes) that Replicate is unable to process using the active log only.