Database mirroring on a clustered Sql Server instance

Mirroring is a high availability feature introduced in Sql Server 2005. Combining it with Sql Server cluster, it can buy an insurance policy for your high availability needs, if you will, in that cluster provides one layer of protection, and mirroring provides another layer.

You can create a clustered Sql Server instance, which provides protection against a server failure (motherboard, CPU, memory, etc.). Sql Server cluster in and of itself will not protect you against storage media failure. A lot of companies use SAN for their storage needs. Therefore it is possible for them to use replication technology at the hardware level (EMC’s SRDF seemed to be the most talked about) to protect the database files.

With mirroring, you can use a mirrored database on a different box to protect against storage media failure. Suppose you have a mission critical database, you can put it on a cluster. Furthermore, you can mirror that database to a different server, which can provide automatic failover.

Now suppose the principal is running on the cluster, and the mirror is running on a separate box. What happens when there is a failover from Node A to Node B on the cluster? Will the mirror take over right away, assuming automatic failover is setup? If it does, what happens when the cluster comes on line on Node B. To find that out, I conducted my experiment. Here is what I got:

1. 3 virtual machines were created on one physical box. Let’s call them Dc for Domain Controller, Node A, and Node B;
2. I set up one Sql Server cluster instance with Node A and Node B. In addition, I created one Sql Server instance on the Dc, one named instance on Node A. All instances were RTM instances, no service packs or hot fixes, because installing service packs broke things on my virtual machines. I turned on mirroring using trace flag 1400;
3. On Sql Server cluster, one database, let us call it LoveMeDo, was created. LoveMeDo was then restored on Dc. High safety automatic fail over mirroring was set up, with LoveMeDo on the cluster as the principal, the one on Dc as the mirror, and the named instance on Node A as witness. I verified that the mirroring worked properly by inserting some data into the principal, then created a database snapshot on the mirror, and saw the data inserted into the principal was in the snapshot;

Here is what I found:

1. I initiated database failover, and verified that LoveMeDo can fail over back and forth between the Sql Server cluster instance and Dc instance;

2. I initiated a failover using Cluster Administrator, now Sql Server cluster instance is running on Node B now. Dc LoveMeDo took over and became the principal. Initially it had the status of “Principal, Disconnected”. The database on the cluster, which is running on Node B at this point, became the Mirror. Shortly after, things are in sync;

3. I was able to fail the database back to the virtual instance on Node B.

I repeated the above steps a few more times, overall, I thought it worked pretty well.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.