Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Mirroring is a high availability solution for individual databases. Implementing mirroring on a business critical database adds many benefits, but also introduces some management dilemmas. Running jobs against the mirrored version of a database generates errors due to the database being in a restoring state. Most solutions focus on enabling and disabling Agent jobs by using job categories. An example solution can be found here.

The issue I have with the aforementioned solution is that it relies on continually calling a job to check the mirroring status of that database. Therefore, wherever possible, I add the following to each job step:-

If Exists (select * from sys.database_mirroring
dm join sys.databases d on (dm.database_id=d.database_id)
where ('Your_Mirrored_Database')and mirroring_role_desc <> 'Principal') PRINT 'Mirrored Version of Database. No Action Taken.'
    EXEC MyBackupProc

The mirroring status (mirroring_role_desc) of the database is tested before calling the applicable stored procedure. If the status is found to not be ‘principal’, then the procedure is not called and a simple text message is printed. With the ‘Backup’ Agent job being present on both servers, only one job will fire the procedure; the other will simply print out the message which can then be viewed within the job history. No more errors!


Tags: , , ,

%d bloggers like this: