Hey,
This is something I’ve been wanting to blog about for a while, because I’m a big fan of database mirroring, however in a lot of scenarios (And I have been guilty of this in the past too), it’s almost a false safety net. Yes, your data is on a separate server in case your primary blows up, but in the event of a disaster, will your back up server actually work? Without checking it, the correct answer is always going to be “Who the hell knows?”
So in light of that, I wanted to do a post to raise awareness of the need to actually check if your disaster recovery solution will actually help you recover from a disaster. Mirroring is obviously only one part of a massive subject, but I’ve outlined two scenarios (and provided two scripts) I would recommend that anyone who uses mirroring, or intends to, carries out some tests on.
***Major Super Not Secret At All Important Tidbit*** – Do these in a test environment first!!!
Scenario 1 – I don’t really trust you any more… – The controlled failover
Take it as your primary server is running, but is experiencing some funky behaviour which you can’t root cause straight away. While you are root causing, you may need to do some reboots, flick some switches, or re-install some operating systems. The fate of your mirror is in your hands, and you feel safer running your active database on your secondary server. Enter, the controlled failover. Take the decision to control the switch over to your secondary server, rather than having to do it in the middle of the night.
The Script – Run on the currently active (Primary) server
USE master
GO
ALTER DATABASE <databasename> set partner failover
In the absence of an error message, you should see your primary database go from
<Databasename> (Principal,Synchronized)
to
<Databasename (Mirror,Synchronized / Restoring)
This is good! This means the servers have correctly switched roles. If you check on your secondary server, it should now be the Principal. Make sure you can access data on that database, and if you have an application which uses it, make sure it can log in and do some operations correctly.
This does not break mirroring, it just switches roles, so failing back is a viable option if you just want to do a quick test
Scenario 2 – Hello? Yes officer, I know that building on fire… – The Primary done died failover
It’s a worst case scenario, your Primary server is dead, resuscitation has failed, next of kin have been notified etc.
You now need to bring your Secondary server up, but can’t run the first script as it will only work from the Primary. Never fear!
The Script – Run on the current mirror (Secondary) server
***Important Note***
This method carries a little risk. If a transaction had occurred on the Primary server, but had not yet applied to the secondary server, running this script will make that transaction very difficult to recover. It’s not impossible, but recovering it would be a book in itself. So if you’re not comfortable doing it (And I would balk at it myself), you have to decide if it’s worth losing that transaction to get your systems up and running again.
***End Note***
ALTER DATABASE <databasename> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
This will cause your secondary server to force itself active and get you back up and running. In the event of your primary server making a recovery at some point, it will automatically set itself to the secondary and suspend mirroring (this is to allow you to recover whatever transactions which may have been lost). If you don’t want to recover them, just resume mirroring and they will synch back up.
To test this in a lab, just shut down the SQL instance on server 1, run the script on server 2, and bring server 1 back up.
I hope this helps people feel a bit more comfortable with doing a failover and takes a little bit of the unknown out of it. Or at least gets you to the point where you’re happy to try it out in a test scenario, as you really don’t want the first time you have to try this to be in anger.
As usual, any questions, just holler! Hope everyone is having a good summer so far. (And if you live in Ireland that you haven’t been swept away in any floods yet).
Stephen