SQL Server Mirroring Failover / Failback Automation

 

SQL Server Mirroring Failover / Failback Automation:

 

1)      Failover/Failback Script for all databases From Reston to Chicago (or Vice Versa) DB Servers:

The below script will failover all the databases to Passive/ Mirroring Node. This will help to minimize the task of right click and perform manual failover for each database

This can only be initiated from the Principal/Active 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 Mirroring Server before you reboot the server.

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

 

2)      Step to check Active/ Principal DB in SQL Server Job:

We can add “Step1” to check Active/ Principal DB for SQL jobs on DB Servers which will check whether the DB is online/Principal every time job runs. This will only allow jobs to run Step2 (which is application job step) only on Active/Principal Server.

This step will reduce the time to Disable/Enable Jobs every time during Failover / Failback Activity.

Step Need to added in Jobs:

IF EXISTS(

SELECT  1  FROM sys.databases

WHERE state_desc = 'ONLINE'

AND collation_name IS NOT NULL

AND name = 'MirrorDBName')

BEGIN

PRINT 'Principal DB Good to Go'

END

Else

--PRINT 'EXITING GRACEFULLY';

THROW 51000, 'This is Mirror DB', 1;