Always On Availability Group Failover Scripts with Notes
1. Set Up an Alert for Always On Failover:
Use SQL Server Agent Alerts to detect the failover and trigger a job. The following T-SQL script
creates an alert for a failover using the Database Mirroring State Change event.
### Notes:
- Replace **YourAGDatabaseName** with the name of your Availability Group database.
- Replace **DBA_Operator** with the name of your SQL Server Agent operator.
T-SQL Script:
-----------------------------------------------------
USE [msdb];
GO
EXEC sp_add_alert
@name = N'AlwaysOn Failover Alert',
@message_id = 1480, -- Database Mirroring State Change
@severity = 0,
@enabled = 1,
@delay_between_responses = 0,
@include_event_description_in = 1,
@notification_message = N'An AlwaysOn failover occurred.',
@event_description_keyword = N'failover',
@database_name = N'YourAGDatabaseName', -- Replace with your AG database name
@job_id = NULL;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'AlwaysOn Failover Alert',
@operator_name = N'DBA_Operator', -- Replace with your operator
@notification_method = 1;
GO
-----------------------------------------------------
2. T-SQL Script to Check Replica Role and Perform T-log Backup:
This job checks whether the current node is the primary replica and performs a transaction log
backup on the new primary after a failover.
### Notes:
- Replace **YourDatabaseName** with your database name in both the script and backup path.
T-SQL Script:
-----------------------------------------------------
USE [msdb];
GO
EXEC sp_add_job
@job_name = N'Transaction Log Backup After Failover';
EXEC sp_add_jobstep
@job_name = N'Check Primary Replica and Backup T-Logs',
@subsystem = N'TSQL',
@command = N'
DECLARE @IsPrimary BIT;
SET @IsPrimary = 0;
IF (SELECT role_desc FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
WHERE ars.is_local = 1) = ''PRIMARY''
BEGIN
SET @IsPrimary = 1;
END
IF @IsPrimary = 1
BEGIN
BACKUP LOG [YourDatabaseName]
TO DISK = N''C:\BackupFolder\YourDatabaseName_TLog.trn''
WITH NOFORMAT, NOINIT, NAME = N''YourDatabaseName-TLogBackup'',
STATS = 10;
END
',
@database_name = N'YourDatabaseName',
@on_success_action = 1,
@on_fail_action = 2;
EXEC sp_add_jobschedule
@job_name = N'Transaction Log Backup After Failover',
@name = N'Failover Schedule',
@freq_type = 1,
@active_start_time = 150000;
EXEC sp_add_jobserver
@job_name = N'Transaction Log Backup After Failover';
GO
-----------------------------------------------------
3. Copy Transaction Log Backups to Original Primary:
This PowerShell script is used to copy the backups from the new primary back to the original
primary.
### Notes:
- Replace **YourDatabaseName** with your database name.
- Replace **OriginalPrimaryServer** with the original primary server's network path.
PowerShell Script:
-----------------------------------------------------
$source = "C:\BackupFolder\YourDatabaseName_TLog.trn"
$destination = "\\OriginalPrimaryServer\BackupShare\"
Copy-Item $source -Destination $destination -Recurse
-----------------------------------------------------
