Sysadmins of the North
Share now!





File cabinet image

Manually fail-over all databases in an SQL Server Database Mirroring configuration

How to manually fail-over an SQL Server database mirroring session?

How to manual fail-over 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 fail-over.

Performing such a fail-over, 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 fail-over the databases on your principal SQL Server to the slave before you reboot the server.

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 (zero)!

This may interest you:   Windows Update error 0xc1420127 and 0xc190010b when upgrading Windows 8.1 to Windows 10

If everything looks OK you can execute the following T-SQL query to initiate the fail-over, and to manually fail over database mirrroring.

--
-- Perform a fail-over 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! 🙂


buy me a coffee
Buy Me A Coffee

About the Author Jan Reilink

My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, WordPress, websites & optimization. Want to support me and donate? Use this link: https://paypal.me/jreilink.

follow me on:


Thank you!

Leave a Comment:

2 comments
Add Your Reply
Skip to content