>> 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]
