{"id":4553,"date":"2022-05-04T07:00:00","date_gmt":"2022-05-04T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=4553"},"modified":"2023-10-03T20:25:05","modified_gmt":"2023-10-03T17:25:05","slug":"pos-mporoyme-na-ekteloyme-backups-kai-loipa-agent","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-ekteloyme-backups-kai-loipa-agent\/","title":{"rendered":"How can we perform backups and other Agent jobs in an Always On Availability Group of SQL Server"},"content":{"rendered":"

In this article we will see how to take backup and run jobs on any server that has it Primary role<\/em><\/strong> or is Preferred Backup Replica<\/strong><\/em> in a Always On Availability Group<\/strong> of SQL Server.<\/p>\n\n\n\n

When installing a standard Availability Group in Enterprise SQL Server<\/strong>, it asks us where we want it to take the database backups. It has the option as default value Prefer Secondary<\/strong><\/em>. But there is a problem. In secondary replica allows<\/strong> only <\/strong>the execution COPY_ONLY<\/code> Full backup and no Differential backup:<\/p>\n\n\n\n

\"\"
01<\/figcaption><\/figure>\n\n\n\n

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 <\/strong>the Primary Replica<\/strong> per case.<\/p>\n\n\n\n

<\/p>\n\n\n\n

The footsteps<\/h2>\n\n\n\n

For Database Backups<\/h4>\n\n\n\n

Let's go to Object Explorer<\/em> of SSMS and select Always On High Availability<\/em>, Availability Groups <\/em>and right click Properties <\/em>in the group:<\/p>\n\n\n\n

\"\"
02<\/figcaption><\/figure>\n\n\n\n

With the Wizard appearing in the Backup Preferences<\/em> we choose Primary<\/em><\/strong> and OK:<\/p>\n\n\n\n

\"\"
03<\/figcaption><\/figure>\n\n\n\n

The following code checks if the Preferred Backup Replica <\/em><\/strong>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:<\/p>\n\n\n\n

DECLARE @preferredReplica int\nSET @preferredReplica = (SELECT \n  max(cast(sys.fn_hadr_backup_is_preferred_replica (database_name) as int)) AS IsPreferredBackupReplicaNow\nFROM sys.availability_databases_cluster)\n\nIF (@preferredReplica = 0)\nBEGIN\nRAISERROR ('No backup produced as this server, is not the preferred replica for backups.  Please run the job again from the preferred replica.', 16, 1)\nEND;<\/pre>\n\n\n\n
We add the code to SQL Server Agent job <\/strong>that we want. To do this we select Job, Steps<\/em>(1), New\u2026<\/em>(2) , add code(3), set to be Start Step<\/em>(4) and first in Job<\/em> step list<\/em>(5) :<\/p>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
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.<\/p>\n\n\n\n
We can of course make it a little more complicated and have the Backup code and the Preferred Replica control in the same step:<\/p>\n\n\n\n
DECLARE @preferredReplica int\nSET @preferredReplica = (SELECT \n  max(cast(sys.fn_hadr_backup_is_preferred_replica (database_name) as int)) AS IsPreferredBackupReplicaNow\nFROM sys.availability_databases_cluster)\n\nIF (@preferredReplica = 0)\n\tRAISERROR ('No backup produced as this server, is not the preferred replica for backups.  Please run the job again from the preferred replica.', 16, 1)\nELSE\n    EXEC msdb.dbo.sp_start_job N'DatabaseBackup - USER_DATABASES - FULL'; <\/pre>\n\n\n\n
<\/p>\n\n\n\n
For Database Integrity Check<\/h4>\n\n\n\n
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.<\/p>\n\n\n\n
<\/p>\n\n\n\n
For Index Optimize (Index Rebuild) and other jobs<\/h4>\n\n\n\n
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:<\/p>\n\n\n\n
DECLARE @Role int\nSET @Role = (select role from sys.dm_hadr_availability_replica_states where is_local=1)\n\nIF (@Role != 1 ) -- if is not primary\nBEGIN\nRAISERROR ('Job did not run, is not the Primary replica. Please run on Primary Replica.', 16, 1);\nEND;<\/pre>\n\n\n\n
We can also in this case if we want to add the control in the same step as the code:<\/p>\n\n\n\n
DECLARE @Role int\nSET @Role = (select role from sys.dm_hadr_availability_replica_states where is_local=1)\n\nIF (@Role != 1 ) -- if is not primary\n\tRAISERROR ('Job did not run, is not the Primary replica. Please run on Primary Replica.', 16, 1);\nELSE\n\tEXEC msdb.dbo.sp_start_job N'IndexOptimize';  <\/pre>\n\n\n\n
<\/p>\n\n\n\n
Bonus<\/h2>\n\n\n\n
With the following script that I have made, with just one click, it installs it MaintenanceSolution<\/a> by Ola Hallengren along with ready configured jobs for Backup<\/strong> ,Database Integrity<\/strong> and Index Optimize<\/strong> with optimal parameters and timing.<\/p>\n\n\n\n
Backup jobs are created disabled<\/strong> as we may have another solution for backup with a 3rd party tool. It also checks for Availability Group<\/strong> so that they run only if it is the Preferred Backup Replica <\/em>(for backup \/ database integrity) and if it is Primary Replica<\/em> (for Index Optimize).<\/p>\n\n\n\n
All we need to do is run the following script on each SQL Server Instance:<\/p>\n\n\n\n
MaintenanceSolution-ReadyToPlay<\/a>Download<\/a><\/div>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n