(light music)
- Up next we'll take a deeper look at recent updates
to SQL Server 2022
and what it means for your hybrid workloads,
including online bidirectional disaster recovery
between your local SQL Servers and Azure.
Differential snapshot-based backup and restore
to achieve massive speedups,
as well as anywhere management of your SQL Servers
with Azure Arc-enabled SQL provisioning,
including new pay-as-you-go licensing.
And if raw performance is your thing,
we'll demonstrate intelligent Degree of Parallelism feedback
where SQL optimizes the thread count
of your queries automatically.
And to walk us through all the updates
I'm joined once again by Bob Ward.
Happy to be back.
It's really great to have you back on the show
to go deep on all the SQL Server updates.
There's a lot of updates there to talk about.
SQL Server 2022 is now generally available,
and there's a bunch of incremental improvements
around resilience, even deeper Azure integration.
And as always, we're incorporating optimizations
to reduce costs for both licensing and compute.
Whether that's local or in the cloud,
this includes new ways to improve query performance.
- And this is going to be an action packed show.
And when you mentioned, by the way, Azure integration,
something that I always think about
coming from an IT background on prem
is that that means like a one-way road to Azure
when we configure hybrid going to the cloud.
- I'm glad you brought that up, that's not exactly the case.
At the end of the day,
on-prem SQL Server and hybrid configurations
are a reality for so many reasons today.
Of course we've done a ton of work to integrate SQL Server
with Azure services for analytics, policy,
directory services, security,
and anywhere management with Azure Arc.
One of the areas Azure integrations
makes a ton of difference
is in backup and disaster recovery.
You can now link your local SQL Servers
to Azure SQL Managed Instances
with either serving as a primary or secondary.
And it's bidirectional, so you can swap their roles.
So data can move up to the cloud and back on-prem as needed,
so you're always in control.
Let me show you.
Here I've got SQL Server 2022 installed.
There's a Wide World Importers database
and I've replicated it to a Managed Instance
as you can see here.
I've also created a couple of custom tables
called vehicles and vehicle stock items.
The data's been synchronized
to Managed Instance for these tables.
To prove everything is synchronized,
I'll run this query to show
the vehicle cargo count per city.
Now the tab on my left is my local SQL Server,
you can see it set to read_write
with database version number of 957.
The right tab is our Managed Instance.
It's read_only with the same database version number 957,
so we're compatible and synced.
Now we're going to failover online
from SQL Server 2022 to Managed Instance
here in Management Studio.
So I'll go to this menu, pick failover database,
and use a new option
called Failover between SQL Server and Managed Instance.
That's because I'd like to keep SQL Server
as part of the distributed availability group,
so I can fail back at any point.
Now I'll failover,
and you can see that I'll be reversing roles
from SQL Server as the primary
and Managed Instance as the secondary.
So I'll confirm I understand and click next.
Now I have to use my Azure account
so it can connect to my Managed Instance.
So I'll hit next again.
And then I'm going to choose, in this case,
a planned manual failover.
So I'll choose I understand.
And now SSMS will go through a process
of synchronizing the two systems and switching roles.
- Okay, so at the moment now your primary
is Azure Managed Instance,
but you've got a secondary then as your local server.
- That's right.
And depending on how much data
this can kind of take a few moments.
We've swapped the roles temporarily
because the thing I really want to show you
is how we can also fail back to the local SQL Server.
Now with the process finished
I can check the state of both systems.
I'll run the same query as I did before.
You'll see the local SQL Server is now read_only.
So I'll run the same query
for Managed Instance and it's now read_write.
And now that Managed Instance is our primary,
let's make a change in that Managed Instance
to make sure changes are reflected
back in SQL Server when I fail back.
So I'm going to change all the vehicle cargoes
and move my cargo vehicles to North Richland Hills
by setting the city to all the same value.
I'll run this query here in Managed Instance
and it's affected in all those rows.
Okay, so now here comes the moment of proof.
We're ready to fail back to our local SQL Server
and switch roles once again.
I'll go back to my SQL Server,
and again I'll select Azure SQL Managed Instance
link failover database.
Next I'll choose failover
between SQL Server and Managed Instance.
And I'll confirm that I understand.
Now I've already signed into Azure,
so what I'll do is I'll keep
the planned manual failover option confirmed
and make sure my workload has stopped.
So let's start the failover.
This normally kind of takes a few minutes.
But all right now we've switched back.
You'll see the local SQL Server is back to read_write,
and the Managed Instance is now to read_only.
Now to prove our data is synchronized
and we didn't lose anything,
I'll run this query to see if all the vehicles
were changed to North Richland Hills
on our primary local SQL Server.
And as expected, they have been.
And in case you're wondering,
using Azure SQL Managed Instance as a disaster recovery site
can be done without a SQL Server license.
- And this is great
because we're all trying to find efficiencies
where we can to save costs.
Instead of buying expensive metal
for things like backup and failover,
you can use the cloud for that instead
and your data can easily flow as we saw,
back from the cloud to your local server.
- It's so super flexible, and now just a few clicks.
And speaking of efficiencies
that can lead to more cost savings,
we've made huge improvements to further decouple
storage from compute.
This opens up some new exciting capabilities
for snapshotting backups,
where you'll be able to do point-in-time restores
for massive multi terabyte databases in seconds,
while also saving on compute and bandwidth costs.
I have a database here that's about 2.5 terabytes in size
that we're going to be backing up.
Let's first see how long it takes
to back up our database to an Azure Blob Storage container.
Now right here we'll create a credential
as an access key to the container.
And then we'll kick off this backup and come back
and see how long it takes.
- Okay, and that's probably going to take some time.
So how long are we talking?
How long does it really take?
- Well maybe as long as your average movie
but not quite as entertaining.
That said, to save time, we ran this in advance.
And as you can see here at the bottom,
it took over like 5,000 seconds,
which is about an hour-and-a-half.
Now we can do much better than that.
Let's see how long it takes to do a snapshot backup
just to gauge how much faster it is.
I'll snapshot this disk.
This is the F drive
which holds all of our data for SQL Server.
In the Azure Portal you can see
for this virtual machine's data disk,
we already have a full snapshot of this drive.
So we can create an incremental snapshot
and back up everything super quickly.
Okay, so here's how we're doing our snapshot backup.
Everything described in the green commented lines
in the code here.
We first suspend I/O for the database using T SQL.
We'll perform the actual incremental snapshot in Azure.
Finally, once that's finished,
backup this metadata of the database so we can resume I/O.
All this being done using scripts to make this very quick,
but I kind of want to show each step manually.
I'll suspend I/O for the database,
and here's that confirmation.
Now back in the Azure portal,
I'll create the snapshot from this data disk
as an incremental snapshot.
Let me give it a name here
and I'll say snapshot from data disk zero incremental.
Okay now I'll scroll down
and mark this as an incremental snapshot,
then review it and create it.
And this time we'll let it run without speeding it up,
because instead of taking more than an hour
it will just take a few seconds.
Now this is using Azure storage
to capture incremental changes to the disk,
and that's how we had to stop our I/O.
So we can do this at the disk level.
Now it's finished, it only took a fraction of the time.
Then we simply run this command
to back up our metadata, resume I/O, and we're done.
Something that took over an hour-and-a-half before
literally now takes 17 seconds.
- Wow, math was never my strongest subject
but if I'm doing it correctly
that's like one third of 1% of the time.
That said though,
how long does it take to restore from the snapshot?
- Well depending on your configuration
your results can vary,
but it's so much faster than incrementals.
Let's try that out.
Now to restore from the backup,
we first need to attach or create a new disk
inside our VM based on that storage snapshot.
Then we can run this restore command
to restore the database from that disk.
Back in the Azure portal,
this new data disk you're seeing
was created based on the incremental snapshot,
and I attached it to the VM.
Now go inside the VM,
this shows up as the G drive with our data.
Now we need to restore to a new database.
Pointing to that metadata backup
with the original database name
and the set of files that are now on the G drive.
So I'll run it, and look at that, in only five seconds.
I've been able to successfully restore
a snapshot backup compared to potentially several hours
to restore a stream backup from Azure storage.
- So that means, by the way if you're keeping score at home,
if you would've scripted the entire procedure
it would've taken just 22 seconds
for two-and-a-half terabytes.
So those are some pretty good updates
in terms of resiliency, but you also mentioned
that we're also improving
the management aspects for SQL Server.
So what's new there?
- That's where Azure Arc comes in.
This gives you a unified control plane
with the same types of controls
you'd use to manage SQL servers in Azure.
And this opens up a lot of additional options
for deployment, management, and security,
just to name a few.
It all starts with SQL Server 2022 setup,
which now offers an integrated option
to use pay as you go billing through Microsoft Azure.
You could even pick Standard Edition to save costs.
So I just need to log in with my Azure tenant,
then you'll enter the standard fields
for things like subscription, a resource group, and region.
Now in the Azure portal, because I'm connecting to Azure,
my servers automatically rolled into Azure Arc
as you can see here.
Notice in the overview screen
I've got some pretty interesting information.
For example, I can tell that I'm running standard edition,
and using that new pay as you go licensing.
And there are other benefits to being on Azure too.
I can use Azure Active Director authentication.
You can see at the top of the screen
I'm set up as an Azure admin using a AAD.
Now optionally I can set up
Microsoft Purview access policies
to push authentication and authorization
down to my SQL server.
And I can see all my databases here
that have been deployed as well on SQL Server.
You know I can even use Microsoft Defender for cloud
to get configuration recommendations and security alerts.
You can see my server is at risk
for potential SQL injection attack.
And the vulnerability assessments
also point out common SQL Server
best practice configurations.
- And by the way, you're just kind of scratching the surface
in terms of what Azure Arc can really do,
where you can literally manage SQL Servers
wherever they are, even across clouds.
Why don't we switch gears though to performance.
I got to say, being a former DBA and IT pro,
one of my favorite parts of having you on the show
is really going deep
on all the different query optimizations
that we're making with SQL.
- I don't want to disappoint.
Last time my showed parameter sensitive plan optimizations.
Now we have built-in query intelligence
with Degree of Parallelism feedback,
where we monitor queries that use parallelism and server.
Then the engine manages resource consumption automatically
based on query duration and CPU usage.
Here I've got a stored procedure I'm running
and it's using what's called a parallel plan
with an index scan.
Now the problem is the max Degree of Parallelism, or DOP,
is configured for auto or zero,
which means it's going to use
up to 32 threads to run this query.
Now we can use built-in intelligence in SQL server
to find out if I can use less threads
to achieve the same query performance.
Now you enable it using SET DOP_FEEDBACK=ON,
and this works on any SQL query running
at compat level 160.
Now here I'm looking at extended events to show overtime
how DOP feedback can reduce the time it takes
to give the same query performance
but reduce the overall CPU consumption of the query.
That's called a feedback cycle.
Doing validation test and stabilization,
as you can see in the query DOP column
across a set of continually lower DOP values.
And I can go in as well,
finally I've got a stabilization point now.
I start at 32 and I'm down to 12.
12 Here is the minimum DOP
needed to run the query for the same duration.
And you see it here through what's called feedback DOP.
And a stabilization point here on this bottom row.
Now inside the query store we store persistent feedback
so that way it's persisted anytime you restart SQL Server.
So we can always go back and make sure
that we run this DOP at 12,
which is our new parallel efficiency or minimum DOP.
You can also see we've persisted memory grant feedback
which is another capability for intelligent performance.
Now if you really want to see the performance differences,
the way to do this is you can go into query store reports
to easily see this over time.
I can look at the average duration
and check out the plan summary view on the right
as SQL lowered the DOP value over time.
Look here, the query gets actually faster
until it gets down to that stabilization point.
Now I'll change the view one more time
because what's really telling is the average CPU time.
Again on the right you can see kind of
a staircase fashion over time.
I start up here at the top
and I've achieved lower CPU values at the very bottom
where I've reached that stabilization point.
- And it almost seems counterintuitive.
You've gone from 32 threads to just 12 threads.
You got civil performance,
even faster performance in your case.
And it's also meaning that your CPU cycles
required to run it are going to be reduced as well.
So love going deep on all the updates
with you today for SQL Server.
For anyone who's watching right now,
what do you recommend they do to get started?
- Well try it out for yourself.
You can get started with a free version
of SQL Server 2022 today at aka.ms/getsqlserver2022.
And also to learn more,
check out our free online workshop
at aka.ms/sql2022workshop.
- Thanks so much again for joining us today Bob.
And of course, keep checking back to Microsoft Mechanics
for all the updates.
Be sure to subscribe to our channel if you haven't already.
And as always, thank you for watching.
(light music)