Availability Databases Backup

Boka

Hi David,
I've just started working with Always On. I created first jobs for full and log backup using SQLBackup 8 and it looks fine. But I will ask you to write some recommendations and advices.

Thank you in advance

Dan B

Hi Boka,

Thanks for posting in the Redgate forums. The below is currently what we have in regards to Always on Backups.

The simplest way to go about this in SQL Backup is to create 2 backup jobs on every one of the nodes:

- a full backup job
EXEC master..sqlbackup '-sql "BACKUP USER DATABASES TO DISK = [<AUTO>] WITH CHECK_PREFERRED_AG_REPLICA, SECONDARY_REPLICA_COPY_ONLY"'

- and a trx log backup job
EXEC master..sqlbackup '-sql "BACKUP LOGS [*] TO DISK = [<AUTO>] WITH CHECK_PREFERRED_AG_REPLICA, SECONDARY_REPLICA_COPY_ONLY"'

On SQL Server's side, you would need to set up the secondary replica's backup priorities so that only one of them will actually run the backup job.

What would happen is for full backups, if the primary replica is the preferred backup replica, a full backup will be made. If a secondary replica is the preferred replica, then a full backup will be made with the COPY_ONLY option added. For transaction log backups, the backup will only be taken off the preferred replica. For examples, see http://www.sqlbackuprestore.com/high-av ... suppor.htm

Points to note:
- the user can offload the backup tasks to the appropriate secondary replicas to lighten the load on the primary server

- new databases will be automatically picked up for backups, BUT there may be some errors at the beginning. The log backup jobs will probably run first, and will fail because there's no full backups made yet. FULLIFREQUIRED would probably not work too as the log backups would most likely be taken off a secondary replica, and the 'forced' backup doesn't know that it needs to use the COPY_ONLY option.

- if you are taking differentials off the primary replica, and all your full backups run off secondary replicas, you should still take a full backup off the primary occasionally to reset the base differential flag. Otherwise, your differential backups will grow over time as full backups taken off secondary replicas do not reset the base differential flag.

- if the replicas are on different subnets, then backups on the secondary replicas may fail if the database does not accept connections. This is because the SQL Backup has to connect to the primary replica to check database level permissions. Pls see https://msdn.microsoft.com/en-us/library/gg471494.aspx. SQL Backup doesn't support the MultiSubnetFailover option yet.

- naming the files - if you want be able to identify the backup files by cluster and availability group name, you can use the new naming convention tags - <CLUSTER> and <AVAILABILITYGROUP>. As the names imply, one tag is for the cluster name, and the other is for the availability group name. These tags actually fall back to <SERVER> and <INSTANCE> values if they are not valid for the current database.

E.g. you have an instance that contains a mix of regular and replica databases. If you named your files with the <CLUSTER> and <AVAILABILITYGROUP> tags and they had no fall back, then the regular databases' backup file names will have empty values for those tags. Or it might get even messier if you had to use <SERVER>_<INSTANCE>_<CLUSTER>_<AVAILABILITYGROUP> as part of the names.

With the fall back, the following 2 naming conventions will generate the same file names for 'regular' databases:

EXEC master..sqlbackup '-sql "backup database model to disk = [e: emp<cluster>_<availabilitygroup>_<database>.sqb] with init"'

EXEC master..sqlbackup '-sql "backup database model to disk = [e: emp<server>_<instance>_<database>.sqb] with init"'

For high-availability databases, the cluster and availability group names will be added accordingly.

Boka

Hi Dan
Thanks for your reply. Your post and SQLBackupRestore give clear view on SQLBackup and AG. I suggest amending SQLBackup Documentation (using explanation in post for two backup parameters as well as highlighting tags cluster and availabilitygroup) also including example from this site.
BTW, you wrote ".. backup log... with... secondary_replica_copy_only". Is that done by mistake or it's my misunderstanding that you can't backup log with copy_only.

Dan B

Hi Boka,

The Microsoft documentation says this about transaction log copy only backups:

Copy-only log backups (full recovery model and bulk-logged recovery model only)
A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create a new routine log backup (using WITH NORECOVERY) and use that backup together with any previous log backups that are required for the restore sequence. However, a copy-only log backup can sometimes be useful for performing an online restore. For an example of this, see Example: Online Restore of a Read-Write File (Full Recovery Model).
The transaction log is never truncated after a copy-only backup.

I believe you need this flag to ensure everything is maintained in the event of the primary and secondary failing over. Please do let me know if you have any further questions.

Boka

Hi Dan,
SQL Backup log command is clear to me. What make me confused is that running sp sqlbackup to backup log with copy only option (or setting this option in gui) is producing error!
Anyway, looking at example on sqlbackuprestore I created scenario like this: We have 2 servers in cluster with AG. Server A is primary and B is secondary read-only replica as well as preferred for backup. I created jobs on both servers, one for full backup using command:
EXEC master..sqlbackup '-sql "BACKUP DATABASES [a, b] TO DISK = [<AUTO>] WITH SECONDARY_REPLICA_COPY_ONLY, CHECK_PREFERRED_AG_REPLICA"'
and second for log like:
EXEC master..sqlbackup '-sql "BACKUP LOGS [a, b] TO DISK = [<AUTO>] WITH CHECK_PREFERRED_AG_REPLICA"'
Please reply if you find any concern about this scenario?