Tuesday, February 19, 2019

SQL Server Backup Solution? Pick Five. Or Six.

SQL Server arguably needs 5 or 6 of these things for backups:
picture of bamboo rice steamer
When you see shapes that remind you of SQL Server...
  1. SQL native backup compression support (less data loss/lower RPOs)

    Please don't ever ask to disable native backup compression on SQL Server backups.  In my experience with SQL Server(which I'll admit is barely over a decade), as soon as a proprietary filter level driver, proprietary stored procedure, agent software, or Virtual Backup Device Interface(VDI) is introduced the feeling of confidence in your backups - and the ability to manage/test/quickly restore them - begins to fade.  I'm looking at you, forever incrementals, and buzzword solutions with zones and boosts.  Same with magic metadata/bitmaps/snapshots/clones - even if they have their own cmdlets.

    And what's the cost per GB?  And the ongoing cost?
  2. Encryption At Rest (because compliance)
  3. High Sequential I/O Throughput (less data loss/lower RPOs/lower RTOs[faster restores])

    Test your storage - can it sustain 1 GB(that's gigabyte) per second of throughput on backup or restore when watching the network traffic and local I/O on a given volume?  More?  How many physical interfaces are you using?  What type?  10gigabit?  VMs eventually map to physical network interfaces.  If/when the interfaces are saturated on a given SQL Server how does it affect the server performance?  Does SQL Server allow new connections at this point?  See number 6.

    What hardware is being used for your backup solutions?  Mechanical drives?  Why not flash instead?  Which CPUs?  How much memory?  How much read and write cache and on what media?  If a vendor isn't forthcoming with the hardware specs I see it as an indication 🚩 there is something to hide.
  4. Share/filesystem security (NTFS ACLs work)
    Not a big fan of IP whitelisting, as it's harder to protect your backups.  Your employer could cease to exist if data is exfiltrated - not just your job.
    Doraemon translates backupspeak.
  5. HA(clustered for patching / ~99.9% uptime or higher)
    What good is awesome throughput if the backup target has to be taken offline every few months or weeks to be patched?  Where will backups be written when the backup target is offline?
  6. Recommended: Dedicated VLAN/interface(s) for backup traffic
    It's reassuring to know that backups and restores aren't going to prevent a legitimate connection from being established to an instance of SQL Server.
  7. optional: Dedupe (to save space)
    Windows Server dedupe can give you a 3:1 ratio on SQL compressed backups.  Really.  I've seen this on a VLDB over time(and it was also heavily using index compression of the page variety).
  8. optional: Replication to another datacenter
    What happens when a fiber-seeking backhoe decides to take out a preferred datacenter for 36 hours?
It seems database backups often get lumped in with some other backup appliance until that backup appliance can't handle the load/storage capacity/dedupe.  How much are you paying for SQL Server?  Should it have its own dedicated backup solution?

Grant Fritchey has a great video(more of a public service announcement) about testing backups, and Chrissy LeMaire has a post on building a dedicated server for testing your backups.  Brent Ozar has a post from 2009 re: dedupe and backups that has been commented on for over 8.5 years.

1 comment:

Lars Rasmussen said...

Another item to consider - are RPOs affected by other business processes on the backup target?

Another way of saying this is do not treat a backup of a spreadsheet or a VM the same way you would treat a backup of your customer database. If a backup "appliance" is being used for other processes that are not database related control and concurrency / backup throughput is being surrendered to those other non-database processes.

Don't backup word docs and VMs to the same hardware being used to backup and restore your customer databases. It'll burn you when something goes wrong. And something - eventually - will go wrong.