Implementing Backup on Secondary Replicas (AlwaysOn Availability Groups)

In this article I’d like to show how to set up the backup of this database on servers holding the secondary database replicas.

We can configure the Backup Preferences setting as “Prefer secondary” (right-click Availability Groups\RESOURCES-Group – Properties):

AL-ON

This setting assumes that a backup can be taken on any server holding a secondary role at some point in time  and given that any server can be Primary – let’s say – on Monday and Secondary on Tuesday, I prefer having  my database backed up to a network share – it allows me to have an unbroken chain of backup files regardles of the server wich currently executes the backup job.

I start by running the Maintenance Plan Wizard on SQL1:

P4-1
P4-2

I’m not going to set a schedule in this test cause I will execute Maintenance Plans manually, but in a production environment execution of the backup tasks should be certainly scheduled.

P4-3

I always use “Maintenance Cleanup Task” as the final step in such maintenance plans but I failed trying to make it work on a network shared folder, that’s why I don’t check it in here.

P4-4
P4-5
P4-6

As you see the Wizard warns us about the backup type – full backups are not allowed on secondary replicas (https://msdn.microsoft.com/en-us/library/hh245119(v=SQL.110).aspx), we’ll fix it a bit later.

P4-7

The account being used for running the Maintenance Plan (SQL Server Agent service account by default as we’ve seen on the Select Plan Properties window) must have the appropriate permissions for the shared folder to create backup files in it. In this test I grant Test\sqlagent Full Control permission (both for share and ntfs).

P4-8

On secondary servers we can use Copy-only backup instead of Full Backup  – checking the corresponding checkbox switches the warning off.

P4-9
P4-10

Now I must create exactly the same maintenance plan on the other server – SQL2.

P4-11

Now let’s execute the ALWAYS ON Group Backup maintenance plan on the primary replica (SQL1):

P4-13
P4-14
P4-15

Look! The plan completed successfully but there’re no .bak or .trn files in the folder – that’s because according to the AlwaysON Backup Preferences setting backups should be taken on secondary servers and only in the absence of any secondary server backup can be taken on a primary server. As both SQL1 (Primary) and SQL2(Secondary) are up and running the backup job should not produce any backup files when run on SQL1.

Now I run the same plan on the secondary server – SQL2:

P4-16

The result:

P4-17
P4-18

– both .bak and .trn files have been created.*

*  Only .bak file should be created in this case since we haven’t check “Backup Database(Transaction Log)”. I find difficulty in interpreting this behaviour.
After recreating Maintenance Plans trn files get created only in case the checkbox “Backup Database(Transaction Log)” is explicitly checked.

Then let’s make a test failover and repeat running the maintenance plan on the new secondary server – SQL1:

P4-21
P4-22
P4-23
P4-24
P4-25
P4-26
P4-27
P4-31
P4-32
P4-33

As you see the new .bak and .trn files have been created in the \\sql1\Netbackup folder – thus you can have all backup files in the single location, regradles of which server the maintenance plan currently runs on.

Also See:

SQL Server 2019