Professional Microsoft® SQL Server® 2008 Administration

17.7. 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 2008 Management Studio and select New Alert. The dialog shown in Figure 17-11 will appear.

Figure 17-11. Figure 17-11

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

SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE

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

SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 8 AND
DatabaseName = 'AdventureWorks2008'

This statement will only listen for the "automatic failover" state change (state = 8) for the AdventureWorks2008 database. The following table lists all the database mirroring state change events so that you can use it ...