Database mirroring and hardware upgrades

One of the new features that arrived with SQL Server 2005 SP1 was Database Mirroring. This protects your database against physical disaster (but possibly not logical disaster) and is great for peace of mind. You don’t need to buy another license for the ‘mirror’ server and you can use SQL Server 2005 Express on your ‘witness’ box.

A huge upside of using mirroring is how easy it is when you need to do maintenance on your db server, or you have to upgrade the hardware or swap out the server entirely.

Lets consider a server swap out. You’ve bought a new server, nice dual proc quad core machine with a ton of RAM that you think will keep you going for a bit. It has SQL Server 2005 installed, with the latest service packs and hotfixes applied. The full backup of your old database (which is still in production) has been restored onto it, and you’ve just finished replaying all the available transaction logs to bring it almost up to sync with the production database. Now you’re pretty much ready to send it live.

Just stop the current mirroring session, and run through the mirroring wizard to reconfigure the setup so you have your new server as the ‘mirror’ server. A minute later, you hit the ‘start mirroring’ button on the databases you’ve configured for mirroring, and the databases sync up. Once they are synchronised (pretty quick unless they are *huge* databases) you just manually fail over the mirroring so that the new server becomes the new principal server.

Now you have your brand new server running your database in production, with no data loss and only seconds of downtime whilst the databases failed over. The old principal server is now your mirror – do what you want with it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: