Backing up Availability Group Databases (ALways On)

SQL Server Administration
1

HI there.
SLQ 2016 ALways On is configured and the AV group shows:
Synchronous Commit
Failover mode: automatic
Where should backups occur? Prefer Secondary

SO do backups occur automatically? Or I assume I need to configure jobs to do the full, diff and log backups?
Thanks for shedding some light.

2

The default backup preference is secondary.
You can change it as per your requirement.
Backups don't occur automatically, there are multiple ways you can perform backups using SQL agent jobs, Maintenance plans, Ola Hallengren's script and backup tools like CommVault, veritas etc...
SQL Server Backup (hallengren.com)

1 Like
3

Thanks, Javeed. I may build a job like this and run it on both the primary and secondary servers:
Declare @DBNAME varchar(50)
set @DBNAME = 'MyDB'

-- run only if this is the secondary:
IF (sys.fn_hadr_backup_is_preferred_replica(@DBNAME) != 1)
BEGIN
backup database........
END

4

When sys.fn_hadr_backup_is_preferred_replica returns a value of 1, you want the backup to happen on that preferred replica..
There are lot of wonderful scripts already available on the internet and Ola's scripts are one of them which many people use.
When you start having 100's of databases you will find them really helpful.
Try to use them.

1 Like
5

Yes I already use Olas bkup scripts and I'm a fan. Thanks

6

@ahmeds08 When I run the Full backup on the preferred (secondary) replica, should I use the copy_only option? I will need to make TLOG backups here as well, correct? Should I use the copy_only option for TLOG backups?
But if I'm not backing up logs on the primary, the log size will grow correct? All I've read advises making backups only on the secondary replica, not on the primary. Thanks

7

You can only perform copy_only backups on a secondary - if you try to perform a backup from a secondary that is not set to copy_only it will fail.

You can perform normal log backups on the secondary. The AG will update the primary as needed to mark the transaction log so it can be reused. You do not want to perform copy_only log backups since those will not update the transaction log.

1 Like
8

Thanks, @jeffw8713

Where should backups occur? Prefer Secondary

Synchronous mode

Ola's script runs on the secondary instance:

EXECUTE [dbo].[DatabaseBackup]

@Databases = 'USER_DATABASES',

@Directory = 'E:\Databasebackups',

@BackupType = 'FULL',

@CopyOnly = 'Y',

@Verify = 'Y',

@CleanupTime = '180',

@CheckSum = 'Y',

@LogToTable = 'Y'

** If I specify CopyOnly = 'N' no bak file is generated. which confirms what you said.

? Do I need to make Fulls with CopyOnly AND Log backups? I assume so but will that give me recoverability protection?

9

Jobs must be created on all the replicas and they will be executed on all of them but the backup will be successful on the preferred replica.
Yes, a COPY_ONLY full backup with a normal log backup can be used incase you need to restore to point in time.