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
Best Answer
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:
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.