Database Mirroring on SQL Server 2008

I know it has been a very long time since I posted, and for that I am sorry. Sometimes work and personal life conspire to evaporate any free time you might have. But here I am with a new and hopefully useful bit of hard won experience to share with you.

I was recently engaged at a client to assist them with implementing database mirroring on SQL Server 2008. Everything seemed straight forward enough, they were conducting a proof of concept (POC) around using SQL Server to host their new ERP system and needed to prove the high availability features of automatic failover. No problem so far. They had a primary and secondary server and a virtual server to act as the witness already in place.

 

The wrench got thrown in at our kick-off meeting when the client informed me that since they are already implementing SharePoint 2007, they want the POC to use SharePoint as the application that will utilize the mirrored database set up. I’m not sure how many of you know this, but in order for an application to be natively ready to utilize a SQL Server mirrored environment and facilitate automatic fail-over, it must be capable of implementing the FAILOVER PARTNER attribute of a mirroring aware data source. SharePoint does not. There are many posts around the net on DB Mirroring, and SharePoint on mirrors, but I failed to find a comprehensive source that provided all the information I needed. Hence, my contribution to the community via this post. Actually this and the next post. I plan to break it up into a pair of posts, one that deals with the setting up of the mirroring, and one that deals specifically with the steps necessary to implement SharePoint on the mirroring session and provide automatic fail-over.

So in this post we will go through the process of creating a mirroring session and implementing mirroring for a database. We’ll use the AdventureWorks2008 sample database that Microsoft was so kind to create for us.

Any time you embark on configuring something in SQL Server, it is a good idea to be sure all the prerequisites are met ahead of time. There is a good source for prerequisites on SQL Server database mirroring at Microsoft TechNet. Here is the link Prerequisites, Restrictions, and Recommendations for Database Mirroring

Some things to note: Make sure both partners are using the same version and EDITION of SQL Server 2008. If you are running Enterprise on the primary, you should run Enterprise on the secondary. Both servers should also utilize the same default collation. You can not implement mirroring on database servers that have implemented the FILESTREAM option. Use of a third server, which can be a different Edition of SQL Server 2008, even EXPRESS, is required to fill the role of witness if you are implementing high availability mode (high safety with automatic fail-over).

So let’s begin. The first step is to ensure we have 3 server instances available to us. In a production application, these will be 3 separate servers, even if any or all are virtual. If you are doing this on your own, you can install a named instance of SQL Server along with your default instance and install SQL Server Express to act as the witness.

 

Once you have established your servers, you will need to ensure you have met all the requirements and pre-requirements as laid out in the above link. When you are ready to begin, follow the steps below.

Step 1:
Set Recovery Model to FULL.

 

Step 2:
Backup the database.

Step 3:
Copy the backup to the designated mirror instance.
Step 4:
Restore the backup on the designated mirror instance with the options selected as below.

 

You must choose the “Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored (RESTORE WITH NORECOVERY)” option for database mirroring.

Step 5:
On the designated principal instance, right click the database and select “Tasks” and then “Mirror”. This will launch the Database Mirroring Wizard.

 

Click “Next” and move through the wizard, selecting the Principal or primary server, the secondary or Mirror server, and the Witness server. In order to achieve automatic fail over a witness server is required.

There will be an identical screen on which to select the Witness server. The wizard creates and configures the endpoints for you.

These accounts are used for communication between the three servers.

 

Final check before finishing.

 

This screen shows the configuration progress for each server. Any warnings or errors will be caught and displayed here. When this screen finishes, you will be prompted to start mirroring.

 

Once the process concludes and the databases have synchronized, the mirroring session is started and all transactions made at the principal will be duplicated on the mirror. In the event of a failure of the primary/principal the Witness and the Mirror form a quorum and determine to switch control to the mirror, which exits the restoring state and becomes a disconnected principal. When the mirroring session is started the Mirror page of the database properties is as shown below.

 

You will see in the database object tree view in SQL Server Management Studio, that one database shows Principal, synchronized, and one shows Mirror, synchronized.

That’s it. You have set up mirroring on your database. Now when a problem occurs with the principal, the mirror will take over operation as the principal database. Any mirroring aware applications can implement the FAILOVER PARTNER attribute of the connection string to ensure continuous operation through a failed database server.

You can manual fail over the database from primary server to the secondary server in two ways.

One: Open the properties for the database and select the Mirror page. There is a button “Failover” that if you press, will manually fail over the database from the primary to the secondary.
Two: You can issue a T-SQL Query to fail over the database. On the primary server with the principal database execute the following code;
ALTER DATABASE AdventureWorksDW SET PARTNER FAILOVER

That’s all there is too setting it up. Of course there is a ton more information on Microsoft MSDN and TechNet as well as in BOL. Look it up, there’s much, much more to learn about database mirroring.

In my next post we will continue from here and implement automatic failover for a SharePoint installation. Until thin, keep slinging the SQL.