Skip to content
Related Articles

Related Articles

Microsoft Azure – Configure Azure SQL with Azure CLI

Improve Article
Save Article
  • Last Updated : 21 Nov, 2022
Improve Article
Save Article

 In this article, we’re going to take a closer look at how you can configure something like connectivity using the Azure CLI. What we have here is we are in Azure Data Studio and similar to the SQL notebook we are actually using a PowerShell notebook. That just means all our code cells are going to run PowerShell and we are just connecting to localhost

Now one thing to note is we can use the Azure CLI or these az commands to basically do various tasks with all our Azure resources, not necessarily just Azure SQL Database or Azure SQL Managed Instance. We can also use this in the Cloud Shell. Or we can use the Azure CLI and something like PowerShell like we’re doing here. 

In this example, we’re going to walk through how you can set up and configure your connectivity to Azure SQL and a brief look at what that impact is on the performance. 

 First what we are doing is we are using the below command to get the current connection policy to our Azure SQL Database logical server. 

az sql server conn-policy show

What you see is we’ve got the Default response. This default response means that for services within Azure, we are going to use the redirect method, but for services outside of Azure, we are going to use the proxy method. 

Now for this little test that we’re going to do, we’re going to update the connection policy to the proxy. So basically we want everything to go to the gateway and then go to the database. Here we used AZ SQL server connection policy update and then specify the proxy policy type and then we are just printing out that connection type to confirm that it worked. 

Now that it worked, we can do an unofficial test to see how it affects performance. So in SSMS, we are just going to create a new query where we select all from SalesLT.Product and we are going to include client statistics. Then we are going to run this 10 times and then we can get a good average to see what our network latency is. 

Now that it’s run, we can go into “Client statistics” and down at the bottom, we can see the wait time on server replies. Now if we scroll all the way to the right, we are going to see that on average the wait time on server replies is about 192.78 milliseconds.

Basically what this means is it can be an indication of the network latency that we are seeing.

Now if we switch back to Azure Data Studio and we remember that number of 50 milliseconds, what we are going to do is update the policy to redirect. Remember in redirect, we’re going to go to the gateway the first time, but then we’re going to go directly to the database after that. So what I’ve done is use the same command to just to update the connection policy to redirect and then we are just printing out the connection type to confirm that worked.

Again, now we’re going to do the same exact thing in SSMS, except we are going to do it with redirect and see if there’s a change. So we’ll include client statistics and run this about 10 times. Now that that’s done, we can check the client statistics again look at the wait time on server replies, which is an indication of latency, and what we are seeing is about 125 milliseconds for wait time on server replies.

With a redirect, we were able to get 67 milliseconds of network latency, which is significantly less than the 192 milliseconds that we saw for the proxy connection. Now, this is going to change on your network and your internet connection and a bunch of other things but what you’re going to see is a reduction in that latency with the redirect method. So in this demo, you saw how to use the Azure CLI to configure various things, in this case, SQL connectivity. 

My Personal Notes arrow_drop_up
Related Articles

Start Your Coding Journey Now!