AlwaysOn Backups on Secondaries

Question

AlwaysOn Backups on Secondaries

SQLSACT

SSC-Insane

Points: 21838
More actions
August 3, 2017 at 6:09 am

#338561

Hi All
In an AlwaysOn environment, I understand that FULL backups can be offloaded onto my secondary replica and I believe that this is limited to copy_only backups.
I believe by testing that a differential backup cannot be restored to a database that was restored with a backup that was taken as copy_only.
My current strategy is weekly FULL's, daily DIFFS and hourly logs.
By offloading my FULL backups to my secondary replica, does this mean that my differential backups are now useless in a restore process?
Any help in understanding this would be appreciated.
Thanks

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Short answer: Yes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

GilaMonster - Thursday, August 3, 2017 7:10 AM
Short answer: Yes.

Thanks
What would you recommend if I were to offload full backups to the secondary replica? Increase the frequency in which full backups are taken then?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Why are you currently doing weekly fulls and daily diffs?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

GilaMonster - Thursday, August 3, 2017 7:38 AM
Why are you currently doing weekly fulls and daily diffs?

Mostly because of the performance hit we incur when running full backups

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

SQLSACT - Thursday, August 3, 2017 7:42 AM
GilaMonster - Thursday, August 3, 2017 7:38 AM
Why are you currently doing weekly fulls and daily diffs?
Mostly because of the performance hit we incur when running full backups

If you offload to the secondaries, you shouldn't have that problem (unless the IO subsystem is terrible), so less of a reason to use fulls and diffs.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

GilaMonster - Thursday, August 3, 2017 8:05 AM
SQLSACT - Thursday, August 3, 2017 7:42 AM
GilaMonster - Thursday, August 3, 2017 7:38 AM
Why are you currently doing weekly fulls and daily diffs?
Mostly because of the performance hit we incur when running full backups
If you offload to the secondaries, you shouldn't have that problem (unless the IO subsystem is terrible), so less of a reason to use fulls and diffs.

Thanks for your help