Recommendations for "phasing out" of local SQL backups for NetApp SNAPS

Question

Recommendations for "phasing out" of local SQL backups for NetApp SNAPS

Toddmonster85

SSC Veteran

Points: 222
More actions
January 10, 2019 at 10:44 am

#382586

We're having a debate regarding administration of SQL server backups.
Recently we've started using NetApp SnapCenter to manage clones and backups of our data, it's much faster than traditional backups and our SAN is part of our high availability backbone.
There's been a recommendation to implement SnapCenter on all our production instances and use it to feed our UAT and DEV environments, some of our team assumes this ultimately means - removing the need to run a traditional local backup.
I'm in the opposite camp, I think local backups are mission critical to a healthy SQL server. They serve as a canary in the coal mine if backup duration start running wild, especially important when you're also log shipping data.
Am I being too stubborn to think that local backups MUST be kept as part of a daily backup life cycle (either fulls or diffs) regardless of how much SNAP technology is implemented?
#LocalBAKS4life

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

For Production, NetApp SNAPs cannot do an individual databases point in time restore, can it?
if they can demo you can rollback to a specific point in time, for one database, i would consider it, but i am under the distinct impression that is not the case; it's basically a dbcc freeze plus a Differential outside of your control, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 2

Let anyone do anything in any way that they want to with backups... right after they prove that they can do a point in time restore as easily and quickly as you can with the native tools. If you haven't defined your Recovery Point Objective (how much data you're prepared to lose) and your Recovery Time Objective (how long it will take you to recover) with your business, do that first. Once that's done, any backup process that meets your RPO and RTO is fine. If it doesn't meet the RPO & RTO, why on earth would you use it, no matter how fast or easy it is?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 3

Toddmonster85 - Thursday, January 10, 2019 10:44 AM
We're having a debate regarding administration of SQL server backups.
Recently we've started using NetApp SnapCenter to manage clones and backups of our data, it's much faster than traditional backups and our SAN is part of our high availability backbone.
There's been a recommendation to implement SnapCenter on all our production instances and use it to feed our UAT and DEV environments, some of our team assumes this ultimately means - removing the need to run a traditional local backup.
I'm in the opposite camp, I think local backups are mission critical to a healthy SQL server. They serve as a canary in the coal mine if backup duration start running wild, especially important when you're also log shipping data.
Am I being too stubborn to think that local backups MUST be kept as part of a daily backup life cycle (either fulls or diffs) regardless of how much SNAP technology is implemented?
#LocalBAKS4life

Heh... that's what they said about the snapshotting they started doing when we installed Nimble SSDs. They started doing snapshots every 20 minutes and that supposedly satisfied the RPO and, because it's so nasty fast, also blew away the RTO. I was steadfast and stubborn and insisted that we continue traditional backups to disk and that those continue to be backed up to tape.

Fast forward almost 3 years and we had a little problem that dictated a restore to a non-production environment to recover some data. It really was a "shame" that the snapshots failed during the critical time and the only thing left was the restore from tape to disk and then disk to database.

NO ONE will ensure that data is recoverable like a DBA and their native backups (and frequent test restores) will. It's the final layer of safety that has been called upon more than once when all that cute shinny new stuff fails.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)