- Coming up, we're gonna take a look at how you can run
your even most demanding workloads on Azure
and future-proof your apps with near limitless
growth capacity using Azure SQL Database Hyperscale,
and we're gonna show you how you can easily update
your existing databases to hyperscale without
rearchitecting your apps, and we'll demonstrate
major advantages from super fast data backup
and restore times, and the flexibility
that comes from independently and fluidly scaling
compute and data replicas.
(upbeat techno music)
So today I'm joined by a well-known industry expert,
Kevin Farlee, from the database engineering team in Azure.
Welcome.
- Thanks for having me.
- It's great to have you on. This is a really popular topic,
Azure SQL Database Hyperscale. Can we talk about what it is,
and what we're solving for here?
- Sure, it's a cloud-native performance-tier
of Azure SQL Database, so it's highly scalable
in the storage aspect, and you'll find that there's
not even a max database size in the portal
when you go to configure it.
You can scale your compute, it's got elastic compute,
you can scale up and down very simply.
It's a fully managed service as with the other
Azure SQL Database offerings.
Uses the same engine as SQL Server, so the behavior's
gonna be very familiar, it's exactly what you're used to
with Azure SQL Database or even SQL Server on-premise.
- You don't have to learn anything new to use it?
- Exactly, there's no new syntax, you use your existing
tool set, you don't have to learn new tools for this,
but you still take advantage of the built-in intelligence
and security capabilities that come
with the Azure platforms, you get the
best of both worlds there.
We have a few major differences from the other offerings
though that come with very large scale, so with very large
database operations, we remove a lot of
the common pain points around performance
and storage limitations.
With Hyperscale, the time to resize compute is independent
of the size of data.
We support up to 100TB of storage today, but of course,
that'll expand further over time.
Backups are near instantaneous, and you can do data restores
in minutes rather than days, or even weeks.
- This is a big deal.
To put this into perspective, that amount of
storage capacity is orders of magnitude much larger
than most PaaS databases out there, so you're not going
to have to provision any more storage.
That's another component effectively that's
broken away from the compute layer and the logging layer,
and you don't have to worry about things like performance
degradation, which is common when you're running ongoing
operations on these huge data sets over time.
So all that pain goes away.
- It does.
- So how's this actually built, what's behind it,
what's the architecture of this then look like?
- [Kevin] So unlike traditional databases,
Hyperscale decouples the storage from the compute
and the log engines.
These components work and scale independent,
and then they work together.
The storage engine, as always, persists all your data.
This case, it's built on a microservices architecture
that can scale out horizontally as needed.
It consists of page servers, each page server
manages 128GB worth of data pages, and each page server
has a secondary for resiliency.
When your application requires even more storage,
we just add more page servers to the configuration
up to 100TB, or more someday.
- [Jeremy] So does this architecture then help in terms of
querying speed as well and performance
against these huge data sets?
- Yes it does.
In fact, let's start with the read operations.
We have multi-tiered architecture
with caching at each tier, so the data engine can go fast.
The computes have their own SSD based caching
that scales with the number of cores you configure
for your compute layers.
That way, we get access to the hot data set very quickly,
and to speed up querying performance, each page server
has SSD cache for the entire range of data that it manages,
so a 128GB page server is gonna have 128GB of local SSD
with all of it's data cached.
So when a query comes in, the compute engine
will first look in it's own cache to see if it's got
the data right there locally.
If so, that's gonna take about half a millisecond
to retrieve it.
Extremely fast.
- [Jeremy] That's the fastest kind of layer
where it can look for it in the cache.
- [Kevin] Right, if it doesn't find what it needs
in it's local cache, it's gonna make a request
to the page server that manages the pages that it needs
and that whole trip is gonna take about two milliseconds.
- [Jeremy] So we're seeing then basically at the top layers
it's really really fast cache, and as it gets slower
and lower into this Azure storage, it's getting a bit
slower, but it's always gonna try to get the local cache
when it can, to avoid having to go down
into the Azure Storage Layer.
- Right, you're getting your cache from where you need it
when you need it.
- All right, so that's read operations, super fast,
half a millisecond to two milliseconds,
but what about write operations?
What happens there?
- To speed up write operations, this is where the log server
really comes in.
The log server is now the source of truth.
It maintains a record of all updates.
When a write update needs to be performed to complete
a transaction, the compute engine is going to write
the log update into a very past region of premium storage
we call the landing zone.
At that point, the transaction is complete.
Now the log engine retrieves the updates
from the landing zone, which is a small set
of very fast storage, and routes the updates
to the page servers so that they can update their copy
of the data, and it will also send it to the secondary
replicas that may have that page cached.
Finally, it takes those updates and persists them
in the long term log storage, which is a much larger
region of Azure standard storage, and we actually keep
the log data online in that long term storage for as long
as your backup retention window lasts.
So instead of doing log backups, we just keep the log
online for the entire time, which makes the restores
very fast because you don't have to do a separate
data restore and log restore.
- Okay, so we've talked about reads and writes.
What do we actually need to do
in terms of scaling out our compute?
- So there's two dimensions to scaling.
You can add more cores to scale up,
and because we've separated out the compute
from the storage, you can scale the compute more rapidly.
We just add a new secondary compute with the new
number of cores, whether that's a greater or lesser
number of cores into the configuration,
and when that new set of compute is up and stable,
we just do a failover to it.
- [Jeremy] So it's really fast.
- [Kevin] It takes a couple minutes
to stand up the new compute, and then the actual
disruption is near instantaneous for just that failover.
- Yeah, when you contrast that to having to move
from say rack 1 to rack 2 of a more powerful machine,
you're saving hours or days of time.
- Exactly.
And you don't have to move the data to do it.
So the other dimension of course is scaling out.
We have the ability to have multiple replicas.
You have the primary which handles the read/write traffic,
and you have up to four secondary replicas which handle
read only traffic, so now you can scale out your read access
to the secondary compute replicas.
So for large reporting workloads,
for seasonal querying, or seasonal reporting needs,
where there's a lot of read access,
you can route all that traffic to the
set of secondary replica nodes and steady state traffic
can still be routed to the primary compute,
and the interesting thing about the way
we've separated the compute from the storage is that
all of these compute replicas share the same set
of page servers.
You're not having them make copies and duplicating
your data in order to have multiple compute replicas.
So now you can see the full picture.
You can see the full architecture and how it comes together.
You've got the compute engines
with a primary and the compute replicas.
The primary's sending the updates into the log landing zone,
the log server then forwarding those updates
to the page servers and to the secondary compute,
and finally the page server's persisting the data
into Azure standard storage.
- So really here the separation between the compute,
the log services, the paging servers, and also the replicas
is really key to Hyperscale, but I can see how
this could be useful from the large data operations
perspective, and also doing online transaction processing,
maybe something that's very analytics intensive
or read intensive, like the Black Fridays or tax seasons
that we might see for these seasonal spikes,
but let's say we wanna convert an existing smaller
Azure SQL Database to Hyperscale.
How would we do that?
- So here, I'm in the Azure Portal.
We have an existing database called AdventureWorks.
It's in the basic performance tier, general purpose.
So we're looking at the database now,
we see that it's in the general purpose performance tier
over here, so we just go to the configure blade,
which is where we'll change the configuration
of this database.
You'll see up here we have the General Purpose,
Hyperscale, and Business Critical
performance tiers as options.
We just check Hyperscale tier--
- [Jeremy] Similar to the provisioning screen
we saw earlier.
- [Kevin] It's exactly the same screen.
So you choose your generation of hardware,
the number of cores, the number of replicas,
and again, there's no max database size here.
So I dropped it down to zero secondary replicas,
of course you've always got the primary,
and just hit go, and at that point,
we're moving the database into Hyperscale,
so we're taking the Azure storage that the General Purpose
databases use for storage, converting that into the
storage under the page servers--
- [Jeremy] So now it's separated.
- [Kevin] Right.
And now you're standing up the rest
of the Hyperscale pieces--
- [Jeremy] So it's kind of splitting my other PaaS
servers into the components of Hyperscale,
so you've got everything separated as separate services.
- Exactly.
So you can also automate the addition of replicas
or scaling of course, for that matter, to plan for
peak times.
So if you have predictable peaks in your usage patterns,
you can set up scripts in advance that automatically
scale up in advance of your peak usage, and then
scale back down when the peak is done.
- Right, so now once we're in Azure,
what other things can we start to light up now
that we're Hyperscale?
- One of the big advantages of Hyperscale
is the time to restore.
Data restorations can be done in a matter of minutes.
Here I have a 50TB database.
This is 50TB of actual data in here, it's not just
an empty 50TB container.
So we'll hit restore.
We're gonna give it a unique name so it gets restored
on the side, it's not restoring over an existing database.
I select the point in time that we want it to restore to.
Choose the hardware spec that we wanna restore to,
and then go down and hit accept, and then we will go.
So now that the restore's happening, I'm starting a timer
over here so we'll know exactly how long it took.
There's an activity monitor here that will show
when the database restore is actually complete,
so we'll know that it's done at that point.
So what's going on in the background?
We have three page servers, and this is a timeline
for the page servers.
Each page server's periodically taking snapshots
of the Azure storage underneath it,
and you'll note that those snapshots
don't have to be synchronized so we're not having
to get a big sync point and install everything.
So we have a collection of snapshots over time,
and of course we've got the log landing zone
and long term storage with the log servers over here.
Backup has no impact on your running application
because it's metadata and it's fully buffered by the cache
and the page servers.
So we decided our point in time that we want to
take the database to, right before
somebody hit delete table,
we choose the set of snapshots immediately before
that point in time, and we also find the point
in the log stream that corresponds to the start
of the oldest transaction active at the time we took
the first snapshot.
- [Jeremy] So that way you can get all the gaps
maybe that you're missing
from when the snapshots were taken.
- [Kevin] Exactly.
So we can roll it forward to a consistent point of time,
and roll it forward to the point that we want to be at
when we're finished doing the restore.
And we have the point in time, now that gives us
the range of log data that we're gonna need
to accomplish this restore.
So now we create the Azure Storage components,
we create the Azure Storage for the data files
and the long term storage container for the log,
we'll copy the log from the database we're restoring
into the new database.
This is a metadata-only operation, and all those
data elements get copied in parallel,
so with our big database, we might be copying
hundreds of page server's worth of data,
this makes for a simpler picture.
So we've got the long term storage primed with the data
as well as the page servers, so now we're standing up
the page servers, attaching them to their data blobs,
and attach the log service with it's log landing zone.
Page servers start filling their cache
from the Azure storage, and the log service
pulls the data from the long term storage and fills
it's log landing zone cache.
Now we create the primary compute to work on this,
it will start running recovery.
Log service then pulls the updates out of the
log landing zone and updates the pages in the page server,
and we use accelerated database recovery to finally
bring the database online.
- So now we've gone over all the different services stood up,
everything's wired back together, and now we can go
back to that restoration point.
- Exactly.
So we've snipped a little bit of time out of the restore
process just to avoid some dead air here.
So we're 14 minutes into the restore.
Again, this is a 50TB database.
So the first thing you'll see is in the list of databases
in the bottom, the restore database just popped up there.
So the database is in existence, it's been put together,
we're just finishing running recovery on the database here,
and that will take a few more seconds to complete.
- So a lot of the time is spent just building out
those page servers, right?
- Exactly, we've got 800 page servers,
each 128GB, and we've got 50TB.
- It is a pretty massive database at the end of the day.
- We can see the database restore is still in progress
while it's just finishing up the last bits
of the recovery, and roll forward,
and in just a couple seconds, it's complete.
So now we've restored a 50TB database in 14 minutes
and 58 seconds, which is just astounding.
- So this is massive, just the physics of moving
that data around before would've taken days
or weeks to be able to restore that size of data,
and you did that in just about 14 minutes.
So what's the experience like, though,
if we switch gears to scaling out?
- So what I have here is a benchmark driver.
This is what we use for driving benchmark testing.
I have two configurations here.
One configuration sends the transactions all to the primary.
So all the data's going to the primary node,
and the other labeled ScaleOut is sending data
to both the primary and the replicas.
- [Jeremy] Okay, so the primary is just one replica.
- [Kevin] Exactly.
So we've started the transactions going all to the primary.
It's the same workload, just whether it gets sent
to two places or not.
So you'll see the transactions per second on the right,
and this is a chart showing the same thing,
and it will jump around when we first start
as all those threads get themselves running and stable.
It's gonna stabilize at about
23/25 transactions per second here.
- Right, so it's starting to settle in,
it's starting to normalize, and we can start
looking at different kinds of polling times,
and now we're seeing a very normalized and smooth
curve of our transaction times per second and also
our reads.
- [Kevin] Right, so this is the baseline.
This is what it takes to drive this workload,
which is very CPU heavy, funneling all the traffic
through the primary replica.
So it's taking 100% of the workload,
and the secondaries are just sitting idle at this point.
- Okay, and there we can see again, our kind of
steady state is anywhere between about 22 and 30 or so
transactions per second, so it's still not a slouch
in terms of speed, but we can do better with this, right?
If we decide we want to go to that scale out scenario
from just the primary.
- Exactly.
So here, we come down, we're gonna pause the threads
that are running the primary only workload,
and start up the threads that
are running the scale up workload.
And the only difference is the connection string
that does the routing.
So you see that hockey stick when that curve
jumps way up.
So we're 23-25 transactions per second,
and as this settles in because it's a new set of threads,
it's gonna settle in right around 70, 65 to 70
transactions per second.
So there's a significant difference in throughput here.
We've taken an application that was tapped out
at 23/25 transactions per second,
and we've about tripled the throughput here.
- So this is awesome because you just had to flip one switch
effectively, and now you can route more transactions
to those other replicas, and you got a throughput
that is effectively triple of what you had before.
- Exactly.
- So you've shown now how you can convert existing
PaaS database workloads then to use Hyperscale,
but I bet a lot of the folks that are watching right now
are thinking "I've got SQL databases that are sitting
"on-prem, and I want to move into Hyperscale,
"maybe retire a data mart that's on premises."
How would I do that?
- Right, not just data marts, people are interested
in using Hyperscale to consolidate all of their databases.
So if they've got a bunch of smaller databases
that had to be separated because of the capacity
they had access to at the time, now they can consolidate
those into a single database with Hyperscale.
And because it's PaaS, we do the managing for you.
It's fewer databases to manage,
and we do a lot of that work for you.
To migrate your on premises large databases,
there's a few standard options.
You can ingest the data directly from your on-premise
applications into the Hyperscale database.
- Over the wire. - Over the wire.
That works just fine, but if you've got terabytes of data,
you may want to use something like
the Azure Import/Export service, and that's where
you copy your data onto hard drives locally,
and they're encrypted and conditioned,
shipped up to the Azure datacenter, and we can copy
the data off there and it ends up being much faster.
- And again, we're talking about terabytes of data,
so the physical shipment of the hard drive
in lots of cases will be faster.
- Exactly.
We're working on additional tools for migration,
and of course you can also tap into
our partner ecosystem for additional help.
- Really great to see all of this working
in Hyperscale and all of the massive things
we can do here.
For people that are watching at home,
where can they go to learn more?
- So you can visit us at aka.ms/SQLDB_Hyperscale
to learn more.
- Thanks, Kevin, and don't forget
to subscribe to Microsoft Mechanics
and keep watching for the latest tech updates
across Microsoft. That's all the time we have for today.
Thanks for watching, we'll see you next time.
(upbeat techno music)