>> Database recovery with
long-running transactions has been a challenge.
In SQL Server 2019,
we introduce accelerated database recovery
to help solve that problem.
Kevin is here to tell us all about it,
today on Data Exposed.
[MUSIC]
>> Hi and welcome to another episode of Data Exposed.
I'm your host, Jeroen and today,
we have Kevin with us to talk about accelerated database recovery.
So welcome Kevin to the show.
>> Thank you.
>> So accelerated database recovery.
So what is it?
>> So it's an interesting feature.
We'll call it ADR for short.
>> Okay, sure.
>> It came from looking at some of the
pain points that customers have had
running databases and keeping them highly available and
part of it has to do with the time it
takes to bring a database online.
There's a number of phases that a database has to come through,
and if you've got a long running transaction,
it can take a long time to clean that up and that
leads to unavailability when it's doing that processing.
>> Right. So we know that that restoring is a pain point.
Bringing it back is something that DBAs,
well, kind of worry about.
>> Right. So the team looked at
that whole process and thought how can we re-imagine it?
So they've come up with ADR,
it's based on a version store.
So all the changes are versioned in the database.
That lives in the file group of your choosing.
Leveraging that, we can make the recovery process much quicker.
>> Cool.
>> I have some slides that demonstrate.
So here we have the classic recovery process.
So it starts, Phase 1 is analysis.
So you have to look through all the transactions
in the log from the last checkpoint forward.
Redo is any data changes
that hasn't been persisted in the data files,
have to be redone from the transaction log,
all the way through from
the beginning of the oldest, uncommitted transactions.
So that's where the long-running transactions really hurt you.
>> Right, exactly.
>> It can take a minutes to an hour or more sometimes.
Then, Phase 3 is undo,
where you undo any transactions that
weren't committed before the time you look forward to.
At the time the reading finishes,
the database is partially available.
What that means is you can access the database but
any data that wasn't under lock from the original transactions,
will be under lock now.
So even though there's nobody doing them,
you can't access that data until undo completes.
>> So basically this is a long-running process
and then only after we arrive at Phase 3,
I can start doing
everything I wanted with the database again, right?
>> Right.
>> So tell me how it was.
>> On the bottom, you see just
log record with different events in the log record.
>> Sure.
>> ADR changes that a lot.
We have the processing version store.
You'll see it referenced as PVS.
When we put that out in the previews,
PVS lived in the primary file group
and there is no ability to change that.
So that happened, that's where all those versions lived.
We got feedback that customers would like to
be able to specify which file group that lives in.
I've got a bulk file group or very fast file group, whatever.
So now you're with
the release candidate and with the GA version when it comes out,
you'll be able to specify which file group and change it,
there's process for changing it as well.
But let's go through what the recovery process
looks like with ADR.
So it starts with analysis,
that is unchanged from what you had before.
>> It's the same behavior, right?
>> Right. We've introduced the concept of an sLog.
An sLog is an in-memory log
that records only those system transactions
that can't be versioned.
So most data versions you can
change before and after pictures of the data.
So some scheme changes,
some things like that, can't be versioned.
>> Sure.
>> So those get recorded in the sLog.
So the idea is that it's,
a very few significant.
>> There will be a small set of projections, right?
>> So part of the analysis and redo phase is
recreating those in-memory logs from the transaction log records.
So redo from the sLog,
is just leveraging the version store.
Because we've got before and after versions of all of those rows,
so it's very quick and then you redo from
the log just from the last checkpoint forward.
At that point, your database is fully available.
Undo is just reverting
the versions so you just
point to the previous version instead of the current version.
You don't have to physically undo the transaction and reverse.
>> So this is going to be way faster than the older one normally?
>> Way faster. We had a customer in
the lab within the last couple of weeks that did some testing with
ADR and they had a very active update workload.
They had a long-running transaction with it.
They did that, this,
and did a rollback of that long-running transaction.
Without ADR, it took about a minute-and-a-half to do that.
>> Which is still not too bad but okay, long.
>> Yeah. In their business,
it makes a big difference.
So then they retried that same scenario
with ADR and the time it took
to do that recovery was zero seconds.
They couldn't measure it, it was so fast.
>> That's impressive.
>> So for them, they're back on the line that much faster,
which makes a big difference too because in their business,
any outages is a loss of revenue.
>> So milliseconds count, right?
>> Very much so.
>> So if we can help this customer
moved from one-and-a-half minute,
you said to basically zero,
that's impressive. So wow.
So all of our customers
are probably wanting to try this and enable this.
So can you tell me how I do that?
I have a database now,
I have it on the normal recovery, so what do I do?
>> So with Azure SQL database,
it's on by default globally.
It's been on by default globally for months.
So you don't need to do anything there.
You've already taken advantage of it.
>> Cool.
>> For SQL Server databases,
it's off by default because there is
some overhead on the range of
one to five percent for keeping track of the versions.
So you'd have to turn it on and that's just, alter database set,
accelerated database recovery equals
on and optionally with file group equals.
>> Something.
>> Yeah. So very simple DDL.
>> Then what happens?
>> Then it starts tracking versions and you get the benefit.
>> Cool. Is that direct,
immediate, or is that like,
there's a restart required.
>> No restart. You're just online.
>> Cool. So wow.
So basically, this is like
a very cool technology to restore a database very quickly.
Anything else that I get from this?
I mean this is really very impressive but
these are like extra benefits.
>> So there's an extra benefit in
that because of the way we reuse the versions,
we don't have to keep as much transaction log online.
So you can truncate the transaction log much more
aggressively up to the last checkpoint than you could before.
Which means, if you've got the situation,
we have a long-running transaction that's keeping you
from being able to truncate
your log and the transaction log starts blowing up,
that doesn't happen with ADR turned on.
>> So basically that's the extra benefit.
No long transaction log dragging along.
>> Exactly.
>> I know what I will do,
I mean MySQL server was go to
accelerate a database recovery right now.
After this video, I'll do that.
Thanks a lot for sharing.
>> Thank you.
>> Thanks for explaining. This was very clear.
Thank you for watching.
Please like and subscribe and stay tuned for the next. Thanks.
[MUSIC]