>> Learn how Azure enables you to use the force to set up
disaster recovery easily for your Azure SQL databases.
Learn more in a special MVP Edition episode of Data Exposed.
[MUSIC]
>> Hi. I'm Anna Hoffman,
and welcome to this episode of Data Exposed MVP Edition.
Today, we have a very special guest in
a special episode because we are here recording on May 4th.
Today, I have John Morehouse with me.
John, why don't you introduce
yourself and tell us a little bit about what you do.
>> Hi, Princess Anna. I am Obi-Wan Johnobi.
I am a MVP data platform with Microsoft.
I currently work for
an organization called Denny Cherry and Associates.
We do a lot of things in Azure
and database automation and performance tuning,
pretty much anything SQL Server-related.
>> Awesome, cool. Well, we're excited to
have a real live Jedi here with us.
I can't say we've ever had one on the show
before, so it's very exciting.
Today, we're going to be talking about disaster recovery.
I think this is a very common topic,
especially when people move to Azure SQL database,
they often wonder, as
a starting point what's happening to my backup.
I know you're going to talk to us a lot
about some of the different things,
but I thought if you could start by telling us a little
bit more about backups in Azure SQL database.
>> Sure you bet. One of the things that I really love
about the Azure platform is as a DBA,
I'm always concerned about,
and it's probably one of my top concerns is how do I recover from
disaster to ensure that my data is safe and
sound and I keep it away from the empire.
With the Azure platform,
we actually have a couple of different things available to us.
The first thing that we're going to talk about is backups.
As you can see on my screen,
I've actually got some servers setup.
We are really concerned about and
focused on that rebelalliance server here.
If we drill into that, we can actually see,
once the portal comes back,
I've got a couple of databases that we are concerned about.
First and primary, we want to be concerned about our bases.
We really don't want our bases database to fall in the hands of
the empire because then
all of our bases will belong to the empire.
We want to make sure that we can recover if we need to,
and the first thing is using backups.
That's these tried and true methodology for a lot of DBAs,
and the Azure platform takes care of that for us automatically.
If you create a new database within the platform,
it automatically does backups for us
and then we can restore those backups.
Because the backups are at the server level,
we actually have to go look at the server.
If we scroll down here in the control plane and this blade,
we can actually see a backups under data management.
We can actually drill into here.
One of the cool things about the platform is,
if we look at the retention policies,
by default out of the gate,
you get seven days a point in time restores for your database.
Just like on-premises installations of SQL Server,
where using a full recovery model
you can restore to a point in time,
Microsoft gives us those seven days absolutely
for free along with the database.
Now, we can change that.
We can actually drill in and say, "You know what?
I want to be able to restore to a point in
time further back than seven days."
If we drill into this retention,
we can actually adjust our point in time up to 35 days.
Now, if we adjusted up to 35 days beyond that seven,
we have to pay for it,
but we can facilitate that and ensure that we
can restore our bases database.
Just in case the empire does get a hold of it,
we can back it up and restore it somewhere.
We can also do long-term retention if we wanted to.
Again, this is an additional cost to the rebel alliance,
but we can say, "Hey,
we want to keep our weekly backups
for a certain period of time up to 10 years."
Weekly backups with the Azure platform only happens about
once a week, and so we can say,
let's keep that weekly full backup
for all the weeks up to 10 years. That's the first thing.
>> Okay, cool. This seems pretty easy to set up, so it's nice.
Then it's just taken care of by the platform,
which is also really nice.
Now there are some other things that people
might consider outside of
just backups when it comes
to protecting the rebelalliance, right?
>> Yeah. As much as I love backups,
and I absolutely love
that the platform takes care of the backups for us,
restoring the database, especially if it's
a large database can be cumbersome.
There are some other options that we have available to us that
are super easy to set up just
like with the backup retention policies.
We can set these things up pretty easily, and there's two of them.
The first one we're going to talk about is active geo-replication,
and then the second will be auto fail-over groups.
Remember that the backups are configured at the server,
and some of these geo-replication and
fail-over groups also have different contexts.
I'm just going to move away from the backup policies
because I'm okay with seven days.
If I look at the bases database
on the left-hand side and the blade,
if I scroll down, we'll see a geo-replication option.
The nuts and bolts of it under
the hood is basically like an availability group.
We can actually stand up a readable secondary somewhere else in
a different region or even within
the same region of that primary database.
We can see here that right now my basis database,
the primary is located in the East US 2.
I currently don't have geo-replications configured,
so let's go ahead and configure that.
I do already have an additional server configured.
If we look in my list of servers,
we can see that I've actually got a rebelalliance-dr.
It's located in the West US 2,
so basically I would replicate
the database all the way from the east coast all the way to
the west coast of the United States to ensure that
just in case the empire gets a hold of my database,
I can fail-over to
that west coast secondary and
bring things back online pretty quickly with the service.
Let's go back to the database.
This is another feature of
the platform that I absolutely love because it's clickable.
As a DBA, I don't have to
understand clustering or how to fully
understand how to set up availability groups.
I can just point and click and get
my database having a DR strategy and solution really,
really quickly with just clicking through. We go here.
The database has paused, it'll come online.
We said that that secondary server was in West US 2.
I'm going to go ahead and select my West US 2.
It's going to come up with my information already pre-defaulted.
It already picked the server for me.
It knows that I'm going to put that database there,
and so it configured the compute and storage for me.
You can change that if you wish.
I'm go ahead and click "Review and Create."
We can see that the deployment has been initialized.
What it's doing right now is just like if you are familiar with
availability groups with on-premises
or Azure VMs with SQL Server installed,
it's actually doing a seeding of
my new database in the new region in West US 2.
As we can see the deployment is in progress.
If I come back to the blade,
we find my database,
go down here to geo-replication.
We can see here is indeed seeding the database.
First is resumed in the database because it was paused,
it was in the serverless tier,
so to bring the database online.
Now it's online, it's going to
start seeding that database in the secondary.
It's going to create the database on that server and it's going
to scheme a copy and
all the data and send it over to that secondary.
It's very much like availability groups under
the hood but then eventually this will come online.
It's going to take a second.
But think about as a DBA,
as you're going through this process and we're
waiting for this thing to seed,
how easy it is to configure it.
I have a solid disaster recovery solution for
my Azure SQL database located in
a completely different region within the United States.
If you're old like me, you
remember back in the day when trying to do
a disaster recovery especially over a long distance,
was a complete pain in the butt.
Lots of hardware, you had to go rent circuits,
you had to get a datacenter.
It was just a complete nightmare to stand
a disaster recovery in another geographical location.
Azure gives us ability really easily.
>> Not only that John,
but I see there are other target regions here,
like is it possible for me to
use this database to use Geo-Replication in
a bunch of different regions or I'm I only limited to
one and can I use them all for read access?
>> That's an outstanding question and the answer to that is yes.
With Active Geo-Replication, you can put,
I believe it's up to four readable secondaries
from the one primary in multiple regions.
You can even put a readable secondary within that same region.
Let's say you had a workload,
you want to offload that workload to a read-only copy,
but you wanted it to be centrally located
to where your users are, your applications reside.
You can put a readable copy
of that database within the same region.
Another thing you can do is you can actually
daisy chain the databases so you
can make Geo-replicated copies of the Geo-replicated copies.
You can daisy chain them along,
you get a performance issue as more change you
got longer that's going to take for the data to catch up,
but you can do that.
We can see that the database is online,
the secondary is online and it is readable.
If we had read-only workloads that
we wanted to move to that secondary we could.
We can also if we go a step further and use Failover groups,
we can now Failover that database if we wanted to that secondary.
Let's go take a look at Failover groups.
Failover groups as I mentioned before is at the server level.
Think of it just like an availability group that you might have
with on-premises or SQL Server on an Azure virtual machine.
But if we scroll down into data management,
we can see we have an option of Failover groups.
In this blade, we'll go ahead and create
a Failover group and we're going
to go ahead and call this Failover group.
I said don't ever type in
a video but we're going to do it anyways.
I misspelled it? I did misspell it.
The server where I'll select is our DR Server,
we're going to leave the read-write policy,
Failover policy as automatic and
the rewrite grace period for
one hour and let me explain those really quick.
The read-write policy, the Azure service itself can
initiate a Failover if there's
a problem with the infrastructure behind the scenes.
The servers can automatically say,
we're going to Failover, and it will do it automatically.
The grace period is because of
the nature of Failover groups by default,
the traffic is asynchronous commits.
There is a grace period of a minimum of one hour to allow any
traffic in flight to get committed to the
secondary before the service actually fails it over,
you can force that Failover,
but you might get data loss.
I recommend that you leave that the same.
We want to make sure that our base is database
and traffic gets securely committed to the secondary.
I'm going to go ahead and configure some databases.
In the Failover group, I can add multiple databases if you want to
fail all the databases over in one big thing.
Or you can do for Failover groups for
individual databases if you want
a granular control as to what's failing over and what's not.
In this case, I'm just going to add the bases database into
the Failover group and select. Then we're going to create it.
What this is going to do is one
of the cool things about Failover groups,
is that you'll actually get
a read-write endpoint and a read-only endpoint.
You can actually configure the rebel alliance,
we can configure our empire fighting
applications to use the appropriate endpoint
for whenever we need it to do.
We can see here that the Failover group has already been created.
Our primary server is
rebelalliance and the other one is rebelalliance-dr.
Here we can see that the Failover group
has successfully come online and so we got
a really nice topology map that we can see that we're
replicating data from the blue checkbox to the green.
The blue is my primary here in the East US 2,
on the Eastern Coast of the United States and data's being
automatically replicated all the way to
the West Coast and West US 2 in the green dot.
If we scroll down,
we can see what roles we are in for each server,
in each database, where
read-write policy is and what a grace period is.
The cool thing is we can actually do
this read-write listener endpoints
and read-only listener endpoints.
I had mentioned I think,
if our rebelalliance apps wanted
to automatically be routed to the right location,
meaning I want to write data so I need to go talk to
the primary in the East Coast,
I can use that endpoint and if I were to do a Failover,
that endpoint actually just does basically
a DNS change behind the scenes and we
don't have to change our rebelalliance applications
to make any of those changes.
We can actually do a Failover real quick and if I were just click
"Failover" this will actually switch the primary and secondaries.
We can see that the map changes and we're now replicating data.
You can see that the traffic
is actually going the other direction from
the green dot to the blue dot and this Failover right now.
Think about availability groups under the hood,
we're just doing the Failover and so
the servers is doing all this work for us.
As a DBA, these types of technology
is in my opinion glorious for DBA to
ensure that our databases are safe and we have
a disaster recovery scenario in case the empire gets our bases.
We can see that the blue dot and a green dot have switched sides,
and now our blue dot is the primary.
We scroll down, we can see that yeah,
the rebelalliance-dr server is now
primary in this role and we have successfully
created a disaster recovery solution with
just clicks of a mouse, no coding, no-nothing.
There is additional cost in this of course,
but very easy to configure.
>> Awesome, Yeah, and I think that's one of
the really cool things about
fail-over groups is that when this Failover happens,
we already have these endpoints available
to you so you don't have to change any connection strings,
so that can be really nice in DR scenarios as well.
As we wrap up this demo John,
I'd love to hear like if you have any tips and
tricks to young future
Jedis on how they should think about DR with Azure SQL database?
>> One of the primary things I want the younger Jedis,
the Padawans to make sure that they understand is,
even with moving to the Cloud
and whether it's Azure or any other Cloud,
I'm a huge Azure fan.
We have to think about disaster recovery.
Things do happen and
in the industry we sometimes we call it a rabbit backhoe,
where a backhoe cuts the wrong fiber cable to a datacenter.
Things just happen.
If you live on the West Coast,
you deal with earthquakes,
on the East coast it's hurricanes.
We have to make sure that our data is protected even in the Cloud,
even if the service that has high SLAs
and it's robust infrastructure and architecture,
we still have to plan for it and doing things
like this is one of the top things I tell people.
>> Thanks so much, John. I think that's a great tip.
This video has been really useful and fun for
learning about disaster recovery options in Azure SQL database.
We'll throw some links in the description if you want to
learn more about what we talked about today.
We'd love to see your comments, likes,
subscribe to our channel to learn more
and we hope to see you next time on Data Exposed.
>> May the fourth be with you.