Failed to perform AlwaysOn Manual Failover using the wizard – Availability-group DDL Operations are permitted only when you are using the master database .

 

Routine maintenance rolls around and it’s time to failover your AG’s to new Primary’s to allow for patching and restarts… the weapon of choice will be the SSMS Failover wizard – and why not its does the job nicely and give us some nice visuals as it progresses through each failover.

 

On this particular occasion I came across an unusual error upon failing over the first Availability group:

 

 

What! USE Master….Well that’s going to be kinda tough as I am using the GUI here and I was kinda hoping that SSMS would handle this part for me….

Rather than wasting too much time scratching my head and looking perplexed I decided to script out the failover for this availability group using the wizard.

 

:CONNECT SQL02

ALTER AVAILABILITY GROUP [AG1] FAILOVER

 

I set the query window to use master and then switched my SSMS Query window into SQLCMD mode and executed the query , this time the failover was successful.

 

Great! Failover success – but what about this strange error?

 

Now I don’t know about you but when it comes to seeing strange errors that I cannot find the answer too I cannot just leave it! I have to find the answer! 🙂

 

Lets just recap on the issue for a second, I used the Failover Wizard to perform a routine failover to a synchronous node with no data loss, and at the Manual failover step (step 2 of the failover process in the wizard) it failed and the error stated that I should re issue the command ensuring that I USE Master.

 

The SQL error 35208  ‘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’

 

I would totally expect to see this error if I was trying to make changes to an Availability group and I was in the context of any other database other than Master within a Query window , makes perfect sense but for the Failover Wizard its a little different.

 

How on earth do you get the Failover Wizard to Use Master? I assumed it did this by default…well this is where I was wrong and here is the reason why:

When you make a connection to a server in SSMS you are prompted with the ‘Connect to Server’ Dialog box which I am sure you are all aware of, normally you would just pick the server in the drop down, select the Authentication type and provide a password if SQL auth then click OK and this will connect you to the server of your choice. Once you are connected SQL will then use your Login details to determine which database is deemed as your default database – in a lot of cases this does tend to be master well certainly for DBAs anyway but you can switch database context as you please.

 

Within the Connect to Server Dialog box there is an Options button (Never really paid that much attention to it before):

 

 

And within the Options Page you will notice that there is a ‘Connect to Database’ drop down box under the Connection Properties tab and in this case the default database for this connection is ‘OooPickMe’  🙂

 

So what happens when we perform a Manual Always On Availability group Failover using this connection …

 

Always On High Availability > AG1 > Failover > Select Node SQL02

 

 

Now if we click on the connect button and then check the connection options here we will see that the connection details have been inherited from the SSMS connection options used to connect to the instance:

 

 

 

Now lets test out that Failover just to be sure that this is definitely where the issue is:

 

 

 

 

Bingo!

 

Now although its not telling us which database it is connecting to , we know its not master and we know that the default is OooPickMe, so lets change this setting in the Options to Master Or Default which ever suits, in my case I will pick Default because the default database for my login is Master.

 

Run through the Wizard as we did before except this time my connection is using Master in the ‘Connect to Database’ Option.

 

 

WholeLottaGreen!

 

It is important to note that the connection option for ‘Connect to Database’ needs to be correct on the Target server , the server which you intend to Failover to as this is where the Failover SQL Statement will be executing against even though you are initiating the Failover on the Primary, so in my case it was SQL02 that was misconfigured.

 

I have no idea why my connect to database setting for that server was not set to Default or Master – but I do know that it is set correctly now!!

Thanks for Reading 🙂