>> Learn about some of the new snapshot capabilities
built in with T-SQL in SQL Server 2022.
This week on Data Exposed.
[MUSIC]
Hi, I'm Anna Hoffman and welcome to this episode of Data Exposed.
Today I'm joined by Perry,
who is no stranger to Data Exposed.
Perry, thanks so much for joining us on the show again.
Can you tell us a little bit about what you do?
>> Sure. My pleasure Anna to be here,
my name is Perry [inaudible].
I'm PM with a SQL Server Engineering team currently focusing
on SQL Server engine features, scalability and performance.
>> Awesome, cool. We're glad
to have you on the show because we're going to talk
about some new and exciting stuff related to backup.
When people think about backups,
a lot of people think about streaming backups.
Can you tell us a little bit about the pros and cons?
>> Sure, streaming backups are essential of course,
but they are very resource intensive.
They require a lot of CPU I/O memory
and their size of data operation.
Meaning that the larger database,
the longer the backup as you very well known we take.
The alternative though is a snapshot backups.
>> Okay, tell us a little bit more.
>> Snapshot backups, again are not new.
They've have been around for a very long time.
They're extremely fast, lightweight,
and customers can backup and restore in seconds.
But that comes with a couple of drawbacks
because customers are relied on third party solutions,
and these solutions are built on top of
SQL writer that itself depends on
VSS and VDI and they can be very complex solutions to implement.
>> Okay.
>> But we have a solution.
>> Everything you're talking about is pre SQL Server 2022?
>> Correct.
>> The solution is in SQL Server 2022?
>> It is, yes.
>> What's the solution?
>> The solution is a set of new T-SQL commands that allow you
to take essentially a snapshot backup much easier.
I'm going to show y'all that.
>> Okay, cool. How does it work?
>> Three steps with SQL Server 2022 to take a snapshot backup.
First, you need to suspend the database that
you're going to take a snapshot backup on.
With this new, this SQL commands suspend for snapshot backup on.
The user still needs to
initiate and perform the actual snapshot off the disk,
and then the user needs to backup the metadata
of the database that he or she just suspended.
You can do this Anna for
a single database or a set of databases with one command.
This is showing you the,
for multiple databases it's the same command,
but you pass a switch
for group of the databases that you will suspend.
Again, user initiates and performs
the actual snapshot backup as before.
Then you can back up the metadata again in the same BKM file.
>> Okay, awesome, cool.
>> Last slide is,
you can do this for the whole server,
meaning you can backup all the database under
the same SQL Server Instance or as I showed you,
a group of the databases.
>> Got you.
>> Restore looks very familiar.
It's the same SQL command,
but we're adding this width metadata only,
and existing options to move
the data and log file if you have
two after you restore the snapshot backup.
>> Got it. It seems pretty straightforward to get started.
>> Yes.
>> Can we take a look
>> Of course. Let's switch to the demo.
Here we are in SSMS.
I'm running SQL Server 2022.
I'm going to create two sample databases
identical with a table containing 1,000 rows.
I'm going to go ahead and execute that.
Executing so 1,000 rows in each.
Just to verify these are my two databases identical.
Next, I'm going to delete all previews,
backup history, and files just to start fresh.
Then three steps to perform the snapshot backup.
I'm going to suspend the database if you remember from the slides.
I'm going to take a snapshot backup of the disk with
a small utility that I have
here and I'm going to backup the metadata.
First 1,000 rows, if you remember in this table,
I'm going to suspend the database with this new T-SQL command.
Here you see that the database now is suspended.
>> Okay.
>> Just to look a little bit under the hood on
the locking that we're acquiring while we suspend our database.
Here we take three locks.
We take a third lock on the master database,
this is the first row here with the resource database ID
and two locks on the database that
we just suspended and exclusive and a shared lock.
Then going back to the script,
I'm going to make it a little bit interesting.
I'm going to copy this, insert in a new window.
I'm going to try to insert
this one more row in the table and
you see that this insert is currently blocked.
>> Because this should not work.
>> The database has been suspended,
so this is not going through.
Then going back to the script,
I'm going to take a snapshot of the disc with disc utility.
>> Okay.
>> Bear with me here.
Then I'm going to backup the metadata
of the database in this new BKM file.
Now I have everything that I need to restore the snapshot backup.
>> Okay.
>> Let's see how that looks like.
But before we do that, let's go to the other Window
and see this is now has been completed,
so that row has been inserted to the table.
But because we performed
the snapshot backup before they succeeded,
we're not going to see that extra row on the restore.
>> Right.
>> This is the command to unsuspend the database,
which is needed only if the backup metadata command failed.
You will see here that the database
is not suspended anymore, so it's ready to go.
Then going back here, let's restore it.
I'm going to drop, this is sample database one,
so this should be gone from here.
>> Yep.
>> Then a couple of steps to restore it.
First, I want to show you the file list
only just to inspect a little bit BKM file,
what are we storing there?
Here I'm going to zoom in a little bit and you see I have
my data file and the log file and other metadata,
the size, the group,
the type, the LSN,
all the necessary information for me
to restore that snapshot backup.
Here I'm going to mount
my snapshot if you remember with this utility.
>> Then it's time for the restore. Restore is done.
As you see these are one of the advantages of the snapshot backup.
It is working so fast, yes.
It's irrespective of the size of the database.
>> Amazing.
>> Here I should have my sample database one back restored.
Can you guess how many rows am I going to have in the table?
>> One thousand.
>> I'm paying attention, but the other one should have 1001.
>> Yes. That was
a quick demo to perform
a snapshot backup of one database and then restore it.
If you remember from the slides,
we can do it for multiple database
with one command. Let me show you that.
>> Okay.
>> Same script, I have two databases with 1000 rows.
It's identical.
Then I'm going to go ahead and suspend
both of them with one command with this group switch.
Here you see both of
the databases will acquire the necessary logs, I/O is frozen.
If you remember, you cannot write to the databases,
but you can still read from it.
Then I'm going to take a snapshot again
with this utility that I have of the disk.
>> It's really just the same commands,
is just instead of using this group and specifying the database.
>> Exactly. Backing up
the metadata for both of those databases in the same file.
Then just verifying that I still have 1,000 rows in
there dropping both of the databases.
This should be gone from here.
>> Yep.
>> This is another cool thing.
Quickly before I restore it,
I'm going to walk you through the header only and file list only,
again, they're not new commands,
therefore regular backup and restore.
But just to show you, here I have two sets,
for DB1 and DB2.
Again, data and log file as before.
These are in the same file.
Then mounting the snapshot
and then restoring both of these databases.
>> Again super quick.
>> Again, super-quick.
>> Yep, nice.
>> I have one more flow,
one demo that I'm going to show
you how you can take a snapshot backup,
but also apply on top of it a log backup.
Hopefully, we will be able to see if you
remember that one extra row.
With this demo, we will be able to see 1,001.
If everything works as expected, yes.
>> You've dropped a lot of databases so [inaudible].
>> I'm going to suspend the SampleDB1 again.
Now I'm going to copy this in my window here,
and I'm going to execute it.
Again, it's not going through because
the database is currently suspended.
I'm going to take a snapshot,
backup the metadata of the database.
These should be now inserted and I'm going to take a log backup.
Now the database probably is not suspended,
but I'm going to execute it.
It is not suspended, it's ready to go.
Once again, dropping the database.
SampleDB1 is gone.
Mounting the snapshot.
>> Okay.
>> Now restoring from
the snapshot backup and also applying the log backup.
>> Okay, got you.
>> Now if everything works as expected,
the count should be 1,001.
>> Nice. Got it.
>> That was a quick demo on the new
SQL commands to take a snapshot and then restore it.
But I'm going to leave you with
the monitoring of the snapshot because I think
it's very important to look under
the hood and see if everything works as expected.
For that purpose, we are introducing three new properties.
One server property called suspended database count.
This will give you the count of
the databases that have been suspended.
Remember if you have multiple database
under same instance and you suspend two,
that should return the number of
the databases been suspended and two database
properties to again inspect
if they are suspended or not individually.
>> Got you.
>> Just to demonstrate that very quickly,
if I suspend the whole server,
and I have two databases.
>> We're expected to see two.
>> Yes. We see two here.
>> Nice.
>> Then individually, this should all return true.
One for SampleDB1,
and this would be one as well.
>> Nice. That's useful.
>> Yes. Finally,
we have a couple more things.
DM servers suspend status can be used to
inspect the session that was used to suspend the database.
If something goes wrong, you can always go
back in this DMV, get the session ID,
kill it, and then the database will become unsuspended.
Also the sys.dm_tran_locks can help you
see the logs that
are taking during the suspend and unsuspended of the database.
I'm unsuspended, and I should be back where I studied.
>> Awesome, cool. No, I think
it's great to show us how it all works,
but it's also great to show us how you monitor it
because maybe something goes wrong or
maybe you want to see under the hood what's happening.
I think it's great that we also added
these new properties that people can use.
>> Absolutely. I think it's very important because we have,
as you very well know critical OLTP workloads.
We cannot afford to leave the database
suspended its mission critical.
We're very excited to
offer DMVs and options to look under the hood,
on the locking on the session that we used, and everything else.
>> Awesome, cool. Perry, thanks so much for coming on the show.
To our viewers, now you've heard it here first about
some of the updates to snapshot backups and SQL Server 2022.
If you like this episode, go ahead and give
it a like leave us a comment,
go and get started with this.
You just need SQL Server 2022 to try it out.
We'll put some links in the description for you to learn
more and we hope to see you next time on Data Exposed.
[MUSIC]