You are here: Saotn.org » Windows Server » Manually failover all databases in an SQL Server Database Mirroring configuration

Manually failover all databases in an SQL Server Database Mirroring configuration

How to manually failover an SQL Server database mirroring session?

How to manual failover mirrored SQL Server databases using Transact-SQL (T-SQL) in SQL Server 2017+. This can only be initiated from the principal server, and the mirrored database must be synchronized (that is, when the database is in the SYNCHRONIZED state). In this article I’ll show you how to force a failover.

Performing such a failover, using T-SQL, comes in handy when you are installing Windows Updates and SQL Server Cumulative Updates, and you need to reboot the database server. It is recommended to failover the databases on your principal SQL Server to the slave before you reboot the server.

Query that shows the current state of each database in the mirroring set-up

The following T-SQL query shows the current state of each database in the mirroring configuration.

-- ============================================================== -- query that shows the current state of each database in the mirroring set-up -- ============================================================== SELECT db_name(sd.[database_id]) AS [Database Name] ,sd.mirroring_state AS [Mirror State] ,sd.mirroring_state_desc AS [Mirror State Desc] -- 1 = Principal, 2 = Mirror ,sd.mirroring_role AS [Mirror Role] ,sd.mirroring_role_desc AS [Mirror Role Desc] ,sd.mirroring_partner_name AS [Partner Name] ,sd.mirroring_safety_level_desc AS [Safety Level] ,sd.mirroring_witness_name AS [Witness] ,sd.mirroring_connection_timeout AS [Timeout(sec)] FROM sys.database_mirroring AS sd WHERE mirroring_guid IS NOT null ORDER BY [Database Name];

When executed against the principal, the Partner Name column identifies the partner, or mirror, server. The Mirror State must not be null or zero.

Everything looks OK?

Perform a failover for all SQL Server databases in a mirroring set-up

You can execute the following T-SQL query to initiate the failover, and to manually fail over database mirrroring.

-- -- Perform a failover for all databases in a mirroring set-up -- declare @databasename nvarchar(255) declare @alldatabases cursor -- Only select principal databases (mirroring_role). set @alldatabases = cursor for select d.name from sys.databases d, sys.database_mirroring m where m.database_id = d.database_id and m.mirroring_role_desc = 'PRINCIPAL' -- Execute the failover. open @alldatabases fetch next from @alldatabases into @databasename while @@FETCH_STATUS = 0 begin print @databasename exec('alter database [' + @databasename + '] set partner failover') fetch next from @alldatabases into @databasename end close @alldatabases deallocate @alldatabases

Once executed and ready, your former principal server is now the partner SQL Server, and can be safely rebooted.

Great heh! :)

Protip: here are some great ASP and ASP.NET connection strings to Microsoft SQL Server databases for you. As a bonus, MySQL connection string examples are provided as well! :)

Check mirroring failover progress

You can use the following T-SQL to check the progress of your failover:

SELECT COUNT(d.name) FROM sys.databases d, sys.database_mirroring m WHERE m.database_id = d.database_id AND m.mirroring_role_desc = 'PRINCIPAL'

This query must be executed against the current PRINCIPAL SQL Server, not the one you are failing over to.

Check SQL Server mirroring state

Use the this T-SQL to check the current mirroring state of all databases (except ‘model’, ‘master’, ‘tempdb’, ‘distribution’, ‘msdb’):

SELECT d.name, m.mirroring_state FROM sys.databases d, sys.database_mirroring m WHERE d.name NOT IN ('model', 'master', 'tempdb', 'distribution', 'msdb') AND m.mirroring_state IS NOT NULL

You should see for mirroring_state = 4 for all databases on your principal server, as that’s SYNCHRONIZED.

mirroring_stateState of the mirror database and of the database mirroring session
0Suspended
1Disconnected from the other partner
2Synchronizing
3Pending Failover
4Synchronized
5The partners are not synchronized. Failover is not possible now.
6The partners are synchronized. Failover is potentially possible. For information about the requirements for failover see, Database Mirroring Operating Modes.
NULLDatabase is inaccessible or is not mirrored.

2 thoughts on “Manually failover all databases in an SQL Server Database Mirroring configuration”

  1. set @alldatabases = cursor for
    select d.name
    from sys.databases d, sys.database_mirroring m
    where m.database_id = d.database_id
    and m.mirroring_role_desc = ‘PRINCIPAL’

    For this script, i’m getting error “Must declare the scalar variable “@alldatabases”.”

    1. JanR. – Groningen, the Netherlands – My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a systems administrator, doing my daily thing at Vevida.com in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization.

      Hi Aditya,
      Do you have the following two lines too:

      declare @databasename nvarchar(255)
      declare @alldatabases cursor

Leave a Reply

Your email address will not be published. Required fields are marked *