How can we perform backups and other Agent jobs in an Always On Availability Group of SQL Server
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
In this article we will see how to take backup and run jobs on any server that has it Primary role or is Preferred Backup Replica in a Always On Availability Group of SQL Server.
When installing a standard Availability Group in Enterprise SQL Server, it asks us where we want it to take the database backups. It has the option as default value Prefer Secondary. But there is a problem. In secondary replica allows only the execution COPY_ONLY Full backup and no Differential backup:
In order to solve this problem and make our installation less complex, we will define that all backups and other jobs for maintenance are executed and not only, on whichever replica of the database is Preferred Backup Replica ή Primary Replica per case.
The footsteps
For Database Backups
Let's go to Object Explorer of SSMS and select Always On High Availability, Availability Groups and right click Properties in the group:
With the Wizard appearing in the Backup Preferences we choose Primary and OK:
The following code checks if the Preferred Backup Replica and if it is not it displays an error. The default behavior of SQL Server Agent is to stop the job if any step shows an error:
DECLARE @preferredReplica int
SET @preferredReplica = (SELECT
max(cast(sys.fn_hadr_backup_is_preferred_replica (database_name) as int)) AS IsPreferredBackupReplicaNow
FROM sys.availability_databases_cluster)
IF (@preferredReplica = 0)
BEGIN
RAISERROR ('No backup produced as this server, is not the preferred replica for backups. Please run the job again from the preferred replica.', 16, 1)
END;
We add the code to SQL Server Agent job that we want. To do this we select Job, Steps(1), New…(2) , add code(3), set to be Start Step(4) and first in Job step list(5) :
So having created the SQL Server Agent Job on all nodes/servers of the Availability Group it will only be executed on whichever is Primary at the moment.
We can of course make it a little more complicated and have the Backup code and the Preferred Replica control in the same step:
DECLARE @preferredReplica int
SET @preferredReplica = (SELECT
max(cast(sys.fn_hadr_backup_is_preferred_replica (database_name) as int)) AS IsPreferredBackupReplicaNow
FROM sys.availability_databases_cluster)
IF (@preferredReplica = 0)
RAISERROR ('No backup produced as this server, is not the preferred replica for backups. Please run the job again from the preferred replica.', 16, 1)
ELSE
EXEC msdb.dbo.sp_start_job N'DatabaseBackup - USER_DATABASES - FULL';
For Database Integrity Check
Somewhere here we should mention that the same process can work for the Database Integrity Check as the Availability Group allows us to do this check on a secondary replica.
For Index Optimize (Index Rebuild) and other jobs
Accordingly, for SQL Server Agent Jobs that we want to run only on the Primary Replica, we do the same procedure and add a Step to the Job with the following code that performs this check:
DECLARE @Role int
SET @Role = (select role from sys.dm_hadr_availability_replica_states where is_local=1)
IF (@Role != 1 ) -- if is not primary
BEGIN
RAISERROR ('Job did not run, is not the Primary replica. Please run on Primary Replica.', 16, 1);
END;
We can also in this case if we want to add the control in the same step as the code:
DECLARE @Role int
SET @Role = (select role from sys.dm_hadr_availability_replica_states where is_local=1)
IF (@Role != 1 ) -- if is not primary
RAISERROR ('Job did not run, is not the Primary replica. Please run on Primary Replica.', 16, 1);
ELSE
EXEC msdb.dbo.sp_start_job N'IndexOptimize';
Bonus
With the following script that I have made, with just one click, it installs it MaintenanceSolution by Ola Hallengren along with ready configured jobs for Backup ,Database Integrity and Index Optimize with optimal parameters and timing.
Backup jobs are created disabled as we may have another solution for backup with a 3rd party tool. It also checks for Availability Group so that they run only if it is the Preferred Backup Replica (for backup / database integrity) and if it is Primary Replica (for Index Optimize).
All we need to do is run the following script on each SQL Server Instance:
