May 24, 2012 at 1:04 pm
Hey everyone.
I have an alert setup that will fire when an failover occurs on a database mirror setup.
USE [msdb]
GO
/****** Object: Alert [Alert_UpdateAliasOnMirrorFailover] Script Date: 05/24/2012 15:01:45 ******/
IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Alert_UpdateAliasOnMirrorFailover')
EXEC msdb.dbo.sp_delete_alert @name=N'Alert_UpdateAliasOnMirrorFailover'
GO
USE [msdb]
GO
/****** Object: Alert [Alert_UpdateAliasOnMirrorFailover] Script Date: 05/24/2012 15:01:45 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert_UpdateAliasOnMirrorFailover',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 1 OR State = 2 OR State = 8',
@job_id=N'bc5caf0e-fcaf-4094-87c1-eb90763744df'
GO
This has been worked for the last 2 years on 6 different occasions. However, for some reason, today, it didn't work.
The history shows that it didn't work. Anyone got any thoughts on where to start looking? I have validated in the Alert history that it didn't fire. SQL Agent Log doesn't show any errors that show an issues.
Trying to figure out what could have happened to not fire this alter. Note that it was an Automatic Failover.
Thanks,
Fraggle.
May 24, 2012 at 3:08 pm
did you want this alert to fire on the principal?
what was the reason the failover occured?
how long did the failover take?
and the alert seems a bit off... you are querying for the state of 1 which means everythining is normal...
May 24, 2012 at 3:25 pm
Geoff A (5/24/2012)
did you want this alert to fire on the principal?what was the reason the failover occured?
how long did the failover take?
and the alert seems a bit off... you are querying for the state of 1 which means everythining is normal...
Geoff,
1) The alert would fire on the server the database is failing over to. So the server that once held the mirror, that is becoming the principal.
2) 12 seconds for the entire failover as near as I can figure.
3) No idea why the failover occurred. Something we are looking at. It appears that there was just a heavy load on the database server, but trying to validate.
4) The following article from MSDN http://msdn.microsoft.com/en-us/library/cc966392.aspx states that a State of 1 means that the Database is Syncronized with the witness. So when the database failsover, by default, it would take a minute to actually sync. So I am looking for the change in state from something to 1, which would happen with on a failover. This was actually proven during our testing.
Thanks,
Fraggle
May 24, 2012 at 3:29 pm
12 seconds for failover....
wmi alerts are run in 30 second intervals by default.
from MS;
SQL Server Agent evaluates WMI-based alerts in fixed-size batches at fixed intervals. By default, alerts are evaluated in batches of five at an interval of thirty seconds. Alerts are processed in a round-robin fashion, so if you have ten WMI-based alerts, five will be evaluated on the first interval, and the next five will be evaluated thirty seconds later. If you have many WMI-based alerts, it may take several minutes to evaluate them all, which can lead to a long delay between when the event occurs and the alert notifies you.
May 24, 2012 at 3:37 pm
I would have been ok with a 1 minute delay. In this case, we never got notified. Period. 30 minutes later we found out by chance as we went to the now mirrored server and realized it wasn't the principal
Fraggle
May 24, 2012 at 4:11 pm
When I setup mirroring at a previous employer I used event notification and Service Broker to handle the necessary processing for fail-over, including notification of interested parties (me and the PeopleSoft Administrator).
From what I have heard since I left, they haven't had any problems.
May 24, 2012 at 9:06 pm
Lynn,
That sounds interesting. Could you provide more details on the implementation on that.
Thanks,
Fraggle
May 25, 2012 at 2:11 pm
Fraggle-805517 (5/24/2012)
Lynn,That sounds interesting. Could you provide more details on the implementation on that.
Thanks,
Fraggle
I can try. I'll have to contact a co-worker there (he actually has my job now) and see if I can get the information from him. It has been over two years since I set it up so I don't remember the details and I left there in August 2010.
May 26, 2012 at 10:10 am
Lynn,
Thanks. I can't wait to see your unique solution to this.
Thanks
Fraggle
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply