Welcome to another edition of T-SQL Tuesday! This month’s blog party post is hosted by Matthew McGiffen. He asks bloggers to write about anything related to SQL Server Encryption and Data Protection.

Security Theater

I’ll start this blog off by saying that I despise “Security Theater. ”

If you’re not familiar with the term, it “is the practice of taking security measures that are considered to provide the feeling of improved security while doing little or nothing to achieve it.” Essentially it is doing something simply for the show of it, to make people feel better, while providing little to no actual security value.

And for us data professionals, especially on the operations side, security and data protection are topics that should be top of mind for all of us.

Transparent Data Encryption – Not a Fan

Of all of the various data protection options available to us in SQL Server, I argue that Transparent Data Encryption (aka TDE) is worthless Security Theater.

TDE encrypts your data files “at rest.” This means one cannot crack open a hex editor and just start reading your raw data. And did you know that you can use a hex editor and start reading string values right out of your MDF files? Go search on that – that’s a separate tangent you can go explore.

So Andy, what’s wrong with that?

I feel that TDE is worthless because it is meant to protect against relatively narrow attack vectors. One attack vector TDE is meant to address is if someone physically walks into your data center, grabs a hard drive, and walks out. Because your data files are encrypted-at-rest, they cannot then go home, hook up said hard drive to a PC, and read your data off of it.

But who stores their SQL Server data on singular spinning hard drives anymore? Okay, there may be some people that still use Direct Attached Storage (DAS) instead of a SAN, but those are few and far between. With a SAN, depending on how your data is laid out, it’ll most likely be distributed across a bunch of physical storage media. So you’d have to waltz out with an entire SAN chassis, as a singular drive would only contain fragments. And if people are waltzing out of your data centers with entire SANs, you have a much bigger problem already.

Fine, but Andy, if an intruder gets access to my Windows machine and copies the data files elsewhere, that’s still a problem that TDE solves, right?

Not so fast… if someone has access to your Windows OS, you’re already screwed. Why? Because then an attacker than very easily exfiltrate your Service Master Key from SQL Server. Once they have that, then can then use that key plus your copied data files and decrypt/open those data files on another SQL Server at their leisure.

I’m not going to link to the piece, but there is a piece out there that showcases all of this, with the exact script one needs. It’s really disgustingly easy.

And I haven’t even noted the trade-offs of TDE, like performance penalties and backup compression consequences.

Not a Sponsored Message – What about Pure?

I should note that all of the above is just my personal opinion. Yes I happen to work for a storage company now and TDE is especially pointless in my world. Why? Because Pure Storage ALREADY encrypts-at-rest all data that is written to our Direct Flash Modules!

The other reason why I dislike TDE, from a Pure Storage context, is that it also inhibits our storage array’s data reduction capabilities. If you’re writing encrypted randomized data down to a data-reducing array like FlashArray, there’s little that can be done with randomized data to data reduce or compress it – simple as that. So if your primary storage is a data reducing array like a FlashArray, you’ve just eliminated one of your primary cost savings… all for nothing.

What Should You Do Instead?

If you really need to have encrypted data, encrypt it WITHIN the application (like within SQL Server). Store your PII using column-level encryption methods. That has actual security value.

And if you are really concerned about security, you need to do a true threat assessment and figure out what attack vectors you want to protect from. Because after all, if someone is already on your Windows OS or physically in your data center, you’ve already failed hard at security and TDE isn’t going to save your butt.

Thanks for reading.

Welcome back to Part 4 of my Backup Internals series. Today, I’d like to spend a little time exploring backup compression.

When you take a regular FULL BACKUP, SQL Server is literally taking a byte-for-byte copy of your data files. Don’t believe me? Then go read this, then come back. Additionally, Microsoft architected BACKUP operations such that the resource utilization and impact would be minimal (when using default parameters).

Blog Series Table of Contents

Back in the Day

Back in the days of my youth, storage cost a heck of a lot more per megabyte (hahaha). And backups were extra copies of your data that had to live somewhere. So who would not want their backups to consume less (expensive) storage? But alas, Microsoft did not offer built-in BACKUP compression with SQL Server in its earlier days. Instead, data professionals had to resort to 3rd party products like Litespeed to have compressed backups. Investing in a software solution often outweighed the extra storage costs of an uncompressed backup, so it was a sensible expenditure. Unfortunately for backup software vendors, Microsoft introduced native backup compression in 2008.

Costs of Backup Compression

Regardless of how one does compression, it comes at a price. Compression reads and manipulates ALL OF YOUR DATA to make it smaller, which requires CPU and memory resources. The benefit is that SQL Server now has less data to send over the wire to your backup target, reducing required storage and (usually) overall execution time. And this (usually) made both DBAs and storage admins happy.

In our last two blog posts, we explored a BACKUP operation’s RAM and CPU worker thread utilization. But what happens when you turn on compression?

The answer for RAM and backup buffers is easy – compression requires 3x whatever backup buffers you were using already. So if a regular BACKUP operation needed 10MB of RAM for backup buffers, turning on compression will now use 30MB of RAM. The buffers themselves do not get larger, rather the number of “Sets of Buffers” goes from 1 to 3.

Digging Deeper: Compression and CPU Utilization

When utilizing compression, most of us know full well that SQL Server burns a lot more CPU. But how much CPU? Does it just burn more cycles on the same Reader & Writer threads that were invoked as normal? Or does it invoke additional threads to do compression work?

To dig a bit deeper, I turned to my good friend Sysinternals’ Process Explorer. If you’re not familiar with it, it’s essentially Task Explorer on steroids. One awesome thing you can do with it is right click a given process/.exe, go to Properties, and see Thread information!

Using this, I can at least see the number of threads that are actively using CPU at any given time. Here’s a baseline shot showing an otherwise idle SQL Server

Note that there are threads “using” CPU, but they’re all marked “< 0.01”, so for purposes of this simple analysis, we’ll consider them idle.

Next, this is what the output looks like when I execute a BACKUP operation, whose files are on a single volume, against a single backup target, with no compression:

Note that there are two threads (ThreadID or TID): 10636 & 5176, showing CPU utilization values that are not “< 0.01”. That’s indicative of my 1 Reader thread and 1 Writer threads for my BACKUP operation.

Now as a different control, I executed a BACKUP whose data files are on 4 separate volumes, against 1 backup target, with no compression.

Note here that I see activity on 5 threads: 4 reader threads and 1 writer thread.

Finally, here’s a BACKUP run with a single volume, single backup target, but with compression turned on.

That’s a heck of a bunch of threads that are not idle – over 20 in this screenshot!

How Many CPU Threads Does Compression Need?

I’ll come right out and say it; I’m not certain. Here’s the thing about the above observation methodology. It updates once per second and while it’s insightful, it’s not bulletproof.

For one, I cannot confirm with absolute certainty, that the given threads are related to the BACKUP operation. In fact, for my single volume, single backup target control, I’d occasionally get a sampling that would have a 3rd thread pop up with some activity, but only for a single snapshot. In multi-volume and/or multi-backup target examples, the number of threads active at any one time would occasionally drop below what I would expect. I attribute that to a CPU thread having to wait for a resource at that precise moment in time.

If I wanted to get deeper, I suspect I’d have to go attach a debugger to SQL Server and go digging to find the actual discrete calls that are invoking additional worker threads. But for the purposes of this blog, I’m content with the above observation that when using compression, many more CPU threads get spun up.

Compression: Yay or Nay?

As we’ve seen, Backup Compression requires more resources – CPU and memory. And unless someone else is willing to fill in the gaps, I cannot tell you the algorithm/methodology to better predict CPU thread utilization when it comes to utilizing Compression. But I would also argue that one should not let that deter you either.

In the end, everything is all about trade-offs… which is what Part 5 of this series will be all about – performance trade offs of using all of these different backup parameters.

Thanks for reading!