Backup for Always ON

Question

Post reply

Backup for Always ON

PJ_SQL

SSCertifiable

Points: 6703
More actions
December 18, 2019 at 5:54 pm

#3708861

Below is the Always On Set up in my environment:
One Primary and 2 secondary's.
Backup Preference : Primary 50%, 1st secondary 50%, 2nd secondary 50%.
Full and T-log backup ( Ola Hallengren’s Scripts) set up in Primary Node.
T-log is using this parameter "@OverrideBackupPreference='Y', so it can run on Primary\Secondary Node.
But, for full backup when the Listener is on Secondary node, it is doing anything.
Do I need to modify script for this parameter@OverrideBackupPreference='Y' for full backups?

Thanks in advance.
December 30, 2019 at 2:29 pm

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

I am not fully understand.

Which radio button you have on 'where should backup occur'?

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 2

Backup Preference "Prefer Secondary" is selected.

But ,for T-log I am able to run in any node adding this parameter ""@OverrideBackupPreference='Y' ".

Thanks.

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 3

Copy only full backup only allowed in Secondary.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 4

Here's my scenario:

I have only one Full backup job running on Primary Node.

When the Node fails to Secondary, backup job does nothing, but as suggested if I add "copy_only" parameter it works when in secondary node only. When the NODE fails back to Primary then using this parameter does not work.

Is it possible to have only one job on Primary for full backup and make it run no matter which Node it is on or will fail over to?

Or, do I need to create backup jobs on both Nodes and run it on both Nodes?

On Primary without "Copy_only" and secondary with "copy_only". How will this affect log backups which occurs on primary node and overrides backup preference, will it break log chain?

Thanks in advance.

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

You would need to look into the fn_hadr_is_primary_replica function, then based on the output from that either 1 or 0 run what you need to run.

For backing up on the primary, you would create the job identical on both servers and check if the replica is primary via returning 1 from the function, or if you want to backup from the secondary, switch it to 0 and run the backup from the secondary with the copy_only switch.

IF fn_hadr_is_primary_replica = 1 --I am primary

BEGIN

DO BACKUP

END

IF fn_hadr_is_primary_replica = 0 --I am secondary

BEGIN

DO BACKUP WITH COPY_ONLY

END

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 6

PJ_SQL wrote:

Here's my scenario:
I have only one Full backup job running on Primary Node.
When the Node fails to Secondary, backup job does nothing, but as suggested if I add "copy_only" parameter it works when in secondary node only. When the NODE fails back to Primary then using this parameter does not work.
Is it possible to have only one job on Primary for full backup and make it run no matter which Node it is on or will fail over to?
Or, do I need to create backup jobs on both Nodes and run it on both Nodes?
On Primary without "Copy_only" and secondary with "copy_only". How will this affect log backups which occurs on primary node and overrides backup preference, will it break log chain?
Thanks in advance.

Backup Preference "Prefer Secondary"

Ola maintenance is AG ware, it will clearly tell which is primary node and what is the backup preference etc.

Yes, you need to create jobs on both node and use @copyonly='y'

If you did not included copyonly on secondary, the script will not take full backup for "Prefer Secondary".

if you run without copy only on primary with "Prefer Secondary" you still get a full backup.

I suggest you to create test AG group and test your requirements.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 7

Thank you Anthony.

This built in function would require ('DBName') as well.

Since I have all the databases as part of AG, would it safe to assume that providing just one DBName on that function for backing up all user_databases.

DECLARE @is_prime bit;

SELECT @is_prime = sys.fn_hadr_is_primary_replica('DBname')

IF @is_prime = 1 --I am primary

BEGIN

EXECUTE [dbo].[DatabaseBackup]

@databases = 'user_databases',

@Directory = 'backuppath',

@BackupType = 'FULL'

END

IF @is_prime = 0 --I am secondary

BEGIN

EXECUTE [dbo].[DatabaseBackup]

@databases = 'user_databases',

@Directory = 'backuppath',

@BackupType = 'FULL',

@copyonly='Y'

END

Thanks.