Re-Thinking What We’ve Known About Storage

TL;DR

I had the honor of recording not one, not two, but THREE lightboard videos for Pure Storage! And they’re up on YouTube for you to enjoy!

• How Volumes work on FlashArray
• How do snapshots make your life easier?
• Rapid recovery using FlashBlade

For today’s blog post, I want to focus on the subject matter of the first Lightboard video: How Volumes work on FlashArray.

Storage is Simple, Right?

For my many years as a SQL Server database developer and administrator, I always thought rather simplistically of storage. I had a working knowledge of how spinning media worked, and basic SAN & RAID architecture knowledge from a high level. And then flash media came along and I recall learning about its differences and nuances.

But fundamentally, storage still remained a simplistic matter in my mind – it was the physical location to write your data. Frankly, I never thought about how storage and a SAN could offer much much more than simply that.

A Legacy of Spinning Platters

Many of us, myself included, grew up with spinning platters as our primary storage media. Over the early years, engineers have come up with a variety of creative ways to squeeze out better performance. One progression was to move from one single disk to many disks working together collectively in a SAN. That enabled us to stripe or “parallelize” a given workload across many disks rather than just be stuck with the physical performance constraints of a single disk.

Carve It Up

In the above simplified example, we have a SAN with 16 disks. And let’s say that each disk gives us 1,000 IOPs @ 4kb. I have a SQL Server whose workload needs 4,000 IOPs for my data files and 6,000 IOPs for my transaction log. So I would have to create two volumes containing the appropriate number of disks from the SAN to give me the performance characteristics that I require for my workload. (Remember, this is a SIMPLIFIED diagram to illustrate the general point)

Now imagine being a SAN admin trying to have to juggle hundreds of volumes across dozens of attached servers, each with their own performance demands. Not only is that a huge challenge to keep organized, but it’s highly unlikely that every server will have their performance demands met, given the finite number of disks available. What a headache, right?

But what if we were no longer limited by the constraints presented by spinning platters? Can we approach this differently?

Letting Go Of What We Once Knew

One thing that can be a challenge for many technologists, myself especially, is letting go of old practices. Oftentimes those practices were learned a very hard way, so we want to make sure we never have go through whatever rough times again. Even when we’re presented with new technology, we often just stick to the “tried and true” way of doing certain things, because we know it works.

One of things “tried and true” things we can revisit with Pure Storage and FlashArray is the headache of carving up a SAN to get specific performance characteristics for our volumes. When Pure Storage first came to be, they focused solely on all-flash storage. As such, they were not tied to legacy spinning disk paradigms and could dream up new ways of doing things that suited flash storage media.

Abstractions For The Win

On FlashArray, a volume is not a subset or physical allocation of storage media assigned to it. Instead, a volume on FlashArray is just a collection of pointers to wherever the data wound up being landed.

Silly analogy: pretend you’re boarding a plane. On a traditional airline, typically first class boards first and goes to first class, then premium economy passengers go board to their section, then regular economy boards and go to their section, and basic economy finally boards and goes to theirs. But if you were on Southwest Airlines, you can choose your own seat. So you’d board, and simply go wherever you wish (and pretend you report back that you’ve taken a particular seat to an employee). Legacy storage is like that traditional airline where you (data) were limited to sit down in to your respective seat class, because that’s how the airplane was pre-allocated. But on FlashArray, you’re not limited in that way and can simply sit where you like, because you (data) have access to sit anywhere.

Another way of describing it that might resonate is that legacy storage assigned disk storage to a volume and whatever data landed on that volume landed on the corresponding assigned disk. On FlashArray, the data can be landed anywhere on the entire array, and the volume that the data was written to simply stores a pointer to wherever the data wound up on the array.

Fundamental Changes Make a Difference

This key fundamental change in how FlashArray stores your data, opens up a huge realm of other interesting capabilities that were either not possible or much more difficult to accomplish on spinning platters. I like to describe it as software-enhanced storage, because there’s many things we’re doing besides just “writing your data to disk” on the software layer. In fact, we’re not quite writing your raw data to disk… there’s an element of “pre-processing” that takes place. But that’s another blog for another day.

Take 3 Minutes for a Good Laugh

If you want to watch me draw some diagrams on a lightboard that illustrate all of this, then please go watch How Volumes work on FlashArray. It’s only a few minutes long and listening to me on 2x is quite entertaining in of itself. Just be sure to re-watch it to actually listen to the content, because I’m guarantee you’ll be laughing your ass at me chattering at 2x speed. 🙂

Thanks for reading!

A Newbie’s Journey into Docker, Grafana, and Prometheus

I’ll admit this up front – there’s a ton of awesome technologies out there that I’ve had my eye on, learned a little bit about, and have hardly touched since. Docker is one of those technologies… along with Grafana. Well conveniently enough for me, Anthony Nocentino just wrote a blog post on Monitoring with the Pure Storage FlashArray OpenMetrics Exporter. And this monitoring solution uses both. And best of all, it’s actually quite easy to implement – even for a clueless rookie like me!

Recap of Andy Stumbling Along…

Ages ago, I had attended an introductory session or two on Docker, and read some random blogs about it, but otherwise not really messed with it too much beyond a few examples. So I thought I’d just take the quick and dirty route, went into my team lab, and installed Docker Desktop for Windows on a random Windows VM of mine. And while I waited… and waited… and waited for the installation to run, let me go on a slight tangent here.

Tangent – Where To Run Docker From

TL;DR – Docker on Windows is a lousy experience. I expected it to “run okay for a dev box.” Nope, it was worse than that. Run it from a Linux machine if you can – you’ll be much happier.

Slightly longer “why.” Underneath the covers, Docker is essentially Linux. So to run it on Windows, you need to have Hyper-V running, essentially adding virtualization layer. And if you’re silly like me, you’ll do all of this on a Windows machine that’s really a VMware VM… so yay, nested virtualization = mediocre perf!

In my case, after rebooting my VM, Docker failed to start with a lovely “The Virtual Machine Management Service failed to start the virtual machine ‘DockerDesktopVM‘ because one of the Hyper-V components is not running” error. Some quick Google-fu revealed that I had to go into vSphere and on my VM, adjust a CPU setting for Hardware virtualization: Expose hardware assisted virtualization to the guest OS.

Three reboots later, and I finally had Docker for Windows running. Learn from lazy Andy… it would have been faster for me to just spin up a Linux VM and get docker installed and running.

Let’s Monitor a Pure FlashArray!

At this point, I could start with Anthony’s Getting Started instructions. That was super easy at least – Anthony outlined everything that I needed to do.

I did encounter another error after I ran ‘docker-compose up –detach’ for the first time: ‘Error response from daemon: user declined directory sharing‘. That one involved another Docker setting about file sharing. Once I changed that, it errored again, because I failed to restart Docker – doh! At least I didn’t have to reboot my VM again?

So finally I ran ‘docker-compose up –detach’ and stuff started appearing in my terminal – yay! I immediately went to the next step of opening a browser and got a browser error. WHUT?!? I thought something was broken, because Docker was “doing” something. But the reality is that prometheus, grafana, and the exporter, all had to do some stuff before the dashboard was up and available. Several more minutes later, I had a working Grafana and dashboard of my Pure FlashArray – yay!

Take the Time to Try Something New

All of the above took maybe a half hour at most? And a chunk of that was waiting around for stuff to complete, and other time was burned resolving the two errors I encountered. So not a huge time investment to stand up something that is really useful to monitor if you don’t have monitoring tools in place already.

But most importantly, this little experience was gratifying. It felt good to try something new again and to be able to stand this up pretty quickly and fairly painlessly. And if you don’t repeat my mistakes above, you can get your own monitoring operational even faster!

Thanks for reading!

A Peek Inside a SQL Server Backup File

Confessions First

In all of my years working with SQL Server, I never really thought about the actual contents of a SQL Server backup file. Sure, it contains data from a given database, but despite my love of storage engine internals, backup file internals is not something I’ve ever had any interest in looking into.

Until now…

The Premise

This first came up during my onboarding with Pure Storage. Anthony Nocentino (b|t) taught me that a SQL Server backup file is a byte-for-byte copy of your data, as materialized in SQL Server MDF files (assuming no backup compression or backup encryption). And that would make sense – how else would SQL Server store a copy of your data in a backup file? It does not make sense for SQL Server to alter your data when it writes it down to a backup file (again, with NO backup compression/encryption) – that’s a waste of compute and effort.

Well, I had a conversation with someone who was unclear about that assertion. I tried some Google-fu to present some supporting materials, but could not actually find any documentation, official or otherwise, to back it up. So here we are.

Why Do You Even Care?

There’s a bit of Pure Storage related background here, so feel free to skip this section if you don’t care about why I’m writing this.

On FlashArray, we de-duplicate your data behind the scenes across the entire array. So if you had three SQL Servers (Prod, QA, Dev) all attached to a given FlashArray, and each instance had an identical copy of AdventureWorks, it would almost completely dedupe down to one copy on FlashArray.

Along those lines, a single database will have many places where deduplication can also occur within it. Think about how much repetition occurs within a stereotypical dataset. Things like dates, product IDs, product category IDs, etc. are all duplicated throughout a typical dataset, thus ripe for FlashArray to dedupe within your data file too.

But much like the data that resides in each of our databases, there’s a great degree of variability too. You may have a database where practically everything is unique. You may have a database that stores binary data. The list goes on and on. So while we see a certain average deduplication ratio with SQL Server databases, that’s AVERAGE. And often our customers want to know what THEIR database will yield.

And this is where a backup file comes into play.

One trick that Anthony taught me is to provision an empty volume on FlashArray and take a single uncompressed, unencrypted backup of your database and stick the file there. Because the backup file contains a byte-for-byte copy of your data, as materialized in your MDF/NDF files, its dedupe yield will be very close to that of your MDF/NDF files.

Great way to test, huh? Unfortunately the individual I was speaking with was not confident about the underlying byte-for-byte composition of a backup file. So I decided to test, validate, and document it!

Testing Setup

Using SQL Server 2017, I created a simple database with a single table and inserted some data.



Then I created an uncompressed, unencrypted backup file. Finally, I shut down SQL Server’s service and copied the MDF and BAK to another location to begin analysis.

All code I used can be found here: https://github.com/SQLBek/PureStorage/tree/main/backup_test

So What’s Inside?

To quickly analyze differences, I found a cool piece of software called Beyond Compare that has a “Hex Compare” feature – perfect for binary file comparison!


Click for high-res

To give you a quick overview, the left sidebar shows an overview of the two files, with red lines/blocks to designate some kind of difference in the file. In the example screenshot, the left is the MDF file and the right panel is the backup file. This is the beginning of each file, so you can see that there are some differences present here.

Why Is More Than Half Red?!

However, look closer at the sidebar. The first half has very few differences. But what about that second half that’s ALL RED?


Click for high-res

At least that answer is easy. All of those 00’s is simply extra empty space that has been padded at the end of the MDF file. And because it has nothing, it has been omitted from the backup file. I could have truncated the data file first, but I kept this here to illustrate that one’s data file may be larger than the backup file due to this nuance.

Okay, Let’s See the Data

Click for high-res

As for the data itself, that’s present in the 2nd quarter of the MDF file or final 3rd of the backup file. And you can see from this screenshot that the backup file is in fact a byte-for-byte copy of the MDF file!

Takeaways

First, I hope that this is enough to prove that data in a database are re-materlized byte-for-byte in a backup file. Sure, there’s some differences in other metadata, but what I care about in this exercise is whether the data itself is identical, which it is.

Second, if you are still in doubt, I’ve published everything to my github here. If you look inside backup_test.sql, you’ll find some extra code in the form of DBCC IND and DBCC PAGE commands. Instead of searching for data, try using DBCC IND and find a different data structure like an IAM page. Then use DBCC PAGE to look at the raw contents and use the hex editor to search for the matching binary data in both the MDF and backup file. I did that myself and found it cool that those underlying supporting pages are also materialized identically.

Third, if you see a hole or gap with this analysis, please let me know in the comments. I did this to learn and validate things for myself, and I definitely want to know if I made a goof somewhere!

Finally, I hope you enjoyed this and stay curious.

Thanks for reading!