May 10, 2019 at 7:51 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 10, 2019 at 11:23 pm
Sorry, moving to more appropriate forum (backups)
May 13, 2019 at 7:18 am
Hi,
I can only speak for experience but I've seen it happen where the backup software/service freezes the IO mid transaction, its a case of bad time but if you try and use that backup its not transitionally consistent and some instances wont restore or results in data loss. Making it utterly useless.
As a DBA (accidental or not) you and I need to ensure you can recover the database and log backups. For that reason I insist on doing native backups to a central file share, infrastructure then snapshot this. We then have an automated routine which asks infrastructures software for the backups from 7 days ago, these are then restored to a backup test server.
That's just my view, if your responsible for the backups then you need complete faith they will work, native ones have never let me down so I go with them and compression these days with them is great.
Any questions then just ask.
Thanks,
Nic
May 13, 2019 at 7:26 am
"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. ".
If he is willing to give you a written confirmation then that these will always be 100% crash consistent then ok but as I say I've seen these go wrong, all it takes it the following time line;
00:00 - VSS snapshot 1
00:05 - Transaction 1 opens but is not closed yet, its a big update lets say
00:06 - VSS snapshot 2 comes along and freezes the IO. Our 1 transaction is stuck open
00:07 0 Transaction 1 closes after the IO resumes.
Can you guys 100% state they can get you back to 00:06? and if so what will happen to the transaction?.
As I say its our job as DBA's to get the data back if needed. If they are insisting you use something your not sure about then insist that they put in writing its not your fault if the backup file is no good and cant be used.
Just my opinion but stick native, that keeps you in control of knowing your backups are good.
May 13, 2019 at 9:02 am
This was removed by the editor as SPAM
May 13, 2019 at 9:04 am
To echo what's said above, you need to be so careful with VSS snapshots where SQL databases are involved because a record that *looks* like a full database backup is written to the msdb backupset tables every time (because the SQL VSS Writer is invoked), so if you have any SQL Agent jobs running that rely on accurate backup history in msdb, it can throw things seriously out of whack. The most obvious example is if you are using Ola Hallengren's backup solution - and you should be :)- there is logic to check when the last full database backup was taken, and that date/time is used to determine which old backups files can be automatically deleted.
Long story short... where I am (City of London), all SQL Server VMs have crash-consistent snapshots and no SAN-level snapshots; compared to non-SQL VMs.
Which brings me to... native backups. Your VM/SAN admin is ill-informed and possibly shit-stirring. I'd say most SQL DBAs prefer to use native backups for all but the largest multi-terabyte databases. In most cases they work very well, and most importantly, they allow a DBA to have full control over their own restores. In an emergency do you really want to have to recover a whole VM/instance just because one database died?
May 13, 2019 at 1:56 pm
On top of what the others have mentioned here, I've had issues in the past of snapshots causing the backup chain to break .. not really sure what the deal is with this but as soon as I had the SAN guy stop taking snapshots our of SQL Servers entirely, we havent had any issues with backup chains.
Which, granted, wouldn't be an issue if you were ONLY doing SAN backups.. but ultimately if the SAN backup doesn't work and One of your databases is down, who is the company going to blame for the database not being able to be restored?
May 13, 2019 at 3:12 pm
That's a good point too, although it's only really an issue if you use differential backups in your environment. If you do, the SAN snapshots need to be copy-only (so they don't reset the differential bitmap).
May 13, 2019 at 3:49 pm
Thanks everyone! So glad I'm not on the wrong track here. Always good to get validation, especially because I don't know what I'm doing 80% of the time (I'd rather be doing developer stuff like digging around in data, creating reports for people, etc.. That's fun!)
--Pat
May 20, 2019 at 8:43 pm
> restoring to our test system and making sure our main business application works.
Any reason you don't do a DBCC on the test instance while you're there? Seems like a good idea to a fellow accidental DBA 🙂
May 20, 2019 at 8:49 pm
I suppose I could do one as soon as I restore - but there are over 20 databases for the software. I do have Ola Hallengren's solution doing DBCC every night ('cuz we were having some nasty warning errors). Database mail set up that would inform me. But, thanks, I might just do that to the "main" databases.
May 21, 2019 at 11:21 am
> restoring to our test system and making sure our main business application works.
Any reason you don't do a DBCC on the test instance while you're there? Seems like a good idea to a fellow accidental DBA 🙂
Bear in mind that this can cause you to fall foul of licensing restrictions if it's deemed that you are doing "Production offset" workload.
May 21, 2019 at 3:52 pm
Being primarily a developer, I don't know exactly which licensing you're speaking of. I do know, however, that our vendor OKs & recommends a test system, and we do have the necessary SQL Server license.
May 21, 2019 at 6:29 pm
I was thinking along the lines of running DBCC on the restored DB instead of on the live one; that would be a scenario where it wasn't possible to run it daily on the live database due to locking/performance issues.
If you're running daily on you production dbs anyway, it may not make as much sense.
Kudos on testing restores...It's not a backup unless it can be restored!
-frank
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply