Replication Agents fail to connect to listener in a multisite cluster

A listener (network name) for a multisite cluster will be dependent on more than 1 ip address.  When connecting to a listener for a multisite cluster, it is recommended to add the parameter multisubnetfailover to the connection string for the application.  Legacy applications may not be able to use the latest version of Microsoft SQL…


Failing back from DR site after primary site is back online

Assume steps similar to those from Manual Failover of Availability Group were used to move to the DR site. When the Availability Group is brought online on the DR site with alter availability group…force_failover_allow_data_loss, the replication from the new primary at the DR site to the secondary(s) at the primary site will be suspended, and…


AlwaysOn Availability Group Listener Cannot be Created or Failed Over on Node if ‘Primary DNS suffix’ is not set

AlwaysOn availability groups will not function properly on Windows servers where the system’s ‘Primary DNS Suffix of this Computer’ is not set. Usually, the Primary DNS Suffix is populated when a Windows server joins a domain. Create Listener on Windows server without ‘Primary DNS suffix’ fails Attempting to create the availability group listener will fail…


AlwaysOn Availability Group Returns Failover Partner for Legacy Mirror Application Connectivity

Availability Groups Simulate Database Mirroring Connection Behavior Given the following scenario, SQL Server will return the failover partner server name to a connection request: The availability group has a single secondary replica. The availability group replicas have ALLOW_CONNECTIONS set to NO or READ_ONLY. The client application makes a successful initial connection to the primary replica….


Recommendations for Index Maintenance with AlwaysOn Availability Groups

  SYMPTOMS Consider the following scenario The database is part of AlwaysOn Availability Groups You run long and log-intensive transactions like Index maintenance/rebuilds You observe one or more of the following symptoms: Poor performing DML operations in availability databases on the primary replica if synchronous secondary replicas are present. Huge log send queue in the…


How to add a TDE encrypted database to an Availability Group

By default, the Add Database Wizard and New Availability Group Wizard for AlwaysOn Availability Groups do not support databases that are already encrypted:  see Encrypted Databases with AlwaysOn Availability Groups (SQL Server). If you have a database that is already encrypted, it can be added to an existing Availability Group – just not through the…


Troubleshooting REDO queue build-up (data latency issues) on AlwaysOn Readable Secondary Replicas using the WAIT_INFO Extended Event

PROBLEM You have confirmed excessive build up of REDO queue on an AlwaysOn Availability Group secondary replica by one of the following methods: Querying sys.dm_hadr_database_replica_states and observing a high value for redo_queue_size You have looked at the redo_queue_size in the dashboard view for an AlwaysOn secondary replica You have observed a high value for the…