What is Azure SQL Database Hyperscale?

What is Azure SQL Database Hyperscale?

– 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)

Danny Hutson

2 thoughts on “What is Azure SQL Database Hyperscale?

  1. This channel is way awesome compared to MVA. Good call in bringing MVA down, we need quality information in 15 min videos. Microsoft Mechanics and Azure Friday are good channels from Microsoft. Great work folks!

Leave a Reply

Your email address will not be published. Required fields are marked *