0

I have two instances of Sql Server 2005 running two copies of a database in a Principal and Mirror configuration.

I want to send an email to someone whenever there is a failover, by which I mean the Principal DB goes down (for whatever reason) and so the two DB's swap roles, the Mirror becoming the Principal (and the Principal becoming the Mirror).

I have already configured "Database Mail" and sent a test message using sp_send_dbmail successfully. But I don't know how to create a Sql Server Agent Job (or whatever will work) that runs on failover.

I imagine the way to do this would be to somehow hook into some sort of "Oh! I was the Mirror a second ago but I have just become the Principal" event and make it exec sp_send_dbmail .

Anyone have any idea how to do this? Or a better way of accomplishing this?

MGOwen
  • 307
  • 2
  • 4
  • 11

2 Answers2

2

There is a soution out there to alert for failover, works great:

http://msdn.microsoft.com/en-us/library/cc966392.aspx

  • I actually use a heavily modified version of the alerts you posted. This does exactly what the OP is looking for. Not only can it send an email upon failover events, you can also have it execute a job as well in case you have extra steps you want to happen whenever failover occurs. – tcnolan Feb 06 '10 at 00:43
0

I'm assuming you have a witness instance here to allow for automatic fail over of the database.

The way I would handle this is to have a table on both the principal and mirror that stores the value of the "mirroring_role_description" column from sys.database_mirroring and then have a SQL agent job run a script that does the following:

  1. Compares the current value with the stored value, emails if changed
  2. Stores the current value in the table
Jason Cumberland
  • 1,579
  • 10
  • 13
  • OK, sounds like this would work, though I thought there'd be a way to schedule a job "on failover" rather than have to check if it happened every minute (or however often). If there is no other answer soon, I'll try this and accept it as the answer. – MGOwen Jan 13 '10 at 04:40
  • Only other thing I can think of is to look into the SQL alerts (under the agent tree in SSMS) and see if one of the default events will let you do capture this. – Jason Cumberland Jan 19 '10 at 15:57