May 10, 2019 at 11:24 pm
Hi,
I'm an accidental DBA at a very small company, and I'm sure I'm doing all sorts of things wrong. But I am running transaction log backups on our full recovery databases every 1/2 hour and full backups after hours. Not big databases, full backups take 1/2 hour for all.
We're switching servers and SAN and upgrading SQL Server to 2017 from 2008R2. We have a third-party to administer and maintain servers. The VM/SAN guy is pushing me **hard** to "get out of 1985" and stop using SQL Server backups. He claims the new Nimble SAN can take VSS snapshots every 1/2 hour and be much easier to recover, even to point-in-time. I asked about testing backups, which I do every couple weeks by restoring to our test system and making sure our main business application works. He did admit that they only test backups quarterly. But of course, the SAN is great and backups will be fine.
I've researched and it seems most people trust native backups over SAN or VM. My question is this: who's right? I'm being told that hardly anyone in the know is using SQL Server native backups. That's not what my research says, but, again...accidental DBA.
Any advice would be welcomed!
Thanks - Pat
May 11, 2019 at 1:39 pm
SAN backup is a backup of a server with everything on it.
SQL backup is a backup of a specific database.
SAN backup is a blunt and, I'd say' dumb, tool.
If you have several databases on your server, most of them would not be highly transactional, therefore backing them up every 1/2 hours would be a huge waste of resources.
On another hand, that one little database which takes thousands online requests per minute, might need much more frequent schedule than once in 30 minutes.
Another point is the restoration time.
If you've got a problem with a single 1GB database, then restoring a single corresponding SQL backup would be faster than even reading through the 10TB server backup.
And - how big is your tempdb database? It will be backed up every 1/2 hours as well. I hope you can think of a good use for those backlups.
But if your small company can afford all those overheads - SAN backups are simpler thing to do.
P.S. Ditch full backups every hour. Once a day should be enough. Unless your TRN backups are compatible in size with full backups.
_____________
Code for TallyGenerator
May 11, 2019 at 9:44 pm
To personify what Sergiy stated, I worked for a company that insisted that they didn't need native backups because they were doing "San Snapshots" every 20 minutes. I insisted that we continue to do native backups. I also know that nothing is guaranteed and that it's not backups that count... only restores count when you're in deep kimchi and so I also do nightly restores for the latest backups and I will have a tape recalled every now and then to make sure all of that actually works, as well.
To make a much longer story shorter, we did have a problem where we needed to restore some data from about a month prior to when the problem appeared. The "San Snapshots" included all days of the month except that about 10 days were missing and one of those 10 days just happened to be the date we needed the data from. It took about an hour to bring the tape on site and less than half an our to get the data we needed from a rather large database. The people that previously touted the "San Snapshots" so highly didn't even have a clue that they had a problem and it wasn't isolated to just the missing 10 days. It also turned out the data wasn't available on the DR site because of the snapshot failures.
You know that ol' saying about "Trust but verify"? Yeah... well screw that. If you're the DBA, trust no one with the data. You and your native backups are the final line of defense against data loss. If anyone ever forbids it, then make sure there's a rigorous nightly restore test and a regular random test from somewhere in the last 30 days for whatever method they chose. Also make sure that the RTOs and RPOs are reasonable. Regularly being exposed to possibly losing at least 20 minutes of data (like the San Snapshots) just doesn't cut it for me as an RPO.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2019 at 8:20 am
Verifying backups is another story. Well, it's kinda connected, but still another one.
It's possible to do robust verification of SAN backups as well as SQL backups.
So, I would not put the blame for not having it on the technology itself.
It's just rarely anybody does it, because of extreme costs of the procedure.
If they brag about the ability of making backups every 1/2 hour, then it's likely they would not be able to restore that backup on another server and verify its integrity within the next 1/2 hour. Most likely, not even within an hour or two.
Which means - the verification will fall behind and will be inevitably skipped/abandoned.
But even if the verification is established and done properly - what extra cost is incurred?
How many times more data stored in SAN backup comparing to SQL DB backups?
How much more space you need to allocate the snapshots?
But what rate it increases disk IO? 3,4,5 times?
If you use SSD, it directly translates to the next question - but what rate it shortens the life span of the drives?
How much more computing power is required for verifying the integrity of SAN backups (and the integrity of every DB file inside of that backup) in timely manner comparing to verifying SQL backups?
You mentioned, you're working for a small company - those extra expenses might not be that easy to ignore for them. And I'm sure you'd better see those funds directed towards your wages rather than to hardware maintenance.
_____________
Code for TallyGenerator
May 13, 2019 at 3:47 pm
Thanks for the validation, you guys are great!!
P.S. >>P.S. Ditch full backups every hour. Once a day should be enough. Unless your TRN backups are compatible in size with full backups.
Maybe I misspoke in my original post, I do them daily after close of business. Transaction log every 1/2 hour, thinking of maybe 15 minutes.....
May 13, 2019 at 3:51 pm
Closing this: post answers here: https://www.sqlservercentral.com/forums/topic/san-backups-vs-sql-server-native
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply