Sql Server 2005 Database Mirroring

Note: The RichText blog has moved to www.ricroberts.com

I was recently investigating disaster recovery techniques for Sql Server 2005, and for my needs Database Mirroring looked like a good candidate. (Note that Mirroring is only supported from Sql Server 2005 SP1).

Having looked into log-shipping and replication techniques in the past, this seemed like a good compromise between the two. The main down-point of Mirroring is that, like for log-shipping, the stand-by database must be kept offline. A big advantage that Mirroring has over replication is that changes to the structure of the Principal site are automatically applied to the Mirror.

Database mirroring in Sql Server 2005 lets you keep a "Mirror" of your live Sql Server database up to date in near real-time. In the event of your Main Site's database becoming unavailable, the Mirror can then serve the database clients.

Database Mirroring can be run in various modes. These can include using a "Witness" server to watch proceedings to see if a fail-over is required. You can also choose whether you want to wait until transactions have been applied at both Principal and Mirror sites before committing (High Safety Mode), or whether you want transactions to be applied asynchronously at the Mirror site (High Performance Mode).

For my situation, I went with no Witness server and High Performance mode, as the Mirror database was going to be in a remote location, with a relatively slow network link.

There are various performance counters and statuses associated with Database Mirroing, and various ways of being notified if any of these indicate a problem. For example, you can set up an email or net-send notification to certain people if the oldest unsent transaction is older than a certain age, or if the connection between Principal and Mirror is broken.

After a bit of fiddling most things seemed to work for me in a pretty obvious manner. However, there were a few little hurdles I had to get over.

1. If both machines are on the same domain then the security configuration is quite simple as long as the Sql Server services run under the same domain user account. If the server instances run under different user accounts, user logins on the Principal instance must be manually created on the Mirror (and vice-versa if you want the Mirror and Principal to be able swap roles). The thing that caught me out was if the machine account is used rather than a domain account, it must be added as a user on the other server instance.

2. If both machines are not on the same domain, then you need to use certificates instead. This msdn page tells you how. I'm not sure if this is because of some time-zone issue as I'm in the UK, but if I didn't specify a start date for my certificates then they didn't seem to work until a couple of hours later!

3. This technet article gave me a good starting point for figuring out how to setup alerts on Database Mirroring Events. However, when I was trying to set up an alert based on all state-change events using the WMI query SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE, my alert didn't fire and I instead got a curious error in the Sql Server Agent Log with an error code pertaining to Namespace errors ( 0x8004100E ). I found out later that it works if you specify the database in the query e.g. SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE Database = 'MyDatabase'

As I said earlier, pretty much every thing else worked the way the documentation said it would.

This msdn page links to lots of useful articles. Happy Mirroring!