I am hoping you can all help me in what feels like a really simple problem, but one I am struggling to find a solution for.
We are running SQL Server Standard 2016 and utilize availability groups between the 2 nodes. Being on standard, I can only have 1 database per Availability group.
Once a month, we need to patch our server, and it's an absolute pain to manually fail-over each group and then back for patching.
My solution is that I have a 3rd server, which has Nodes 1 and 2 as linked servers and this helps me manage the 50 odd availability groups as well as some other application related things we do.
I have a connection to the 2 nodes, and as a job, I collect and update a table on server 3 with each database, availability group name, node name, etc for the 2 main nodes. this runs every 5 minutes and alerts me of issues.
My final step is to now have a stored procedure on server 3, that will simply allow me to failover the availability groups or just a single one ect. It works perfectly except on the one node and on the other, i get this error :
Availability-group DDL operations are permitted only when you are using the master database. Run the USE MASTER command, and retry your availability-group DDL command.
My code for this command is as follwos :
SET @Query = 'EXEC (''ALTER AVAILABILITY GROUP [' + @CurrentAG + '] FAILOVER'') AT [' + @FailoverServer + '];'
EXEC sp_executesql @Query
Which ends up with this command :
EXEC ('ALTER AVAILABILITY GROUP [MyAvailGroup] FAILOVER') AT [DestinationLinkedServerNode];
Thank you for any help on this.
Best Answer
To answer your question, it should be as below provided you are executing below on primary replica.
A better solution would be to use dbatools to initiate failover
Invoke-DbaAgFailover. This way, you dont have to use any extra server or linked server. It will be much flexible solution as well.