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;
