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.
- The application is not specifying the availability group listener when connecting.
SQL Server will return the failover partner server name (the SQL Server name hosting the secondary replica). The data access provider will cache the failover partner server name.
Following a failover, if an application is designed to reconnect on connection failure, it will first fail to connect to the replica now hosting a secondary replica, and then attempt to connect to the cached failover partner, the primary replica.
This behavior may be unexpected. For example, an identical SQL Agent job is created on the primary and secondary replicas of an availability group and the job is not qualifying that the replica is primary sys.fn_hadr_is_primary_replica before executing its commands. The expectation is that the job succeeds when accessing the availability database on the primary replica and fails when attempting to access the availability database on the secondary because the replica is configured for ALLOW_CONNECTIONS=NO.
Instead, following a failover, the job that is now executing at the secondary replica will not behave as expected. The next time the job executes, it will fail to connect locally and then attempt to connect using the cached failover partner name and will successfully connect to the now primary replica. This can cause unexpected behavior and results because the job at the secondary is connecting and executing successfully against the primary replica.
This behavior is by design. AlwaysOn availability groups are designed to be backward compatible with applications that expect legacy database mirroring connection behavior.
The reconnect behavior will only occur given a certain configuration, when 1) there is a single secondary replica and 2) the availability replica’s ALLOW_CONNECTIONS is set to NO or READ_ONLY. The following table reports the expected behavior depending on these variables.
Workaround Database Mirroring Connection Behavior
If the legacy connection behavior is not desired, consider using one of the following workarounds to ensure your SQL Agent job only executes successfully when the local replica is in the primary role.
- Set the availability group Backup Preferences to Primary and then in the job at each replica, use the sys.fn_hadr_is_primary_replica to ensure execution only locally at the primary replica.
- Qualify execution by querying sys.dm_hadr_availability_replica_states where is_local and role both return 1.
- Add a third replica to the availability group.
Database Mirroring Connection Behavior
For more information on database mirroring connection behavior see:
Making the Initial Connection to a Database Mirroring Session