SQL 2014: Implementing Backup on Secondary Replicas (AlwaysOn Availability Groups)

In Part3 of the series of articles on building highly available SQL Server 2014 solution using guest clustering and AlwaysON availability group we have completed the creation of the AlwaysOn availability group with the test database – Resources – and in this article I’d like to show how to set up the backup of this database (or any other database, of course) on servers holding the secondary database replicas.

As you might remember,  we have configured the Backup Preferences setting as “Prefer secondary” (right-click Availability Groups\RESOURCES-Group – Properties):

AL-ONThis 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-2I’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-6As 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-7The 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-8On secondary servers we can use Copy-only backup instead of Full Backup  – checking the corresponding checkbox switches the warning off.

P4-9 P4-10Now 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-15Look! 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-16The result:

P4-17P4-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-32P4-33As 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.

Summary:

In this article we configured database and log backups to be run on secondary replicas and set the single network share as the target for backup jobs for both servers. In the next article I’ll show you how to restore a database that’s part of an AlwaysOn availability group.

Advertisements

6 responses

  1. I understand that there is a COPY ONLY backup on the secondary, but how is trn file also created. My understanding is that using COPY ONLY backup there cannot be transaction file backup, OR am I making a wrong assumption.

    1. Hello Iman,
      Thank you very much for your question: “My understanding is that using COPY ONLY backup there cannot be transaction file backup” – so is mine… My apologies for not noticing it earlier. Frankly speaking I don’t know why trn files have been created in my initial tests: backing up a database should produce the .bak files only. Today I re-created the same maintenance plans and they work as expected – only .bak files are being created. When I added trnasaction log backups to the plans the .trn files started being created as well.
      Thank you for your question once again.

  2. Hello,

    I get the below message when running execute command from the Secondary.

    The target database, ‘xxxxxxx′, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.

    Is there a solution for this error.

    1. Hello Kevin,
      “To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.” – have you specified “Readable secondaries = Yes” in the “Specify an instance of SQL Server to host a secondary replica” window as illustrated here: https://michaelfirsov.wordpress.com/sql-2014-deploying-alwayson-availability-group-in-a-guest-cluster-part-3/ ?

  3. HI Michael,
    IF I have 3 secondary replicas, I have my backup preference as secondary and I dont want to store it in a file share, how can we have consistent log chain of backups.

    How to configure in this case?

    1. Hi sanj,
      You don’t necessarily have to place your backup files into a file share – it’s just allows to have the whole backup chain in one place. You can place your backup files to any folder on your servers – in that case you just need to pay extra attention to what files you are restoring – I mean it would be up to you to form the consistent chain of files to restore accross all secondary servers, for example: to restore a DB you’d restore 1) backup file 1 from secondary2 2) backup file 2 from secondary3, 3) backup file 3 from secondary1 and etc..

      Regards,
      Michael

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: