>> Auto-failover groups are a relatively new feature of Azure SQL.
I don't know a lot about it,
but Anna is here to tell us how and when to use them,
today on Data Exposed.
[MUSIC]
>> Hi, this is Data Exposed again,
and I'm your host Jeroen,
and today I'm joined by Anna. Welcome.
>> Thanks Jeroen.
>> We're going to talk about
Auto-failover groups which for me is a completely new subject.
So I'm just going to ask,
to start with the basics. What are they?
>> Yeah, so its good question.
So Geo-Replication for Azure SQL
has been around for Azure SQL database for awhile,
and what this allows you to do is basically create
readable secondaries in other regions or the same region.
>> Right.
>> Now, more recently we introduced
Auto-failover groups which adds some certain capabilities to that.
>> So let's imagine I'm a customer.
I have everything set up. I'm happy.
So why would I consider using Auto-failover group now?
>> Over geo-replication?
>> Yeah.
>> Yeah. Good question. So Auto-failover groups, like I said,
are for different things in a way and I actually pulled up
this great comparison chart that you can use to
decide what's best in your scenario because it will depend.
So for example, if you need something like automatic failovers,
so if something happens and you need to
switch your primary and secondary,
then failover groups is the way to go.
>> Okay.
>> On the other hand, if you want to deploy in
the same region as your existing one,
then geo-replication is the way to go
because that's not something you can
get with failover groups today.
>> Okay.
>> Then another thing to call out is that failover groups
are supported in managed instance,
whereas geo-replication is not today.
>> Right. So now that I know a little bit
about when would I use when,
can we include that link in the description?
>> Of course.
>> Cool. So let's make sure that we do that.
So how do I get started then?
I mean, I can probably run some commands I would hope.
>> Yeah, exactly. A great question.
So what we have today
that we want to walk through is this tutorial.
So I really think that
after this video you should go give it a spin.
I think you'll learn and see how this works.
So this is a tutorial we're going to walk through.
What you do is you create a failover group.
We're going to use PowerShell and
then we'll run a Java application
to see if and how we're able to connect,
to read and write from our primary,
and then our secondary in our failover group.
>> Cool, I'm excited to see it.
>> Cool.
>> Yeah.
>> Let's get it going. So what I have here is I
have Azure Data Studio on one side,
and on the other side I have this Java application running.
Now, I've already done some stuff to prepare this but,
so we'll talk about what's going on here in just a second.
So what you have to do in order to get started is,
first we're going to use the Az module on PowerShell.
So I'm just going to go ahead and set
this subscription that I want to use.
Once I do that,
then I can create a backup server in the failover region.
So this is something I went ahead and did
it's just takes a few minutes.
We basically create that backup logical server
and then we'll select the Databases we want to move over,
and then we'll do a copy in a way of the original.
>> Cool and this is also
a great reason why we're using Notebooks here.
Because you just said,
I run the commands, so we can see the results here.
>> Exactly, a great reason for using Notebooks.
>> Right.
>> So this is the command that
shows how to create a failover group.
So now, I'm just linking
my original server and what I've called here my drServer,
so Disaster Recovery Server.
Then I'm giving that failover group a name.
Now, when I want to connect,
I will just connect to the failover group name,
and I can show that and either
using just the normal database on windows.net,
or adding that secondary for the secondary in this case.
>> Okay, Check. So you set it all up?
>> Yeah. So we set all up. We added
the database to the failover group.
We can go ahead and check the role of that drServer.
So in this case it's running a secondary.
>> Right, has to be expected.
>> Exactly. So now I can give
a little bit more info on what's happening over here.
So what you'll see is for each line,
there's an insert and there's a read.
So what we're trying to see is,
can we insert on the server that we've identified as the primary?
So our main server.
>> Right.
>> Then, are we able to use
that second server as a readable secondary?
So we're basically using T-SQL in the app to do
an insert of a row and then we're trying to
read that row from the secondary.
>> Right now, both the right,
so to insert and read are successful, right?
>> Right. So they are successful.
So what I'm going to do is I'm going to go ahead and spin this
off and what this command does,
if we take a look at it,
is we are switching and what that means is
we're going to switch the primary and the secondary in
this case or failover to the secondary.
>> Right. So we just face keep forcing the failover
now to just make sure it works.
>> Exactly. Now, so what's going to happen,
is as this switch happens,
there will be a brief time where
you might not be able to insert or read.
>> Right. So I'm assuming
that my app needs to be able to handle that somehow?
>> Yes. So that's a very important thing to call out is
that having retry logic in your application is super important.
So that this doesn't bring you
down and you're still able to failover.
So we can now see on the left what happened.
We had two times that we tried to
insert and read and it was unsuccessful,
but now we can see it's successful again.
So now we were able to switch,
and we can confirm that by running
this command before and checking the role of that drServer,
and now we see it's running as the primary.
>> Yeah, cool.
So basically we've been able to prove that this works and
then we have our retry logic in
our application we've been able to restore.
Basically, the connectivity after
those small time of downtime a little bit.
>> Exactly-.
>> a little bit help.
>> We can also fail back as needed and
you'll see the same results.
>> Right.
>> Yeah, it's a simple tutorial to just really bring
home what's happening behind the scenes and
how important retry logic is.
>> Right. So cool.
So I encourage you to try the tutorial, go check it out.
Go check out the app and the retry logic and give it a spin,
and thanks Anna again,
for sharing this with us.
I hope this was useful.
Please like, subscribe, and comment,
and I hope to see you next time.
[MUSIC]