Setting database mirroring and minimizing downtime
How are you doing?
The topic of today is how to setup mirroring by minimizing downtime. Well it is not that difficult but it requires some tips to achieve this.
First, we need to make sure we have all the prerequisites set up.
0. secondary server identical if possible
Set the secondary server identical (same edition, same version, same patches, same drive letters, etc... This is the true high availability you need. Otherwise the high availability setup will eventually fail due to the lack of ressources on the secondary server. But this is the client's call! and the client's wallet.
1. Get all the logins (with their SID) copied on the secondary server. This is something that is usually forgot by clients who want to do the thing alone. Once they failover, they realize that their logins do not connect. Ouch!
This link will help you transfer the logins easily: http://support.microsoft.com/kb/246133/
2. Get all the other server objects you feel usefull for the immediate failover:
like setting SQL server jobs and some DTSX packages that need to be turned on quickly after the failover either by an automatic job that would check whether the database is online or by activating by hand after the failover.
3. The database you wish to mirror needs to be in full recovery mode.
4. Set up a test database and do the mirroring for it, whether you will use active directory or certificates/master key.
This is obviously an important step. You need to make sure that the network setup is working and you need to perform manual failover on this test database to make sure all is in good shape. Ideally, try to set up the witness on it and let it go for a day or so to check networkk disruption and tune the timeout accordingly. Please comment if you need more info. If you have an application on .Net, Coldfusion, java etc... set up the connection string with a failoverpartner, do a stupid query on it for a page and look at the page while performing manual failover to see if the failover partner is working at the application tier.
Once all the prerequisites are set up and basic mirroring tests are done.
perform a full backup of your primary database (no need of course to stop services) and restore it on the secondary server. Obviously a shared path need to be available.
Then perform a log backup by scripting it. Perform a copy by scripting it. Restore the log backup. Ideally do it on the same batch.
To do so there are many ways:
1. use Sqlcmd to access remotely the server.
2. use xp_cmdshell (need to activate it with sp_configure) to be able to transfer file
If you do not succeed with SqlCmd, you can always set up a linked server with the credentials you like to perform the log restore.
Concerning the copy, if you have troubling getting the copy working because of security, you can set up a proxy account and run the cmd via a sql server job in your script (sp_startjob). If you need more details, let me know.
Once you have perform the log backup, the copy and the restore once via the same batch, you actually minimize the time you will need to stop the activity on the principal server. Yes, indeed, you need absolute synchronization.
Within your batch, add your script concerning the mirroring set up.
prepare script: the "alter database set partner = 'TCP://principal.domain.com:Port' " on the secondary server via linked server or sqlcmd. You can test it on your test database.
Then script the same for the principal server:
"alter database set partner = 'TCP://secondary.domain.com:Port' "
"alter database set witness= 'TCP://witness.domain.com:Port' "
Third, the time to synchronize
Choose the right time, like not much activity to do your mirroring.
You can actually try to run it directly without even stopping the activity. Technically you can still read. There will be no effect in reading the principal server by your application logins. It can works. I had databases for client that I mirroried without absolutely any downtime.
If activity still persists, you will need to kill them! and apply the "Alter database set single_user with rollback immediate" script. Obviously, you cannot forget to activate multi_user back after your mirroring is set up.
If you follow specific testing protocols and run thorough testings. There is absolutely no way you can miss or fail your mirroring.
I would be happy to assist you on your mirroring needs if needed. I have performed hundreds of them already on Sql server 2005 and 2008.
Any comments are welcome!
- Inspiration (1)
- Security (2)
- SQL Architecture (21)
- SQL Monitoring (1)
- SQL Performance (16)
- Tips & tricks (1)
- Uncategorized (5)
- Utilities (1)
Leave a Reply