Rajibsqldba.files.wordpress.com



MOVING ALL AVAILABILITY GROUPS IN THE EVENT OF A SINGLE AVAILABILITY GROUP FAILOVER

Author: Vivek Janakiraman

Environment

[pic]

-> 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 dbo.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.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download