Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Mirroring Status Check and Manual Failover T-SQL Scripts


By:   |   Last Updated: 2019-01-17   |   Comments   |   Related Tips: More > 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.

status of databases using database mirroring

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.

alter database

Output from Mirror Instance

The result set shows the status of the databases under database mirroring.

status of databases using 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.

high safety databases

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.

status of databases using database mirroring
messages

Output from Mirror Instance

The same situation as with the Principal instance.

status of databases using database mirroring
messages

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.

status of databases using database mirroring

Now if we execute the SP with @dbStatusOnly = 1 on the other instance, you can see that it is now acting as the Principal.

status of databases using database mirroring

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 '[email protected]+' SET SAFETY FULL;'+CHAR(13);
         SET @operatingModeTSQL += 'ALTER DATABASE '[email protected]+' 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 '[email protected]+' SET PARTNER FAILOVER;';
      SET @failoverTSQL += CHAR(13)+'ALTER DATABASE '[email protected]+' 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!


Last Updated: 2019-01-17


next webcast button


next tip button



About the author
MSSQLTips author Alejandro Cobar Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools