In a previous tip on Database Mirroring Automating Failover for Multiple SQL Server Databases, you've seen how you can failover all of the databases in a mirroring session if one database fails over. However, what happens if you don't have a witness server and your principal server goes, how do you make your mirror server the principal and bring all your databases online?
This solution extends the tip by reading the sys.databases and the sys.database_mirroring catalog views from the mirrored server and automatically (or manually, depending on your requirements) failover databases that are in a database mirroring pair. One case would be when you have a database mirroring pair in a high-protection operating mode without a witness requiring you to do a manual failover, whether planned or unplanned. You can use this script in the Failover Databases job as described in this tip or simply run it on the mirror database in case you decide to do a manual failover instead of an automated one.
This script uses the FORCE_SERVICE_ALLOW_DATA_LOSS option as in a real disaster, the primary database will be inaccessible. The process will recover the mirror database by finishing all completed transactions and rolling back unfinished ones. Since the old primary is inaccessible, some data may be lost because the synchronous data transfer may not have completed between the time the primary database went down and the transactions got committed on the mirror database.
Once the databases are online, you can now redirect your applications to the new principal server.
- Review your disaster recovery process and include this automation procedure as part of your plan
- Take a look at other database mirroring tips
Last Update: 3/5/2009
About the author
View all my tips