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 PMWhen
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).
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
