Backup Availability Group Database

Question

Post reply

Backup Availability Group Database

jayoub

SSCertifiable

Points: 6991
More actions
August 21, 2020 at 9:13 pm

#3782408
I am new to Availability Group, but not new to SQL Server and I want to set up a good FULL and DIFF backup of my SQL Databases
Currently we are using the Ola Hallengren SQL Database Backup script\process and I am finding it is not working well for us.
I want to create a maintenance plan that I can deploy on each node and backup all the database. More specifically, If the job runs on the primary node - I want it to backup all AG, local and system databases and if the job runs on a secondary node I want it to backup just the local user and system databases.
I understand that creating a maintenance plan for database backup on the primary and secondary server will work fine. It will backup all the databases. The problem is that on the secondary node, even though the databases are backed up, the job gives a failure result. When that happens SCOM will alert us and we do not need so many false positives.
First question. What can i do to make the job successful on the secondary node
What I tried to using a maintenance plan
I Added an TSQL task to first determine if the node is primary or secondary
If the node is primary a precedence constraint goes to the next task Backup all database - it works fine
if the node is secondary a precedence constraint goes to a different a TSQL task with a script to backup all none AG database.
My problem is getting it to work. I do not know if the SQL maintenance plan can work like an SSIS package and make these type of conditional logic decisions
Some of my trial include
A: I put a raiseerror in the script of my first task [check for primary] and my Precedence constraint is Primary success or Secondary Failure. Success goes to the database backup task and failure goes to the TSQL task script to backup all local databases. It actually worked, but a the end the job gives a failure result.
B: I am trying to use a Count(*) in the [check for primary] task - Primary = 1 and Secondary =0, but i cannot figure out how to get this to work with the precedence constraint. How to a get the count output to a variable [@VariableCount]? I see in the expression of the precedence constraint can put @variablecount ==1, or 0.
Any help is appreciated.
- This topic was modified 4 years, 3 months ago by jayoub.
- This topic was modified 4 years, 3 months ago by jayoub.
Jeff
August 26, 2020 at 3:36 am

This was removed by the editor as SPAM
August 26, 2020 at 9:48 pm

This was removed by the editor as SPAM

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

JMartDBA Grasshopper Points: 21 More actions · Answer 2

Hi jayoub, I'm curious what parameters you are using for the Ola Hallengren script, and what error you're getting? Also, is that the latest version of the script off github? If the error can be solved there, would that resolve your issue with the SCOM alerts?

jayoub SSCertifiable Points: 6991 More actions · Answer 3

Thank you very much for the reply.

I do not have the exact parameter information, but we are using USER_DATABASES, 28 days retention, compression and a few other basic settings - nothing crazy

The errors that we are getting is at the end of a huge message in the job history we get "Backup failed to complete". It just does not seem consistent and its very hard to figure out what servers are backing up and what are not especially when the result is failure and yet the databases are backed up.

We are likely not using the most current version since the scripts were implemented probably more then a year ago.

So trying to use a maintenance plan to determine if the node is primary or secondary and using conditional logic with the Precedence constraint just seems impossible. Trouble is that there is no information on what the properties of the Execute TSQL Task do - more specifically how to output to variable, so I have scrapped that idea

What I have decided is continue with the simple maintenance plan and just make the preferred backup = Primary. This way when I set up the backup job on all the nodes in the AG it will result in a successful backup. I do not really like using the primary for backups but there is nothing i can do at this point.

Any feedback is appreciated.

Jeff

JMartDBA Grasshopper Points: 21 More actions · Answer 4

I keep trying to reply back, but I get a message say "This was removed by the editor as SPAM." So I won't use links. If you review the updated Ola site, it has AG support for backup, index maintenance & check_dbs. Backups to secondaries cannot be done for FULL backups unless you use the COPY_ONLY option.

jayoub SSCertifiable Points: 6991 More actions · Answer 5

Thanks

Yes, since my FULL and DIFF backups must be on the primary anyway, I am planning to set the AG to Primary as the preferred backup and creating the jobs on all the nodes.

I tried it today and even on the secondary the jobs are completing successfully and what Is good is that on the secondary the jobs are backing up the none AG databases too.

The only drawback is now the TRN backups are also on the primary and this happens every 30 minutes, but there is noting I can do.

I did more research on using the execute t-SQL maintenance task and looks like it cannot output to variable. Only way is to use an SSIS package and I am not going to bother with that.

Thanks for your feedback.

Jeff