Know the moment SQL Server restarts or fails over: startup jobs, AG role-change alerts and event-log checks

A restart, a cluster failover or an availability group role change are all events you should hear about from your own monitoring, within a minute. Three building blocks cover them all.

#1. Startup notification job

USE msdb;
DECLARE @job_id uniqueidentifier;
EXEC dbo.sp_add_job @job_name = N'DBA - Notify - SQL Server started', @owner_login_name = N'sa',
     @category_name = N'[Uncategorized (Local)]', @job_id = @job_id OUTPUT;

EXEC dbo.sp_add_jobstep @job_id = @job_id, @step_name = N'Send notification', @subsystem = N'TSQL', @database_name = N'master',
     @command = N'
WAITFOR DELAY ''00:01:00'';   -- let Database Mail and the network settle after startup
DECLARE @body nvarchar(max), @subject nvarchar(255);
SELECT @subject = CONCAT(N''SQL Server started: '', @@SERVERNAME, N'' on node '', CAST(SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS nvarchar(128)));
SELECT @body = CONCAT(
  N''Instance:            '', @@SERVERNAME, CHAR(10),
  N''Running on node:     '', CAST(SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS nvarchar(128)), CHAR(10),
  N''SQL Server started:  '', CONVERT(nvarchar(19), sqlserver_start_time, 120), CHAR(10),
  N''Agent started:       '', CONVERT(nvarchar(19), GETDATE(), 120), CHAR(10),
  N''Windows uptime (min):'', ms_ticks / 60000, CHAR(10),
  N''Version:             '', CAST(SERVERPROPERTY(''ProductVersion'') AS nvarchar(32)), N'' '', CAST(SERVERPROPERTY(''ProductUpdateLevel'') AS nvarchar(32)), CHAR(10),
  N''Clustered (FCI):     '', CAST(SERVERPROPERTY(''IsClustered'') AS nvarchar(5)))
FROM sys.dm_os_sys_info;
EXEC msdb.dbo.sp_send_dbmail @profile_name = N''DBA'', @recipients = N''dba-team@corp.example.com'', @subject = @subject, @body = @body;';

EXEC dbo.sp_add_jobschedule @job_id = @job_id, @name = N'At Agent start', @freq_type = 64;
EXEC dbo.sp_add_jobserver @job_id = @job_id;

How to read the email:

Add an event-log write (RAISERROR ... WITH LOG) to the same step if your monitoring tool reads the Application log.

#2. Availability group role-change alerts

When a replica changes role, SQL Server logs error 1480 ("The ... database ... is changing roles from ... to ... because ..."). Create an Agent alert on every replica:

EXEC msdb.dbo.sp_add_alert @name = N'AG role change (1480)', @message_id = 1480, @severity = 0,
     @include_event_description_in = 1, @delay_between_responses = 60;
EXEC msdb.dbo.sp_add_notification @alert_name = N'AG role change (1480)', @operator_name = N'DBA On-call', @notification_method = 1;

Other useful AG messages to alert on (check that they're logged on your version: SELECT message_id, is_event_logged FROM sys.messages WHERE language_id = 1033 AND message_id IN (...)):

An alert only fires if the message is written to the Windows Application log. If is_event_logged = 0, make it logged with sp_altermessage message_id, 'WITH_LOG', 'true' where that's supported, or detect it from AG DMVs instead.

#3. Find out why it happened

-- current and previous error logs: startup, shutdown, failover reasons
EXEC master.dbo.xp_readerrorlog 1, 1, N'shutdown';      -- previous log
EXEC master.dbo.xp_readerrorlog 0, 1, N'Server process ID';   -- startup lines in current log
EXEC master.dbo.xp_readerrorlog 0, 1, N'1480';

-- AG health right now
SELECT ag.name, ar.replica_server_name, ars.role_desc, ars.connected_state_desc, ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ar.group_id = ag.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = ar.replica_id;

# Windows: unexpected shutdowns (6008), user/initiated restarts (1074), cluster resource failures
Get-WinEvent -FilterHashtable @{ LogName = 'System'; Id = 6008, 1074; StartTime = (Get-Date).AddDays(-2) } |
    Select-Object TimeCreated, Id, Message | Format-List

# cluster: generate the cluster log for the last 60 minutes on all nodes
Get-ClusterLog -Destination C:\Temp\ClusterLogs -TimeSpan 60 -UseLocalTime
Get-WinEvent -LogName 'Microsoft-Windows-FailoverClustering/Operational' -MaxEvents 50 | Select-Object TimeCreated, Id, Message

#4. Test it

• Restart the SQL Server Agent service: you should get an email (Agent start only).
• In a maintenance window, fail over the FCI or AG: you should get the startup email (FCI) or the 1480 alert (AG) from each replica.

Tip

Keep the startup job on every instance as part of your build standard. It costs nothing, and it turns "the database was slow at 3 am" into "the cluster failed over at 02:57 because the lease timed out".