I needed a way to be notified by email when a SQL failover occurred. The simple solution for this is to setup a job to send an email and set this job to be triggered when SQL agent starts. The SQL agent service on the passive server starts when a failover occurs.

The instructions are for SQL Server 2008 but the steps should be similar in other versions.

First we need to setup a SQL mail profile to connect to a mail server. This mail service does not need to be installed on the same server as SQL. If this is already configured skip to the “Configure the SQL job” section below.

3. Select Set up Database Mail by performing the following tasks -> Next

4. Enter a relevant profile name then hit Add next to SMTP accounts

5. Enter the email address you want to use for these alerts. I highly recommend using an existing user and enabling basic authentication. Otherwise you may have to allow the SQL server to send through your mail server without authentication.

6. Click Next -> Next -> Next -> Finish

Configure the SQL job:1. Expand SQL Server Agent

2. Right click Jobs -> New Job

3. Fill out the General page with relevant details then click the Steps page -> New

4. Enter an appropriate name for the step and set type to Transact-SQL script (T-SQL)

5. Enter the following in the Command field. Make sure to modify the values as needed.