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!
New Stars of Data has had a tremendous impact on our technical community, helping to grow new speakers. But the task of finding, encouraging, and mentoring new speakers is always ongoing. To that end, wanted to share some thoughts for #NewStarNovember.
Let’s Do the Time Warp Again!
Because I got my start speaking through the encouragement of others, growing new speakers became a passion of mine as well. In late 2016, I put my money where my mouth is, with this T-SQL Tuesday: Growing New Speakers blog. I challenged prospective new speakers to blog about their ideas and existing speakers to share their experiences and wisdom. And to really drive home how serious I was, I openly offered mentorship to any new speaker who wanted it.
Then and Now
The round-up to that call was tremendous. If nothing else, I would encourage you to revisit that roundup and look over the names of the people who were categorized as New or Novice speakers. And consider where many of them are today!
I don’t know about you, but I think that’s absolutely amazing. We speakers often say things like “speaking opens doors” and other sayings like that. I’d strongly argue that this list of people, looking at them then and now, is solid proof that speaking and presenting pays off exponentially.
Call to Action
Are you thinking about getting into speaking? Or are you relatively new to speaking but want to keep growing that skill? I would like to repeat my offer from 2016 and offer mentorship once again.
If you’ve never presented before and take me up on this challenge I will take the time to work with you.
• If you want help developing your first presentation out, I will help you. • If you are wary of putting your first PowerPoint together, I will help you. • If you need ideas on how to write demo scripts, I will help you.
I will do whatever I can to help you begin this journey. But it’s up to you to take that first step. Trust me (and others) when we all say that it’s absolutely worth it!
Welcome to another edition of T-SQL Tuesday! This month’s edition is hosted by Tom Zika (b|t) and Tom wants to know what makes “code production-ready?”
Measure Twice, Cut Once…
I still remember learning early on my career, the mantra of “Measure Twice, Cut Once.” It’s a important lesson that I’ve had to be re-taught periodically over my career, sometimes in a rather stressful or painful manner. But in the unforgiving realm of code, that is quite literal if you think about it, accuracy is paramount. We should always thoroughly test our code before it gets to Production.
No One Develops in Production, Right? RIGHT?
On the hand, I’ve also had more than a handful of occasions where Production is broken in a BAD WAY, and you have to FIX IT NOW. I remember when I worked in the financial commercial credit card industry, where our post-deployment QA smoke testing would take anywhere from 8-12 hours, and that’s AFTER a minimum 4 hour deployment. We rolled out a big release on Friday night, QA started their work in the wee hours of the morning Saturday and we found a nasty breaking bug that only manifested with Production data in play. Our app devs were trying to figure out how they could fix the code, but even the effort of committing, integrating, and cutting a new build was a multi-hour affair. In the meantime, we database developers were trying to figure out if there was a viable SQL Server/stored procedure workaround that could be implemented to allow us to not have to rollback everything.
I was the db dev lead on this particular release and my gut told me that there had to be a workaround – I just had to iron it out. I requested 90 minutes of “do not bother me NO MATTER WHAT,” focused, and 90 minutes (and one interruption) later, I had successfully coded a workaround fix. But I also committed a cardinal sin – I developed in Production.
Was that Production Ready? Well, QA did spend a few hours testing it and validated it. But was it subject to the full battery of integration and performance tests that our application would typically go through? No… but we had little choice and in this case, it worked. Funny thing is that workaround fix, like many thing things that were never meant to be permanent, remained permanent.
Is There a Lesson to be Learned Here?
If I had to share one key takeaway, is that I believe it is critical that every business have a true “Prod + 1” environment. This means having a second FULL COPY of Production that’s refreshed on a regular basis, into which “next builds” are installed for testing. Unfortunately, this is not an easy or inexpensive task. Fortunately, there’s many more solutions available these days (like a certain orange organization I happen to know) that make it far more feasible too.
One of the many things I speak about regularly at Pure Storage is using storage-array based snapshots to create crash consistent snapshots of SQL Server data & log files. But what if one of my databases is using In-Memory OLTP?
Can I still take storage-array snapshots and if yes, will I lose data in my memory-optimized tables? What about data inside my non-durable tables?
Yes, you can take storage-array level crash consistent snapshots on FlashArray and data in your memory-optimized tables will remain intact in all volumes cloned from the snapshot.
How Does All Of This Work?
In-Memory OLTP is all about storing your data in RAM. However, there are two different types of table constructs: memory-optimized tables and non-durable tables.
Memory-optimized tables have a secondary copy of their contents stored on disk, but only for the case of server crashes.
Non-durable tables are also memory-optimized tables. However, they differ in that they are defined with their DURABILITY property set to SCHEMA_ONLY. This means the structure of the table is persisted to disk, but never the underlying data.
Because of the need for durability, we can still take storage-array level snapshots that are crash consistent, and use those snapshots for various purposes like dev/test database refreshes. We just would not get the ephemeral data in the non-durable tables, but that’s no different than not getting data in temp tables or table variables.
And remember that crash consistent snapshots differ from application consistent snapshots. Most of us SQL Server professionals are familiar with the latter, that have to go through VSS and stun SQL Server. Crash consistent snapshots do not stun the server though there’s other trade-offs (I ought to just write a blog breaking that difference down, shouldn’t I?).
Have a Demo?
At some point in the future, I’ll make a video recording of me doing this demo, which I’ll post to YouTube and link to here.
I’ve taken the liberty of creating some scripts that will help demonstrate this. This specific example is for two SQL Server instances on VMware, with FlashArray behind the scenes, using vVols. The example database, AdventureWorks_EXT, has the data and log files all on the same vVol for demo simplicity.
1_SETUP – SourceSvr – AdvWrks.sql Restores a copy of AdventureWorks on the SOURCE server. Get a backup file here. Then adds memory-optimized objects and data, using sample code from here.
2_SETUP – TargetSvr – AdvWrks.sql Creates a stub of AdventureWorks on the TARGET server. Was generated from SSMS using ‘Script Database as CREATE’ on the SOURCE server’s copy of AdventureWorks. You can substitute your own instead of running this script.
3_DEMO – SourceSvr – AdvWrks.sql First step of the actual demo, which executes the in-memory OLTP sample code to create some durable and non-durable data.
4_DEMO – PowerShell Snapshots.ps1 Second step of the actual demo, that uses PowerShell to take a crash consistent snapshot of the Source vVol and overlay the Target vVol.
5_DEMO – TargetSvr – AdvWrks.sql Final step of the demo, to query the memory-optimized and non-durable tables on the Target, to see if the data created in ‘3_DEMO – SourceSvr – AdvWrks’ was replicated to the Target server.
One More Caveat
If you also use delayed durability, note that transactions that have not yet been hardened will also not be present in a crash consistent snapshot. It is a trade-off of delayed durability, but is no different than losing a non-hardened transaction with a SQL Server crash/failure.
One of the amazing aspects of my job at Pure Storage, is that I get opportunities to work with new and emerging tech, oftentimes before it is available to the general public. Such is the case with SQL Server 2022, where I got to help test QAT backups for SQL Server 2022.
Using QAT compression for your native SQL Server backups will give you better compression with less CPU overhead, than “legacy” compression. So I get smaller backup files, faster, with less CPU burn. What’s there to not like?
Tell Me More About This Q… A… T… Thing!
So QAT stands for Intel’s Quick Assist Technology, which is a hardware accelerator for compression. It’s actually been around for many years, but most regular folks like myself never got exposed to it, because you need a QAT expansion card in your server to even have access to its powers. And for us SQL Server folks, we had nothing that took advantage of QuickAssist Technology… until now thanks to SQL Server 2022.
In SQL Server 2022, Microsoft has introduced QAT support for Backup Compression. And as I demonstrated in this blog post, your backup files are essentially byte-for-byte copies of your data files (when not using compression or encryption). And I don’t know about you and your databases, but the SQL Server environments I see these days, database sizes continue to grow and grow and grow… so I hope you use compression to save backup time and space!
But I Don’t Have QAT Cards In My SQL Servers
I said earlier that QAT has been around for a number of years, available as expansion cards. But because SQL Server had no hooks to use QAT, I strongly doubt that any of us splurged for QAT cards to be added into our SQL Servers. But there’s two things coming that’ll change all of that…
First, SQL Server 2022 has both QAT hardware support AND QAT software emulation. This means you can leverage QAT goodness WITHOUT a QAT expansion card.
Second, the next generation of Intel server processors will have QAT hardware support built in! So the next time you do a hardware refresh, and you buy the next gen of Intel server CPUs, you’ll have QAT support!
Third, if you cannot get the latest snazzy CPUs in your next hardware refresh, QAT cards are CHEAP. Like, less than $1k, just put it on a corporate charge card cheap.
IMPORTANT – QAT hardware support is an Enterprise Edition feature. But you can use QAT software mode with Standard Edition. And if you stay tuned, you’ll come to find that I’ve become a big fan of QAT software mode.
How’d You Test This Andy?
In my team’s lab, we have some older hardware lying around that I was able to leverage to test this out. Microsoft sent us an Intel 8970 QAT card, which we installed into one of our bare metal SQL Servers, an older Dell R720 with 2x Xeon E5-2697 CPUs and 512GB of RAM.
Database being backed up is a 3.4TB database, with the data spread across 9 data files across 9 data volumes. The data volumes were hosted on a FlashArray and the backup target was a FlashBlade.
To test, I used the above database and executed a bunch of BACKUP commands with different combinations of parameters. I leveraged Nic Cain’s BACKUP Test Harness to generate my T-SQL backup code. If you haven’t used it before, it’ll generate a bunch of permutations of BACKUP commands for you, mixing and matching different parameters and variables. I was particularly pleased that it also included baseline commands like a plain old BACKUP, and a DISK=NUL variant. I did have to make some modifications to the test harness to add in COMPRESSION options: NO_COMPRESSION, MS_XPRESS (i.e. legacy COMPRESSION), and QAT_DEFATE.
Tangent: Backup READER & WRITER Threads
So I’ve always known that if you specify more output backup files, that’ll decrease your backup tremendously. But I never quite understood why, until I started this exercise and Anthony Nocentino taught me a bit about BACKUP internals.
In a backup operation, there’s reader threads that are consuming and processing your data, and there are writer threads that’s pushing your data out to your backup target files. If you run a bare bones basic BACKUP command, you get one READER thread and one WRITER thread to do your work. If you add additional DISK = ‘foobar.bak’ parameters, that’ll give you more WRITER threads; 1 per DISK target specified. If you want to get more READER threads, your database has to be split across multiple data VOLUMES (not files or filegroups).
If you were paying attention above, you’ll note that my test database consists of 9 data files across 9 data volumes. I set it up this way because I wanted more READER threads available to me, to help drive the BACKUP harder and faster.
Keep in mind, there’s always a trade-off in SQL Server. In this case, the more threads you’re running, the more CPU you’ll burn. And if you’re doing a bunch of database backups in parallel, or trying to run your backups at the same time as something else CPU heavy (other maintenance tasks, nightly processing, etc.) you may crush your CPU.
Tangent: FlashBlade as a Backup Target
FlashBlade is a scale-out storage array, whose super-power amounts to parallel READ and WRITE of your data. Each chassis has multiple blades and you can stripe your backup files across each of the different blades for amazing throughput. When you look at the sample BACKUP command, you’ll see different destination IP addresses. It is through these multiple Virtual IPs, which go to same appliance, but helps to stripe the backup data across multiple blades in FlashBlade.
No BACKUP compression used at all.
“Legacy” BACKUP compression used.
QAT BACKUP compression – Software emulation mode used
QAT BACKUP compression – Hardware offloading used
Baseline: DISK = NUL
Remember, when using DISK = NUL, we’re NOT writing any output – all of the backup file data is essentially thrown away. This is used to test our “best case” scenario, from a READ and BACKUP processing perspective.
It’s interesting to see that without WRITE activity, QAT acceleration did help speed up our BACKUP execution vs legacy compression. And QAT does offer slightly better backup file compression vs legacy compression. But what I find the most impactful is CPU utilization, from both QAT hardware and software modes, is MUCH lower than legacy compression!
Note the Backup Throughput column. We actually hit a bit of a bottleneck here on the READ side, due to an older FibreChannel card in my test server and only having 8x PCIe lanes to read data from my FlashArray. The lab hardware I have access to isn’t cutting edge tech for performance testing, rather older hardware meant more for functionality testing. Moral of this story? Sometimes you I/O subsystem “issues” are because of network OR underlying server infrastructure, like the PCIe lanes and subsequent bandwidth limitations encountered here.
The Best: Files = 8; MTS = 4MB, BufferCount = 100
I’m skipping over all of my various permutations to show the best results, which used 8 backup files, MAXTRANSFERSIZE = 2MB, and BUFFERCOUNT = 100.
Much like the DISK = NUL results, QAT yields superior compressed backup file size and CPU utilization. And in this case, Elapsed Time is now inverse – NO_COMPRESSION took the most amount of time, whereas in the DISK = NUL results, NO_COMPRESSION took the least amount of time. Why might that be? Well in the DISK = NUL scenarios, we don’t have to send data over the wire to write a backup target, whereas in this case we did. And using compression of any sort means we will have to send less data out and write less data to our backup target.
Stuck with TDE?
I also TDE encrypted my test database, then re-ran more tests. I found it interesting to see how a TDE database wound up taking more time across the board. And I found it interesting that with TDE + legacy compression, CPU usage was slightly lower but throughput was worse, vs non-TDE + legacy compression.
Of course, the above is just a relatively small set of tests, against a single database. Yet, based on these results and other testing I’ve seen by Glenn Berry, I will admit that I’m VERY excited about SQL Server 2022 bringing QAT to the table to help improve BACKUP performance.
Even if you are stuck with older CPUs and do not have a QAT hardware card to offload to, QAT software mode beats legacy compression across the board.
I do need to test RESTORE next, because your BACKUPs are worthless if they cannot be restored successfully. But alas, that’s for another time and another blog post!
A few months ago, Brent Ozar called for bloggers to write about about Free Community Tools in the month of September. While it’s been a while since I’ve done any work with it, I’d like to offer up sp_helpExpandView.
Does Anyone Actually LIKE Nested Views?
A number of years back, I joined a company that happened to LOVE nested views. And they also happened to have an ETL/DW loading process that literally took SEVEN DAYS to complete.
“Wow Andy, SEVEN DAYS?!? That DW must have been several terabytes! (circa 2015)” “Uhh, no… less than 250GB at most?”
Yeah, that thing was small. But the processing was a horrific case study in T-SQL worst practices. And the architect that created it LOVED nested views (and scalar functions… and MERGE… on Standard edition).
I spent a good amount of effort trying to unravel those as part of my efforts to improve performance, and as a result, decided to create my own community tool to help with unraveling them – sp_helpExpandView.
Andy – Why the Nested View Hate?
In a nutshell, after a number of layers of nesting, cardinality estimates in nested view operators go off a cliff. And cardinality estimates play a critical role in execution plan quality, which is why it is important to have good estimates. If you’ve dug deeper into your nested view performance, you’ll see exactly what I mean.
I do sincerely hope that this tool helps you unravel your nested views. Sure wish there was a magic tool to rewrite T-SQL to just stop using them, but unfortunately that still requires human intervention. Hopefully you’re a consultant and can get paid by the hour for this work!
The thing is, I’ve already written about exactly that, when I first kicked off this blog after PASS Summit 2013: SQL Family Got Me Here. And nine years later, I’ve been blessed with a wealth of speaking opportunities, and two amazing jobs at SentryOne and Pure Storage.
Don’t Forget the Little Things
I’m not going to write about something that resulted in “an amazing” whatever. Instead I’ll share this story.
One evening, early in the week of my second PASS Summit, a friend was organizing a group to go out to dinner. I asked to tag along and they said sure, figuring the restaurant could accommodate one more. As we departed, we wound up picking up two more folks, who were also newcomers to PASS Summit and the SQL community.
When we got to the restaurant, we learned that they could not accommodate beyond the original reservation headcount at a single table, but could seat us three “tag-alongs” at our own table. Okay, that’s cool. Two of the three of us were relatively new friends and the third, I don’t quite recall if we were just Twitter friends at that point or what… but the point more was that we had a great time getting to know one another. We referred to ourselves at the “kids table.” Coincidentally because it was just the three of us and not a larger group, we managed to order, eat, and pay before the other table had even ordered their dinner!!!
That story is not an amazing life changing whatever… but it is one that has stuck with me, despite being insignificant in of itself. And the truly amazing thing is, I have many of these that I could share… mundane, but joyous gatherings of good people, who I am happy to have in my life in one way or another… and never would have met had I not gone to one of these conferences and pushed beyond my anti-social shell.
This month’s edition is hosted Deborah Melkin (b|t). Deb asks for each of us to share our personal infamous “rant” as we database professionals all have strong opinions about many things!
What’s Your Rant Andy?
Those of you who know me might think this post will wind up being about…
NCHAR/NVARCHAR vs CHAR/VARCHAR – aka Unicode or non-Unicode
Leading or trailing commas in SELECT statements?
All flash or spinning rust?
Coffee – with creamer or black?
But it won’t be about any of those! Instead, as I often like to do with T-SQL Tuesday themes, I’m going to take a slight twist and talk about what happens after you make a potentially controversial decision.
When X, then Y…
Consequences… ramifications… or afterclaps (my new favorite thesaurus-supplied alternate)… we’ve all had to pay the price of decisions that were made long ago. But sometimes we find ourselves having to make a less-than-desirable decision with only losing options to choose from.
I once worked for an Entity Framework SaaS company that was having horrific T-SQL performance issues. We put out a major release one weekend and spent the subsequent week buried in horrible performance hell. Because all T-SQL code was generated from Entity Framework (stored procedures were banned by a development exec prior to me joining the company), there were no easy fixes that could be implemented (and a Production release typically took 12 – 36 hours, I wish I was joking).
The manager of infrastructure had heard about SQL Server’s missing index recommendations:
Infra Mgr: Hey Ops DBA (I was a DB Dev). Are there any missing index recommendations in Production?
Ops DBA: Yeah, over 200.
Infra Mgr: Creating indexes doesn’t require us to do a Production Release. We can apply these overnight when client use is minimal. APPLY THEM ALL!!!!
I did my best to explain why this was an absolutely horrific idea. But the Infrastructure Manager was desperate for any kind of relief from their performance woes and did not care to listen about how this could make things even worse. I was sidelined and the Ops DBA was ordered to script and implement each and every one.
And what was the afterclap? <giggle>
A couple of our key, gigantic core tables now had over 30 non-clustered indexes on them. Many of which were redundant and/or duplicate indexes as one might expect. A few queries were improved but backup times went through the roof, nightly ETL execution times doubled, and the Production database became horrifically bloated. A month later, I was finally given authorization to go through an index consolidation and clean up effort, which took me another month of workload analysis and index prioritization/consolidation.
… Then Y… but Why?
In the end, the Infrastructure Manager made a less than ideal decision, yes. But that is not what I have a problem with. What I have a problem with is that he refused to listen and understand the ramifications of the decision. He acted in a willfully ignorant manner, choosing not to listen to his subject matter experts, and instead grasp at straws in the hopes of resolving the issues. And the cost in the end was high in terms of cleaning up the bigger mess that was left behind.
Moral of the Story
So what’s the moral of the story here?
I want to encourage you to try and listen and understand the consequences of choosing X over Y. If you really want to save a few bucks and buy spinning disk instead of an all-flash SAN, understand and acknowledge the short term and long term consequences. And sometimes you do need to choose the less-than-ideal option. But if you must, I say do so with a full understanding of the afterclap.
About a month ago, Deb (b|t) wrote a blog about our newest adventure together: building a shared home lab! And now it’s my turn to blog about what we’ve done thus far.
A Bit of Background
I’m not quite sure how old I was when I built my first PC, but it was definitely greater than 5 and less than 10 years of age. In my 20’s, I had desktop builds that I ran at home as Linux web, DNS, e-mail servers, and file servers. Admittedly, as I got into my 30’s, self-hosting became less of a joy and more of a chore, so I retired most of that. But throughout the years, I’ve built many desktop PCs both for myself and others.
In more recent years, I’ve been using VMware Workstation for my lab needs. My current laptop is a fairly hefty hexa-core X1 Extreme, with 32GB of RAM and 2TB of NVMe SSD. It’s been great for my SQL Server lab needs, but with Deb needing a SQL Server lab environment beyond her laptop, it was finally time to build anew.
For our lab needs, our priority was at 128GB of RAM and at least 8-10 CPU cores for parallelism and running multiple VMs. I also wanted NVMe storage (since I’m still too new at Pure Storage to get my hands on a decommissioned FlashArray). I am fortunate to have a Micro Center near me, so I was planning on just sourcing parts and building a desktop machine to meet our needs. I had a number of friends offer their suggestions on Twitter and one privately offered me a used DL380. I hadn’t really considered a rack server, mainly due to space and heat concerns, and it would probably be overkill. While some might run their home labs 24/7, we’d probably only be turning ours on on-demand, negating the need for 24/7/365 durability and longevity. In the end, I opted to stick with building a desktop vs the DL380, mainly because it is what I was comfortable with and I’ve known PCs my entire life.
Hardware: So What’d Y’all Buy?
Intel i7-12700K Alder Lake 3.6GHz Twelve-Core CPU
ASUS Z690M-PLUS Prime microATX motherboard
G.Skill Ripjaws V 128GB DDR4 RAM
2x Samsung 980 Pro 2TB NVMe SSD
Cooler Master ML240L Water Cooling Kit
Thermaltake Versa H17 mini-tower case
G.Skill 750w power supply
Software: So What Will You Be Running?
Now that we had hardware, the next question most think about is what operating system? Many would default to Windows but because this machine is intended as a home lab, the more important question became which Hypervisor? Do we remain with Windows 10/11 and keep using VMware Workstation for our VM needs? Or embrace Windows fully and learn Hyper-V? Considering so many organizations run their SQL Servers on VMware, I decided to do some research about a full-blown VMware installation.
Let me preface this by saying that I’m a VMware novice at best. I’ve encountered it almost daily for many years, but never had the opportunity to truly manage or work with it until I got to Pure. I’m familiar with general virtualization concepts, which I’ve used with VMware Workstation. But I’ll also admit that until very recently, I had no idea what the actual difference was between vSphere, vCenter, and ESXi (platform, management software, and hypervisor). And the other key thing I never knew was that ESXi is essentially a standalone OS (on top of Linux I think?). But I knew as part of this endeavor, I wanted to use it as a gateway to learn more VMware.
I started to research vSphere costs and discovered that there’s a vSphere Essentials Kit that’s reasonably priced and geared towards small businesses. I thought that that’d be perfect for us, even though the fact that it’s good for 3 ESXi hosts meant it was a bit of overkill for us since we’d only have one ESXi host. Then I discovered that there’s a FREE version of ESXi, good for a single host! The key drawback is that vCenter is not available. I figured I’d go with that, and if we ever had a need for vCenter functionality, we’d make the investment in the Essentials Kit.
Behold: We Have a Home Lab!
And with that, we now had a machine running ESXi. In my next blog, I’ll write about some of the first things I did, to get the lab operational and some of the hiccups I ran into along the way. Until then, thanks for reading!
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.
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!
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.
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?
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.
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!
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.