The Barking Seal

MS SQL Mirroring for High Availability

16Apr
Author: admin Categories: Infrastructure Comments: 1
Recently I needed to setup MS SQL mirroring. As usual there are plenty of guides attempting to describe the process, but inevitably none of them really cut it when you actually start implementing. I’ll try to clear up the process and hopefully provide some help to anyone out there wanting to implement MS SQL mirroring.

Why?

Do you have a database or databases? What do you do if they go down? Probably restore from your latest backup (which may or may not be very recent and may take hours to restore). Let’s say you have a scheduled backup that runs every night at 11pm; what if your database/server fails at 10:59pm? What now? SQL mirroring will keep an updated copy of your databases on another server that can easily be “turned on” in case your primary server/databases fail.

How?

The primary server is called the “principal” and the secondary server is called the “mirror.” The principal is your live database, and the mirror is your standby copy of the database. In addition to the principal and the mirror, you can introduce another optional server called the “witness.” The witness is a third instance of SQL Server that determines when to failover. This option is only used when you want to do an automatic failover. We chose to implement manual failover to avoid a situation of an unwanted failover.

Mirroring creates an endpoint at each server that connects the servers and allows the principal to keep the mirrored database updated. The method of mirroring can be set to high performance (asynchronous-changes are committed to the principal database then transferred to the mirrored database) or high protection (synchronous-changes are committed to both the principal and the mirror) depending on your needs. During a failover, the mirror databases become the principal databases and are “activated” so applications can immediately start accessing them. Once the failed server/database comes back online, it is now the mirror until the next failover or until you manually reverse the roles by performing another failover.

Requirements:
- Two instances of SQL Server 2005 sp1 or higher (serve as the principal and mirror).
- A third instance of any version of SQL Server (serves as the witness if using automatic failover).
- Databases to be mirrored must be using the “Full” recovery model.
Manual Failover

Automatic Failover

Implementation:

To implement mirroring, as ususal, you can use T-SQL statements or the SQL Studio Management Suite. I chose the GUI method so I could include screenshots instead of boring SQL statements. Note, our client was using SQL 2005, but the process should be exactly the same for SQL 2008.

Step 1: Make sure the principal database you are mirroring is set to use the “Full” Recovery Model.
- Expand the Databases folder and Right-Click and select Properties then Options for the principal database you want to mirror.
Step 2: Run a Full backup of the principal database you are going to mirror.
- Right-Click the principal database, select Tasks and then Backup.
Step 3: Restore the backup to the mirror instance.
- On the mirror instance, Right-click the Databases folder and select Restore Database.
- When the Restore Database dialog box appears, select the backup you just created.
- Make sure you select the “No Recovery” option under the Options tab.
Step 4: Setup mirroring from the principal.
- Back on the principal instance, Right-Click the principal database, select Tasks then Mirror.
- Now click the Configure Security button to start the Mirroring Wizard.
*Note: SQL 2005 Standard does not allow asynchronous mode
- Now you will select the principal, mirror and witness (if needed) instances.
- You can use the default port or select your own.
- Now click finish and if you are ready to start mirroring, click the Start Mirroring button.
Step 5: Testing
- To perform a quick test, you can Right-Click the principal database, select Task, then click Mirror.
- Make sure in the Mirror Status box that it says the databases are fully synchronized.
- The Mirror Properties box pops up and there is a Failover button that you can click to perform a manual failover.
- This causes the mirrored database to become the principal and the principal to become the mirror.
- You can then make a change to the database and perform the failover again, checking to see if the changes made are reflected in the mirror database.
Conclusion

So, that pretty much sums up configuring a simple SQL mirror. Obviously, each environment will come with it’s own set of challenges, but hopefully this post will provide help and/or direction to someone out there. Keep in mind when performing a real test for a production database, you will need to account for any applications that access the database. Depending on your applications, this will usually involve editing the connection string and/or creating an alias within DNS so in the event of a failover the application knows how to access the mirror server.

Thanks for taking time to read this, and feel free to post any questions you may have.
Tags: high availability, redundancy, sql