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?
 
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.
0
 
Eprs_AdminSystem ArchitectAuthor Commented:
How to make a backup from the DB which are on the SECONDARY node ?
0
 
Eprs_AdminSystem ArchitectAuthor Commented:
anybody here to answer this question ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Eprs_AdminSystem ArchitectAuthor Commented:
???
0
 
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.
0
 
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 ?
0
 
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.
0
 
Eprs_AdminSystem ArchitectAuthor Commented:
They told me on the Microsoft trainig....
But can you explain the setting in my picture ?

Microsoft writes : SECONDARY ONLY - ALL AUTOMATED BACKUPS FOR THIS AVAILABILITY GROUP MUST OCCUR ON THE SECONDARY REPLICA !!

So how it works now ?
I make my backups from the primary and internal the sql makes it from the secondary ?
0
 
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.
0
 
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.
0
 
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.

Cheers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.