WEBVTT

00:00:00.000 --> 00:00:02.055
>> Database recovery with

00:00:02.055 --> 00:00:05.190
long-running transactions
has been a challenge.

00:00:05.190 --> 00:00:07.050
In SQL Server 2019,

00:00:07.050 --> 00:00:09.780
we introduce accelerated
database recovery

00:00:09.780 --> 00:00:11.190
to help solve that problem.

00:00:11.190 --> 00:00:13.605
Kevin is here to tell
us all about it,

00:00:13.605 --> 00:00:15.390
today on Data Exposed.

00:00:15.390 --> 00:00:26.130
[MUSIC]

00:00:26.130 --> 00:00:28.755
>> Hi and welcome to another
episode of Data Exposed.

00:00:28.755 --> 00:00:30.745
I'm your host, Jeroen and today,

00:00:30.745 --> 00:00:34.415
we have Kevin with us to talk about
accelerated database recovery.

00:00:34.415 --> 00:00:35.975
So welcome Kevin to the show.

00:00:35.975 --> 00:00:36.665
>> Thank you.

00:00:36.665 --> 00:00:39.125
>> So accelerated database recovery.

00:00:39.125 --> 00:00:40.750
So what is it?

00:00:40.750 --> 00:00:41.930
>> So it's an interesting feature.

00:00:41.930 --> 00:00:43.340
We'll call it ADR for short.

00:00:43.340 --> 00:00:44.890
>> Okay, sure.

00:00:44.890 --> 00:00:46.970
>> It came from
looking at some of the

00:00:46.970 --> 00:00:48.530
pain points that customers have had

00:00:48.530 --> 00:00:51.770
running databases and keeping
them highly available and

00:00:51.770 --> 00:00:53.270
part of it has to do with the time it

00:00:53.270 --> 00:00:55.475
takes to bring a database online.

00:00:55.475 --> 00:00:58.970
There's a number of phases that
a database has to come through,

00:00:58.970 --> 00:01:01.340
and if you've got a long
running transaction,

00:01:01.340 --> 00:01:04.010
it can take a long time
to clean that up and that

00:01:04.010 --> 00:01:07.080
leads to unavailability when
it's doing that processing.

00:01:07.080 --> 00:01:10.545
>> Right. So we know that that
restoring is a pain point.

00:01:10.545 --> 00:01:13.530
Bringing it back is
something that DBAs,

00:01:13.530 --> 00:01:15.075
well, kind of worry about.

00:01:15.075 --> 00:01:16.790
>> Right. So the team looked at

00:01:16.790 --> 00:01:19.520
that whole process and thought
how can we re-imagine it?

00:01:19.520 --> 00:01:21.335
So they've come up with ADR,

00:01:21.335 --> 00:01:23.210
it's based on a version store.

00:01:23.210 --> 00:01:26.170
So all the changes are
versioned in the database.

00:01:26.170 --> 00:01:29.920
That lives in the file
group of your choosing.

00:01:30.140 --> 00:01:34.925
Leveraging that, we can make the
recovery process much quicker.

00:01:34.925 --> 00:01:35.600
>> Cool.

00:01:35.600 --> 00:01:40.965
>> I have some slides
that demonstrate.

00:01:40.965 --> 00:01:46.515
So here we have the
classic recovery process.

00:01:46.515 --> 00:01:48.350
So it starts, Phase 1 is analysis.

00:01:48.350 --> 00:01:50.360
So you have to look through
all the transactions

00:01:50.360 --> 00:01:53.020
in the log from the last
checkpoint forward.

00:01:53.020 --> 00:01:56.150
Redo is any data changes

00:01:56.150 --> 00:01:58.700
that hasn't been persisted
in the data files,

00:01:58.700 --> 00:02:01.850
have to be redone from
the transaction log,

00:02:01.850 --> 00:02:03.020
all the way through from

00:02:03.020 --> 00:02:05.420
the beginning of the oldest,
uncommitted transactions.

00:02:05.420 --> 00:02:07.790
So that's where the long-running
transactions really hurt you.

00:02:07.790 --> 00:02:08.560
>> Right, exactly.

00:02:08.560 --> 00:02:12.170
>> It can take a minutes to
an hour or more sometimes.

00:02:12.170 --> 00:02:14.660
Then, Phase 3 is undo,

00:02:14.660 --> 00:02:17.270
where you undo any transactions that

00:02:17.270 --> 00:02:20.975
weren't committed before the
time you look forward to.

00:02:20.975 --> 00:02:23.285
At the time the reading finishes,

00:02:23.285 --> 00:02:25.375
the database is partially available.

00:02:25.375 --> 00:02:28.670
What that means is you can
access the database but

00:02:28.670 --> 00:02:33.270
any data that wasn't under lock
from the original transactions,

00:02:33.270 --> 00:02:34.320
will be under lock now.

00:02:34.320 --> 00:02:36.200
So even though there's
nobody doing them,

00:02:36.200 --> 00:02:39.230
you can't access that data
until undo completes.

00:02:39.230 --> 00:02:41.930
>> So basically this is
a long-running process

00:02:41.930 --> 00:02:45.835
and then only after
we arrive at Phase 3,

00:02:45.835 --> 00:02:47.900
I can start doing

00:02:47.900 --> 00:02:49.580
everything I wanted with
the database again, right?

00:02:49.580 --> 00:02:50.165
>> Right.

00:02:50.165 --> 00:02:53.585
>> So tell me how it was.

00:02:53.585 --> 00:02:55.865
>> On the bottom, you see just

00:02:55.865 --> 00:02:59.145
log record with different
events in the log record.

00:02:59.145 --> 00:03:00.165
>> Sure.

00:03:00.165 --> 00:03:02.190
>> ADR changes that a lot.

00:03:02.190 --> 00:03:03.750
We have the processing version store.

00:03:03.750 --> 00:03:06.375
You'll see it referenced as PVS.

00:03:06.375 --> 00:03:09.464
When we put that out in the previews,

00:03:09.464 --> 00:03:11.915
PVS lived in the primary file group

00:03:11.915 --> 00:03:13.820
and there is no ability
to change that.

00:03:13.820 --> 00:03:16.780
So that happened, that's where
all those versions lived.

00:03:16.780 --> 00:03:19.550
We got feedback that
customers would like to

00:03:19.550 --> 00:03:22.280
be able to specify which
file group that lives in.

00:03:22.280 --> 00:03:26.180
I've got a bulk file group or
very fast file group, whatever.

00:03:26.180 --> 00:03:27.740
So now you're with

00:03:27.740 --> 00:03:31.130
the release candidate and with
the GA version when it comes out,

00:03:31.130 --> 00:03:33.910
you'll be able to specify which
file group and change it,

00:03:33.910 --> 00:03:35.880
there's process for
changing it as well.

00:03:35.880 --> 00:03:38.120
But let's go through what
the recovery process

00:03:38.120 --> 00:03:39.755
looks like with ADR.

00:03:39.755 --> 00:03:42.110
So it starts with analysis,

00:03:42.110 --> 00:03:45.695
that is unchanged from
what you had before.

00:03:45.695 --> 00:03:47.015
>> It's the same behavior, right?

00:03:47.015 --> 00:03:49.805
>> Right. We've introduced
the concept of an sLog.

00:03:49.805 --> 00:03:52.705
An sLog is an in-memory log

00:03:52.705 --> 00:03:55.640
that records only those
system transactions

00:03:55.640 --> 00:03:57.005
that can't be versioned.

00:03:57.005 --> 00:03:59.150
So most data versions you can

00:03:59.150 --> 00:04:01.715
change before and after
pictures of the data.

00:04:01.715 --> 00:04:04.070
So some scheme changes,

00:04:04.070 --> 00:04:06.195
some things like that,
can't be versioned.

00:04:06.195 --> 00:04:06.570
>> Sure.

00:04:06.570 --> 00:04:07.890
>> So those get recorded in the sLog.

00:04:07.890 --> 00:04:09.195
So the idea is that it's,

00:04:09.195 --> 00:04:11.580
a very few significant.

00:04:11.580 --> 00:04:13.920
>> There will be a small
set of projections, right?

00:04:13.920 --> 00:04:17.525
>> So part of the analysis
and redo phase is

00:04:17.525 --> 00:04:23.100
recreating those in-memory logs
from the transaction log records.

00:04:23.230 --> 00:04:25.850
So redo from the sLog,

00:04:25.850 --> 00:04:28.300
is just leveraging the version store.

00:04:28.300 --> 00:04:31.195
Because we've got before and after
versions of all of those rows,

00:04:31.195 --> 00:04:34.010
so it's very quick and
then you redo from

00:04:34.010 --> 00:04:38.905
the log just from the
last checkpoint forward.

00:04:38.905 --> 00:04:42.810
At that point, your database
is fully available.

00:04:43.420 --> 00:04:46.910
Undo is just reverting

00:04:46.910 --> 00:04:48.875
the versions so you just

00:04:48.875 --> 00:04:51.710
point to the previous version
instead of the current version.

00:04:51.710 --> 00:04:55.345
You don't have to physically undo
the transaction and reverse.

00:04:55.345 --> 00:04:59.825
>> So this is going to be way
faster than the older one normally?

00:04:59.825 --> 00:05:01.880
>> Way faster. We had a customer in

00:05:01.880 --> 00:05:04.280
the lab within the last couple of
weeks that did some testing with

00:05:04.280 --> 00:05:10.050
ADR and they had a very
active update workload.

00:05:10.050 --> 00:05:13.065
They had a long-running
transaction with it.

00:05:13.065 --> 00:05:14.430
They did that, this,

00:05:14.430 --> 00:05:17.450
and did a rollback of that
long-running transaction.

00:05:17.450 --> 00:05:20.555
Without ADR, it took about a
minute-and-a-half to do that.

00:05:20.555 --> 00:05:24.765
>> Which is still not
too bad but okay, long.

00:05:24.765 --> 00:05:26.190
>> Yeah. In their business,

00:05:26.190 --> 00:05:28.105
it makes a big difference.

00:05:28.105 --> 00:05:30.680
So then they retried
that same scenario

00:05:30.680 --> 00:05:32.780
with ADR and the time it took

00:05:32.780 --> 00:05:36.720
to do that recovery was zero seconds.

00:05:36.720 --> 00:05:38.505
They couldn't measure
it, it was so fast.

00:05:38.505 --> 00:05:40.110
>> That's impressive.

00:05:40.110 --> 00:05:43.580
>> So for them, they're back
on the line that much faster,

00:05:43.580 --> 00:05:47.425
which makes a big difference
too because in their business,

00:05:47.425 --> 00:05:49.560
any outages is a loss of revenue.

00:05:49.560 --> 00:05:51.375
>> So milliseconds count, right?

00:05:51.375 --> 00:05:52.230
>> Very much so.

00:05:52.230 --> 00:05:53.880
>> So if we can help this customer

00:05:53.880 --> 00:05:55.575
moved from one-and-a-half minute,

00:05:55.575 --> 00:05:58.305
you said to basically zero,

00:05:58.305 --> 00:05:59.895
that's impressive. So wow.

00:05:59.895 --> 00:06:02.930
So all of our customers

00:06:02.930 --> 00:06:05.810
are probably wanting to
try this and enable this.

00:06:05.810 --> 00:06:08.450
So can you tell me how I do that?

00:06:08.450 --> 00:06:09.470
I have a database now,

00:06:09.470 --> 00:06:12.995
I have it on the normal
recovery, so what do I do?

00:06:12.995 --> 00:06:14.585
>> So with Azure SQL database,

00:06:14.585 --> 00:06:16.775
it's on by default globally.

00:06:16.775 --> 00:06:19.130
It's been on by default
globally for months.

00:06:19.130 --> 00:06:20.540
So you don't need to
do anything there.

00:06:20.540 --> 00:06:22.520
You've already taken advantage of it.

00:06:22.520 --> 00:06:23.740
>> Cool.

00:06:23.740 --> 00:06:26.940
>> For SQL Server databases,

00:06:26.940 --> 00:06:29.060
it's off by default because there is

00:06:29.060 --> 00:06:31.610
some overhead on the range of

00:06:31.610 --> 00:06:35.880
one to five percent for
keeping track of the versions.

00:06:36.190 --> 00:06:41.015
So you'd have to turn it on and
that's just, alter database set,

00:06:41.015 --> 00:06:42.635
accelerated database recovery equals

00:06:42.635 --> 00:06:46.410
on and optionally with
file group equals.

00:06:46.410 --> 00:06:47.310
>> Something.

00:06:47.310 --> 00:06:49.810
>> Yeah. So very simple DDL.

00:06:49.810 --> 00:06:51.710
>> Then what happens?

00:06:51.710 --> 00:06:54.410
>> Then it starts tracking
versions and you get the benefit.

00:06:54.410 --> 00:06:55.970
>> Cool. Is that direct,

00:06:55.970 --> 00:06:58.065
immediate, or is that like,

00:06:58.065 --> 00:06:59.250
there's a restart required.

00:06:59.250 --> 00:07:01.740
>> No restart. You're just online.

00:07:01.740 --> 00:07:03.705
>> Cool. So wow.

00:07:03.705 --> 00:07:05.160
So basically, this is like

00:07:05.160 --> 00:07:08.545
a very cool technology to
restore a database very quickly.

00:07:08.545 --> 00:07:10.730
Anything else that I get from this?

00:07:10.730 --> 00:07:12.140
I mean this is really
very impressive but

00:07:12.140 --> 00:07:13.580
these are like extra benefits.

00:07:13.580 --> 00:07:15.590
>> So there's an extra benefit in

00:07:15.590 --> 00:07:19.115
that because of the way
we reuse the versions,

00:07:19.115 --> 00:07:22.470
we don't have to keep as
much transaction log online.

00:07:22.470 --> 00:07:24.920
So you can truncate the
transaction log much more

00:07:24.920 --> 00:07:28.725
aggressively up to the last
checkpoint than you could before.

00:07:28.725 --> 00:07:30.530
Which means, if you've
got the situation,

00:07:30.530 --> 00:07:32.540
we have a long-running
transaction that's keeping you

00:07:32.540 --> 00:07:34.460
from being able to truncate

00:07:34.460 --> 00:07:36.620
your log and the transaction
log starts blowing up,

00:07:36.620 --> 00:07:38.665
that doesn't happen
with ADR turned on.

00:07:38.665 --> 00:07:41.400
>> So basically that's
the extra benefit.

00:07:41.400 --> 00:07:43.650
No long transaction
log dragging along.

00:07:43.650 --> 00:07:44.505
>> Exactly.

00:07:44.505 --> 00:07:45.990
>> I know what I will do,

00:07:45.990 --> 00:07:47.660
I mean MySQL server was go to

00:07:47.660 --> 00:07:49.760
accelerate a database
recovery right now.

00:07:49.760 --> 00:07:51.470
After this video, I'll do that.

00:07:51.470 --> 00:07:52.805
Thanks a lot for sharing.

00:07:52.805 --> 00:07:53.345
>> Thank you.

00:07:53.345 --> 00:07:55.940
>> Thanks for explaining.
This was very clear.

00:07:55.940 --> 00:07:57.575
Thank you for watching.

00:07:57.575 --> 00:08:00.990
Please like and subscribe and
stay tuned for the next. Thanks.

00:08:00.990 --> 00:08:13.210
[MUSIC]