SQL Server Backup Solution? Pick Five. Or Six.

SQL Server arguably needs 5 or 6 of these things for backups:

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.

Las Vegas SQL Server User Group

Thanks, Jason and Stacia, for allowing me to present tonight!

Here are the materials from the presentation, including bonus favorite scripts/functions.

PowerShell: The Way of the DBA Dragon – presentation scripts and slide deck.

I used Start-Demo to play back the commands to avoid typing.

Did I commit that transaction?

“Hey, can you run this for me in prod?”

There are many reasons the wording in this request is a red flag/indicator of broken processes, but let’s put those aside for a moment.
The dev that asked you to run this query may have a legitimate reason.

Let’s say you check the estimated execution plan, and the query seems to be low-impact, and unlikely to cause blocking.  You run the query.  Yep, it only modified a few rows and ran in less than a second.  A few minutes later an alert indicates that the query has become a long running statement and is still active.

What the wha…?!?

It turns out there were two BEGIN TRAN statements in the t-sql, and a COMMIT was only executed once.  The transaction is still open – uncommitted!

In the future you can guard against this scenario by just checking @@TRANCOUNT after the commit and the time of the commit.  If @@TRANCOUNT has a non-zero value, a transaction is still open.

Placing the following two commented lines in SQLfile.sql or pasting the second line at the end of the query can help:

--BEGIN TRAN
[paste query to be executed here, and uncomment the above line if the query modifies data and lacks a BEGIN TRAN]

After successfully running the t-sql, commit the transaction with the below statement by selecting and executing the remainder of the line after the two dashes:

--COMMIT;SELECT @@TRANCOUNT [Open Transactions], Getdate() [timestamp]

A zero value in the Open Transactions column indicates this session has committed the transaction, and the timestamp lets you know when the transaction was committed, just in case you return to this session later on.

Identifying i/o Bottlenecks in SQL Server

In November of 2012 I presented to the Utah Valley SQL Server User Group on finding performance issues within SQL Server instances by focusing on storage and i/o for data and log files.

The Identifying i/o Bottlenecks slide deck is now available.


I used some slides from Wes Brown’s May 2011 SQL Rally presentation on Understanding Storage Systems and SQL Server, and included some PowerShell snippets.

There is a PowerShell script mentioned in a previous post I frequently use to view the storage used by a particular server referenced in the deck.