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.
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)
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
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.
Yes I already use Olas bkup scripts and I'm a fan. Thanks
@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
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.
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?
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.
That helps, @ahmeds08, I appreciate your time.
This is the script I plan to use on BOTH primary and secondary:
It runs Olas backup script.
declare @DBNAME varchar(50)
set @DBNAME = 'MyDB'
IF (sys.fn_hadr_backup_is_preferred_replica(@DBNAME) != 1)
PRINT 'not the preferred replica dont backup'
ELSE
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'MyDB',
@Directory = 'E:\Databasebackups',
@BackupType = 'FULL',
@CopyOnly = 'Y',
@Verify = 'Y',
@CleanupTime = '180',
@CheckSum = 'Y',
@LogToTable = 'Y';
If you look at the documentation for Ola's utility - you will find that it already uses the AG backup preference setting.
In other words, you don't need to check for preferred replica - the utility will do that for you. See here: SQL Server Maintenance Frequently Asked Questions
BTW - consider creating a separate system that is not in the AG and using SQL Server Agent on that system as a master agent.
If you do that, you can ensure any agent jobs that need to be on all nodes in the AG will be pushed by the master agent to the defined agent targets. This will also ensure all agent jobs that need to be setup/configured on each node are setup and configured exactly the same.