Home / server / Questions / 101813
Accepted
MGOwen
Asked: 2010-01-12 20:34:19 +0800 CST

Send Email on failover in Sql Server 2005

  • 772

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?

sql-server email database-mirroring failover

2 Answers

  1. Best Answer

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

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

    • 2

  2. 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
    • 0