1:09:03 Webinar
Are Snapshots Backups? Revisiting Modern Data Protection for SQL Server
In this updated session, we revisit the snapshot vs. backup debate with fresh perspective.
This webinar first aired on January 22, 2026
The first 5 minute(s) of our recorded Webinars are open; however, if you are enjoying them, we’ll ask for a little information to finish watching.
Click to View Transcript
Sqltips.com webcast. Are Snapshots Backups? Revisiting Modern Data Protection for SQL Server. Today's session will be res-be presented by Anthony Nosentino. I'm Jeremy Kadlec from SQLTips.com, and welcome you to today's event.
Today's, today's event, well, it might get you thinking. I know, SQL Server backups, they just keep those backup windows, they just keep getting smaller. Would you agree? If something unexpected happens, and as a DBA, the decision is made to restore a database, kinda what happens?
People are hovering at our desk to find out when the system's gonna be back online and figure out how much data is lost. So those retor- restore time expectations, they keep shrinking. Would you agree with that as well? So what do we do as DBAs?
Well, in today's session, Anthony Nosentino is gonna talk about SQL Server backups and snapshots. Some of his demos may raise an eyebrow, some of them may ruffle some feathers, or may even give you a new way of thinking about database recovery. So as we progress through today's session, and maybe Anthony covers something you may not
know about or are surprised by the results, and if you have a question about it, please post them in the GoToWebinar controls. At the tail end of today's webcast, we'll definitely answer as many questions as possible. So Anthony, can you please take it away from here and help educate the community?
Love it. Thank you so much, Jeremy, for the intro, and thanks, thanks for having me. I appreciate the opportunity to speak to the community. So, so let's get started. Great introduction there, so I'm gonna dive straight in. I'm Anthony Nosentino. I'm a senior principal field solution
architect at Pure Storage. I, I work on databases, much like most of us probably do. I blog a bunch, at nosentino.com. A lot of the content from today, is also available on there. And so let's get started.
We're kind of revisiting a topic that we covered a couple of years ago when SQL Server 2022 launched, because since then, some additional functionality at Pure, they're doing some really cool things with what came out in SQL Server 2025, with the REST API integrations and things like that. So I kinda wanna just go through the things that we talked about before, do a little bit
of recap. We'll talk about some new stuff as well. And so for today, I think the big buckets that we're gonna cover is kind of the anatomy of a full backup. Like, when you type backup database to something today, like, what really occurs, right? Then we'll look at the anatomy of a T-SQL snapshot backup.
We'll go through some use cases, and we'll kinda have the conversation maybe that gets this really backup. Then we'll go there, and I'll talk a bunch about building a searchable snapshot catalog using tags. Cause I think one of the things as DBAs is we don't speak storage, right? We speak databases and servers, and storage professionals speak arrays and volumes and
protection groups, and, and how can we language that bonds those two things so that I can ask our storage, "Hey, where's this database?" Not, "Where's this volume?" So let's get started. Oh, yeah, I'll cover some best practices for, for snapshots too. Cool. All right. So regular old backup.
This is what we do, right? When I type backup database to disk, let's talk about what happens. And here you can see I have a database split across two, disks. Basically, the database file or files, and also a transaction log. The red portion there represents, the active portion of the log.
And so when I type backup database or run that, what happens is checkpoint. So all the pages that are in memory that are dirty get flushed to disk, right? We'll go ahead and then mark in the transaction log that a backup is started. We create a backup file somewhere on disk, maybe it's a blob or S3, whatever it is, and then the SQL Server slaps a header on of that, and it marks the metadata about the
stuff that it's gonna write into the backup file. Then what happens there is it reads the database file or files sequentially and just scoops up all those pages and writes them into this backup file. It's quite literally the pages from the database if you're doing this in an uncompressed way. My partner in crime at work, Andy Yun, wrote a
blog post about this exact thing. These two you can visualize. These things are nearly identical. The core difference is the backup header in the front of the database backup file. The last thing that goes into the backup is the active portion of the log.
And so just to pause and kinda think about what happens here is we're basically scooping everything up from the production database, writing it somewhere else in the hopes that sometime in the future, I'll read everything from the backup file and write it back out to the volumes on disk to get me back to the state that I want to be in, right? That's kind of the core concept I wanna focus on, is getting back to the state that I wanna
be in, in the event that there's a failure. When the backup's finished, we mark that in the log, we record the fact that this occurred in msdb, and then we go about our day, and run backups again and again and again, nights and weekends. What's, what's hard about this, right?
It's the size of data operation. So as I scoop up all those pages and my database gets bigger and bigger, there's just more data to scoop up, right? So it takes as long as it takes, and it's gonna continue to take longer as data grows. I see databases in tens and hundreds of TiB now, and some folks just can't backup
up their databases at that scale with that size, and they're missing backup windows and all these different things because it takes, again, as long as it takes. Additionally, it puts pressure on your infrastructure, the CPU and the server, the network if we're writing out across the wire, we're reading from the drives. And, you know, I think we've seen some innovations in SQL Server 2025 that attack the,
the CPU challenge, where we're doing compression, inside a SQL Server. But still, we have to-- when you're that volume of data, it's still gonna put some pressure on the resources in the system, and it's gonna potentially impact your workload. I think we've all done it before, where a backup fails, we start it again, and then it runs into the business day and transactions are slow and, you
All the things that we don't wanna get involved in, when it comes to running, backups in the middle of the day and impacting our end users. Costs, right? I gotta store these things somewhere. And But the real-- I think this is the real thing is, is RTO, right? How fast can I get my database back online and get my business back online?And that's
something that can be challenging, as Jeremy alluded to in the introduction. These numbers are getting narrower, the windows are getting smaller, the backups are getting-- the databases are getting bigger, the backups are taking longer. So it's a constant arms race, and honestly, it's an engineering exercise for us as DBAs to make sure that we can hit our RTOs for our company.
'Cause I like to meet those RTOs 'cause I like staying employed. So let's talk about what a snapshot is, right? Not the technology yet in SQL Server, but just like what's going on inside of storage when I take, and just air quoting, a snapshot. Regardless of what infrastructure it is, Pure, Azure Disk, whatever, a snapshot is generally
gonna be a full read-only representation of the data that's on a disk or a volume, in a storage subsystem, right? And so in Azure, some recent functionality that came out maybe about a year or two ago, is the ability to take a snapshot of a virtual hard disk in Azure. So you can quite literally do that, and you have a separate resource that is the snapshot of that Azure virtual disk.
And on storage devices like ours, you know, it's a point-in-time representation of the data that's on the volume or volumes that support, something, right? It could be a database, could be anything. And so the key thing here is we can revert the snapshot to a previous point in time, right? Again, focusing on that concept of getting back to the state that I wanna be in when I
wanna bring that database back online. We can also copy and clone these things to give other systems access to the data that are in, in snapshots. So one of the concept that we're gonna pull on as we get in some more complex idea of protection or consistency groups.
In Pure, we call them protection groups, which enable snapshots of databases that span multiple volumes. And so I take a snapshot of multiple volumes at the same exact point in time So if I have a database file on disk one and a, a log file on disk two, I have to have them in a protection group to snapshot entirely 'cause any database that supports write-ahead logging
can support what's called a crash-consistent snapshot, and we'll talk a little bit more about that in a second. And of course, snapshots can be replicated to other physical media. So if we take a snapshot in Azure, I can replicate that to another region. If I take a snapshot on a storage array in a data center, I can replicate that to another
data center, or another array even in the same data center. And one of the things I do wanna talk about, and just kinda address this right in the beginning of the conversation, is I don't wanna confuse backup and restore with information lifecycle management. What we've seen over the years is backup-- enterprise backup systems also have become
kinda regulatory compliance systems and kinda have multiple purposes. I'm talking about backup and restore, right? You know, I still am gonna advocate for native backups that hit your enterprise, infrastructure, that replicate between the data centers, that go into the backup vaults and all the other things that you might be doing with your data, right?
But today's conversation is solely about backup and restore. All right. Cool. So Jeremy, poll number one. Let's ask the salacious question that we wanna ask. All right. Here we go. We've launched our first poll for today.
So number-- "Are snapshots backups? Yes, no, or I need to learn more." So everyone, if you could please vote, I would definitely appreciate it. We will share the results with everyone. So there again, please take a minute or so to vote. And then we'll basically go through and share the results and then, have Anthony continue.
He does have quite a bit of content, so, I wanna give him as much time as possible. So I'm gonna give everybody about, fifteen more seconds to vote and once again, are snapshots backups? Yes, no, or I need to learn more. All right. All right.
So I'm gonna go ahead and end the poll and share the results. Cool. And Anthony, feel free to jump in here. So at forty-three percent, no. Ooh, okay. At twenty-nine-- no at forty-three. Okay. Twenty-nine percent, I need to learn more.
And at twenty-eight percent, yes. Okay. All right. So let me, let me see if I can change-- so much we can change that opinion towards the end of the presentation. So- Okay. Cool. All right. Sorry.
And so let's, let's address it right now. Like, this is probably the issue, right? I've used snapshots before, grimace emoji, right? And I think we've all been there. So let's kinda walk through the landscape of snapshots.
App-consistent, crash-consistent, database snapshots, like literally the ones inside of SQL Server, and I kinda have this bucket of, vendor-specific implementations. And so, application-consistent snapshots. Historically, we had to use a thing called VSS, right? Which was part of, of the Windows operating system.
And I guess you can just tell, like, no-nobody loves VSS, right? It had weird limits. It had-- It also had it-- non-deterministic runtimes. You know, you know how long your I/O STUNs were gonna be. It was a complete I/O STUN.
Was, was not fun stuff there. It also required third-party vendors to build and support those tools, and that could be spotty across multiple things, right? Cause next thing you know, you have, multiple vendors involved and, you know, things are getting a little bit weird from that standpoint.
And obviously, like I said a second ago, it, it just never really, I think, handled well in the enterprise 'cause the you couldn't really know how long a STUN was gonna take or the limits that it had with that. Crash-consistent, talked about this a little bit. The idea that if your database supports write-ahead logging, the idea is I can
to the server, yank the power cable out, plug it back in, and the system will come back online. Effectively, that's what a crash-consistent snapshot does. It takes a point-in-time representation of the data. When I come back and clone it or revert it to it, I can bring the database back online.
The problem or challenge with, with this implementation is you don't have point-in-time recovery, right? I can't bring a crash-consistent backup, put it in restoring mode and roll it forward with locks. Database snapshots, there's no portability. It's kind of bound to a single database engine.
You had copy-on-write performance issues and then vendor-specific implementationsUh, just think of things like snapshot in a VM in Azure or snapshot in a VM in VMware. Well, that could lead to consistency issues and the granularity of restore. Again, we speak databases, not VMs, and if I want to get a database out of a VMware VM, that could be challenging from a snapshot standpoint.
And so this is kind of the-- what we've seen, I guess as DBAs and what we've been over the years and, and Microsoft saw this problem. So starting in '22, they rolled out a new feature called T-SQL Snapshot Backup, right? The idea is I can request the database with no external tools, right? The, the database engine itself controls the functionality, of, of being able to take and
coordinate a snapshot. And I can go, and I can talk to the storage tier and actually make, the snapshot occur, right? Whether it be Azure infrastructure or Pure Storage FlashArray or whatever that is, right? And, and given this, it unlocks point-in-time recovery because then again, the database
engine is in control coordinating the work. It can have instantaneous restore for a singular database, a group or a collection of databases, or the syntax says server, so I'm gonna say server here, but-- or whole instances worth of databases, right? My favorite use case is this, like bar none, I can build a brand-new or reseed an
availability group replica or a log shipping partner, right? But it also enables, cross-platform scenarios. Y- M-Microsoft brought SQL Server to Linux. They couldn't bring VSS to Linux because I think that's probably a much harder ex-engineering exercise, even though they did some special stuff with SQL Server on Linux.
But the fact that it's the database engine controlling this functionality, it doesn't have to coordinate with the OS, now we have same, same in functionality across Windows and Linux. Now you can't move snapshots between the two because of file s-constraints, but the idea is the functionality is the same.
And it's, and it's rocket fast when you compare it to vSAN, and we'll look at that when we get into a bunch of demos. So let's talk about what a snapshot backup is. And so let's We have our previous database, right? And when I type backup database to disk, and there's gonna be some special syntax that
we'll look at more closely, in a second. When I run that at the database engine level, it runs a checkpoint. It marks a, a backup to start in the transaction log. It freezes the log file for writes, and then we can take our snapshot, right? So that's the work that's got to happen at the storage level.
And so remember, that's, that's gonna be at a, an immutable point in time, a read-only representation of the data. Now, the database engine knows what's happening there, what's in there because it marked it in the transaction log. It creates a metadata header file.
So quite literally the same header that would be embedded in a backup file is written out, to disk or to URL, which is gonna have information about what's in the snapshot that file. And that's a couple hundred K, and we'll k-talk about how to protect that and what to do with that little file, which describes what's in the snapshot. We thaw the log, and then we can just move
forward ahead in time, right? And then so during that freeze period, you're gonna see, write log waits, but we can shave this down pretty far. I can get it down to just a couple hundred milliseconds for that freeze. And if you're talking about a h- multi-hundred terabyte database, that's a pretty reasonable
trade-off, I think, in that sense. And so one thing that, Oh, then we mark the backup as finished. So cool. One thing to think about for the duration of the I/O freeze for the writes is how big is your checkpoint. And so I think as seasoned DBAs, we've probably all done this before, where we'll
checkpoint and then do a different operation. Same thing here. We would checkpoint first if we had a lot, lots of, of dirty pages that we had to flush down, and then we come back and run the backup command. That dramatically shortens how long that I/O freeze for writes is, and then we update msdb. Cool.
So then moving forward, we can just go ahead and take regular old log backups, right? So backup log to disk or backup log to URL, whatever that is, and just move forward in time. So collectively now I have a snapshot backup with a metadata file that describes what's in the snapshot. I now can have regular old transaction log backups, which I would write to whatever media
I would normally write them to. Cool. And so the syntax for that looks like this: alter database DB1 set suspend for snapshot backup equals on, right? That's for a singular database. If it was a group, we have some syntax for group.
And if it's a whole instance, you'd have to say alter database server. For whatever reason, they said server. And then we go ahead and take our snapshot at the storage tier, and then to generate that backup metadata file, some new syntax backup database, and then the database disk or to URL, wherever you want to
With metadata only is the new keyword or new in SQL Server 2022, which is gonna describe that we don't have all the pages in this backup file because they're somewhere else. They're in a snapshot. And what I do to kind of close the gap between the snapshot backup and the backup file is in the media description, I use these labels to say, this backup file uses this snap-snapshot name on that snapshot location.
So in Azure, this could be URI, on-prem, FlashArray, this would be the FlashArray name and snapshot. Cool. Not required, just one of the ways to tie those two things together, and then look at the tagging solution towards the end. So the backup metadata file describes what's in the backup.
You probably should protect it, right? And you'd You're doing this anyway. You're backing up your stuff. Some of the integrations that I've it'll land in S3 and then replicate another array. Then we can lock those to protect that. Similarly, you could use the same pattern in Blob or just write them out to file, right?
And so if you're thinking about this as like your enterprise backup catalog, right? This is the catalog that describes what you have, in your snapshots. You wanna protect that. But if you lose the backup metadata file, all is not lost. You just lose point in time recovery. I can revert the snapshot back and bring the database online.
I just can't roll it forward, and put it in restoring mode and restore log backups into it to end the And use the media description to locate your snapshot backup name. So other scenarios, I'm gonna focus on single database, but just to highlight that there's other backup scenarios that you can do. So more complex configuration here, the backup may be a group of databases.
If I wanted to takeA snapshot backup of just databases one and three and not two. I could specify that in some-- with some syntax. I'm still gonna take a snapshot of all of the volumes. The difference is DB one and DB three would have the writes done, DB two would not. I could do point-in-time recovery on DBs one and three.
I could not on DB two. But I don't really see a common use case for this, but I'm sure there's edge cases that somebody would have to leverage this type of functionality. I can then backup a whole server. Remember, it's really an instance, but they call it server in syntax.
This would then freeze all of these for write IO, take the snapshot, and about my day. Cool. So, what does a restore look like? Let's kinda walk through that scenario. So now I have a snapshot backup.
It's on my array or it's in my Azure infrastructure, and I wanna go consume that back, right? And say I damaged the database in some way. I dropped a table or corruption occurred, some bad things happened. So what we do is we offline the database.
We would then offline the volumes associated with that database. At the infrastructure level, we'd copy or clone from the snapshot and overwrite the volumes that the database was on. And then we have the bits and bytes that were there at the point in time of the backup. Again, this is the state that we wanna get back to, even if we had backup files, right?
The whole idea is to, literally just to go back in time, and we did. And now I can go and restore log backups if I want to. To put the database in recovery mode, you're familiar with, restore DB one that's the backup metadata file. With metadata only instructs SQL Server that it's, it is a snapshot backup, no recovery,
puts the database in restoring mode, and that can start rolling back the log backups, right? That I would expect to do to bring this thing to the point in time that I want. So let's go ahead and do this together in a lab. And I have a bunch of code for this to, to share with y'all at the end, on a GitHub repository.
So let's go ahead and get started. So snapshot backup, using SQL Server 2022. Got a bunch of blog posts about this in the code here, or code comments here. And we'll get, let's get started. Cool. Why is my PowerShell window locked up?
I don't know. Start a new window here. And All right, we'll just use this on the terminal. No big deal. Cool. I'm gonna use DBA tools to kinda make this process a little bit easier, and I'll talk about why in a second.
And then we're gonna use our PowerShell module, as Pure to do-- to coordinate some work under the hood. So, I need some credentials here. I'm gonna work with a SQL Server named AEN SQL twenty-five A. It is a SQL Server 2025 instance. And I'm gonna use PowerShell remoting to connect to that instance from my laptop here.
The hardest part of the demo is me typing my password right there, hopefully. And then I'm gonna use connect DBA instance to build a SMO connection, connection, to my SQL Server. And I'm gonna hold that connection open in this SQL Server instance variable. And the reason why I'm doing that is this.
If you use invoke SQL CMD, or just invoke DBA query to go and coordinate the freeze and thaw with the engine, what's gonna happen is it'll make the connection. It'll then set the command to put the database in, snapshot mode, and then it'll terminate the connection and log back out, which will then terminate the SPID that you froze the database with for write IOPS, and then it'll immediately thaw.
Right? So somebody thought that through. Like, you can't just You have to make the connection, hold it open to do the work, and then do the rest of the things that you wanna do to the database. And so that's why I have to have this, connection be persistent throughout the, this process rather than logging in, freezing it, logging out, immediately thawing it.
Cause I'm not gonna tell you how long it took me to actually figure that out when I worked with this the very first time. It's longer than I'd care to admit in public. So this database that I'm working with, is gonna be a four TiB database. Four TiB in file size, about three TiB, two point nine
in change, in data size. So pretty, pretty healthy database. I'm gonna connect to my FlashArray, which will allow me to do the snapshot operations. And so the next thing I'm gonna do is use that syntax that we introduced. I'm gonna do alter database TPCC four T, and I'm gonna set my database into suspend mode.
And we have that. So now you can see at the bottom, database is successfully suspended for session fifty-seven. So let's go ahead and take a snapshot. So I'm gonna coordinate with the FlashArray. It's gonna take the snapshot, and you can see that was pretty fast.
And now I'm gonna create the metadata backup file. So I'm gonna run this, and then we'll kinda walk through what the syntax does. I'm gonna write the backup file to just out the disk. You can see backup database, TPCC 4T. That's where the backup file's gonna go with metadata only.
And I'm encoding the FlashArray name and the FlashArray snapshot there. And so you can see that's the name of the snapshot ending in two oh eight. And that is where it lives in my infrastructure today. And from a database standpoint, if I look at the error logs, I get the DBA error log, you can see at the bottom here, backup database successfully processed zero pages in just
under zero point zero zero four seconds, right? And you can see kinda walk back the functionality there. You can see IO is It says IO is frozen. Trust me, it's right, so I have a whole blog post about that concept. And then you can see suspended for snapshot backup, and then it finishes.
So what we have left is the actual, an actual backup header, right? So if I go and I read the backup header, it's what you would get out of a full backup. In fact, there's no additional functionality in DBA tools to support because it literally is just a regular old backup header, and so I can read that out. And so there we see the, the actual data size, where the backup file is.
The database from a physical standpoint, all the things you'd expect inside of a backup header and there we see IsSnapshot true, right? Cool. So let's go ahead and take a log backup because we wanna do a point-in-time restore. So I'm gonna take a log back again just using DBA tools 'cause I love DBA tools, it makes my
life easier. Hopefully, it's making your life easier. And then I can go ask the database "Hey, give me your backup history." And at the bottom here, you can see there's the full that I took just a couple minutes ago, and there is the log that I took just a couple minutes ago. And the full is reported because it's a full snapshot backup.
All right. Cool. All right, so let's do something that generally would give us that funny feeling in our stomach, if we did this on a production system. Drop table customer, right? I think this is like a one point two billion row table in my system. And so let's go ahead and do the restore process.
I'm gonna alter database TPCC 14, set offline with rollback immediate. That's gonna immediately set the database offline. Since they're volume-level operations, I'm gonna go after This is resident on just one volume for simplicity and demonstration purposes, so the log and the database are on the X drive, and so I'm gonna go ahead and offline that drive.
And then I'm gonna read out of my backup file the location of the snapshot and where it is in my infrastructure. So there's snapshot two oh eight. There's the FlashArray that it lives on in production in my system, and I'm gonna use our clone command or new PFA two volume command to literally take the data from the snapshot and overwrite the data on the source system.
So now all the bits and bytes are back to a point in time inside a FlashArray array, so this is instantaneous. And now I go, and I online my disk from a Windows standpoint. I then restore database from disk. There we see with metadata only.
Replace, no recovery 'cause I want it to stay in restoring mode, so it's things that we are normally used to as DBAs, to put a database in restoring mode. So now the database is in restoring mode right there. Cool. Going down a little bit further, let's go ahead and run a restore and still leave the database in restoring mode.
Once that finishes, I'm gonna online the database. So there we see restore database with And let's see if my customer table is back. Right. So the customer table is back. It's got a bazillion rows in it.
It's pretty healthy in size. And so that's the idea, right? Is I can get this system back to its point in time nearly instantaneously. That was a four-TiB database. Roll it back. Customer table's back.
I'm still employed, right? Cool. Let's go ahead and look at the next use case, which I think is my favorite use case. Or actually, we'll talk about some topics like is this backup, right? So I think this is what everybody thinks, right?
I have this snapshot. It's on a singular array, and what do I do with it then? Well, in both our infrastructure and Azure infrastructure, you can replicate to other arrays, right? So you have to take this snapshot and send it somewhere else. I can also send it to another array in site or to another array in or into a blob
store or into S3, right? So we can get the stuff on multiple in-- or multiple FlashArrays across different media types and, and have that restore pattern in any of these infrastructures. And so that's really where I start to blur the lines between is this backup, right? Because now it's on mult-- it's in multiple arrays of physical media
It could be between data centers and also in different media types, right? Cool. So now my favorite use case. So let's kinda take that same layout where I have three arrays, two in one site, one in another, and I wanna build an AG, right? So historically what I would do is I'd have to take this database, it's four TiB.
I'd have to land it somewhere in my data center, restore it to SQL Server two, land a backup in DR, restore it in SQL Server three in DR, and that takes as long as it takes. And I have to have the infrastructure to, land that backup, and I also have to be to restore that during the seeding process.
I might have to turn off log backups 'cause I kinda don't wanna have to erase those, which could potentially put me in, an RPO not so fun place, let's just say. And we have to go about what we do. And, and honestly, I don't like spending nights and weekends re-seeding AGs. We've all done it. Like, I've spent many nights and weekends
re-seeding AGs. And direct seeding, well, we all know. So let's take a snapshot, replicate it between our infrastructure, and then I can clone these databases back on all of these replicas and then just finish the seeding process. Cause conceptually, these are gonna be all full restores.
Gonna put the database in restoring mode, which then means I can come back and take the log backup, which then means I can restore them on all the replicas, and then I can just bring the availability group online, right? Now, the first time we do this, I'm gonna have to move the snapshot between the two arrays. On SQL Server or, excuse me, on FlashArray, the data reduction is gonna be applied there.
So if it's four to one, five to one in your infrastructure, that's really gonna reduce the amount of data that's gonna shift between the two. Where this really starts to shine is the second time I have to come back and re-seed an AG. Because when I take that snapshot, I'm just gonna move the change blocks
from the last snapshot. And so I'm gonna show you in the, in the demo here in a second where I'm gonna have a f- that four-TiB database, but it's gonna re-seed instantly because it's gonna be on two completely separate physical arrays in my infrastructure. And for me to do that normally would take as long as it takes.
But now I have the ability to just move the change blocks, have two full fidelity snapshots at both replicas, and then build the AG from that. And so let's go ahead and do that together. Cool. So let's get started. Blog post for this, and there's a bunch of scripts for that.
One of my favorite usages for LLMs now is to give me, ASCII art representation of my infrastructure. So there we can see I have my two SQL Servers, AEN SQL Server twenty-five.C and D. They're on two separate FlashArrays. These two are in the same data center, so a slightly different use case than, in the
PowerPoint, but you can see how this would be extended to solve that problem too. All right, so let's get started on this one. So again, using DBA tools, and our PowerShell module, I'm gonna connect to a couple servers. So let's load up some credentials. I'm gonna get a PowerShell remoting session to my secondary or soon-to-be
And I'm gonna connect to both SQL Server instances here, so I can coordinate some work between the two as I build this AG out. And just to remind you, this is a real four-TiB database. Gonna connect to my primary FlashArray. Once that's connected, I'm gonna put the database in snapshot backup mode on the
primary or the soon-to-be primary. I'm gonna then take a snapshot. But this time, when I take the snapshot, I have two additional parameters for replication that tells it to send it somewhere else. I've set up replication between the two arrays behind the scenes.
I can walk you through that if you wanna look at that later. And I'm gonna force it to replicate now, right? Cause I want it over in my other array right now, rather than hitting a queue and then replicating when it needs to. I'm going to, then I'll take the metadata backup file, park that on a share that both
SQL Servers can read from, and I'm gonna connect to my secondary array now. Now, here is a bunch of code. Don't worry about the syntax. I'll tell you what it does, is it checks to see if there's a snapshot is on the other Array, right? So it took the snapshot name from the
that I just took, and now it's checking if it's available and fully replicated to the other array, and it is. And so you can see. So that now means between the time I took the snapshot and the time I checked if it was on the other array, it already moved what it needed to move to the target array.
So now I have two full snapshots on both. So again, this is gonna be a four-TiB seed that is gonna take about as long as it takes for me to, to talk through this demo because the data's already there. So there is snapshot seventy-six on the primary. Seventy-six is on the secondary, and a, the prefix of the source array's
And so now it's time on the secondary to offline the volume. Clone the data from snapshot back into the volume, on my soon-to-be secondary replica on SQL Server-25D. Online the volume. And so now that database is available on that system for me to put it in recovery mode.
And it's a metadata only replace, no recovery. There's the restore database command. I'm gonna take a log backup on the primary. Now, it's just regular old da- availability group seeding here. Restore that log on the secondary.
I have it as with no recovery because now I'm gonna tell SQL Server for the availability group to set the CD mode as manual, and then add that database to the AG, which kicks up data, data synchronization. When I come back and check the state, you can already see that both sides so in a synchronous replica.
So now I have two full fidelity copies. I've cloned that database, seeded the availability group in about three minutes of me talking, four minutes of me talking through it. Which again, this is probably my favorite use case for all of this because I've, I've done this before many nights and weekends, many stressful scenarios, have been lived through,
in that case. So cool. Love that. Let's head back into the presentation now. All right, so we went through point-in-time recovery. We just seeded an AG. And now I want to kind of focus on things that I've done since the last time
we've, we did this, we had this conversation about snapshot backups. And so one of the things that we don't do is, as DBAs, is we don't, we don't speak volumes and protection groups and arrays and snapshots. We speak instances and servers and database names and things like that. And so, inside of FlashArray and also of Azure, I can build tags to describe
what's in these snapshots now, these objects that live in my storage infra. I can say, "You know what? This is what is in this snapshot." And so I can take the keys and the values and describe the work that I did in the database engine, right? So I could do things like tell it what SQL Server instance is there, tell it what
database is there, what, what was the the backup, where the backup metadata those things. And when I replicate these objects, it's in Azure or whether it's on FlashArrays, those tags will go with the snapshot. So think about it this way. Inside the snapshot is what describes what's
in that snapshot. I know it's, looks a little heady. Once you see some code, then hopefully that'll become a lot more clear. So we can then come back later, and then we can ask our arrays, "Where is this key? Where is this value?" And it's gonna give us an answer, right? In SQL Server 2025, I'm coordinating all this work with REST.
In prior versions, you can coordinate this in PowerShell, and I have two links to blog posts that I wrote, to do that work, whether it be on the '25 side in REST, and then or on, SQL Server '22 with PowerShell. What I mean by REST is SQL Server now has a stored procedure which will allow me to interact with REST API endpoints, and then I can go coordinate that work with no external
tools, just inside the database engine. So let's look at what that looks like. And so before I go take a snapshot backup now, and this is code that, well, is gonna be available to download, I need to go some variables that describe what I'm snapshot, right? So instance name, database name, backup type,
date stamp, where the backup file name or what the backup file name is, where it's gonna be, right? And so then I can build the JSON payload toDescribe what's in the snapshot. And so here you can see, the payload. So I'm gonna call an endpoint, I'm gonna pass in the source names, which is the protection
group or, or the volumes that support this database. I'm gonna force it to replicate now. And then here's all the keys and values that I'm gonna add to the snapshot. And then I go and I use this new SQL Server stored procedure, sp_invoke_external_rest_endpoint.
I call that endpoint, it creates the snapshots, embeds the tags in the come back later, and I can ask, "Hey, FlashArray, give me all of the most recent snapshots for my SQL Server instance, AENSQL25A." Right? And here you can see at the end, I'm gonna sort it by created descending, limit five, so the five most recent snapshots.
And so this is normal, kind of standard REST API interactions that you do when you're working with an endpoint for filtering and things like that. And so you'd wrap this up with a nice stored procedure, and you'd get some interesting functionality. I'm gonna show you towards the end where I took this exact code, and I poured it all how it gets backup solution to do this work, right?
So pretty cool stuff there. So sp_invoke_external_rest_endpoint goes and runs the query, and then what I get back is something like this, right? I get some rows that describe the name of the snapshot backup, where the backup file is, what database is in the snapshot. And so I can now have a catalog that follows my backups or my snapshots
through my infrastructure. And so that looks like this, right? So again, if we take our existing architecture and we extend it, where backup, but now I'm gonna embed those tags into the snapshot. And so when they replicate somewhere else, I know what's in that snapshot, and I can just
ask the FlashArray. So I can actually ask the whole collection of FlashArrays, "Give me the most recent snapshot for a SQL Server named AENSQL25A." gonna give me that answer, right? And it's gonna have all the information that I expect. And so now I can start to build higher-level constructs to support the backup and resource
scenarios that I would need to do. Cool. So let's do it. So I have Now we're no longer in PowerShell land. Sorry, beard. Let's go ahead and look at what here, just solely within, SQL Server, using the REST Endpoint command.
I'm running SQL Server 2025. I'm running CU-one, and I'm gonna roll the error log just to keep things nice and tight, when we look at some output. And again, I have some blog posts to support the, what we're talking about here. So the first thing you'll need to do is to turn on the REST Endpoint functionality in
your SQL Server. And for FlashArrays, what we'll do is we need to log into the FlashArray. So I'm gonna go through a bunch of code. Don't focus on the syntax. I'm gonna walk you through the process, we're gonna focus on, on the output, really.
But for our systems, you have to log into the array and get a, a bearer token or what's called an XAuth token. And so that's the first thing that occurs. I go and knock on the door. It gives me the XAuth token back.
So that's what I have here. And I'm gonna use that for the duration of the session to coordinate the work. Now I'm authenticated. So then we'll go ahead and set the snapshot backup mode. And then we'll go through here.
This code we reviewed in the presentation portion. We're gonna load up all the variables for the JSON payload that I'm about to create. Going down a little bit further, there's the JSON payload that I'm about to create. So that's my protection group name. Force it to replicate right now.
This copyable attribute says, "Copy these tags when it replicates somewhere else." Database name, instance name, things like that. And then I go and I make the external REST API Endpoint call to this endpoint, pop in the payload. It takes a snapshot, embeds the tags, right?
And then we take our metadata backup, which is happening right here. I have some other c-code for error checking around this. And towards the bottom, I'm just gonna read the error log. And so let's go ahead and run this whole stored procedure together. Or not stored procedure, but run this code together.
And we'll see, since I rolled the error log, there we see the backup database is successful, zero pages. And if you look at the messages or the text output, we can see the process that I walked you through. I logged into the FlashArray, got a two hundred code returned, which means I logged in. We've seen this before in PowerShell, where I went and I suspend the database for write IOPS.
Here's my populated JSON payload. There you see the source names, which is the, protection group name. Replicate now. And then there is all the populated tags. And then I take the snapshot, return code zero. That means happy, happy. And then we see the output of the metadata
file write, which hits S3 in my infrastructure. Could be block, could be disk, right? Cool. So now that lives in my storage with all those tags associated with that snapshot. And so what I wanna do now is I want to ask my storage infrastructure,
Where is..." I'm gonna jump down to that point right away. I'm logging into the FlashArray. There's my XAuth token. I wanna Where is the five most recent snapshots for my SQL instance named AENSQL25A?
Now, I could search on any attribute. I'm just searching on that key and value right there. So let's go ahead and run this code. And we'll see these are the five most recent snapshots. You can see I woke up pretty early this morning to run these demos before I came to
work today. And here's the one that we just took, right? Sorry, two oh nine. And that was the one that we took, when I ran this demo script here, for take snapshot backup with tags. Now, I have a listing of all the snapshots 'cause I searched by instance name. I can then go back to the array and ask for all of the tags associated with that, so I can
get more information. And here we can see the snapshot that I just took on two oh nine. Got a backup timestamp, the backup type snapshot, the backup metadata file lives there.The database name is this, the SQL instance is that, right? And I, I-- There's other information I could clearly add, by just extending the keys and
values to describe what I want out of my backup. Cool. All right, so final demo. Super glad that I actually was able to get to this 'cause this is some code that I wrote last week. Very excited about it. So, this is not a supported solution.
Ola, this is not a PR against Ola's repo. Like, I mean, hopefully, maybe one day I can do that. But I just wanna show you what's possible with this functionality now, right? And so hopefully, we're all familiar with Ola Hallengren's maintenance solution. It's been the gold standard for me for up my databases, managing my indexes, and all
the different things that it does. And so what I did here is I extended the backup database store procedure to support snapshot backup, right? And so here I have databases. It could be one, it could be a group, it could be the whole instance.
Backup type is snapshot, where that normally would be full, diff or log. The backup software, which is kind of a way that Ola describes, or integrates with third parties, so like, like Redgate and and then I added Pure Storage Snapshot. And then we'll need the additional information about where that data lives in our infrastructure. So that's my FlashArray, rest at the endpoint.
There's my API token. Clearly not a best practice to have that during a webinar, but all good. And we have, the protection group, which is where my database files live in my infrastructure, and then replicate now. And then rather than dumping this out to S3, I'm just dumping it to C backups, but it could
be S3, it could be HTTP, blob, or it could be a network share. And then honestly, my favorite piece of functionality out of Ola's maintenance solution is the command log table. So I wanna store the fact that this occurred in the command log table. So let's go ahead and run this code.
There we go. And in the bottom here, we can see the output that we'd expect, from, from Ola's solution. We see that the backup has occurred. There you can see the command that was run. And then we have the output that I have to extend it.
We can see the, the snapshots occurring, the payload's passed, the tags are occurring, the snapshot backup occurs, all the different things that you'd expect. And so now, I have the ability to have that all wrapped up in a tight package that I'm, I'm used to supporting, right? Using Ola's stuff. And if I go look in the command log table, if
I just go grab the rows associated with that, you can see Or it just, it marks as if it was a, a snapshot, or it marks as if it was a backup that occurred. Gives us the command that occurred, which you'd expect from Ola's solution, start time, end time, and potential error codes. If I go back a little bit further, you can see, the time which where I've done
the server-level testing. So there I, I did a, a server-level backup right there, so it included server. You can see that in the, the command there, and it backed up all of the still got a row for each database to describe what's in there. And so now I can come back in.
I want to ask for some tags. So this is just a code again to read the tags from the FlashArray. And there's the one we just took with Ola's maintenance solution, and then I added the additional tag backup software so that I know that this was a backup ran or coordinated by Ola Hallengren's maintenance solution.
Everybody say thank you, Ola, for what you've done for us, right? So let's jump back into the presentation portion, kinda bring things in for a landing. So best practices. We've learned some things over the years working with this, and just kinda share with you, what that looks like. So if you don't like that write IO stun, you're gonna do these things during your
normal backup window anyway. So if I have a, a four-TiB database or a forty-TiB database, is, is taking a write stun for a short period of time better than a multi-hour scenario where my backups are slowing down my infrastructure, right? That's the, the way I think about it there.
But if you really wanna shave it down, if you run a checkpoint before you run the metadata-only backup statement, you're gonna run You're gonna checkpoint the vast majority of your dirty pages prior to the metadata backup. Otherwise, you will get some variance in, the duration of the IO freeze, right? Write freeze. Protect the metadata files, but all's not lost
if you lose them. You just lose point-in-time recovery. Replicate your snapshots to other physical systems, locations, and media types. Definitely wanna do that. And also consider using DR, accelerated database recovery. The idea here is if you have a, a of the active portion of log in your, in the snapshot, it's the same as if it was in part
of your full backup. And so when a database comes online, it's gonna have to do the work that it has to do to bring that database back to a transactionally consistent state. So if you really wanna start shaving down for your, RTO, this is a very good tool for that, in addition to all the snapshot backup stuff that we're talking about. Retention equals costs, right?
So keep that in mind. The more snapshots you keep, just like the more full backups you kept, that's gotta store stuff somewhere, so that's gonna be part of how you think of your architecture. And then data file layout and storage. So noticing how those restore scenarios involved the Windows volumes, obviously, if
there's shared databases on those volumes, we can't offline those volumes, so you have to think about your data file layout, and the restore patterns that you wanna support. Only user databases, not system databases, and you can only snapshot in fact, I'm pretty If you try to snapshot or put a, a secondary replica in backup mode, it won't let you. The idea here is it's really not a lot of
heavy lifting on the primary. It's a very small IO freeze, so just, you know, you take that on the primary. And there's very technical reasons as to why. I can geek out later about why that doesn't work, the secondary replica snapshotting. But I think for me, the, the real answer to the And I'm, I'm kinda preloading the final
poll here, isIt's, it's both, right? The idea is I have the ability now to restore as fast as possible, like nearly instantly. I have the ability to build an AG nearly instantly. But I'm still gonna combine this with regular native backups because I have to support other restore scenarios, right?
So imagine this, like, if I have a bug in my application, and I don't realize there's a bug in my application and it caused a data anomaly from seven weeks ago, and I backup from seven weeks ago and one day, might not be on tier one storage as a snapshot on your primary data center, right? That might have archived off somewhere.
And so the idea is the restore pattern for seven weeks and one day ago is completely different than save my business right now, right? And so the idea is you have both of these things for the restore patterns that you're trying to support for your infrastructure. And so then, Jeremy, let's go ahead and, and, and, and test the waters
and see how we did, right? And ask the question, right? All right, here we go. So for our second poll for today, are snapshots backups? Yes, no, or I need to learn more.
So if everyone can please take a minute to vote, we will share the results with everyone. We have gotten a lot of questions so far. I know we still have more coming in. I apologize, I have not been able to get to all of them just yet. Cool. But if folks do have questions, please post
them in the good webinar controls. We're gonna definitely try to get everything wrapped up here very shortly. I'd like to ask everyone there again to please vote for our current poll. There again, are snapshots backups? So yes, no, or I need to learn more.
So if everyone can please take a minute to vote, we certainly would appreciate it, and we will be sharing results. I'm gonna give you about five more seconds to vote, and then I'm gonna close the poll and share the results. Very cool. All right, so here we go. All right, Anthony.
At, seventy-four percent yes. At fifty- Oh, wow. That's a big jump . Oh, so just, for folks who might not here at the beginning, we were at 28 percent for yes when we started. Uh- Nice so that's a huge jump.
At fifteen percent no, and there again, we were at forty-three percent when we started off. And I need to learn more, we're at twelve percent now, and we were at twenty-nine percent when we started. So yeah, definitely, definitely appreciate you taking some time today, Anthony, to educate
the community. Awesome. Awesome. So yeah, let's definitely get, You wanna switch over to Q&A now? Sure. Yep, I definitely gave you a couple questions there. If you, can check those out, I would appreciate it. Oh, yeah. I know we have a bunch more
questions, coming in from everybody, so if you could, check that out, that'd be great. All right. I It's, it's Okay. Go through- And, uh- Okay you definitely have a, definitely have a secret admirer on, today's session, just so you know, Anthony.
People are saying, "Why are you so amazing?" Or "How, how are you so fantastic?" You've been getting some, hearts and, claps, during this session as well too. So I just wanna pass those along, as you're, checking out. Oh, and there's, there's some more claps coming in, so thank you guys for that. Cool. Yeah, thank you, everybody.
Some hearts from, from your, kind of biggest, secret admirer here. A lot of claps coming in. All right. So let's- So just wanna make sure you know you're getting a lot of love from everybody, so, Love it. All right. You want me to dive in?
I see six questions. You want me to just dive into them? Yeah. Let's, let me just, go through them kind of one at a time. So- Okay first question was related to DBA credentials. Do you require- Mm-hmm any special permissions to access the storage array,
a snapshot versus, potentially completing a backup? Yeah. The answer is yes. That API key that I, I have ha- is, is bound to a user that has rewrite access on our arrays, because it's gotta take the snapshot and it's got to, do the volume overwrite operation.
And on our arrays, that's, those are the permissions that are required. In the immediate future, we're gonna look or have the ability to kinda have what we call a secure multi-tenancy, or the ability to put a kind of a boundary around what a, what a, a user can do in the array. And so that would be a way to kinda keep us, the DBAs in the corner, so we don't do bad
things to the rest of the array. So that's the answer is yes. Okay. So Anthony, before we go any further, I'm gonna also push out a quick poll, uh- Sure related to Pure Storage solutions. So everyone, if you're interested in learning more about Pure Storage, and what they
have to offer there, and again, for us Server DBAs, as well as there, again, enterprise, and I know Anthony didn't get into what Pure Storage has to offer. But they really can do, do a lot to help us as DBAs from a lot of different perspectives. The performance is definitely amazing. There, and again, if you're facing issues and maybe have a need to really
underlying performance without making application changes, Pure Storage can certainly help. Same thing with things like, ransomware. They have a And there again, I know Anthony didn't get into any of this today. Yeah. But Pure Storage has a whole ransomware solution where, they can really protect your organization from ransomware by using
essentially a vault-type paradigm- Mm-hmm as well as the ability to restore databases. And there again, I'm talking about not 1 database, I'm taking, talking about, like, all of your databases at once, very rapidly. They also have, I know they have an S3 solution as well, which there again, is very flexible.
They also can help folks, and I'm sure these folks aren't here today, but you probably work with them at your company. Folks that are doing, quite a bit on the, the analytics or business intelligence or machine learning side, Pure can help them as well. There again, they have solutions there again, that can really help those
different types of workloads. So if you are interested in maybe there again, what you saw today related to backups, and having a solution that, and there again, what Anthony presented today is-Available natively, but what Pure does is really help from that recovery standpoint. And things like, cloning, cloning snapshots and really providing availability
across multiple data centers or clouds or whatever the case may be. So Pure really does have a lot to offer, and they've really been a great partner to, SQL Tips for a lot of years. So if you are interested, there again, if you're interested in maybe a personal demo to talk about some things you kind of have in your environment, some of your needs.
They also have a way to kind of test drive, so we'll call that a free trial. They do have a lot more technical information, that there again, those if you're looking for a proof of concept, there again, I know another thing Anthony talked about, and unfortunately, I've seen the pain is something like reseeding an availability group is definitely very difficult.
And there again, these guys can help, with some of the scenarios like that. So if you have a chance, definitely check out what they have to offer or s-fill out the survey and there again, we'll have some folks from Pure Storage, get in touch And I apologize I've been talking so long, but Pure really does have a lot to offer. Yeah. So I apologize for that.
So, um- No, no worries. I threw some, I threw some QR codes on the screen for, for everybody. So hopefully, yeah, you can, can see that there, so. It, it, I'll definitely pass those along. I know we have a couple, technical resources as well.
So another question there again, Anthony, was related to snapshots. Um- Mm-hmm so in terms of storage pool, if you're on AWS, can you something like a EBS volume? Yeah. I've, I've researched it. I haven't written that code, but I know, if you can take, if you can take a snapshot, a
collectively of an EBS disk, yes. The difference is I think, like in Azure compared to what I showed you in, in our platform, the protection group, it takes a snapshot atomically of all the volumes in the protection group. In the Azure implementation, I have to loop through all of the virtual disks and snapshot
them independently, which does increase the, the duration of the IO stun because I have to walk through each virtual disk whilst the database is in backup mode. So it does take a second, but it's totally doable. Okay. Awesome. Thank you. Next question is related to SQL Server table level, level recovery.
Is anything like that available natively in SQL Server, Anthony? No, but it You know, I think one of the that we talk to our customers about is that four TiB database, right? Like, I might not wanna overwrite that thing, but what I could do is the same exact process, but I just clone it to new volumes on the server, and then I just drag the rows back
over, right? Or maybe to another server and drag the rows back over. So it's not that we have table level recovery, but we can erode the time that it takes to, to perform that operation. Cause I've done it before where I accidentally did some bad things in the database, and it took me a very long time.
Well, I, I, I threw back everything I could because I had a BI system that that I deleted. And then for me to get the other rows that weren't in that latest BI system took me, another day, like, to get the remaining So, the idea is I don't have to land a backup somewhere. I don't have to do the restore.
I don't have to figure out the rows. I can just snap clone, drag the rows, right? Okay. Perfect. Next question is related to your second set of demos. Um- Mm-hmm do you need to run the PowerShell code from a primary AG?
That code was actually running from my laptop, and connected over the network there's no dependency on running it on the AG, but you would-- you do need to coordinate the work, against the right replica. So for example, when I put the database in, in, snapshot backup mode, yes, that was coming from the, the soon to be primary.
And then when I cloned it and brought the database online on the, in restore mode on the secondary, that was targeted at the secondary. And so the variable names in the code when you review the code are, should who, who and where, I'm talking to, right? Okay. Awesome. next question is related to what tool
you're using to run the PowerShell and, uh- Oh snapshot code. Yeah. So I'm a, I'm a VS, I'm a VS Code nerd. Don't, don't tell Aaron Stellato, who's in charge of SSMS now. But, all of this is using, VS Code, and the PowerShell extension here at the bottom, you can see that. And then when I switch over to the SQL ones,
I'm using the SQL extension, in VS Code, gives you kinda If you remember Azure Data Studio, they took that functionality and dragged it into VS Code and, and gave us, gave us this. And so, I just, I love being able to do that work natively on my Mac. And so I'm doing that here, in VS Code.
Okay. I've been popping a couple more questions in there for Anthony, if you could, take a look at them. Oh, cool. All right. Okay. So- Differentials. Yeah. Got it. So as, Anthony's looking at questions, I just
wanna remind everyone, we do have a poll up right now, and I know we've kinda hit that two o'clock mark and we still have some questions. So Anthony, if you have a couple more minutes, we'll try to get through a couple more questions. Yeah. Okay. so everyone, we do
have a poll up right now. They're again related to interest in Pure Storage solutions. So if you'd be interested in a personal demo, a free trial, more technical information or proof of concept, please respond to the poll now. I did send, three URLs out in the chat as well to everyone.
So if you are interested in some Pure Storage, information, feel free to check out that as well. Um- Yeah. And I'm gonna drop, I'm gonna drop the URLs for the repos, in the chat here too. So. Okay, cool. So are you good with the
questions I sent you, Anthony? Yeah. Seven's good. Eight's good. Yeah. Yeah, we can do all those. Okay. So let's start with, so, can snapshot backups be full, differential or mixed?
Is, is there I know everything you went through kind of today was a full backup of transaction logs. Uh- Yeah can differentials get into the party too? They cannot. But conceptually, we, we as, as Pure and, and Azure, I know are doing that under the hood, right?
In that it's the change blocks from-the last snapshot, right? Not necessarily the next snapshot backup, but the last snapshot. So, physically, on our system, they're incremental because it's the changes from the last snapshot, where differential would be the changes from the last full. So there's really no need to implement that functionality, at the SQL Server level because
they're always gonna be incremental at the, at the storage tier, right? Okay, perfect. So the next question is related to system databases. Mm-hmm. If the system database is on the same volume or protection group as your user databases, would you not wanna do a restore like you
demonstrated today, or would you- Correct. Yeah. Okay. No, I Yeah, I, historically I would always park, When I build a SQL Server, I park my system databases on a dedicated volume just for them. U-Usually I call it the S volume, 'cause I still use drive letters. I know mount points are a thing, but
and I, I just, that's always my preference, to keep them in the corner. Yeah. Okay, perfect. Next question is related to, So this is kind of a similar ques- Nope. Let's go to question nine instead. I'm sorry. So what do you think about snapshots,
VMware, VMware snapshots? How do those kinda compare, or how do they relate? Sure. Yeah, so that's gonna snap the whole, whole VM. And one of the challenges with let's just, we'll focus on VMware snapshots, 'cause the vast majority of SQL Servers that I see are still on VMware.
You know, if you look at the snapshots that, that we do on our system, they use a concept called redirect on write, where the VMware snapshot uses a thing called, copy on write. And we've all gotten yelled at by our VMware administrators for holding snapshots too long, right?
We're deleting the snapshot, and it has to merge physically in the VMFS file system to get to a consistent state, and that causes a lot of IOPS pressure. On FlashArrays, those are gonna be metadata-driven, so literally the, the bits and bytes aren't moving around when I delete a snapshot. It's a metadata operation.
And so it's, it's a lot faster and less There's no performance penalty in the system. So that's, that's really the advantage there over VMware snapshots. Now, there is a whole concept of ops, like who does the restore, right? If the VMware admin's doing the restore, they're gonna use their tools, right? If you're doing the restore, you're gonna use your tools.
And so there's, there's that, lens to view that question through, too. So, yeah, so good for that one, Jeremy. Okay. A couple more questions I popped up there, make sure they're okay. Mm-hmm. Downsides. Yeah, yeah, we can kinda go through 'em all.
Oh, okay. So what is the main, disadvantage or downside of doing snapshots, backups, restores via snapshots? I mean, honestly, y'all tell me. Like, I clearly believe that this this works well. I think obviously, like, there's the cost, right?
Like, if I'm, if I'm maintaining snapshots on Tier 1 storage, I have to m-make sure that, you know, if we re- meet the recovery objectives of the org, how many snapshots do I retain? But I still have that challenge when I'm backing up natively, too. I have to store those backups somewhere.
So, there's that. I think, you know, the fact that we extend those DBAs to the storage tier storage is, is a challenge, right? But I think the outcome of instantaneous restore, regardless of database size, is compelling, right? And if you have a, a ten TiB, 50 TiB,
100 TiB monster that you have a hard time backing up, then this can help you solve that problem. So it's, it's that part. Like, the, really it's the, it's the operations, I think is, is a, is a challenging thing, too, to kinda overcome the, the WEKA have to learn a little bit about storage part, right?
Okay. Perfect. this next quest-question is related to, TDE or encryption. Sure. Can you use TDE or any other type of, encryption with snapshots? Yeah, totally. So we are This, when the bits and bytes hit
our Flash, it's encrypted by us. It's also encrypted in transport. But TDE specifically, that, that's just bits and bytes that are on storage. So as long as you have the, the, the certificate stack implemented inside the instance, then you'll be able to restore the database.
And I've, and I've done this, we've probably all done this with AGs. If I have a TDE-encrypted database on AGs, I have to take all the certs from the primary and put them on the soon-to-be secondary, and I have to do that same work, when I'm using snapshot backup. I have to make sure that the certificate stack is on that target or soon-to-be replica, so that when SQL Server goes to try to open up
the database, it has the encryption keys to do that work, right? So, yes. Okay. Awesome. Next question is related to, snapshots working on-prem and in the cloud. So is it possible to create a snapshot, on-prem primary and then have an Azure
secondary for, for disaster recovery? Yeah. So in our, our universe is Pure FlashArrays on-prem, Pure Storage Cloud dedicated, or Pure Storage Cloud native in Azure. So Pure Storage Cloud dedicated, we can, is quite literally, the same API, the same version of Purity in Azure, so that those snapshots would just replicate to Pure Storage
Cloud-native in Azure, and then you'd, you could literally use the same code, that I showed here. It's just, it's that it would be pointed to, our device in Azure. Right. Awesome. Okay. So at this point in time, I wanna let, just remind everyone we do have a poll up.
If you haven't had a chance to vote, please vote now. There again, are you interested in learning more about Pure Storage solutions? So please vote now. I also wanna make sure everyone knows that we will be sending a follow-up email to check out the archive here in the next 24 hours or so.
So please be on the lookout for that. All right, and then Anthony, is there a QR code or something you wanted to share? We had one of the poll results. Yeah, it was just Yeah, so here we have, just a couple links for everybody to look at, here at the bottom, and I put those in so we can grab them there. And then, yeah, so you talked about Test Drive.
If you match that QR code in the middle, you can get a Test Drive. If you match the QR code on the right, you can grab me, Andy, Andrew Pruski. We'd all lo-love to talk to you more about your SQL Servers. And on the left is, is kind of the stuff that, the, the links that you put in the chat there, Jeremy, the, kind of the wider solution architectures.
And there's a bunch of, sorry, I'm looking for it. There's a bunch of content around these concepts, in the QR code on the left here. Awesome. Thank you so much, Anthony. Any, final words for today? I just wanna thank everybody. Seems lots of interaction, fantastic questions.
And I love the fact that there's open minds to kinda shift the, the, folks from no to yes. Loved that. So super excited to see that. So thank you, everybody. Absolutely. Definitely getting a lot of claps from a whole bunch of people. So just wanna make sure you know, and they're just keep flying in.
So wanna make sure you know- Very cool. Very cool that that happened, now, when we first started Q&A, and then a few times during your session. So definitely got a lot of love from the community today. So, with all that being said, definitely thank you so much. Please be on the lookout for some emails from MS SQL Tips.
And, please help spread the word about MS SQL Tips. Once again, this is Jeremy Catholic. Have a great day, and please tell a friend about MS SQL Tips.com. Thank you. Thanks, everybody.
Anthony Nocentino
Senior Principal Field Solutions Architect, Everpure
A few years ago, we asked the question: “Are snapshots backups?”
Since then, application landscapes have evolved, SQL Server has introduced new snapshot-aware capabilities, and data protection expectations have shifted toward faster RPOs, automated policies, and fleet-wide recovery intelligence.
In this updated session, we revisit the snapshot vs. backup debate with fresh perspective. We will look at how array snapshots work and how they can provide consistent recovery points. We’ll dive into SQL Server’s T-SQL Snapshot Backup feature for application-consistent snapshots, common backup and restore scenarios, and show you how to build a queryable metadata-driven snapshot catalog enabling you to search snapshots by database name, instance, and timestamp across your entire storage fleet.
This will be a demo-heavy session with practical scenarios and deep dives, and I’ll share my opinions on why snapshots have become a cornerstone of modern data protection strategies.
You will learn:
- How storage array snapshots work and differ from traditional backups
- How to use T-SQL Snapshot Backup for application-consistent database snapshots
- How to seed an availability group from a snapshot
- How to build a searchable snapshot catalog that follows your data through replication
- Why snapshots have become a cornerstone of modern data protection strategies
We Also Recommend...
Personalize for Me
My Updates
No updates at this time.