Problem
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?
Solution
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.
Next Steps
- Review your disaster recovery process and include this automation procedure as part of your plan
- Take a look at other database mirroring tips
Edwin M. Sarmiento is the Managing Director of 15C, a consulting and training company that specializes on designing, implementing and supporting SQL Server infrastructures. He is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures running on the Microsoft server technology stack ranging from Active Directory to SharePoint and anything in between. He is very passionate about technology but has interests in music, professional and organizational development, and leadership and management matters when not working with databases. Edwin lives up to his primary mission statement: “To help people and organizations grow and develop their full potential.”
- MSSQLTips Awards: Champion Award (100+ tips) – 2017 | Author of the Year Contender – 2020, 2017