Mirroring Event Listener Setup

In this section, we provide steps you can use to take some action when the database mirroring session changes state (for example, from disconnected to synchronizing or from synchronized to suspended). You can perform the following steps to configure an alert for mirroring state change events and take some action on these events.

Right-click the Alert folder under SQL Server Agent in SQL Server Management Studio and select New Alert. You'll see the window shown in Figure 17-10.

Figure 17-10

Type the name of the event and select the event type as "WMI event alert" from the drop-down box. The namespace will be automatically filled out for you. In the query field, type the following query:

 SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE 

In this example, the alert will be fired for all the database mirroring state change events for all the databases mirrored on this server. If you want to be alerted on a specific database mirroring state change events for a specific database, you can add a WHERE clause to the SELECT statement, as show here:

 SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 8 AND Database = 'TestMirroring' 

This statement will only listen for the "automatic failover" state change (state = 8) for the TestMirroring database. The following table lists all the database mirroring state change events so that you can use this table to build the WHERE clause to listen to specific events.

Open table as spreadsheet

State	Name	Description
0	Null Notification	This state occurs briefly when a mirroring session is started.
1	Synchronized Principal with Witness	This state occurs on the principal when the principal and mirror are connected and synchronized and the principal and witness are connected. For a mirroring configuration with a witness, this is the normal operating state.
2	Synchronized Principal without Witness	This state occurs on the principal when the principal and mirror are connected and synchronized but the principal does not have a connection to the witness. For a mirroring configuration without a witness, this is the normal operating state.
3	Synchronized Mirror with Witness	This state occurs on the mirror when the principal and with are connected and synchronized and the mirror and witness are connected. For a mirroring configuration with a witness, this is the normal operating state.
4	Synchronized Mirror without Witness	This state occurs on the mirror when the principal and mirror are connected and synchronized but the mirror does not have a connection to the witness. For a mirroring configuration without a witness, this is the normal operating state.
5	Connection with Principal Lost	This state occurs on the mirror server instance when it cannot connect to the principal.
6	Connection with Mirror Lost	This state occurs on the principal server instance when it cannot connect to the mirror.
7	Manual Failover	This state occurs on the principal server instance when the user fails over manually from the principal or on the mirror server instance when a force service is executed at the mirror.
8	Automatic Failover	This state occurs on the mirror server instance when the operating mode is high safety with automatic failover (synchronous) and the mirror and witness server instances cannot connect to the principal server instance.
9	Mirroring Suspended	This state occurs on either partner instance when the user suspends (pauses) the mirroring session, or when the mirror server instance encounters an error. It also occurs on the mirror server instance following a force service command. When the mirror comes online as the principal, mirroring is automatically suspended.
10	No Quorum	If a witness is configured, this state occurs on the principal or mirror server instance when it cannot connect to its partner or to the witness server instance.
11	Synchronizing Mirror	This state occurs on the mirror server instance when there is a backlog of unsent log. The status of the session is Synchronizing.
12	Principal Running Exposed	This state occurs on the principal server instance when the operating mode is high safety (synchronous) and the principal cannot connect to the mirror server instance.
13	Synchronizing Principal	This state occurs on the principal server instance when there is a backlog of unsent log. The status of the session is Synchronizing.

Now select the Response page, shown in Figure 17-11. In this page you will be able to fill out what you want SQL Server to do if the event happens. In Figure 17-11, you want to execute the SQL job "TestEventChange" when database mirroring state change event happens and notify me via e-mail and also page me. You can also go to the Options page, where you can specify an additional message. Click OK. You are now all set to receive the alert when database mirroring process changes its state.

Figure 17-11

In the SQL job, you can actually add the following script to store the history of database mirroring state change events in a table. You should create this table first in some other database, such as msdb.

 CREATE TABLE dbo.MirroringStateChanges (  EventTime varchar(max) NULL ,EventDescription varchar(max) NULL ,NewState int NULL ,DatabaseName varchar(max) NULL ) 

Add the following script as a job step to insert into this table.

 INSERT INTO dbo.MirroringStateChanges (  [EventTime] ,[EventDescription] ,[NewState] ,[DatabaseName] ) VALUES (  $(ESCAPE_NONE(WMI(StartTime))) ,$(ESCAPE_NONE(WMI(TextData))) ,$(ESCAPE_NONE(WMI(State))) ,$(ESCAPE_NONE(WMI(DatabaseName)))  ) 

You can change the database mirroring state using the ALTER DATABASE command to test this alert. Also, the database mirroring event change is logged to Event Viewer under Application events, something like the following:

 The mirrored database "TestMirroring" is changing roles from "MIRROR" to "PRINCIPAL" due to Failover from partner