HOW TO: Setup Cluster Failover Alert Email

To be creative, I will try to model my posts into the below format, dragging my lazy bum to finally come back and post.

Why
It is strange a few years as DBA I have not yet seen a de-facto Cluster Alert solution. One can monitor event logs, SQL error logs, but there is no wizard in SQL/Windows Cluster to setup alerts for failover. With help from Google, I used this solution to send me email when a cluster fails over (i.e. SQL Server Agent will fail over too and cause the job to run).

To my surprise, not that it is something I want to happen, it worked and sent me an email Tuesday
I am certain there is a better way, someone please let me know.

Who
DBAs who want to get notified when cluster failed over

How
Prerequisites (I will post on this tomorrow)
- Database Mail profile & account configured
- SQL Server Agent operator created

The job sends an Email to the specified Operator when it runs

JOB RUN: 'Cluster Alert' was run on 3/17/2009 at 4:06:40 PM

DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Start Sequence 0. The last step to run was step 1 (Cluster).

When
The job is scheduled to run WHEN SQL Server Agent Starts, which happens when the cluster service fails over

Where
SQL Server 2005/2008

What


USE [msdb]


GO


/****** Object:  Job [Cluster Alert]    Script Date: 03/19/2009 16:27:37 ******/

BEGIN TRANSACTION


DECLARE  @ReturnCode INT


SELECT @ReturnCode = 0


/****** Object:  JobCategory [Database Maintenance]    Script Date: 03/19/2009 16:27:37 ******/

IF NOT EXISTS (SELECT name

               FROM   msdb.dbo.syscategories

               WHERE  name = N'Database Maintenance'

                      AND category_class = 1)

  BEGIN

    EXEC @ReturnCode = msdb.dbo.Sp_add_category

      @class = N'JOB' ,

      @type = N'LOCAL' ,

      @name = N'Database Maintenance'



    IF (@@ERROR <> 0

         OR @ReturnCode <> 0)

      GOTO quitwithrollback

  END


DECLARE  @jobId BINARY(16)


EXEC @ReturnCode = msdb.dbo.Sp_add_job

  @job_name = N'Cluster Alert' ,

  @enabled = 1 ,

  @notify_level_eventlog = 0 ,

  @notify_level_email = 3 ,

  @notify_level_netsend = 0 ,

  @notify_level_page = 0 ,

  @delete_level = 0 ,

  @description = N'No description available.' ,

  @category_name = N'Database Maintenance' ,

  @owner_login_name = N'sa' ,

  @notify_email_operator_name = N'SqlDbas' ,

  @job_id = @jobId OUTPUT


IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback


/****** Object:  Step [Cluster]    Script Date: 03/19/2009 16:27:37 ******/

EXEC @ReturnCode = msdb.dbo.Sp_add_jobstep

  @job_id = @jobId ,

  @step_name = N'Cluster' ,

  @step_id = 1 ,

  @cmdexec_success_code = 0 ,

  @on_success_action = 1 ,

  @on_success_step_id = 0 ,

  @on_fail_action = 2 ,

  @on_fail_step_id = 0 ,

  @retry_attempts = 0 ,

  @retry_interval = 0 ,

  @os_run_priority = 0 ,

  @subsystem = N'TSQL' ,

  @command = N'print ''cluster''' ,

  @database_name = N'master' ,

  @flags = 0


IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback


EXEC @ReturnCode = msdb.dbo.Sp_update_job

  @job_id = @jobId ,

  @start_step_id = 1


IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback


EXEC @ReturnCode = msdb.dbo.Sp_add_jobschedule

  @job_id = @jobId ,

  @name = N'SQLAgentStart' ,

  @enabled = 1 ,

  @freq_type = 64 ,

  @freq_interval = 0 ,

  @freq_subday_type = 0 ,

  @freq_subday_interval = 0 ,

  @freq_relative_interval = 0 ,

  @freq_recurrence_factor = 0 ,

  @active_start_date = 20090309 ,

  @active_end_date = 99991231 ,

  @active_start_time = 0 ,

  @active_end_time = 235959


IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback


EXEC @ReturnCode = msdb.dbo.Sp_add_jobserver

  @job_id = @jobId ,

  @server_name = N'(local)'


IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback





GOTO endsave


QUITWITHROLLBACK:

IF (@@TRANCOUNT > 0)

  ROLLBACK TRANSACTION


ENDSAVE:

GO