Manual Failover error on SQL Server 2014SP1 AlwaysOn

21 Jan

I implemented AlwaysOn Availability groups solution on SQL Server 2014 SP1.

During the testing of a manual failover (via the GUI), I encountered the following 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. (Microsoft SQL Server, Error: 35208)

I was puzzled by the error, as it manifested itself just after we installed the latest  Cumulative update package 4 (CU4) for SQL Server 2014 Service Pack 1.
For a few moments I though that I have encountered a new bug.

I tried restarting the machine (the whole implementation wasn’t in production just yet) and I could perform one manual failover, but upon attempting another manual failover we got that error message again.

I tried performing a manual failover via Transact-SQL:

ALTER AVAILABILITY GROUP <AG01> FAILOVER;

the failover went fine with no issues.

This led me to suspect that it’s a login/user related issue.

I logged out and connected with SA account. I tried performing  a manual failover via the GUI, and it finished successfully.
This confirmed me it was a login/user and not a GUI bug.

I checked the properties of my login (it was a group for the SQL admins) and saw that the default database wasn’t master, but a user database that was recently created by the application.
Once the default database was changed back to master, we had no issues with the manual failovering.

Hope it’ll help somebody in case of need.
I haven’t found many articles regarding this issue online.

Advertisements

One Response to “Manual Failover error on SQL Server 2014SP1 AlwaysOn”

  1. Juanjo Navío May 23, 2017 at 3:47 pm #

    Thanks for this article. It helps me to understand this error that I also suffered the last time thaT I executed a manual failover. I could resolve the problem executing the ALTER statement, but I didn’t understand by this error happened. Thanks again for sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: