Environment
-> Disaster Recovery for a SQL Server 2016 Standard Edition Database Server was implemented using Alwayson Basic Availability group.
-> One of the availability group failed over and that caused application outage, as other databases required by the application were residing on a different server. Implementing a Listener doesn’t makes sense in this scenario as the application is dependent on bunch of databases and creating Listener for each Availability group doesn’t seem practical and the application cannot use multiple Listeners in their connection string.
-> We decided that in an event of a failover for any Availability group. All other availability groups will also be failed over, Just to make sure that all the availability groups are on the same replica.
-> We decided to implement a Powershell script to perform this. This solution will work only for Basic Availability groups.
-> Create a Linked Server on each of the 2 SQL Server nstance to access the master database of other Database Instance. For security reasons we can use “Be made using the login’s current security context’.
-> Make sure all the Availability groups are at the same instance. Execute the below query on both Primary and secondary instance on Master Database only once.
IF EXISTS(SELECT * FROM sysobjects where name ='AG_role')
DROP TABLE dbo.AG_role
create table AG_role (AGName varchar(30)
,PrimaryReplica varchar(30)
,DBName varchar(30)
,Role int)
set nocount on
Declare @AGName varchar(30)
Declare @PrimaryReplica varchar(30)
Declare @DBName varchar(30)
DECLARE AG_Cursor CURSOR STATIC FOR
SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name
OPEN AG_Cursor
FETCH NEXT FROM AG_Cursor into @AGName, @PrimaryReplica, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
insert into AG_role values(@AGName, @PrimaryReplica, @DBName,sys.fn_hadr_is_primary_replica(@DBName))
FETCH NEXT FROM AG_Cursor into @AGName, @PrimaryReplica, @DBName
END
CLOSE AG_Cursor
DEALLOCATE AG_Cursor-> Save the below file as a powershell script on both Primary and secondary Database server. Create a Windows task on both servers to execute the script every 1 min on both servers. Please make sure $PrimaryReplica is same as the server where the powershell script is saved. The $SecondaryReplica will be the other server that is part of the Alwayson Availability group.
$PrimaryReplica = "JBSERVER1";
$SecondaryReplica = "JBSERVER2";
Invoke-Sqlcmd "create table AGFailover(AGSQL nvarchar(max),flag int default 0);" -ServerInstance $PrimaryReplica;
Invoke-Sqlcmd "
Declare @Ag_Current int
Declare @Ag_Previous int
Declare @AGName varchar(30)
Declare @PrimaryReplica varchar(30)
Declare @DBName varchar(30)
Declare @Role int
Declare @Flag int
DECLARE AG_Cursor CURSOR STATIC FOR
SELECT * from AG_Role
OPEN AG_Cursor
FETCH NEXT FROM AG_Cursor into @AGName, @PrimaryReplica, @DBName, @Role
WHILE @@FETCH_STATUS = 0
BEGIN
set @Ag_Current = (select sys.fn_hadr_is_primary_replica(@DBName))
set @Ag_Previous = (select Role from Ag_Role where DBName=@DBName)
if(@Ag_Current = @Ag_Previous)
BEGIN
print 'No Failover occured'
insert into AGFailover (AGSQL)
select 'ALTER AVAILABILITY GROUP ['+@AGName+'] FAILOVER;'
END
ELSE
BEGIN
--print 'Failover occured'
if (@Ag_Previous =1)
BEGIN
set @flag=1
END
END
FETCH NEXT FROM AG_Cursor into @AGName, @PrimaryReplica, @DBName, @Role
END
CLOSE AG_Cursor
DEALLOCATE AG_Cursor;
if (@flag=1)
begin
update AGFailover set flag=1
end" -ServerInstance $PrimaryReplica;
Invoke-Sqlcmd "
DECLARE @AGSql NVARCHAR(MAX) = N'';
SELECT
@AGSql = @AGSql + AGSQL FROM $PrimaryReplica.master.dbo.AGFailover where flag=1;
EXEC sp_executesql @AGSql;" -ServerInstance $SecondaryReplica;
Invoke-Sqlcmd "drop table AGFailover;" -ServerInstance $PrimaryReplica;
Invoke-Sqlcmd "delete from AG_role;" -ServerInstance $PrimaryReplica;
Invoke-Sqlcmd "
set nocount on
Declare @AGName varchar(30)
Declare @PrimaryReplica varchar(30)
Declare @DBName varchar(30)
DECLARE AG_Cursor CURSOR STATIC FOR
SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name
OPEN AG_Cursor
FETCH NEXT FROM AG_Cursor into @AGName, @PrimaryReplica, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
insert into AG_role values(@AGName, @PrimaryReplica, @DBName,sys.fn_hadr_is_primary_replica(@DBName))
FETCH NEXT FROM AG_Cursor into @AGName, @PrimaryReplica, @DBName
END
CLOSE AG_Cursor
DEALLOCATE AG_Cursor;" -ServerInstance $PrimaryReplica;-> Whenever a failover of any of the availability group happens, the script executing from the task scheduler will check for the failover and fails over all other Availability groups.
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
