Udostępnij za pośrednictwem

AlwaysOn Availability Group Returns Failover Partner for Legacy Mirror Application Connectivity

  • Artykuł

Availability Groups Simulate Database Mirroring Connection Behavior

Given the following scenario, SQL Server will return the failover partner server name to a connection request:

  1. The availability group has a single secondary replica.
  2. The availability group replicas have ALLOW_CONNECTIONS set to NO or READ_ONLY.
  3. The client application makes a successful initial connection to the primary replica.
  4. 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.

image

 

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

Comments

  • Anonymous
    March 20, 2015
    Hello TeamGreat articles on this blog.. Keep it going . I assume this blog needs more markting because its not reaching to all audience.  try link to original sql alwayson blog ( blogs.msdn.com/.../sqlalwayson)When will be the following article will be published ?Coming Up: Part 2 - Describe the log backup process on an ROSR