1

Hi all I'm having a problem configuring a WMI alert that I would like to use with database mirroring. I'm running on Windows 2008 Enterprise X64 with Server 2008 Enterprise X64 also SQL Server has SP1 installed. Basically I click on alert select WMI after that I typed the below SQL statement

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

I have also made sure the service broker is enabled for the msdb and all mirrored databases however I still can't get this to work basically the alert never fires. I'm testing with just the alert functionality I have not even added in the agent job yet. I tested this by right clicking on my mirrored database and forcing it to fail over. Any help with this problem would be much appreciated

Improve this question

2 Answers 2

Reset to default
1

Based on the query in your question, the event will fire only when there is an automatic failover (state = 8). If you want the WMI event to file on a manual failover, as in your test, you need to also check for state = 7, which is the state of a manual failover.

To check for both manual failovers and automatic failovers, your query would read:

SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE DatabaseName = 'testmove' AND State IN (7,8)

Additional information can be found here: Database Mirroring State Change Event Class

Improve this answer
0

Which mode are you using: High Safety or High Performance? High Performance requires Forced Service.

"Mirroring is stopped on the mirror database using ALTER DATABASE <dbname> SET PARTNER OFF. The mirroring database is then recovered using RESTORE DATABASE <dbname> WITH RECOVERY. When the principal server becomes available, mirroring would need to be reestablished, this time in the reverse direction." Section 11.5.4 Failure Scenarios, SQL Server 2008 Administration in Action, Rod Colledge

USE MASTER
GO
ALTER DATABASE [DATABASE1] SET PARTNER OFF
RESTORE DATABASE [DATABASE1] WITH RECOVERY
GO

ALTER DATABASE [DATABASE1] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .