August 30, 2011 at 7:16 am
Hi All
Not sure if my issue was posted here or not but here goes.
I have 3 servers (Principal,Mirror and Witness). When I set up a WMI event alert for one or 3 databases to alert when a manual failover occur; I get alerts for ALL the other 28 databases on my Principal server (I did NOT fail them over).
Is it possible to have a solution/anything out there to just alert me once via sms and email when a manual or automatic failover occurred on the Principal WITHOUT having to use WMI events or some third-party tool? :ermm:
Thanks in advance.
Anchelin
August 30, 2011 at 7:56 am
a failover is entered into the sql server log on the principal. You could capture these events and act on them. Messages will be something along the lines of
The mirrored database "AdventureWorks" is changing roles from "PRINCIPAL" to "MIRROR" due to Failover.
Or
The mirrored database "AdventureWorks" is changing roles from "MIRROR" to "PRINCIPAL" due to Failover from partner.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 31, 2011 at 5:05 am
You can use Database mail to send the alerts based on the alerts raised by the DB mirroring.
Further you can also use the system catalog View sys.database_mirroring which contains one row for each database in the instance of SQL Server and also contains state information of all mirrored databases. This can be used to query and raise an email alert for each mirrored database that we find in an abnormal state.
-Satya SK Jayanty
SQL Server MVP (Follow me @sqlmaster)
Author of SQL Server 2008 R2 Administration CookBook
SQL Server Knowledge Sharing network
August 31, 2011 at 5:08 am
Hi
Thats the thing; he does not want to be spammed. If one alert was sent, it should not send another alert. He only needs one alert.
Thanks for all comments. I am actually working on the one that reads the errorlog.
Regards
Anchelin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply