When setting up an AlwaysOn Availability Group, should the master database be one of the “Availability Databases”?

Question

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?

asked 2017-04-12 by Vaccano

---

Answer

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.

answered 2017-04-12 by Andy Mallon