O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required