Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: SET PARTNER FAILOVER

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.

Tags: , , , , , ,

%d bloggers like this: