SAN backups vs SQL Server native

  • miapjp

    Right there with Babe

    Points: 777

    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

  • miapjp

    Right there with Babe

    Points: 777

    Sorry, moving to more appropriate forum (backups)

  • NicHopper

    SSCrazy Eights

    Points: 9034

    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

  • NicHopper

    SSCrazy Eights

    Points: 9034

    "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.

     

  • This was removed by the editor as SPAM

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32407

    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?

     

    • This reply was modified 1 year, 3 months ago by  Beatrix Kiddo.
  • oogibah

    SSC Eights!

    Points: 802

    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?

    • This reply was modified 1 year, 3 months ago by  oogibah.
    • This reply was modified 1 year, 3 months ago by  oogibah.
  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32407

    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).

  • miapjp

    Right there with Babe

    Points: 777

    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

  • Frank Ramage

    Valued Member

    Points: 57

    > 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 🙂

  • miapjp

    Right there with Babe

    Points: 777

    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.

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32407

    Frank Ramage wrote:

    > 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.

  • miapjp

    Right there with Babe

    Points: 777

    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.

  • Frank Ramage

    Valued Member

    Points: 57

    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

     

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32407

    miapjp wrote:

    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.

     

    SQL licensing.

Viewing 15 posts - 1 through 15 (of 15 total)

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