4

I just got a Sql Server 2016 Availability Group setup (A Primary, a Syncronous Secondary and a Async Secondary). I have five databases on the servers.

I setup an application to run queries against the server (using Entity Framework with a "failover partner" connection string.)

When I tried to test the failover by rebooting the primary server, I got the following error:

Server MySecondaryServer, database master is not configured for database mirroring.

It seems odd to mirror a system database like this, so we did not have it setup as one of the "Availability Databases".

So, here is my question: Should I add the master database to the list of "Availability Databases" and will it cause problems if I do it?

1
  • 1
    The failover partner property is not used for an AG in your connection string. You have to configure a listener for the AG and use that as your server name in the connection string.
    – user507
    Commented Apr 12, 2017 at 18:00

1 Answer 1

9

It is not possible to include master as an Availability Database within an Availability Group (AG). System databases (master, model, msdb, tempdb) cannot be part of the Availability Group.

System databases are instance-level objects. They are needed in order for the instance to be online & available. Availability Groups are a level below that, and provide database-level replication from one instance to another (or several others).

SQL Server will prevent you from adding system databases to an AG because those databases need to be read-write on every instance in order for the instance to be online.

Additionally, as stated in the answer on your other question, you should not be using the failover partner connection string attribute. That attribute is designed for use with database mirroring--a different, deprecated availability feature. Instead, you should use an AG Listener, which will also provide proper direction to the primary node, and also allow you to use other AG-specific features, like read-only routing.

Getting back to your error....

Your code probably shouldn't rely on the master database, particularly in an AG, where your database could move to a different instance with a different master database. In the interest of the simplest & most bullet-proof AG architecture, I generally recommend that you go out of your way to make your database self-contained, or make the intentional, educated decision to put something outside of the database.

My first guess (and not knowing more about your code or your connection) is that your query shouldn't be referencing master at all. It could be just that your connection string is landing you in the master database instead of the database you want--so fix your code or connection string, not your AG.

1
  • Thank you for the answer! My query is not referencing master as far as I know. (It is a Entity Framework query, so it is possible that it calls the master database.) It was just returning one row of a view (that pulls from a few tables in one database). We may have been messed up because we were trying to do the failover via connection strings rather than a "Listener". We are going to try that and hopefully this error will go away.
    – Vaccano
    Commented Apr 12, 2017 at 20:17

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.