Manually failover all databases in an SQL Server Database Mirroring configuration

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.

ASP and ASP.NET connection string examples for Microsoft SQL Server and MySQL

Query 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 mirroring.

--
-- 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.

Block brute force attacks on SQL Server, block IP addresses in Windows Firewall using PowerShell

Great heh! :)

SQL Server Mirroring connectivity flow diagram

In a SQL Server Mirroring setup, both the Principal (master/primary) and Mirror (secondary) database servers have a connection with each other and a connection with the Witness server.

Principal <-> Witness <-> Mirror ^ ^ -----------><-----------/

When the Principal server loses its connection with the Witness server, or misses a "ping", an automatic failover is initiated and the Mirror becomes the Principal database server for databases. See Database Mirroring (SQL Server) on Microsoft technical documentation for more information.

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.