>> If we take a deeper look at what's happening and
what's available for availability under the hood,
we have to talk about database availability and consistency.
Bob, how does this work in Azure SQL
and how is it different from SQL Server?
>> Thanks Anna. When you talk
about the topic, we've got availability.
There's a couple of things that DBAs and SQL Server experts are
used to doing that
aren't needed in the Cloud actually quite frankly.
Like setting databases offline into emergency mode,
we don't do those things.
We provide so much availability, backups, consistency,
things in the Cloud that I just in
my experience don't feel those things are needed anyways.
Second of all, if you want to restrict user access,
you can do things like set restricted user
to make the database available only to a certain set of users.
Now, in my career,
Dedicated Admin Connection or DAC is something
that's been used by some users in
emergency situations and we do allow that functionality
for Database Managed Instance for you
to get a Dedicated Admin Connection.
In case your SQL Server is in
a state where it has been failed over,
but you need to dig into something in
the back door of what's going on in SQL Server.
Then we're going to talk about shortly
an amazing story of accelerated database recovery which
is on by default in Azure database and Managed Instance.
That's some notes and nuggets about
availability comparing to SQL Server
and Azure SQL that you should be aware of.
When you talk about consistency,
consistency being a story that has always
been around in SQL Server and customers deeply care about,
we have an amazing story in
Azure SQL that quite frankly is game changing.
Think about all the things we've talked about,
about the multiple copies we have of your data and backups in
different storage regions and also replicated in those regions.
First of all, the fact that we just have multiple copies of
everything reduces the likelihood
of an integrity problem for your database.
You can execute CHECKDB if you need to.
You can't do repairs,
but if you'd like to run CHECKDB to manually check
the consistency of your database
including system databases like in MI,
you have the ability to do that.
Database CHECKSUM functionality we've put and having
the product for a long period of time
is on by default for databases,
and we do look at scenarios where we've detected
a checksum error might occur to take action if necessary.
Now, one of the cool things about business critical is
using that availability group scenario with replicas,
and if you know availability groups,
there's a concept called Auto Page Repair
and we turn it on by default.
In some cases, an Auto Page Repair kicks in
to repair a specific page in a primary replica,
and we don't even have to do a failover if that occurs.
Now in the back end, the engineering team has
several different alerting capabilities so that
we look at your database and we take action if necessary,
in some cases, even proactively notifying you.
If we find data integrity errors have occurred and we
cannot immediately just do a repair or something,
we may work with you proactively.
Integrity checks we've talked about a backup restore,
and we've even added in by default lost write and
stale read detection problems which
can show up in transient I/O error scenarios.
We've got that enabled as well and we can take
corrective action for you and even
repair things without even telling you.
Now we're just trying to hide something from you,
but if we think an easy repair can
happen with no data loss in your environment,
we can do that behind the scenes and you don't even see
an impact or know that actually has occurred.
>> Thanks Bob. There are a lot of
nice nuggets and information in that.
I have a confession with Accelerated Database Recovery, ADR.
We've talked a lot about it,
it's a really awesome feature but I'm not really sure
how it works or what the big deal is for Azure SQL.
Can you help me and hopefully other people understand?
>> Sure, Anna. ADR is an amazing story,
and let me tell you why it's significant by telling you a story.
Imagine a scenario where somebody kicks off,
there's a long transaction deleting a bunch of rows and
they realized that's a mistake, and they got to roll it back.
They try to come in and kill
a session on this big deleted, this transaction.
All of a sudden, your business is down.
Everybody is blocked, everybody is frustrated,
they don't know what to do, and at the same time,
the wrong can't be truncated.
In an Azure environment, in a Cloud environment,
we're doing all these different automations in SLAs,
that is really a problem.
Along comes a project called
Constant Time Recovery, couple years back,
turned into this feature called Accelerated Database Recovery,
which is in SQL Server and on default in Azure.
Here's how it works.
We use something called a Persistent
Version Store in your database.
It's a little bit like versioning that we have
today for isolation for TempDB but different.
Similar concepts but it's implemented differently,
and it's really implemented not for
versioning for isolation levels,
but for recovery purposes.
It's a version store in your database.
It's independent on those isolation levels
so even with this versioning,
you can do things like read committed isolation.
When this is on, it's an amazing story.
Rollback happens at the snap of your fingers.
Undo recovery happens
extremely fast and transaction log truncation
is not tied to these active transactions.
All of these are very important when it comes
to how we do built-in availability in the Cloud.
Now, you're going to have some questions about
more how this works and how it impacts maybe your application.
Here's a couple of key questions
I'm going to provide you some answers to.
You might ask yourself,
does it require more space and the answer is a little bit,
in some cases even more.
But it all depends on how many changes you're
making and how long these version of DB are kept around.
Now, will it affect performance
like anything we always talk about, we turn something on?
Maybe, it depends on how many again,
changes you're making and how
often you make those changes and keep versions around.
Now, you're not going to see versions in TempDB
anymore when this database option is
on for your database and it's the only option
that is on for all databases which is the case for Azure,
because when we need isolation levels like snapshot,
we'll use the versions in your database.
Now it says here, does it work with HA?
Well, not always work with HA but is
one of the key powering technologies
to make the SLAs at HA possible in Azure today.
Now to get more details about,
does it affect performance,
how it affects performance,
how much more space might it take up in your database,
we've got a really amazing white paper which
when you see the slide from this deck, you can click to,
or you can search for the word
constant time recovery in
your favorite search engine and read our white paper.
Our development team went to
an incredible exercise of
showing all the details in experiments and
measurements of any possible impact
and how internally ADR works on the covers.
It's truly one of the amazing stories
that we've built for the Cloud
and for technologies like SQL Server 2019.
Yeah, if you think about availability and consistency,
everything between ADR,
built-in integrity checks, Automatic Page Repair,
I've lived my life in SQL Server worrying
about these concepts and I think in Azure SQL we've got
a lot of automation and things that are
going to make your job a lot
easier when it comes to availability and consistency.
We'll see you around in the next video.