Manually fail-over an SQL Server database mirroring session
How to perform a (forced) manual fail-over by using Transact-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).
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
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 witness and can be safely rebooted.
Great heh! 🙂
Want to say thanks?
If I’ve helped you out and you want to thank me, why not buy me a coffee?
Thank you for your support.