Automate SQL Server Database Mirroring Failover for Multiple Databases With No Witness
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.
--This script does a failover of all the databases in a database mirroring session --to the Mirror server. This will be used in cases where the PRINCIPAL server is no longer available --and the mirrored databases have to be brought online. --NOTE: Run this script in the MIRRORED server instance SET NOCOUNT OFF DECLARE @strSQL NVARCHAR(200) --variable for dynamic SQL statement - variable size should change depending on the DECLARE @strDatabasename NVARCHAR(50) --variable for destination directory DECLARE MyCursor CURSOR FOR --used for cursor allocation SELECT name FROM master.sys.databases a INNER JOIN master.sys.database_mirroring b ON a.database_id=b.database_id WHERE NOT mirroring_guid IS NULL AND mirroring_role_desc='MIRROR' OPEN MyCursor FETCH Next FROM MyCursor INTO @strDatabasename WHILE @@Fetch_Status = 0 BEGIN ---Run the ALTER DATABASE databaseName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS SET @strSQL = 'ALTER DATABASE ' + @strDatabaseName + ' SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS' EXEC sp_executesql @strSQL PRINT 'Bringing ' + @strDatabaseName + ' ONLINE' PRINT '========================================' FETCH Next FROM MyCursor INTO @strDatabasename END CLOSE MyCursor DEALLOCATE MyCursor
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
About the author
View all my tips