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 creation of the AlwaysOn availability group with the test database – Resources – and in this article I’d like to show how to set up 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):
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 Maintenance Plan Wizard on SQL1:
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.
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.
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).
Now let’s execute the ALWAYS ON Group Backup maintenance plan on the primary replica (SQL1):
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:
* 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:
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.
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.