Friday, March 27, 2009

Who is the fairest of them all?

Database mirroring is a technique that's been supported by MS SQL Server since the first SQL 2005 service pack came out a few years ago, but I haven't had the chance to put it through it's paces until recently. Not everyone requires the sort of data protection that mirroring offers, and not everyone has the means to implement it, but an upcoming SQL 2000 to SQL 2005 production conversion meant that I got the chance to put it properly through it's paces for the first time.

What is mirroring? Simply put, it's a means of maintaining a live and continuously updated copy of a database on a separate server, at a separate location if needs be. In the event of a production server failure, it's possible to have the mirror take over seamlessly so that application users don't even notice that the database server has failed or is inaccessible. This depends on how it's configured, of course, and it's also possible to use it just to maintain a realtime physically separated copy of the database for disaster situations.

This is the theory, anyway, and I was curious about the situation in practice. My client is currently using SQL Server log shipping for an extra layer of data availability, but we're never been too charmed by the technique. It's relatively painful to set up and maintain, expensive (because it requires SQL Server 2000 Enterprise Edition instead of the Standard Edition) and never quite up-to-date as it relies on restoring log backups from the production server. If this server disappears suddenly, all data changes since the last log backup can disappear with it.

So, is mirroring any improvement? The answer is YES, it's a huge improvement, in fact. Setup is straightforward, maintenance is easy, switching between servers is easy, it doesn't interfere with any other processes and, most importantly, it works and appears to be highly robust. I've tried my best to break it this week with no success, so I'm pretty impressed. It's definitely worth considering for your high-availability data solution.

Labels: , ,