>> You've seen an introduction to Hybrid Solutions for SQL Server.
In this video, you'll dive deeper into
the architectures of what's possible for disaster recovery,
this week on Data Exposed.
[MUSIC]
Hi, I'm Anna Hoffman and welcome to
this episode of Data Exposed: MVP Edition.
Today I'm joined once again by Allan Hirt.
Thanks for joining us again.
>> Thank Anna. Nice to be back.
>> Today we're going to continue our discussion
about hybrid solutions for SQL Server.
In the previous video,
Allan talked about why you might want to do this and some of
the key things you need to be aware
of, if you're going to do something like this.
In this video, we're going to dive a little
bit deeper into what those options actually are.
With that, I'll pass it over to you Allan.
>> Thanks Anna. In this one I'm going to
focus more specifically on disaster recovery,
because that's what I said in a previous video,
the number 1 thing that I see.
Really, that's honestly the thing most of you guys who are
looking at this video probably going to be looking
at this for first.
With that said, let's dive right in.
As I mentioned in the last video,
backup and restore is a key availability scenario for
SQL Server and I talked about how you could use it
for generating backups directly up to Azure, testing them.
I'm not going to really spent any time in
this particular presentation talking about that here,
you can refer back to that.
What I want to focus on are if you're On-premises and you want to
have Azure as a DR datacenter if you will,
what could you set up?
There are really three main options that we're looking at here.
Log shipping, and I'll explain what that is.
Always-on Availability Group,
otherwise known as an AG,
it's not AOG or always on or AAG or anything like that,
it's just AG and Always-on Failover Cluster Instances or FCIs.
Let's talk first here about log shipping.
Most of you know what this is.
But I just want to reiterate,
because we had to look at it just slightly different.
Location A could be your On-premises,
location B could be an Azure region.
Simply put, you take a full backup of your SQL Server Database,
you copy it to what will be the secondary server.
You restore full backup with no recovery.
I need to stress that.
Then you generate transaction log backup.
It gets copied over to the secondary,
gets restored with no recovery and that process just repeats
out of an item until you either stop it or something happens.
Now, this has been around forever.
It was first in the product in SQL Server 7.0,
and it was in Enterprise Edition,
then it was in all editions from SQL Server 2000 on.
Even if Microsoft rip this feature out of SQL Server today,
you could implement this on your own because
it's just "Backup", "Copy" and "Restore",
there any number of ways to slice and dice this one.
Now, the nice thing about this one is this is
very tolerant of things like networking.
As long as you don't have massive T log backups,
you could do this all the time
and probably nobody is going to break a sweat.
The fact is, people have been doing this for 20,
25 years now, so it's a known quantity.
This is just easier to set up, easy to maintain.
There's not a lot involved to admin this.
Quite frankly, it just works.
Now, you do have to worry about
logins and jobs and things that sit outside the database.
We're not going to delve into all that in this presentation.
But just know, that this is an option
and you don't have to do anything fancy to have DR.
Even if you're just putting your backups
in Azure, that's one thing.
This is another and this is the next easy step from that.
Now, going on to something a little more modern
than something that we've had in the product since SQL Server 7.0.
Let's talk about AGs which
have been in the product since SQL Server 2012.
Now, up until SQL Server
2016 and then thing I'm going to talk about on the next slide,
this is how everything had to look.
Underneath your Availability Group, you have a cluster.
Now the cluster would either be a Windows Server Failover Cluster,
also known as a WSFC or Pacemaker, if you're using Linux.
You would extend that cluster from On-premises up to Azure.
That means things like your witness or how you're
doing the similar concept called STONITH in Pacemaker,
that has to all work because now you're
bridging two different locations.
That's a bigger top and
I want to get into in just introducing the concept here.
Just know that that's important.
But within Availability Group,
and in this traditional way of deploying it,
it's a monolithic view of the world.
Meaning, it's one Availability Group,
one cluster stretched across two locations.
Here what we have is we've got the primary replica,
which is this P right here and we've got a few secondary replicas.
Just so we'll be clear,
this scenario that I'm showing here would be Enterprise Edition.
If you have Standard Edition,
you can only have two total replicas,
one primary, one secondary.
Imagine for example that this one
and this one didn't exist if you're running standard.
But having said that,
what that also means is that if you have network issues
between Azure and On-premises, or any problems in connectivity,
let's say, the primary can't send up to Azure,
the primary is going to hold on to
those transactions until it can do that,
meaning your T log is going to grow.
Having reliable networking, which
we talked about a lot in the last video,
really starts to come into play here.
Log shipping is much more tolerant
of things like that because you're backing up the T log,
and it's getting flushed.
Here, things are going to stick around
until it's able to flush things out.
The other thing about this is,
from a management point of view,
while it's monolithic and simpler on that level,
getting everything right so that things don't
blip or things just flow, is a lot harder,
because you're managing one thing across two different locations.
Now, I do have customers who do this,
this is nothing new.
Again, this was in the product and
the way you have to deploy this,
starting with SQL Server 2012.
Just making you aware that this works,
but maybe there's a better option
and we'll look at that here in the next slide.
Let's talk about distributed Availability Groups.
Distributed AGs were introduced in SQL Server 2016.
Unfortunately, it's an Enterprise Edition Only feature.
For those of you running standard, I'm really sorry.
I might have to say this is probably one of
my favorite features that Microsoft has done.
At the same time it's also one of my most frustrating
because right now as of the time of recording this video,
so if you're watching this in the future,
maybe this will change.
You can only deploy this via
T-SQL and all the monitoring and everything is
just looking at the SQL Server Dynamic Management Views or DMVs.
The management of this is
inherently a little bit more difficult from
a SQL Server standpoint and deployment because you're
talking about no GUI if that's what you like to do.
However, let me explain
your architecture and why I really like this.
Just a quick refresher, monolithic.
One thing feeds everything.
In a distributed AG,
which is purely a SQL Server concept,
you won't see this in any cluster mechanism.
If I'm on On-premises at location A
and I want to go to location B,
I'm setting up two distinctly different clusters
each with their own Availability Group.
Now notice that each of these has a primary and are going,
"Well how does that work?"
Well, this primary here is known as the global primary.
This one here is known as a forwarder.
What happens is the global primary sends things to
both its own secondary replicas, as well as the forwarder,
then that synchronizes everything on its end.
The advantages here, obviously you're not worried
about one of the things I talked about earlier,
which was, if I want to stretch my cluster,
how do I do that effectively across two locations?
Here, you're just dealing with them locally.
Now, I can tell you from my experience,
I've used this to migrate to Azure more than once and it works.
I've also done it On-premises,
but this is nice and elegant.
The other nice thing about distributed
Availability Groups compared
to a lot of other features, is
that you can flip the script meaning if I flipped up to Azure,
let's say during a migration and I
needed to go back to On-premises at some point,
we wanted to make sure everything was okay in Azure,
well, the good thing is you can do that.
Then at some point, when you decide that everything is good,
you can just cut the distributed AG back down to On-premises.
But the really nice thing here is
that it enables that type of scenario.
I've customers doing this for testing DR every couple of months.
If you wanted to flip from On-premises to Azure,
you can do that and flip back.
It's just pretty elegant when it's all working,
but understand that from a SQL Server perspective,
not a cluster perspective,
this is a little bit more complex that way.
They're both great architectures.
You just have to really think about
maybe what's the best one for you.
Last but certainly not least,
I'm going to talk about something that I've been using now
for well over 20 years and that's FCIs.
Now, FCIs near and dear to my heart,
but I will tell you,
not my preferred method for any kind of DR,
because they're inherently more difficult setup
and they're a bit different than AGs.
Let me explain.
FCIs require a cluster and they're
supporting both Linux and Windows, which is good.
But the biggest difference
between this and an Availability Groups is that
FCIs always required some form
of shared storage when this becomes the rub,
because that shared storage
becomes a single point of failure locally,
which is why we usually use
an Availability Group or Log Shipping or something
else to make it extra available so
that storage isn't a single point of failure.
How they work is that an FCI
is running on this server [inaudible] denoted the cluster
and its resources, and in particular here we're talking about
the storage, is owned by this node, if you will.
That's why this has a dotted line here,
is that the storage associated with
this FCI isn't seen by this server
until the FCI fails over and then
this one will become the solid one
and then this will become the dotted line.
Fairly straightforward again, most of you who are watching this,
you probably have implemented one or seen one,
it's somewhere along the way.
The problem is when we're talking about DR,
unlike an AG or unlike a distributed AG,
which is purely a SQL Server method,
to achieve Disaster Recovery,
you've got to somehow get your storage replicated somewhere else.
In this case we are talking location B or Azure.
Unfortunately,
most hardware storage vendors
don't have a direct connect to Azure, so that would be out.
You can set up storage replica
in Windows Server Failover Clusters,
but you have to do that at setup time.
At least that's how it works today.
That could be a potential issue and there might be
other go-no-go type of things that go along with Storage Replica.
You can use a third party program and
the one that's most common is SIOS DataKeeper.
That one has been around for awhile.
But again, you need to figure that out a
bit ahead of time and get that done when you install.
This isn't something I completely am on board
with, unless you've thought this out ahead of time.
Now can you get it working?
Absolutely. The other thing you need to
figure out which were out of scope for
today's little video here is
what shared storage options are even available in Azure.
That's a whole different thing.
Now the other thing that you want to think about here,
and it's largely true with Availability Groups
as well, and its wires had four there,
is that if you failed over here,
you probably just don't want a single node FCI up in Azure.
You're probably going to want something like this.
Now, just like that initial Availability Group picture,
notice this is a monolithic view of the world.
The only one that doesn't give you a real monolithic view of
the world is a distributed AG.
Log shipping is a little bit disconnected
and it's more manageable that way.
But in terms of anything that's using a cluster underneath,
distributed AGs are the only thing is to
really separate out different clusters.
There are different features in
Windows Server Failover Clusters
that in theory could give you this,
but they're not supported by
SQL Server, so that's why I'm not talking about them.
Well, as I presented there in the slides,
there's a lot to digest and I
hope maybe you have to go back and rewatch a few things.
I threw a lot actually there,
but that is the Hybrid Disaster Recovery story for
SQL Server going up to Azure
and specifically talking about having IaaS,
VMs up in the Cloud that you would be talking to.
You can still use everything you use
On-premises today and honestly,
if you just want to think about it, as I said in the last video,
it's just an extension of your datacenter.
You'd be doing a lot of the same things
if you're going to a physical datacenter.
But in this case we're just talking about Azure.
>> Awesome. Thanks Allan.
This was really interesting.
Personally, I don't have years and years
of SQL Server experience,
so I learned a lot in this video and I
think probably our viewers did as well.
I also learned about these availability options
that are built-in in our past services,
how there is an extra effort that actually
goes into setting them up.
I'm sure you have lots of stories good and
bad about your experiences with those.
Now, if you liked this video as a viewer,
please like our video and subscribe to our channel.
Again, thanks so much Allan for being here
and we'll see you next time on Data Exposed.
[MUSIC]