>> Azure SQL provides
several connection policy settings that may have
a positive impact on the performance of your application.
Now Anna is here to tell us how and when to use
this and how to set it up today on Data Exposed.
[MUSIC]
>> Hi, and welcome to another episode of Data Exposed.
I'm your host Jeroen, and today I'm joined by
Anna to talk about connection policies.
So welcome to the show.
>> Thanks.
>> Connection policies, so what do I need to know about that?
>> So basically, in Azure SQL,
there are different ways that you can connect to
your database or managed instances.
Actually, we had someone on the show,
I think you might have spoken to him,
he did a few videos on this topic.
So we definitely recommend if you're new to this topic,
go checkout Rohit's videos and then come back here.
>> Okay. Cool. So suppose I check them out,
so how do I use this?
Can you show me a little bit about how I do this?
>> Yeah, sure. So we will go ahead and flip,
since you guys are hopefully now familiar with this,
to Azure Data Studio.
What we're going to do is we're going to use
the Azure CLI to determine what the connection policy is,
so there's three options that might be: the
default, redirect, or proxy.
Through this exercise, we compare them and see,
I've heard some things about one being faster,
so maybe see what it looks like.
>> Cool. So yeah, let's get started.
>> Great. so the first command to look at is this,
az sql server conn.policy show.
What this is going to do is show you what your policy is.
Now this should be set to the default policy,
and what that policy does is,
you see that default here,
that policy means that for everything within Azure,
we're going to use the redirect policy.
What this means is initially,
we'll go to the gateway and once it's all good there,
we can actually connect to just straight to the database.
So the argument there is that it's a little bit faster.
So by default, within Azure, we'll do that.
If it's something outside of Azure,
we'll actually proxy you through gateway every time.
So that's what the default is set at.
Now, if we wanted to update it to proxy just to see,
we'll run a little test and see what's faster,
I can go ahead and run this, and same commands,
I just say "Update the connection type to Proxy" and
then we'll just go ahead and confirm that that worked.
Now we've gotten confirmation that now we're running in proxy.
So what we're going to do is we're going to try a little test,
this isn't super official or anything.
But if I hop over to SSMS and I create a new query,
I can go ahead and run this command,
and I'm going to include client statistics,
so we can try to get an understanding
of what that network latency is.
>> So basically, you're just going to query very simple query
against this database for which
you just updated connection policy?
>> Exactly. So this "Go 20" means do it 20 times,
and we're actually going to do 10 iterations of that.
So we'll just go ahead and do that and then look at our results.
So we've run this 10 times,
and now we can use client statistics to actually take
a look at the average over those trials.
What we're going to look at is this last value,
and that's a wait time on server reply.
So this gives some indication
of the network latency that we're hitting.
So here you see we're getting 48.5.
So if you just hold that in your head,
we would try it again with proxy and see what happens.
So now if I go back to the notebook,
what I can do is kick off this command.
You see, we're using the same commands,
so az sql server connection policy update, I accept.
Now we're going to change everything to redirect.
So this means everything,
once it gets information from the gateway,
will go straight to the database.
So we're hoping to
see an increase in
performance or a decrease in that way time on server.
>> Okay, so we're expecting a decrease here.
Cool. So let's do the same experiment again.
>> Exactly. So I'm just going to go ahead
and copy that command, go back to SSMS,
open a new query,
so we want a fresh connection,
and include client statistics and we'll run that 10 times.
So we've run this 10 times,
so we'll take a look at our client statistics.
Again, we're going to look at the wait time on server replies.
Immediately what we noticed when we look at
our average, this is 22.
Do you remember what the weights are?
>> Forty-eight and half.
>> Forty-eight and half. So in this case,
it's less than half the latency that you're hitting.
So this can really help in
those scenarios where you have a really chatty application,
or just your running at an enterprise level.
>> So basically, by running these commands,
I can change the setting.
You're just saying I shouldn't be using it all the time,
I don't want to use very specific scenarios?
>> Well, it will depend on your scenario and
the restrictions that your organization has because
running this redirect policy does require
you to open up a few more ports.
So really, is going to depend on your scenario and of course,
you should do your own testing to make sure it
does result in our performance.
>> Right, and follow all the security rules
that the company you're working with?
>> Exactly. Yeah.
>> Okay. So it's not a silver bullet.
I was almost tempted to say, "Oh,
you should set it to redirect all the time."
But that's not entirely the case.
Thanks for showing that.
I hope this was useful.
Thanks for watching. Please like and subscribe,
leave a comment. I'll see you next time.
[MUSIC]