Tag Archives: SQL Server 2014

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