Hi All,

Need a query help.

I am running SQL 2014 2-node AlwaysON Availability groups, Enterprise Edition in our environment
and 5 databases are part of AG.

Question is, sometimes AG is getting failed over to node2 but always our preferred node is node1 due to some business needs otherwise some of our jobs will fail.

So, what I looking for is, a sql script which can handle a situation wherein, for some reason, AG is failed over to node2, it should be able to detect if node1 is back online or not and if so, it should failback to node1. How to do this using tsql query or stored proc or sql agent job ?

Thanks in advance.

Hi Samantha,

Based on my research, failback is not recommended for SQL Server AlwaysOn Availability Group.

In your scenario, it is better to setup a job to monitor the status of the preferred server that you wish to be the primary and then perform a planned manual failover of the Availability Group. You can execute the following scripts in your job, for more details, please review this similar thread.

IF EXISTS

 (

     select * from sys.dm_hadr_availability_replica_cluster_states

     join sys.dm_hadr_database_replica_cluster_states on sys.dm_hadr_availability_replica_cluster_states.replica_id = sys.dm_hadr_database_replica_cluster_states.replica_id

     join sys.dm_hadr_availability_replica_states on sys.dm_hadr_availability_replica_states.replica_id = sys.dm_hadr_availability_replica_cluster_states.replica_id

     where replica_server_name = 'Replicaname'

     and join_state = 1

     and database_name = 'DatabaseName'

     and is_failover_ready = 1

     and is_database_joined = 1

     and is_pending_secondary_suspend = 0

     and operational_state = 2      --ONLINE

     and recovery_health = 1        --ONLINE

     and synchronization_health = 2 --HEALTHY

     and role = 2 --SECONDARY

 )

 BEGIN

     ALTER AVAILABILITY GROUP [My AlwaysOnGroup] FAILOVER

 END

There are also some articles about monitoring AlwaysOn Availability Group for your reference.
Monitoring of Availability Groups (SQL Server)
Monitor SQL Server AlwaysOn Availability Groups

Thanks,
Lydia