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.

Setup SQL mail profile:
1. Open Management Studio, connect to your SQL cluster, and expand Management

2. Double click Database Mail to start the Mail Configuration Wizard

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.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'My mail account name',
@Body='SQL Agent has started on the passive server.',
@from_address= 'alerts@mydomain.com',
@subject='SQL Cluster Failover Alert';

6. Click OK

7. Click the Schedules page -> New

8. Name the schedule appropriately and set schedule type to “Start automatically when SQL Server Agent starts”

9. Click OK -> OK

10. Right click the new job and select “Start job at step” to test the new job and make sure it can send mail properly

Leave a Reply