>> In this demo, we're going to take a look at
auto-failover groups in Azure SQL database.
We're back in my virtual machine,
and once again, there are a few things going on.
Ignore this right side for a moment and we're going to talk
about how I set up this auto-failover group.
In this case, you can see I'm leveraging
Azure PowerShell in a PowerShell Notebook.
I went ahead and ran
these cells just because it takes a few minutes.
But what you can see is
the first thing that I'm going to do is I'm going to
create a new logical server in a different region.
In this case, my primary is in East US and I'm going to
deploy what I'm calling a DR server in East US 2.
I'm just applying basic information like
the resource group name, the server name,
the location for the new server,
some credentials, as well
as the password which I'm passing securely.
Basically this creates a backup server in that failover region.
The next thing that I'm going to do is
create a failover group between those two servers.
The new server that I created is completely empty at this point.
Now, once I create
the failover group or a connection between these two servers,
I can then start to add multiple databases to
the failover group and it will manage the sync between them.
In this case, I'm just adding one that
mean Adventure Works database.
We can add it basically by just using this command,
Add database to failover group.
Now once that completes,
it's basically going to restore the database on the other server,
the DR server, what I'm calling it in this case.
The final thing that you need to
remember is that you're going to have to have
access to that database in the event of an emergency.
You want to make sure, or for even read scale if you want
your application or users to be able to access that server,
you need to make sure you have
the appropriate firewall rules or virtual network rules in place,
as we've talked about in previous modules.
That's what we do there.
Then finally, we can start to talk about
what the heck is going on, on the right side.
What we have is a simple Java application,
and basically what it does is it tries to do
an insert on the primary to the failover group.
What you'll notice is it's actually connected
to the failover group connection string.
That's that read, write,
listener or the read-only listener that Bob talked about.
We're going to try to do an insert on the primary,
whatever it happens to be,
and then we're going to try to read
that insert from the secondary.
We updated this application which is included in
the sample code and then I went ahead and ran the application.
It's running now.
What we can do is we can check the status of
our secondary server or our DR server in this case.
I'm just referencing the failover group and then the DR server.
The key part here is I want to grab the replication role.
I can also check this in the Azure Portal.
If I go into my server,
under failover groups, I can select it.
Even on the map, I can see what's in the primary,
my East US is in primary and my secondary is in East US 2.
Here I'm getting those two read/write listener
or read-only listener endpoint,
so those don't change.
Now if I flip back to here,
we can actually go ahead and use
this switch statement to
actually switch the primary and the secondary.
I'm going to kick this off and what you're going to see is
potentially for a few moments as this switch occurs,
we're not going to be available on either of those servers.
But very quickly what you can see is we were
able to get back to running,
and reading, and inserting from both the readable and writable.
Now I can check the replication role of the secondary server.
I'm seeing that as before,
it was the secondary.
It's now the primary.
Again, also in the portal,
if I switch back,
I can see that the primary is now that East US,
my disaster recovery, what I call DR,
and my secondary is in East US.
That's a pretty easy way to see how easily you can set this up.
I'm sure it's very difficult to set this
up in the on-premises world in
your organization to deploy
an auto-failover group to a whole other region.
Now the last thing we can do is fail back.
I'm going to basically use the switch command again, and say,
I want the original server to be the primary now.
I'm going to kick this off and I want to show you something else.
First of all, what we'll see in the portal
is that this failover is happening,
so we can track it here.
Another thing you can do is leverage SSMS and leverage some DMBs.
Right now I'm hitting the master in my logical server.
What I can see is a few things here. Let's see.
What I can see here is that the replication state is catch-up,
which basically means we've been in sync.
In that moment where we're down,
this would show up as suspended.
The DMB that shows you that is geo-replication links.
Now the other DMB you see below is dm operation status.
This one is actually going to show me data failover occurred.
It'll show me what the state is,
so completed and any errors or the
start time and the last modified time which is when it completed.
If we swap back to the Notebook and our app,
we can see again we momentarily became unavailable,
but then we're able to start reading and writing again.
Our status of our secondary has switched
back to the secondaries. This is awesome.
But one other thing I wanted to throw
in there is related to read scale.
Let's imagine this was a business critical database.
I basically have an availability group,
and in my auto-failover group,
I also have an availability group.
How many read replicas do I actually have?
There's an exercise you can go through, but basically,
I'll be able to access three readable secondaries in a way.
The way I can do that is I can access
the app using this secondary.
These are those listeners I spoke about.
I can use the secondary and that'll be a readable.
I can use the secondary,
but since it's business critical,
it has always an availability group,
so I can also leverage
the readable secondary that's associated with that.
Then for my primary,
I can also leverage the readable secondary.
The way you do that is basically by specifying
the application intent as read only.
This is another T-SQL command you can run to check,
are you in a readable stat,
a type of replica or are you in the right one?
In this demo, you saw how
easy it can be to set up auto-failover groups,
and how a failover actually incurs,
and very short amount of downtime that each have.
You also saw some various tools that you can use.
You can use PowerShell,
you can leverage the Azure Portal,
and you can monitor the success and progress in SSMS.
We also saw how you can get read-scale
from these various auto fail-over groups,
even if it was geo-replication,
especially due to the fact if you leverage business critical.
See you in the next video.