>> Automated backups are
a 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. I'm Anna Hoffman, and welcome to this
episode of Data Exposed. In the previous episode, we took a part 1 look at the story for Azure
SQL automated backups, looking at point-in-time
restore, long-term retention, and Geo-restore, use
cases, and capabilities. In this episode, Shreya
is going to dive deeper into how to actually configure these things and use
them in the case of an emergency or some other situation. With that, I'll pass it
over to you, Shreya. >> 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, so in three different regions. I'm going to go to the server created in the US
West here real quick, and now 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 time. 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 blade, and it tells me that this
is a point-in-time restore. Again, click here and select
a random backup retention, restore also if I have configured
LTR backups on my database. Then it tells me to
pick a 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 no-error 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 2:00 PM, so I want to restore to 1:59 PM. A minute before I made the mistake, so I pick the date, I pick the time. I call it AdventureWorks_noerror. Then it has picked a
slow and size number of cores based on what I had
on my original database. It's not an elastic pool. I'm just going to say, "Review" and "Create" here, click and then ''Create," and then
I just go ahead and "Create." This will start the
restore process for me, point-in-time restore process for me. Now while this is running,
because this will take a few minutes to run. 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 backup, LTR restore on your database. Let's look at 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. Instead of picking
point-in-time restore, the same, I'm going to pick long-term
retention, backup retention, and it shows me the backup
set 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. 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 available to me, and so I can change the
name of the database here. I pick the time, I change time, and then I click on
''Review and Create.'' and I create, I start
creating the database. This has queued my point-in-time
retention restore, and my LTR restore on my
database on my server here. >> Awesome. Hey, Shreya, 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? >> Yeah. 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. What we do is we restore it into
another database with a new name. Once the restore is complete and
once the users have verified that their database restore 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 where their application can now start pointing
to the new database. We can see this point-in-time
restored 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. Now I want to restore
in another region. I had this server
created in Brazil South, which I believe will not be used that much 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 name. What I'm going to do is I'll say,
"WideWorldimporters_restore. Actually, it will 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'll 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 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 computer
and storage settings that are there in the
original database, and I click on "Review
and create" here. It just shows me that it is
going to restore in brazilsouth and this is the name of the database and 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? >> The backups, SQL DB
manages these backups. They are stored on a RA-GRS account, which means a geo-redundant
storage account. So you do not have to set up
anything for those backups. We take care of replicating them in a paired 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. That's awesome. Makes it very easy to
recover from some 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 a 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 to brazilsouth, but with all these backups, 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 go on a database, at the bottom of the bin here you have database metrics
available to you. When you click on these metrics, along with other metrics that
are available for the database, there are three backup metrics
which are available to the users, which is differential
backup storage sites, full backup storage sites, and log backup storage sites. They can come here, click on these metrics and they can see the storage size
for the differential, full, and log backups, the huge storage utilization. Now, to look at how much
their storage actually cost for this backup, what they can do is in
their subscription, the 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 costs that you have. You have to pick service names, SQL database, and then
meter subcategory, it could be a point-in-time
restore storage or LTR restore subcategory and it will show you the storage costs for your database for any
month that you ask for, previous month or the current month. It could also, in some cases, it also does forecasting for that. So 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'll 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 and all the different backup policies that I have configured
on these databases. It shows that all of these databases have some
bit of 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 are
their backup policy as shown 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. I can say I want to take weekly
LTR backups once every week, and first week of the month, I want to retain it for
let's say 10 years. I go and say that right here, and then I can click on "Apply". This will 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. So I can look at my LTR backups and I find a bit
of backup policies from there. >> So 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 restoring
LTR did finished. So 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 online state here. Now I can start running my queries on these databases
because they are available to me. If I want to let's say fill 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. That is also possible. All right. Now, let's look at notifications here to see whether
our geo-restore is finished. It looks like it has finished here. So let's go to the server, the Brazil server that I had
made the ran the geo-restore, and we come here and we see that the geo-restore that
I ran for this database, this one, has finished
and this databases is online now and available to use. I can connect my application
to this by 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 and it's the cheapest option in
the case of a disaster. Thanks so much for
joining us today, Shreya. We're going to put a
bunch of links for our viewers in the description tabs. So we encourage you to go learn more, and if you like this video, please like this video or
subscribe to our channel or comment on which way you're
using Automated Backups today. Thanks for joining us this
week on Data Exposed. >> Thank you, Anna, and thanks for having me again. [MUSIC]
