Intro

In SQL Server 2016 SP1 Standard Edition, you can create Basic AG’s, which can only hold one database for each AG.

Recently a customer asked me to make sure that all databases stay primary on the same instance when one or more fail-over to the other side.

Setting:

SQL Server 2016 SP 1

3 Basic availability groups with 1 database each.

Reasoning:

To help him reach his goal, I created the following script, which will run via a job, every 10 seconds on  both servers, and if the server has exactly 2 primaries, then that instance will issue a command so the last secondary fails over to his side, which results in 3 primaries remaining on the same instance.

If an even amount of groups is used, you will need to come up with a plan so they don’t start failovers at the same time. ( Like adding another ‘Quorum’ AG or setting different start times).

Script:

if (
SELECT
	count(name)
FROM
 sys.availability_groups_cluster AS AGC
  INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
   ON
    RCS.group_id = AGC.group_id
  INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
   ON
    ARS.replica_id = RCS.replica_id
  INNER JOIN sys.availability_group_listeners AS AGL
   ON
    AGL.group_id = ARS.group_id
WHERE
 ARS.role_desc = 'PRIMARY' 
 ) = 2
 begin
		if (
			select count(*) from
			sys.availability_groups_cluster AS AGC
			INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
			 ON
			RCS.group_id = AGC.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
			ON
			ARS.replica_id = RCS.replica_id
			INNER JOIN sys.availability_group_listeners AS AGL
			ON
			AGL.group_id = ARS.group_id
			WHERE
			 name = 'AG1') = 1
			begin
				ALTER AVAILABILITY GROUP [AG1] FAILOVER;
			end
		if (
			select count(*) from
			sys.availability_groups_cluster AS AGC
			INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
			 ON
			RCS.group_id = AGC.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
			ON
			ARS.replica_id = RCS.replica_id
			INNER JOIN sys.availability_group_listeners AS AGL
			ON
			AGL.group_id = ARS.group_id
			WHERE
			 name = 'AG2') = 1
			begin
				ALTER AVAILABILITY GROUP [AG2] FAILOVER;
			end
		if (
			select count(*) from
			sys.availability_groups_cluster AS AGC
			INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
			 ON
			RCS.group_id = AGC.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
			ON
			ARS.replica_id = RCS.replica_id
			INNER JOIN sys.availability_group_listeners AS AGL
			ON
			AGL.group_id = ARS.group_id
			WHERE
			 name = 'AG3') = 1
			begin
				ALTER AVAILABILITY GROUP [AG3] FAILOVER;  
			end

 end

Leave a Comment

Your email address will not be published. Required fields are marked *