TSQL to help with mirroring failover

We ran a mock fail-over from our staging cluster to an offsite mirror server today. Here’s a few queries I used to help me get this done.

I first took a backup of the SQL Agent jobs status on each server so I could easily return the jobs to their previous status on each server. We copy the SQL Agent Jobs from the principal to the mirror. They are created in a disabled state on the mirror.

SELECT'EXEC msdb.dbo.sp_update_job @job_name = ''' + [name] + ''', @enabled = ' + CAST([enabled] AS CHAR(1)) + ';'
FROM msdb.dbo.sysjobs;

Next a script to disable all active jobs on the mirror. WE don;t want these running after failover.

SELECT'EXEC msdb.dbo.sp_update_job @job_name = ''' + [name] + ''', @enabled = 0;'
FROM msdb.dbo.sysjobs
WHERE [enabled] = 1;

Next a job to enable all the jobs we’c copied to the mirror. You run this on your principal server before the failover and run the output on the mirror afterwards.

SELECT'EXEC msdb.dbo.sp_update_job @job_name = ''' + [name] + ''', @enabled = ' + CAST([enabled] AS CHAR(1)) + ';'
FROM msdb.dbo.sysjobs
WHERE [enabled] = 1;

Finally a statement to generate the TSQL to failover all mirrored databases.

SELECT 'ALTER DATABASE [' + d.name + '] SET PARTNER FAILOVER;', dm.*
FROM sys.database_mirroring dm
INNER JOIN sys.databases d
       ON d.database_id = dm.database_id
WHERE dm.mirroring_guid IS NOT NULL;

Leave a Reply