How to fail over availability groups on multiple remote servers

Question

We have many SQL Server installations with availability groups. During our maintenance window, we will need to fail over all the HA groups to secondaries, patch the primary servers, then fail back, patch secondary servers. It's a tedious process to log on to each server and do the failover/failback job.

I am looking for a way to automate this process. Is there any way we can run failover statement from our central management server to multiple linked servers, with high availability group name and remote server name as variable. Tried this and it doesn't work:

declare 
@remote_server [char](24),
@hag_name  nvarchar(24),


set @hag_name  = 'group1'
set @remote_server = 'Server1'

execute ('ALTER AVAILABILITY GROUP ? FAILOVER', @hag_name) at @remote_server

Answer 1

You could use PowerShell and the dbatools module to orchestrate this for you. Basically, you query your CMS via PowerShell to get a list of servers, then iterate over the list and use Get-DbaAvailabilityGroup to fetch the availability groups on each server. This can then be passed to Invoke-DbaAgFailover to perform the failover.

You can expand the PowerShell to check for things like correct AG state prior to failover and the dbatools functions have in-built logic to safely perform failovers (no data loss) by default.

Set this script up as an Agent job on your CMS server and execute it either on a schedule or manually as part of your patching process. By fetching the servers from CMS and using Get-DbaAvailabilityGroup, any new AGs will be handled automatically provided you have added the replicas to the CMS.

Answer 2

This solution doesn't use a CMS, but I think it'll work. It's a little script I put together for searching through a comma delimited list of servers for SQL Operators that should be changed as DBAs are swapped in and out.

I modified the script for multiple AG failovers. You can adapt it to suit your specific environment.

You have to build a comma delimited list of of the secondary servers and availability groups: server1,ag1,server2,ag2,.... ...and plug that into the code.

This script's output is the executable code. The output from this sample script should look like this:

(1 row affected)
:CONNECT server1
ALTER AVAILABILITY GROUP [L1] FAILOVER;
GO

:CONNECT server2
ALTER AVAILABILITY GROUP [L2] FAILOVER;
GO

:CONNECT server3
ALTER AVAILABILITY GROUP [L3] FAILOVER;
GO

:CONNECT server4
ALTER AVAILABILITY GROUP [L4] FAILOVER;
GO

Then you copy/paste this into a new SSMS window, remove the non-TSQL lines like "(1 row affected)", switch to SQLCMD mode in SSMS, and execute.

What this script really needs is an intelligent way of knowing which server is secondary, so you don't have to know it for the CDL. I'll try to put that together and post an edit here.

Of course, test this on a couple guinea pig servers.

/*
This script will create a TSQL script that you can run separately in SQLCMD mode. 

Steps:
1. Edit @Servers with a comma delimted list of your servers.
2. Edit @SearchVariable1 and @SearchVariable2 with the search variables. 
        If you want to search for only 1 string, then make both variables that string.
        For example 'kanjana', which is part of the name of the DBA who had this set of servers before me. 
    You can add more SearchVariableN variables. Edit the dynamic SQL statement. 
3.  Edit the dynamic SQL to do whatever you need.
4. Copy and paste the result into a new window, specify Query | SQLCMD mode, and execute.
    Remove any non-TSQL lines, such as '(1 row affected)'
5. If logical, you can re-run the script to check the results.


*/

USE MSDB
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
    AND xtype IN (N'TF'))
DROP FUNCTION SplitString
-- SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
GO

CREATE FUNCTION [dbo].[SplitString]
(      
      @Input NVARCHAR(MAX),  
      @Character CHAR(1)=','  
)  
RETURNS @Output TABLE (  
      Item NVARCHAR(1000)  
)  
AS  
BEGIN  
      DECLARE @StartIndex INT, @EndIndex INT  

      SET @StartIndex = 1  
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character  
      BEGIN  
            SET @Input = @Input + @Character  
      END  

      WHILE CHARINDEX(@Character, @Input) > 0  
      BEGIN  
            SET @EndIndex = CHARINDEX(@Character, @Input)  

            INSERT INTO @Output(Item)  
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)  

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))  
      END  

      RETURN  
END
GO


-- User configuration variables:
DECLARE @Servers VARCHAR(MAX) = ''

-- ************ Edit these variables:
SET @Servers = @Servers +
    'server1,L1,server2,L2,server3,L3,server4,L4' 

-- ************ 

-- other variables:
DECLARE @ThisServer VARCHAR(100)
DECLARE @ThisL VARCHAR(100)
DECLARE @SQL VARCHAR(MAX) = ''
DECLARE @B CHAR(2) = CHAR(13) + CHAR(10) 


DECLARE MyCursor CURSOR
    FOR SELECT * FROM dbo.SplitString(@Servers,',')
OPEN MyCursor 

FETCH NEXT FROM MyCursor INTO @ThisServer
FETCH NEXT FROM MyCursor INTO @ThisL

WHILE @@FETCH_STATUS = 0
BEGIN
    -- PRINT @ThisServer
    SET @SQL = @SQL + ':CONNECT ' + @ThisServer + @B
    SET @SQL = @SQL + 'ALTER AVAILABILITY GROUP [' + @ThisL + '] FAILOVER;'
    SET @SQL = @SQL + @B + 'GO' + @B + @B

    FETCH NEXT FROM MyCursor INTO @ThisServer
    FETCH NEXT FROM MyCursor INTO @ThisL
END

PRINT @SQL

CLOSE MyCursor
DEALLOCATE MyCursor



IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
    AND xtype IN (N'TF'))
DROP FUNCTION SplitString