By: Alejandro Cobar | Comments | Related: 1 | 2 | 3 | 4 | 5 | > Database Mirroring
Problem
SQL Server Database Mirroring is a technology used for disaster recovery scenarios and is used by many DBAs. Even though Microsoft states that it is a deprecated feature, it is still hanging around in SQL Server 2019. If you currently support a wide variety of SQL Server instances with database mirroring configured, then hopefully this tip can be useful to you. This tip assumes that you don't have a witness server and that any mirroring related activity requires your direct intervention.
The stored procedure that I will present in this tip can help you either script out the actions or directly execute the failover of the databases you want to failover, which might come in handy in scenarios where you have many databases with mirroring and are being required to fail them over to the DR server (for whatever reason).
Solution
Ideally, the stored procedure must be created on the Principal and Mirror instances. Depending on from where you execute it, it will return different output for each case, which I will be covering shortly.
The stored procedure can receive a two parameters (@printOnly and @dbStatusOnly).
Let's walk through the ways to execute the stored procedure with examples.
EXEC database_mirroring_failover
Since the default values for the parameters are @printOnly = 1 and @dbStatusOnly = 0, then calling the stored procedure with no parameters is exactly the same as calling it as:
EXEC database_mirroring_failover @printOnly = 1, @dbStatusOnly = 0
Output from Principal Instance
The result set shows the status of the databases under database mirroring and will always be shown in the "results" tab.
In the "Messages" tab you will find the script to proceed with the failover of the databases.
In this particular case, since both of them are in "HIGH PERFORMANCE" mode, the script will include the T-SQL to change the databases to "HIGH SAFETY", prior to the failover step.
Output from Mirror Instance
The result set shows the status of the databases under database mirroring.
If you run this on the Mirror instance and the databases are acting as the mirror, then you will only get informative output in the "Messages" tab, instead of actionable T-SQL commands to be applied. Here is the output.
EXEC database_mirroring_failover @dbStatusOnly = 1
Output from Principal Instance
Nothing too crazy for this mode, you will simply get the status of the databases and absolutely nothing in the "Messages" tab.
Output from Mirror Instance
The same situation as with the Principal instance.
EXEC database_mirroring_failover @printOnly = 0
This is the command to actually perform the failover of all the databases instead of just obtaining the T-SQL code.
The code contains the logic to move forward with the failover only if all the databases are in a synchronized state after changing them all to "HIGH SAFETY".
Output from Principal Instance
As you can see in the examples above, instance MC0Z5A9C\TEST1 was the Principal instance, but after executing the failover it became the Mirror.
Now if we execute the SP with @dbStatusOnly = 1 on the other instance, you can see that it is now acting as the Principal.
Here's the code to create the Stored Procedure on each instance. This was created in the master database for demonstration purposes, but you can put in any database you want. It is best to create a utility database and put these types of scripts on all of your servers.
USE [master]
GO
/******
Object: StoredProcedure [dbo].[database_mirroring_failover]
Script Date: 11/29/2018 3:25:18 PM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Alejandro Cobar
-- Create date: 11/29/18
-- Description: SP to generate script for database mirroring failover execution
-- =============================================
CREATE PROCEDURE [dbo].[database_mirroring_failover]
@printOnly INT = 1,
@dbStatusOnly INT = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @operatingModeTSQL NVARCHAR(MAX) = '';
DECLARE @failoverTSQL NVARCHAR(MAX) = '';
DECLARE @messages NVARCHAR(MAX) = '';
DECLARE @highPerformanceDBCount INT = 0;
--Get the number of databases under HIGH PERFORMANCE mode
SET @highPerformanceDBCount = (SELECT COUNT(*) FROM sys.database_mirroring WHERE mirroring_safety_level = 1 AND mirroring_role_desc = 'PRINCIPAL');
--If all the databases are in HIGH SAFETY mode already, then there's no need to do anything
IF @highPerformanceDBCount > 0
BEGIN
SET @messages += '/* These databases must be changed to HIGH SAFETY MODE */'+CHAR(13);
DECLARE @db VARCHAR(128);
DECLARE mirroring_mode_cursor CURSOR FOR SELECT DB_NAME(database_id) FROM sys.database_mirroring WHERE mirroring_safety_level = 1;
OPEN mirroring_mode_cursor
FETCH NEXT FROM mirroring_mode_cursor INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @messages += 'ALTER DATABASE '+@db+' SET SAFETY FULL;'+CHAR(13);
SET @operatingModeTSQL += 'ALTER DATABASE '+@db+' SET SAFETY FULL;'+CHAR(13);
FETCH NEXT FROM mirroring_mode_cursor INTO @db
END
CLOSE mirroring_mode_cursor;
DEALLOCATE mirroring_mode_cursor;
END
ELSE
SET @messages += '/* All databases are either already in HIGH SAFETY mode or nothing can be done from the DR instance */'+CHAR(13);
--Prepare the failover statements for all the databases
DECLARE @primaryDatabases INT = 0;
SET @messages += CHAR(13)+'/* Statements to failover the databases acting as PRIMARY */';
SET @db = '';
DECLARE failover_cursor CURSOR FOR SELECT DB_NAME(database_id) FROM sys.database_mirroring WHERE mirroring_role_desc = 'PRINCIPAL';
OPEN failover_cursor
FETCH NEXT FROM failover_cursor INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @primaryDatabases += 1;
SET @messages += CHAR(13)+'ALTER DATABASE '+@db+' SET PARTNER FAILOVER;';
SET @failoverTSQL += CHAR(13)+'ALTER DATABASE '+@db+' SET PARTNER FAILOVER;';
FETCH NEXT FROM failover_cursor INTO @db
END
CLOSE failover_cursor;
DEALLOCATE failover_cursor;
IF @primaryDatabases = 0
SET @messages += CHAR(13)+'## There are no databases acting as the PRINCIPAL...';
IF @dbStatusOnly = 0
BEGIN
IF @printOnly = 1
PRINT @messages;
ELSE
BEGIN
EXEC sp_executesql @operatingModeTSQL;
--Failover only if the databases are fully SYNCHRONIZED
DECLARE @synchronized INT = 1;
WHILE @synchronized <> 0
BEGIN
SET @synchronized = (SELECT COUNT(*) FROM sys.database_mirroring WHERE mirroring_state <> 4);
WAITFOR DELAY '00:00:01'
END
EXEC sp_executesql @failoverTSQL;
END
END
--Display the status of the databases
SELECT
DB_NAME(database_id) AS 'DB',
mirroring_role_desc AS 'Role',
mirroring_state_desc AS 'State',
CASE mirroring_role_desc
WHEN 'MIRROR' THEN mirroring_partner_instance
WHEN 'PRINCIPAL' THEN SERVERPROPERTY('SERVERNAME')
END AS 'Principal Instance',
CASE mirroring_role_desc
WHEN 'MIRROR' THEN SERVERPROPERTY('SERVERNAME')
WHEN 'PRINCIPAL' THEN mirroring_partner_instance
END AS 'DR Instance',
CASE mirroring_safety_level
WHEN 1 THEN 'HIGH PERFORMANCE'
WHEN 2 THEN 'HIGH SAFETY'
END AS 'Operating Mode'
FROM sys.database_mirroring
WHERE mirroring_state IS NOT NULL;
END
Next Steps
- Remember to be very careful if you are going to execute this stored procedure in a production environment. That's why the default behavior of the stored procedure is to display only the status of the databases and the script with the T-SQL code without moving forward with the failover itself (you definitely don't want to trigger an unwanted failover by mistake).
- Just in case you don't have any kind of monitoring for your servers using database mirroring, I have a tip that you might find useful as well.
- For part 2 I'm planning to present a solution using PowerShell, so stay tuned!
About the author
Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
