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