There’s a kind of confusion when discussing the roles of SQL Server databases and instances in high availability scenarios. In particular, with database mirroring, does the term ‘principal’ apply to the server, or the database, or both? The same question can be asked of the mirroring partner: is it the server or the database? In this article, I’ll clarify the meanings of the terms, hopefully showing that the best way to look at it is that we really have principal and mirrored databases, and the server can have dual roles.

Background

A fellow SQL Server DBA asked me about an issue related to database mirroring versus clustering. Is it possible to group databases in database mirroring, so that they all fail over together? It is often the case that a SQL Server will have multiple databases, and a given application may need to access more than one of them.

However, the way both SQL Server 2008 and SQL Server 2005 support database mirroring, the answer is no: you cannot group databases and have them fail over together. A database failover may occur automatically (with a witness instance acting as a tie-breaking vote) or manually, but it only occurs one database at a time. If the principal server remains intact, and only a single mirrored database becomes unavailable on the principal server, then just the one database will fail over in the automatic failover scenario.

The bottom line is that failover occurs at the single database level. There is no built-in support for grouping mirrored databases so that they will fail over together, either automatically or manually.

Well, suppose you are mirroring multiple databases from one server to another and you manually fail over only one of the databases? Then as long as the initial principal server and its other databases are still available, the mirroring will now occur in the opposite direction.

The question is: which is the principal server, and which is the mirror server? The answer is both are both! Both servers have both roles, whereas the databases involved never have more than one role. Let’s explore this a little more closely.

Single-Database Mirroring

When you set up database mirroring with or without automatic failover, it’s customary to refer to the principal server, the principal database, the mirror server, and the mirrored database, assigning the role of ‘principal’ and ‘mirror’ to both the server and the database. (If you add a witness, that role belongs to a SQL Server instance only, and there is no user database involved.) Figure 1 shows this type of configuration:

Figure 1. Mirroring a single database does not distinguish the server and database role.

In this diagram, the roles are clear at both levels: at the SQL Server instance level, and at the database level. (I’ve left out the witness server here because it plays no active role in the mirroring process, and it never changes its role.)

Now if a failover occurs with both servers intact, each SQL Server instance and its database change roles. The principal server becomes a mirror, and the principal database becomes a mirrored database, as shown in Figure 2:

Figure 2. When a single mirrored database fails over, both the server and the database reverse roles.

The roles of principal and mirror are clear at both the server and database level. Now let’s change the conditions and add a second database.

Multiple-Database Mirroring

Suppose you mirror more than one database from one given SQL Server instance? Now it can get a little trickier. Let’s assume you are mirroring two databases. Figure 3 shows the initial picture:

Figure 3. Mirroring two databases starts out with the servers and databases having the same roles.

If the principal server, i.e., the entire SQL Server Instance A, becomes unavailable, the two mirrored databases will come online and the former Mirror Server Instance B will become the new Principal Server. There is no role confusion in the case of an entire SQL Server failure.

Now let’s just fail over one database, and assume that both SQL Server instances are available. You’ll end up with the situation shown in Figure 4:

Figure 4. Failing over just one database gives mixed roles to the server instances.

SQL Server Instance A now has a dual role: it is a principal server for Database 2, and a mirror server for Database 1. The reverse is true for SQL Server Instance B, which is also now both a principal and a mirror server.

Database Roles Are Fixed, Server Roles can Be Dual

The conclusion you can draw is that for database mirroring, the database roles are fixed, but the server or SQL Server instance roles may be dual. A database is either a principal or mirrored, but not both. However, a SQL Server instance can be a principal server for one set of databases, and a mirror for another set.

Note that a parallel lesson can also apply to log shipping and replication, though each of those other technologies has some special considerations, and they do not offer automatic failover. A log shipping primary server could log ship a set of databases from one instance to another, while being a secondary server for a different set.

Therefore when discussing a database mirroring solution where more than one database is mirrored, it is possible that a given server can be both a principal and a mirror.