>> When you move into the Cloud,
there are certain things you need to do to make sure
your application will be resilient to failovers.
This includes for Azure SQL Managed Instance,
learn about some new capabilities as well as tips
and tricks in this latest episode of Data Exposed.
[MUSIC].
Hi, I'm Anna Hoffman.
Welcome to this episode of Data Exposed.
Today I'm joined by Dani,
a Program Manager on the Managed Instance team.
Hi, Dani, thanks for being with us today.
>> Hi there. Thanks for having me, Anna.
>> Happy to have you here.
Before we get started, I'd love to do just a quick intro so
you can tell us a little bit more about what you do.
>> Sure. My name is Dani Ljepava.
I'm a Program Manager in Azure Data in SQL Managed Instance Team.
My areas of expertise include high availability,
data mobility, hybrid scenarios,
and backup and restore.
>> Awesome, that's quite a few things,
quite a list [inaudible].
>> Thank you.
>> Today we're going to be talking about failover resiliency.
This is a new topic
or sometimes it's a new topic when folks move to the Cloud.
I'd love to just start by asking you,
what are transient errors and how do they
relate to Cloud applications and Managed Instance?
>> Exactly. I'd like to turn your attention to my slide.
Transient errors are something that is very normal in the Cloud,
and this is especially true of a remote system.
If you have a remote SQL Server,
such as SQL Managed Instance in application,
communicating one with another.
There could be network issues.
There could be temporary unavailability of service.
There could be timeouts because the service is busy,
or the system could have an automated failover,
which is a built-in capability of high availability systems.
Some of the examples when transient errors
can happen are software upgrades,
which we do regularly to maintain the service,
security patches if systems are busy.
All these errors are actually self-correcting.
What is important for your apps to be Cloud ready is they need to
be able to handle such occurrences
with the appropriate retry logic.
Typically, this is not
the case of applications coming from On-prem.
Maybe sometimes developers coding those applications did not
implement retry logic or maybe in the best way.
Here I'd like to
then tell you more
about it and maybe what are the best practices in this area.
>> Awesome.
>> What do you need to do if you're developing
a new Cloud app for Azure or
if you're moving an existing app to Azure?
These are all the capabilities that your app needs to have.
It needs to detect faults when they occur.
But most importantly, your app
needs to distinguish between what is
a transient error and what are maybe
permanent errors where it cannot proceed.
Then in the case that a transient error is identified,
an app needs to retry,
needs to keep a count of how many times it retried,
and it needs to have an appropriate strategy for the retry logic.
For example, if there is a fault,
your app might retry maybe in a couple of seconds.
But if the fault is lasting longer,
then your app might want to increase those waits for a little bit
longer to allow the service to recover.
>> Cool. This is really interesting.
I think you're probably going to get into this,
but I think a big part of understanding this is understanding how
high availability is implemented on MI and
also what would happen if a failover occurs,
like what happens to my application?
>> Exactly. On this slide here,
I'd like to tell you about
how failovers work on
SQL Managed Instance General Purpose
and Business Critical service tiers.
This is a part of high availability
that's built-in for Managed Instance.
In a General Purpose [inaudible] ,
Managed Instance is a single node.
If there's a fault with
the service or there's a faulty node or any kinds of issue,
that single node system gets basically thrown away
and replaced by healthy new node from
the pool of spare nodes and then you get a new node.
This technology is an equivalent to
what FCI failover cluster instance would be On-prem.
For Business Critical service tier,
we have always on implementation,
which is equivalent to AlwaysOn On-premises,
where there exists a primary SQL Server
and then three secondary replicas.
If there is a failover,
then a primary replica and secondary replica switch the place.
Or maybe if a node is faulty,
that node will be taken out and then a new node will be rebuilt.
These are all capabilities of
built-in high availability of Managed Instance.
But when these situations occur,
they do ensure that your applications
run with four nines of high availability.
But when they do occur,
there is a temporary loss of connectivity
lasting a couple of seconds and
your applications need to be able to handle that.
>> Awesome. How can I alter my application or build
my application in a resilient way so that it does handle that?
>> Excellent question.
Let me show you that in a demo.
>> Awesome.
>> What I have here is I'd like to draw
your attention to Visual Studio.
This is a demo application that I wrote.
By the way, at the end of the session,
we'll provide a link where you will be able
to download this application.
I will draw your attention to the following parts of the code,
I will run and I will insert the query in the table 100 times.
I will wait two seconds between inserting a query.
Then I will show two ways
of application connecting to SQL Managed Instance,
which will be controlled by this variable here.
One would be resilient and the other will be
non-resilient way to connect to Managed Instance.
Let's try first with
non-resilient way to connect to Managed Instance.
I will go to SSMS.
I will show that we have created a table.
In that table we have inserted a timestamp.
There's the current timestamp and there is only one row.
I will now run the application.
The application will start in a couple of seconds.
What this application does,
it is inserting rows into Managed Instance.
So you see, just take some time.
Now you see execution 1, 2, 3, 4.
If we go back to SSMS,
if I refresh this table,
we see that these rows are now appearing.
Now what I will do is I
will manually initiate a failover of this Managed Instance.
It is a PowerShell command,
something new that we have built
something that that you could use and be able to read after this.
Let's go back to our window,
failover has been executed,
and here is a problem.
My application was terminated because it was not
resilient to the failover in the Cloud,
and I do get a message that there was
a failover occurring and that it cannot connect to the server.
Obviously this is a problem.
How are we going to fix this?
Let's go back into our application.
Let's see true here to
initiate connectivity to Managed Instance
that has built-in resiliency.
If we look into the code and once again,
you will be able to look at this code yourself,
in a link that we will provide.
We will implement some resiliency.
If there is a fault every 10 seconds,
we will try up to four times to reconnect to the Managed Instance,
so that will make the application more resilient.
But more importantly, I would
also like to draw your attention to this part of the code,
is where we identify transient errors for SQL.
So these are SQL Server errors that we watch for.
By application identifying these errors,
it will know that it is a transient error in that it is safe
for the application to retry.
Now there's one more thing that I need to do is because we have
a limitation that one failover can be initiated every 15 minutes,
which we see in here.
At least 15 minutes needs to pass between instance failovers.
I will just switch this application to a second MI,
so we will now have the implemented resiliency, I will start it.
An application will start.
We see a little bit of a difference in here.
We see that I wrote in here that the application is resilient.
It reports success on inserting the rows.
Let's go back into SSMS,
on a second instance,
let's see if the rows are being inserted.
There's a lot of rows being inserted in here.
Then let's execute a failover on this second Managed Instance.
Let's go back to the application window,
and let's see how the application handles it.
Application is obviously detected that there is a failover,
but then it is waiting to retry.
So the application did not abort,
it did not terminate,
so it did continue running.
Now, we did put a timeout of 10 seconds,
so we need to wait at least for 10 seconds for it to resume.
Now we see something is happening here.
It says that has identified this transient error,
and then we'll attempt now to reconnect two or four times,
and let's just give it
a little bit more time until it continues to insert the rows,
and here it goes.
Voila. So the application has continued
running because it was built in with
resiliency. How's that happen?
>> That's really cool.
I think that built-in availability stories
always really interesting.
If this happens, because here you invoked it,
but maybe there's some issue we
need to value over on the back-end.
Like is there a way that I can see
that these failovers have occurred?
>> Excellent questions. So if we go to the portal,
just give me a second to switch in here.
This is my Managed Instance,
I click into the ''Activity Log'',
and here in the activity log,
I can see a log of what has
happened for my user-initiated failovers.
>> Awesome. That's really cool.
I especially like how the application can now
continue to run as long as we hit one of those transient errors,
because we know it safe to retry.
As opposed to your first example where we just got disconnected,
the app became broken.
So that's where we're going now,
as we think about implementing this
and best practices and where to learn more like,
what advice would you have to our viewers?
>> Excellent question. So this is what I would advise
our viewers to do for
the existing gaps migrating to the Cloud or for building new apps,
is you need to invest
a little bit time into application resilience.
It will pay significantly.
You need to use the latest drivers.
The latest drivers have built-in
better handling of the resilience in the Cloud.
I would recommend to use those drivers
to set the retry logic in the connection string as well.
Here is an example in
the connection string to SQL Managed Instance,
you can set the timeout
to 30 retry count to three retry connection,
retry interval to 10.
This is something that really strongly recommend.
But most importantly, build
the retry logic in your applications and
use the PowerShell command and
the functionality that we have that I've just shown you,
and we'll show you in the article at
the end of this session
to test your applications for the resiliency.
So once you built all this in,
you follow the best practices.
You read the article,
you'll find out a ton more stuff
about how to build the resilience in your applications.
Use the manual failover PowerShell for
Managed Instance and test your applications for resiliency,
and then there'll be a Cloud-ready apps.
>> Awesome. Well, thanks Dani so much for coming on the show.
We are putting up a link to go
and learn more that will also be in the description,
so you can learn more about application resiliency.
We covered a lot in this short little video.
We talked about the built-in high availability,
we talked about application resiliency and some tools,
even some pre-built samples you can leverage that Dani shared.
So Dani, thank you so much.
To our viewers, thanks for joining us today on the show.
If you liked this video,
please give us a like.
Let us know what you're doing for
application resiliency in your Managed Instance applications,
and we hope to see you next time on Data Exposed.
>> Thank you.
[MUSIC]