Hi All,
Need some help on OLA Hallengren's backup stored procedure.
We have an 2-node ALWAYS ON setup (node1- primary , node2 - secondary)
The credential is setup with name "ProdBackupCredential" which has access key for storage account
We have 2 databases which are around 3.5TB in size and want to take a stripped backups with 8 files each.
SQL Version:
Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64) Feb 9 2021 12:08:50 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)
Requirement:
I want my backups to happen only on SECONDARY replica(node2 per say) not on PRIMARY.
For this, we already set the backup preferences as Prefer Secondary and weight as node1-50%,node2-50%
2 questions.
================
1. Which version of Ola Hallengren supports, Alwayson Secondary backups
2. Is there any option do I need to add in the below backup cmd.
(also, any node can be acting as primary or secondary based on failover scenarios)
I'll be creating same backup job on both nodes but my backup should only run on SECONDARY.
In that case, how does my command look like?? what options do I need to add in the below backup cmd?
--sample backup command
use master
go
EXECUTE dbo.DatabaseBackup
@databases = 'USER_DATABASES,db1,db2',
@URL = 'https://abcxyahskskskskskoopsstor.blob.core.windows.net/backups', --storage aant and container
@Credential = ProdBackupCred, --credential which we have created
@BackupType = 'FULL', -- want to take Full backup for db1 and db2
@Compress = 'Y', -- take compress backup
@NumberOfFiles = 8, --stripped backup
@verify = 'N' -- no need to verify this backup
go
Kind Regards,
Bob
November 22, 2022 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 22, 2022 at 10:47 pm
@databases = 'USER_DATABASES,db1,db2', -- USER_DATABASES will do all user db's including db1 and db2 - if your intention is to backup only db1 and db2 then your strings should be
@databases = 'db1,db2',
As you are backing up to a azure blob you need to see if you are using block or page blob and adjust other settings as well if using block blob. see https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver16 for details.
parameters to define are BLOCKSIZE and MAXTRANSFERSIZE. VERY important if using block storage.
note that if backup was to onprem san/local drive these should be set for sure with values that depend on your server and storage details.
November 25, 2022 at 9:43 pm
Hello @bobrooney.81
the solution to your problem is quite easy: Start thinking by your self. Then switch to https://ola.hallengren.com. There you find every necessary information about your problem.
But hey: It's far more convinient just to ask and not to read 😉
November 25, 2022 at 10:44 pm
Hello @bobrooney.81 the solution to your problem is quite easy: Start thinking by your self. Then switch to https://ola.hallengren.com. There you find every necessary information about your problem.
But hey: It's far more convinient just to ask and not to read 😉
the OP is already using Ola's scripts so pointing them to that is pointless.
but as you are so well versed on this maybe you can answer the core question of how they can force the backup to be executed on the secondary node of the AG
November 26, 2022 at 4:57 pm
Since backups on a secondary node must be copy_only backups - you have to make sure copy only is set. Once you do that - Ola's utility will look at the backup preference and the copy_only flag.
If backup preference is preferred secondary or secondary only and copy_only = 'Y' and that node is the preferred backup replica, then the backups will be performed on that node.
You can force a specific secondary node if you have multiple nodes by also setting the parameter @OverrideBackupPreference to 'Y', or you can manage which node is defined as the preferred backup replica by setting the backup preferences in the AG.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 28, 2022 at 3:28 pm
Since backups on a secondary node must be copy_only backups - you have to make sure copy only is set. Once you do that - Ola's utility will look at the backup preference and the copy_only flag.
If backup preference is preferred secondary or secondary only and copy_only = 'Y' and that node is the preferred backup replica, then the backups will be performed on that node.
You can force a specific secondary node if you have multiple nodes by also setting the parameter @OverrideBackupPreference to 'Y', or you can manage which node is defined as the preferred backup replica by setting the backup preferences in the AG.
Hi Jeffery,
When we are taking backups on secondary with COPY_ONLY='Y' option, and if we continue to take log backups on primary, in case of a disaster, can we restore the log backups in combination with COPY_ONLY full backups which I'll be taking on secondary?
Thanks,
Yes - transaction log backups are not dependent on anything in a full backup and can be applied to copy only or non-copy only full backups. What you cannot do is perform copy only full backups on a secondary - and then perform differential backups - which do rely on specific non-copy full backups for the differential.
BTW - you can also perform log backups from a secondary and Ola's utility will do that based on the backup preference setting for the AG.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 29, 2022 at 6:47 am
Thank you Jeffery. Will test it on my sub-prod env.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply