>> Automated backups are very important part of
maintaining your Azure SQL Database or Azure SQL Managed Instance.
In this video, we'll dive into the various options that are
available and some of the details surrounding them.
Tune in to this episode of Data Exposed.
[MUSIC]
>> Hi. Welcome to Data Exposed.
I'm Anna Hoffman and today I'm joined by
a Program Manager on the Azure SQL team, Shreya Verma.
Shreya, thanks for joining us today.
>> Hey, Anna. Thanks for having me.
>> Today, we're going to talk a little bit
about automated backups in Azure SQL,
and this is a really important thing.
Many people are familiar with how this works in the on-prem world,
but you're going to tell us a little bit more
about how it works in Azure SQL.
>> Yeah, that's correct.
Both Azure SQL DB and Managed Instance
have something that we call as automated backups.
That is reading backups for each database on behalf of customers,
and they can use these backups to run point-in-time restores.
They can also configure
long-term retention backups for
their database on both Azure SQL DB,
and Managed Instance now.
They can also run GOD stores to recover
from data center or a region outage in another region in Azure.
>> Awesome. So it seems like
Azure is going to take care of a lot of
the legwork and headache
related to configuring some of these things out.
Now, when we look at some of the use cases,
what are some of the common use cases for using some
of these different strategies for Azure SQL?
>> That's a very good question, Anna.
Let's look at some of the use cases here real quick,
and I have a presentation to share.
Let's look at some of the use cases here real quick.
When would you use point-in-time restore backups?
Generally, point-in-time restore,
we'll let you go to a previous state of the database.
Let's say, if I am a database user and I deleted
a table in the database by mistake or I deleted restore procedure,
or I deleted some data in a table by mistake,
and I wanted to go back to a state prior to this error,
which I have introduced now in the database.
Let's be honest, this happens a lot of times in production systems
when users by mistake
delete some data and they have to recover from that.
Point-in-time restore backup, which
Azure SQL DB takes
automatically for each of the databases which I created,
and the database lets you recover from those type of user errors.
So you can go back to a database state
prior to the error when it
occurred and you have your database recovered.
Point-in-time restore backups are also
used for Dev/Test environments.
For example, you copy your production database into
a Dev/Test environment on a daily or a weekly basis,
and then you would want to use
the latest point in time restore backup,
and recover from there and then run your test from there.
Now, coming to long-term retention backups,
point-in-time restore backup in
Azure SQL DB and Managed Instance let you
restore your database up to 35 days of retention period.
If you want to recover
beyond that 35 days of retention period, what are your options?
Then you would configure long-term retention backup on
your database if in
your application you need to go and recover beyond those 35 days.
Long-term retention backups let you
recover up to 10 years of data,
and LTR backups, lot of companies
they also need it for compliance and auditing purposes.
For compliance reasons or auditing reasons,
they need to show that they have backups stored
and they can show their backups using these LTR backups.
Now Geo-Restore helps you recover from a regional outage.
So it's the cheapest way of disaster recovery.
If Azure region, let's say it has an outage
and you want to recover and restore into another Azure region,
you can do that using geo-restore for this original Azure region.
Or the source Azure region does not have to be up and running,
geo-restore backups can just restore in
another region and your database can be up and running.
It does have a little bit higher RTO as compared
to our GODR or [inaudible] groups.
But again, it's the cheapest solution
and you can use that if it's not important,
that RTO is not that crucial for your database.
Now, let's look into detail
of each one of these type of backups that are available to you.
Like I said, point-in-time restore backups
help you recover from user error.
So we take full backups in Azure SQL DB weekly,
and then we take differential daily backups
and log backups every 5-10 minutes for the database.
These backups, which wherever we take
frequent full differential and log backups,
they are stored on a geo-redundant Azure Storage.
We have self-service restore API for this.
So you can go and restore a database up to a second granularity.
So if I deleted a table,
let's say, at 2:00 PM today and I wanted to
restore it back to 1: 59 PM,
closest to the error time,
I could do that using point-in-time restore backups.
We have our REST API,
PowerShell and Azure Portal available for
point-in-time restore and point-in-time restore
creates new database on the same server.
So we do that because we do not want
to change anything in the existing database that is running.
So we create a new database,
and then if customers want to find
their workload to the new database which
has been restored, they can do that.
We have a user-controlled retention policy.
So for each database that you create,
by default, we have seven days of retention period.
But users can configure the retention period from 1-35 days.
So for example, if you have
a Dev/Test database and you do not want to keep backups that long,
then you can configure the retention period to be one day.
But if it's a production database and you
want to keep backups for a long time,
then you can configure the retention period
35 days for that database.
>> Thanks, Shreya. That's pretty powerful stuff. Here we are.
But this is great if I delete a table.
But what if I go and delete a whole database?
Is there some way I can recover from that?
>> That's a very good question.
So point-in-time restore also
allows you to recover from a deleted database.
Let's say if I deleted a database,
I can come back and within the retention period of that database.
For example, when I deleted the database,
my retention period was seven days and I realized it the next day
I delete the database that I have
accidentally deleted the database.
My database is still within the retention period,
so I still have those six days of backups available to me,
I can come back and I can restore it to
the point when it was deleted.
Again, like I said, it creates a new database on the same server,
and so then users can then decide to create a new one,
and start using this new recovered database after that.
Now, going forward to a long-term data retention backups.
So this earlier backups can be
configured by users on their database.
What we do is we take the full database backups and we
store them on geo-redundant storage,
RA-GRS Azure storage,
and we store these backups for up to 10 years.
So these are available to
the users for auditing and compliance reasons or
even if they want to restore the database to
a particular state from few years ago,
then they can use this LTR backups.
>> Does this also mean that if,
maybe a few years ago I deleted a server,
but now I'm being
audited or for compliance reasons or for whatever reason,
I need access to information in that server,
can I restore a whole server?
>> That's a very common question that we get from users.
Yes. If you have deleted the database and you
also over the time eventually deleted the server,
but now you want to recover from LTR backup.
As long as your subscription is
active and you have the subscription [inaudible] permissions,
you can recover your database
from LTR backup and server from there.
>> Awesome. That's pretty awesome.
We talked about automated backups for
point-in-time restore and long-term data retention.
I think there was also geo-restore.
>> Yeah, that's correct. Let's go over geo-restore next.
We have a self-service restore API for geo-restore.
You would use geo-restore generally when a given Azure region
is down and you want to bring up
your database and your application backup again.
You would restore that database in another region,
and then you can run your application
against the database in a different region.
Now, because for automated backups,
we use RA-GRS storage,
which is geo-replicated storage,
so you can restore it in
any other Azure region which is available to you,
and it restores the last replicated backup on
that storage into the new database, new region.
For this, there is no extra cost.
The only cost you incur is when you restore
the database in a new region and your database is up and running,
then you will start paying for the database usage at that time.
But for the backups, there is
no extra cost for geo-restore backups.
There is no capacity guarantee.
By no capacity guarantee,
what do we mean is if you are,
let's say running your work load in
US West and if US West is down,
you are trying to restore it in US East because US West is down,
a lot of customers will be trying at that time to
restore their databases in different Azure regions,
US East might run out of capacity,
so we might not be able to restore your database.
So we recommend that customers choose their region accordingly.
That when a region is down,
they choose the region where
they think that their workload might be low and then
the restore will complete and the database will be up and running.
Now, RTO for geo-restore is
about 24 hours and RPO is about one hour for this.
Like I said, it's the cheapest way of disaster recovery,
but then it has high RPO and RTO values also.
So if you are looking at lower RPO and RTO values than
GODR and auto-failover group
might be a better solution for you.
Now, let's look at a quick demo of all of
these type of restores
and backups that are available to you in Azure.
You can see that I have three servers created,
one in East US, other one is West US and in Brazil South,
in three different regions.
I'm going to go to the server created in US West here real quick,
and you can see that I have three databases hosted on this server.
Let's look at a quick demo of point-in-time restore,
how you can run point-in-time restore
for one of the databases here.
I go to the database, there in
the Overview tab I can see the different details of this database,
and I click on "Restore" here.
When I click on "Restore", it takes me to this restore
[inaudible] and it tells me that this is a point-in-time restore.
I can click here and select
a long term backup retention restore also
if I have configured LTR backups on my database.
Then it tells me to pick date
and time and it shows me the database name.
It picks the database name based on
the time that we have picked here for restore,
but I can also change this database name and I call it
noerror because this is
when I know that there was no error in my database.
Let's say I deleted a table accidentally yesterday at
about 02:00 PM so I want to restore to 1:59 PM,
a minute before I made the mistake.
I pick the date, I pick the time,
I call it AdventureWorks_noerror and then
it has picked a slow and size,
number of pools based on what I had on my original database.
It's not an elastic pool,
I'm just going to click "Review + Create"
and then I just go ahead and create.
This will start the point-in-time restore process for me.
Because this will take a few minutes to run,
so while this is running,
what we do is we go back to our servers here
and I'll show you how you
can also run LTR restore on your database.
Let's look a demo for that.
I pick another database here for which I know
I have configured long-term retention backups.
I click on "Restore" again and instead of
picking point-in-time restore this time I'm going to pick
long-term backup retention and it shows me the backups that are
available to me based on
the retention period that I have set up for this database.
We'll see how you can look
at the retention period later in the demo.
But based on the retention period that I have set up,
I have these backups available to me.
So let's say, I want to go back to
the oldest backup that is available to me, which is this one,
I click on that and then I call it oldestLTR.
I can change the name of the database here,
I pick the time, I change time,
and then I click on "Review + Create",
and I start creating the database.
This has queued my point-in-time retention restore
and my LTR restore on my server here.
>> Awesome. Hey [inaudible] very
interesting how you have to pick a new name for your database,
I think oftentimes our on-prem folks are
used to when you do a point-in-time restore,
you replace the database.
Here is there an extra step
I need to do in order to get back to running?
>> Good question. What happens is we don't
restore it in the same database or we don't restore
with the same name because we do not want
to alter anything with the database which is running right now,
which is already running.
So what we do is we restore it into
another database with new name.
Once restore is complete and once the users have verified
that the database restored is what they were looking for,
they can change the connection string in
their application to now
connect to the new database that they have
restored and their application
can now start pointing to the new database.
Now, going back to we can
see that both that this PointInTimeRestore
and LTR are still running.
While this is running,
what I'll do is I'll quickly show you the Geo-restore,
how you can run a Geo-restore on your server.
Let's assume that US West 2 where I created my server here,
this region is done, and now
I want to restore it in another region.
I had this server created in Brazil South,
which I believe will not be
used as much as some of the US regions.
I go there and I want to create
a new database and restore from a backup that is available to me.
I click on "Create database" there and I enter a database names.
What I'm going to do is save
the WideWorldImporters_restore actually we'll
just pick up the name from the restore of the backup.
Then instead of clicking
on "Review" and "Create" or creating a database,
I go to additional settings tab here.
In this additional settings tab,
it lets me pick a data source,
this data source could be some existing data backup or a sample,
so I'm going to say backup here.
When I click here on the "Backup",
it starts loading all the backups,
geo-replicated backups that are
available to me in my subscription.
Here, the subscription that I am
using that has lot of backups available,
I'll go and look at the backup that I'm trying to restore.
Once it loads here,
we will see a full entire list of
all the backups that are available to us.
I'm going to use an existing backup to restore the database,
and when I click on "Backup" here I see a list,
and this is the database that I want to restore.
List available in my subscription,
and this is the database that I want to restore.
I click on the database, I say,
okay to pick the same compute and storage
settings that are there in the original database,
and I click on "Review + Create" here.
It just shows me that it is going to restore
in Brazil South and this is the name of the database.
I'm going to hit "Create" here.
This starts the restore process for me in a different region.
>> Shreya, this is really interesting, this Geo-restore,
I don't think I've actually ever done one of these before.
When it kicks off and when the backup that I'm using,
did I have to set up those backups to be globally
replicated storage so that I could restore
them like this or how does that work?
>> So the backups,
Security B and Managed Instance backups,
they are stored on RA-GRS account,
which means Geo-redundant storage account.
You do not have to set up anything for those backups.
We take care of replicating them in their geo-region,
so that they are available to you
whenever Azure region is down and
you can restore from
those backups in a different region altogether.
>> Wow, that's pretty cool.
I didn't know that. It's awesome.
Makes it very easy to recover from some sort of disaster.
I don't have to configure anything.
>> Yeah. That's the point actually.
We make it easy for the customers by replicating them
on geo-region so that recovery from a disaster is easier for them.
>> Now, while this is running,
because I think it's probably going to take a few moments
to restore a database all the way [inaudible]
But with all these backups
and it seems like there's a lot of things going on,
is cost a concern?
Is there a way I can monitor or manage the cost of these backups?
>> That's a very good question.
While this is running, let's quickly go back to
my slides here and I can show you
how customers can look at
different metrics that we have available for backups,
and also how they can look at their storage costs for backup.
On portal here, when you click,
go on a database, you can,
at the bottom of the pane here,
you have database metrics available to you.
When you click on these metrics,
along with other metrics that are
available to other database that are
three backup metrics which are available to the users,
which is differential backup storage size,
full backup storage size,
and log backup storage size.
They can come here, click on,
configure these metrics for the database,
and click on these metrics and they can
see the storage size for the differential and log backups,
and the storage utilization.
Now, to look at how much their storage
actually costs for this backup,
what they can do is they can in
their Azure subscription that they have,
they can go to the Cost Analysis tab
in that Azure subscription on portal.
In that Cost Analysis tab,
you can actually look at the different metrics
for storage size cost that you have.
You have to pick service name SQL Database,
and then meter subcategory,
it could be point-in-time restore storage
or LTR restore subcategory and it
will show you the storage costs
for a database for any month that you asked for,
previous month or the current month.
It could also, in some cases it also does forecasting for that.
You can come here and look at those.
Now, let's go back to our portal here,
and let me show you real
quick how you can actually look at your backups.
Because I've said that in the beginning,
that I will show you how you can look at your backups here.
Let's go to my server here real quick,
and I click on "Manage Backups" here.
When I click on "Manage Backups" here,
it shows me all the databases that are available on my server.
All the different backup policies that
I have configured on these databases.
It shows that all of
these databases have some better backup policies
like AdventureWorks only has one day of Point-in-time retention.
The others have seven days,
and I have two databases for which I have
configured LTR backup policy,
and these LTR backup policies show here.
Now, let's say, I want to change these LTR backup policies,
or I want to configure LTR backup for this database here,
for which I do not have.
I can click on the database here,
I can click on "Configure retention",
and I can come and
change the point-in-time restore configuration from here,
from anywhere between 1-35 days,
or I can also change the long-term retention configurations,
so I can say I want to take weekly LTR backups once every week,
the first week of the month,
and then I want to retain it
first week of the month for, let's say, 10 years.
I go and say that here and then I can click on "Apply".
This start applying those new LTR backup policies for my database.
After the first full backup is taken,
that first full backup will be copied as
my long-term retention backup and
then that LTR backup will be available to me.
To look at the available LTR backups for my databases,
I can come in this tab Available Long-Term Retention backups,
and then I can click on one of these databases here,
and then this shows me
that these are the LTR backups available to me.
I can look at my LTR backups and I
find future backup policies from there.
>> By looking at both the metrics and
the cost management or the amount of storage
and configuring my Manage Backups here.
That's how I can optimize my costs
versus how long I want to keep this stuff around.
Because if I keep it around longer,
that's going to be more storage costs?
>> Exactly. Now, it looks like
our point-in-time restore and LTR did finish.
Let's come back to the server here and I click on "Overview",
and you can see that the databases
that we restored AdventureWorks_noerror.
This was the point-in-time restore that we ran and
this was the LTR long-term retention restore that we ran.
Both of them are finished.
Both of the databases are available to
me now they are in an online state here.
Now, I can start running
my queries on these databases because they are available to me.
If I want to let say fail-over to
this database from my original database I can do that also.
Customers can change the connection string
and just start pointing their application to this database,
so that is also possible.
Now, let's look at notifications
here to see whether our Geo-restore finished.
It looks like it has finished here,
let's go to the server,
the Brazil server that I had where I ran the Geo-restore,
and we can come here and we see that the Geo-restore,
that I ran for this database,
this one has finished,
and this database is online now,
and available to use, so customers can,
I connect my application to this using this connection string,
and start using this database.
>> Awesome. Thanks Shreya.
In this video you've shown us a lot of
things from point-in-time restore for
mistakes or even deleted database to
long-term retention for databases up to 10 years,
even for a server.
I might have deleted all the way through Geo-restore,
which really doesn't require any setup.
It's the cheapest option in the case of a disaster.
Thanks so much for joining us today, Shreya.
We are going to put a bunch of links for
our viewers in the description tab,
so we encourage you to go learn more and if you liked this video,
please like this video or subscribe to our channel or
comment on which way you're using automatic backups today.
Thanks for joining us this week on Data Exposed.
>> Thank you. Thank you, Anna, and thanks for having me.
[MUSIC]