Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: mirroring_role_desc

Many software systems use multiple databases to support an application. If the system requires all databases to be available on the same server, then issues can arise in a mirrored setup. Problems occur when one or more databases fail over unexpectedly, but others remain as Principal. In this scenario, the required response is for any remaining principals to also failover to the secondary server. We can achieve this response by proactively monitoring the mirroring status of all databases.

To setup the mirroring status check, setup a WMI Alert that queries DATABASE_MIRRORING_STATE_CHANGE.

Add a new alert and select WMI Alert from the Type box. The namespace should automatically be populated with the correct setting. The following example should be pasted into the Query box. It checks the status of three mirrored databases. Change the database names accordingly:-

SELECT *
FROM DATABASE_MIRRORING_STATE_CHANGE
WHERE ((State = 7 or State = 8) AND DatabaseName = 'Database1') OR ((State = 7 or State = 8) AND DatabaseName = 'Database2')
OR ((State = 7 or State = 8) AND DatabaseName = 'Database3')

State 7 indicates a Manual Failover whilst state 8 indicates an Automatic Failover. If any database now fails over then the alert should fire.

Obviously we need to setup an appropriate response for the alert. Create an Agent job containing the following SQL task:-

if exists (select database_id
from sys.database_mirroring
where db_name(database_id) = 'Database1'
and mirroring_role_desc = 'principal')
ALTER DATABASE Database1 SET PARTNER FAILOVER
GO

if exists (select database_id
from sys.database_mirroring
where db_name(database_id) = 'Database2'
and mirroring_role_desc = 'principal')
ALTER DATABASE Database2 SET PARTNER FAILOVER
GO

if exists (select database_id
from sys.database_mirroring
where db_name(database_id) = 'Database3'
and mirroring_role_desc = 'principal')
ALTER DATABASE Database3 SET PARTNER FAILOVER
GO

The above code checks to see whether any mirrored databases are still set as principal and automatically fails them over. Open up the Alert in SQL Agent and select this newly created job to execute if the alert is fired. Now, if one or more of the databases fails over, the remaining databases will also do so.

Advertisements

Tags: , , , , , ,

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 (d.name=N'Your_Mirrored_Database')and mirroring_role_desc <> 'Principal') PRINT 'Mirrored Version of Database. No Action Taken.'
ELSE
BEGIN
    EXEC MyBackupProc
END

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: