>> Have you ever struggled getting
connected to your Azure SQL database?
In this episode, you'll learn all about how to break down
the different issues and how to get back
to being connected faster.
This week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman and
welcome to this episode of Data Exposed.
Today I'm joined by Subbu.
Why don't you tell us a little bit about what you do?
>> Sure Anna. I'm Subbu Kandhaswamy and I am from
the Azure SQL DB support team and I work on
customer escalations and also work on improving tools and
operations for our SQL CSS engineers
to improve best customer experience.
>> Awesome, cool. You're the perfect person
because we're going to be talking about
things people sometimes struggle with and
that's understanding connectivity issues.
Can you take us through all the things we should be aware
of when we might hit these connectivity issues.
>> Sure Anna.
Customers go through multiple challenges and it
always becomes an issue of understanding where
the problem starts and we will go through some of
those and then we'll also cover
a little bit on connectivity issues,
network issues that customers would
face and how they can efficiently handle them.
When it comes to understanding a problem,
we need to know where the issue is occurring,
whether this is happening on the client side with
a login failures or if they're having
an issue specific to the network layer.
Most of the time, the error messages
the customer would receive will tell exactly where
the problem is and that's the best place to start
with so that we spent focused time on the right area.
>> Makes sense.
>> If I'm having issues related to accessing my database,
the best place to start with is the Azure Resource heath plate and
the health plate at the status of
your database showing my databases available,
and is my database has gone through a different states,
whether it's a warning or it's a critical.
Understanding the warning issues and
the critical issues gives us an idea
of whether I should take action to
escalate it if I continue to have this issue,
or I should be ignoring those warnings and move forward.
Coming to the SQL side of connectivity issues,
we often get customer saying, hey,
I'm not able to reach my database
because there's an availability issue.
Majority of the time,
these issues are transient,
meaning it goes through a reconfiguration process during
a planned or unplanned events
and it takes only less than 60 seconds.
Most of the time it's under 10 seconds.
Many situations in might go a little longer,
but it's still a transient behavior
and we should be able to handle it efficiently.
When it comes to handling the transient behaviors,
we always recommend customers to use the retry logic.
The challenges in some cases,
customer uses retry logic in many situations,
but they come back and say,
I'm having still a challenge.
I'm still seeing the error messages
every now and then, what's going on.
Majority the situations they did not follow
the rules that needs to be applied or the principles.
A minimum five seconds interval between
the retries are critical because
otherwise we'll end up having a situation called blocking period.
In a connection pool environment if I'm hitting an error message,
it takes at least five seconds,
in that five seconds window if I'm going to
retry my connection, it's going to fail.
If I'm going to retry the subsequent error messages
plus the error message,
I'm going to get into initial tab.
It's going to compound and up to 60 seconds,
I will be getting an exception saying
that I'm not able to reach the database.
Well, we should be mindful of applying those rules,
and that way we can handle those transient behaviors efficiently.
I can walk you through a quick retry logic demo to show
how my application able to handle those transient behaviors.
>> Cool.
>> Here I am, this is
my application which connects to my database
and pulls the simple select query results for my sis objects.
In this case, I'm setting my number of attempts
to connect is four times and the five second interval.
That way I'm ensuring that every five
seconds my connection retries.
>> What I understand while this is kicking off is
that we have these samples available for people to try.
>> Yes. We do have samples for
various platforms that customers can make use of it.
In this case, I intentionally
failed it for my connection and it waited
for five seconds and then made a retry
and then able to successfully
deliver the results for this database.
It's fairly simple and it's available for
all the customers to use it
directly from our public documentation.
Coming back to this, we also
experienced non transient behaviors,
similar to the availability issues that we have seen a transient.
Sometimes this can also become non transient where we
loose connections for a few minutes or a few hours.
Primarily, it could be related to the issue
of the issue that database went through.
It could be going through a long transaction while
this transient behavior occurs.
The chances are it takes longer time
for performing the recovery operations.
The longer the recovery operation,
longer the availability issue customer is going to face.
In other scenarios they also hit the request limit reached
where the request limits assigned for each service tiers,
they might hit the threshold limit and they have this issue.
The recommendation is to actually scale
up the database to the higher service tier.
Also, we have an option to go and check whether this behavior is
happening by using a dedicated admin console.
The dedicated admin console allows us
to connect to the database despite the limit has been
reached and only one dedicated admin console connection
is available for us to log in and check in the database.
Sometimes we also see customers saying, hey,
I'm trying to connect to my dedicated admin console,
but I'm still having challenges connecting to it.
Chances are they already have a session
open and then they're trying to connect it again and
the solution is to go and
ensure that they don't have
active sessions and connect to the database.
I can walk you through a quick demo
on how the dedicated admin console works.
Here I am connecting to my Azure SQL database.
It connected to my Azure SQL database
for my dedicated admin console.
In this case, if I wanted to test whether my database is going
through this session limit,
all I have to do is run the query to see whether
my max work percentage is 100 percent.
This will allow us to say,
I'm hitting the limits every now and then and
make necessary recommendations for that.
In other scenario, if the DAC limit issues,
I already have a connection existed for that connection,
if I'm going to make another connection here,
assuming that I don't have a connection open,
this would be most time customers would
experience a connectivity failure.
This does not mean the database is not available,
this means there is an existing connection somewhere,
that user has to close it and
then attempt the connection again to check it.
Moving on, we also see challenges
where the customers are not able to read
the exceptions they receive because sometimes it could
be an issue related to a performance,
but not looking into it, saying,
I'm having a connectivity issues.
Understanding the error message and the exceptions in this case,
you can check the class,
whether it's a SQL connection class or it's
a SQL command class would tell us whether this is
a performance related issues or a connection
timeout issues and then take necessary steps onward.
>> That's key.
>> Coming to the network connectivity issues,
we do see where customer experience connections getting dropped,
or the network components,
network layer dropping the connection for customers.
One of the common situation we would see is
with the idle TCP connection failures.
One of the networking devices dropping the connection.
In some situations our SQL gateway drops the connection if there
is no query requests come in
for 30 minutes from that specific connection.
The recommendation for those scenarios is for the TCP level,
you need to configure the keepalive session in
the registry and then for the gateway layer,
related connection drops,
it's always happens for proxy connections.
Using a redirection connection policy
allows us to eliminate that completely.
So that is why we always recommend customers to
use the direct connection policy to reduce
the number of hops every time it goes through
the gateway versus directly connecting to the database.
Just to give a high level and the connection policy,
so we offer two sets of connection policy,
the proxy and the redirect collection policy [inaudible] By
default proxy connects from outside and from within Azure,
if you're connecting to your SQL database, it's the redirect.
But it's configurable and in many situations,
we always recommend customers to use redirect
connection policy for the improved performance.
Then we also see communication link failures on the network side.
Where customers come back and say something went
wrong in between the client and the server.
We get this situation where customers not using
the updated drivers and that's when they
see these issues called the forcibly close by remote host.
The recommendation for this situation
is to have the updated driver,
which would support the TLS 1.2
version that's just default for the SQL database.
We do also have an option for
customers to use the lower version of TLS.
They can change it and we have steps for that,
but it is not recommended.
We always recommend to use the updated version of the driver.
Also wanted to quickly talk about this connectivity checker tool.
Which is a tool that allows us to check into any connection
and it's available as part
of public documentation which it's a GitHub based,
it's a PowerShell driven tool.
I already ran this to for my database,
sorry and what it does,
it performs an end-to-end connection.
It checks for your certificates,
it checks for your gateway connections
and then it goes and hits your master database,
user database and then also performs
a pre-login checks and in addition,
it also allows us to capture the network trace for it.
This gives us a clear idea
of where my connection is failing and if I'm not sure what to do,
I can do the network capture and give it to
the support team to handle this efficiently.
With that, I just want to conclude,
whenever you have a connectivity issue or a situation, first,
identify the problem, what is happening,
where it's happening more importantly,
whether it's a transient behavior or not and
how to handle the transient if it's a transient behavior.
What are the different errors I get,
how I can read those error messages efficiently and then using
the redirection connection policy to avoid some of
those communication failures that we have.
Also using the connectivity checker tool to understand where
the connection is failing and then
report to us in case they need additional support from there.
>> Awesome, wow, Subbu you showed us so much,
I personally learned so much about connectivity,
so I really appreciate you coming on the show.
To all our viewers, I'm sure you learned a lot as well.
I might bookmark this video in case you face any issues,
you know exactly how to go about
not wasting time on the wrong thing.
For our viewers are going to put links to
all of those recommended links in the description,
so be sure to check out the description,
to learn more about these connectivity issues and
spend less time on the wrong things essentially.
Thanks again so much for coming on the show.
To our viewers, if you'd like this episode,
go ahead and give it a like,
leave us a comment and let us know what you
think or what you learned or how you use
this video to get back to being connected
and we hope to see you next time on Data Exposed.
[MUSIC]