Backup SQL AlwaysOn DB´s

Hi Experts,

I have running SQL AlwaysOn with SQL2014.
I have two nodes and I want to setup my backup with BackupExec from the SECONDARY Node.
What are your best recommendations ?
Eprs_AdminSystem ArchitectAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eprs_AdminSystem ArchitectAuthor Commented:
How to make a backup from the DB which are on the SECONDARY node ?
Eprs_AdminSystem ArchitectAuthor Commented:
anybody here to answer this question ?
Eprs_AdminSystem ArchitectAuthor Commented:
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Vitor MontalvãoMSSQL Senior EngineerCommented:
From the secondary node you can't perform a full backup but Backup With Copy_only option.
This is an article from Symantec that explains it.
Eprs_AdminSystem ArchitectAuthor Commented:
Ok thanks.

That means I just can use BE on the primary node of my AG.
When I have configured the AG, I selected the maintenance and backup tasks should be done only on the secondary.
See the picture...

Secondary only...
And what does it means ?
SQL AG ships the task to the secondary ?
The advantage of the SQL AG in 2014 is to make maintenance on second node, they told me.
What do you think ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
The advantage of the SQL AG in 2014 is to make maintenance on second node, they told me.
Who are they? How can you perform something that isn't allowed in secondary replica?

The advantage of AG is to get ride of the single point of failure that storage can be and also allow read access to replicas.
Eprs_AdminSystem ArchitectAuthor Commented:
They told me on the Microsoft trainig....
But can you explain the setting in my picture ?


So how it works now ?
I make my backups from the primary and internal the sql makes it from the secondary ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't know where you got that statement from but in MSDN article you can see isn't true:

Backup Types Supported on Secondary Replicas
• BACKUP DATABASE  supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.

•Differential backups are not supported on secondary replicas.

• BACKUP LOG  supports only regular log backups (the COPY_ONLY option is not supported for log backups on secondary replicas).

A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).

•To back up a secondary database, a secondary replica must be able to communicate with the primary replica and must be SYNCHRONIZED or SYNCHRONIZING.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Only if they were talking about in running the backup jobs from secondary node but backups should be make on primary databases.
Eprs_AdminSystem ArchitectAuthor Commented:
ok I found out on the secondary I cannot make a backup.
I created all jobs on the primary and this works.
I wanted to do this all on the secondary because of performance issues, but this was more a joke.

Thanks a lot my friend.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I think that can be done (I never did) so I went for a search and found this article. It's a little bit more complex than regular backup jobs but I think is this you are searching for.
Even that maintenance tasks should be done in non working hours so performance shouldn't be an issue.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.