You can emulate a SQL cluster using database mirroring by configuring a coordinated failover mechanism that monitors a primary "anchor" database and automatically fails over all mirrored databases together when that anchor changes state. This approach was the go-to solution before SQL Server 2012 introduced Always On Availability Groups, and it's still relevant today for environments running SQL Server 2008 or 2008 R2 that haven't yet migrated to a newer platform.

This article walks through the technical implementation, including the Service Broker setup, WMI event monitoring, and the T-SQL logic required to make coordinated mirroring failover work reliably.

Why Would You Need to Emulate a SQL Cluster with Mirroring?

SQL Server Failover Clustering gives you a single logical instance that fails over as a unit. All databases move together. That's the behaviour most applications expect, especially multi-database applications where referential integrity or application logic spans more than one database.

Database mirroring, by contrast, operates at the individual database level. Each mirrored database fails over independently. If you have five databases and one fails over while the others don't, your application is likely broken. The databases are now split across two servers with no coordination between them.

Before Availability Groups existed, the only way to get coordinated failover from mirroring was to build it yourself. That meant monitoring one database as the "anchor" and triggering failover on all the others the moment the anchor changed state. It's not elegant, but it works, and it kept a lot of pre-2012 environments running reliably.

Prerequisites Before You Start

This solution assumes you've already done the groundwork. Specifically, you need:

  • Database mirroring configured for all databases in the group, operating in high-safety mode (synchronous) with a witness if you want automatic failover
  • SQL Database Mail configured and tested on both the principal and mirror servers
  • SQL Agent operators and Active Directory distribution groups set up for failover notifications
  • Sysadmin access on both servers to create the required objects

Service Broker must also be enabled on the msdb database. This is a hard requirement because the WMI alerting mechanism we're building relies on Service Broker to queue and process event notifications. Check the current state with:

SELECT name, is_broker_enabled
FROM sys.databases
WHERE name = 'msdb';

If is_broker_enabled returns 0, enable it:

ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

How Does the Coordinated Failover Logic Work?

The mechanism works in three stages.

First, a WMI alert fires on the principal server when the anchor database (call it DBX) changes its mirroring role. SQL Server exposes mirroring state changes as WMI events, and SQL Agent can respond to them directly.

Second, the alert triggers a SQL Agent job. That job checks the current mirroring role of DBX and, if it confirms that DBX is no longer the principal, it executes a failover command for every other database in the mirroring group.

Third, Database Mail sends a notification to your operator group so someone knows a failover occurred and can verify the environment is healthy.

Step-by-Step Implementation

Step 1: Enable Service Broker on msdb

Already covered above. Confirm it's enabled before proceeding.

Step 2: Create the WMI Alert for Mirroring State Changes

In SQL Server Agent, create a new alert using WMI event type. The WMI namespace and query to use are:

  • Namespace: \\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER
  • WMI Query:
SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE
WHERE DatabaseName = 'DBX'

This alert fires any time the mirroring state of DBX changes, including when it transitions from principal to mirror during a failover.

Step 3: Create the Coordinated Failover Job

Create a SQL Agent job that the WMI alert will execute. The job should contain the following logic:

-- Check the current mirroring role of the anchor database (DBX)
-- If this server is no longer the principal, fail over all other databases

DECLARE @role INT;

SELECT @role = mirroring_role
FROM sys.database_mirroring
WHERE DB_NAME(database_id) = 'DBX';

-- mirroring_role: 1 = Principal, 2 = Mirror
-- Only proceed if this server has become the mirror (role = 2)

IF @role = 2
BEGIN
    -- Fail over each database in the mirroring group
    ALTER DATABASE DB1 SET PARTNER FAILOVER;
    ALTER DATABASE DB2 SET PARTNER FAILOVER;
    ALTER DATABASE DB3 SET PARTNER FAILOVER;
    -- Add additional databases as required

    -- Send notification
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'YourMailProfile',
        @recipients = 'dba-alerts@yourdomain.com.au',
        @subject = 'SQL Mirroring Coordinated Failover Completed',
        @body = 'A coordinated failover was triggered on this server. DBX changed state and all mirrored databases have been failed over. Please verify the environment.';
END

A few things worth noting about this script. The mirroring_role column in sys.database_mirroring returns 1 for principal and 2 for mirror. Checking for role 2 before executing the failover commands prevents the job from running on the wrong server. Without that guard, you'd risk a failover loop where both servers try to fail over to each other.

The ALTER DATABASE ... SET PARTNER FAILOVER command only works when the executing server is the principal for that database. If DBX has already failed over to the mirror server, the other databases should still be in principal state on this server, so the commands will execute correctly. This is why the anchor database approach works: DBX fails over first (automatically or manually), and then the job on the old principal server detects that and fails everything else over too.

Step 4: Configure the Alert to Execute the Job

Link the WMI alert you created in Step 2 to the job you created in Step 3. In SQL Server Agent, open the alert properties, go to the Response tab, and select "Execute job", then choose your coordinated failover job.

Step 5: Replicate the Configuration on the Mirror Server

This is the step people often forget. The WMI alert and the coordinated failover job need to exist on both servers. After a failover, the original mirror becomes the new principal. If a second failover occurs (for example, a failback), the same mechanism needs to be in place on that server to coordinate things properly.

Script out the alert and job from the principal and deploy them to the mirror. The only change you may need is the Database Mail profile name if it differs between servers.

What to Watch Out For

Timing gaps. There's a small window between when DBX fails over and when the other databases fail over. Applications that span multiple databases may see brief inconsistencies during this window. In most cases this is measured in seconds, but it's worth documenting and discussing with your application team.

Witness server configuration. Automatic failover in high-safety mode requires a witness server. Without a witness, failover must be manually initiated. If your mirroring is configured without a witness, the WMI alert will still fire, but you'll need to understand under what conditions it fires and whether the job logic handles manual failovers correctly.

Testing. Test this in a non-production environment before relying on it. Trigger a manual failover of DBX and confirm that all other databases fail over within an acceptable time window. Check Database Mail to confirm the notification arrives. Then test the failback.

SQL Server 2012 and later. If you're on SQL Server 2012 or newer, use Always On Availability Groups instead. Availability Groups handle coordinated failover natively, with better performance, better monitoring, and without the complexity of building your own coordination logic. Database mirroring was deprecated in SQL Server 2012 and removed in SQL Server 2022.

Key Takeaways

  • Emulating a SQL cluster using mirroring requires a coordinated failover mechanism built around an anchor database, WMI event alerts, and a SQL Agent job that fails over all databases together when the anchor changes state.
  • Service Broker must be enabled on msdb before WMI-based alerting will function correctly.
  • The failover job must include a role check (mirroring_role = 2) to prevent accidental failover loops between principal and mirror servers.
  • This solution applies specifically to SQL Server 2008 and 2008 R2 environments. SQL Server 2012 and later should use Always On Availability Groups, which provide native coordinated failover without custom automation.
  • Both the principal and mirror servers need identical alert and job configurations so the mechanism works correctly in both directions.

If you're still running SQL Server 2008 or managing a mirroring environment that hasn't been migrated to Availability Groups, DBA Services can help you assess your current configuration, identify risks, and plan a migration path to a supported high availability solution. Our SQL Server health checks cover mirroring state, agent job reliability, Database Mail configuration, and overall HA readiness. Get in touch to find out where your environment stands.