1

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.

Improve this question
1
  • 1
    Does the login you are using for linked server authentication have a default database defined? In any case, you can add a "USE [master];" to the start of your dynamic query.
    – Jonathan Fite
    Commented Jun 22, 2019 at 0:47

1 Answer 1

Reset to default
1

To answer your question, it should be as below provided you are executing below on primary replica.

SET @Query = 'EXEC (''use master; ALTER AVAILABILITY GROUP [' + @CurrentAG + '] FAILOVER'') AT [' + @FailoverServer + '];'
        EXEC sp_executesql @Query

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.

Improve this answer
1
  • That worked thank you. User permissions were the same. I will have a look at dbatools and see if it can do what i need as it may have some other things i want. Thanks
    – WadeH
    Commented Jun 24, 2019 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.