As I write this blog post, There are six location options while provisioning a SQL Azure server. And so while provisioning a SQL Azure server, who may have to decide the optimal location of the SQL Azure server based on the criteria that the latency between your application and the SQL Azure server is the minimum. And as you may know, we get better performance – if we are are able to minimize the latency between client and SQL Azure. So let’s get into action.

The first task would be to figure out the location from which the SQL Azure would be accessed. For e.g.: There’s an app hosted at some facility in Dallas (Texas, USA) and it uses the SQL Azure as it’s Database. Simple scenario – And I want to figure out which is the optimal location for my app. Intuitively, I can say that the South Central US would be best choice for this scenario. But how do we test it? In other words, we need to test the latency between the client application and the SQL Azure. So how do we do it?

We can test the latency between client and SQL Azure via SSMS. The client statistics helps here:

 

Ok, so I am based in Dallas, Texas and I am going to test the latency for South Central US and West Europe data center. Intuitively, I know I would go for South Central US datacenter – But let’s prove it too.

 

So before the test:

  • I already have identical databases in both – South Central US as well as in West Europe Data center
  • I have set SQL Azure firewall rule so that I am able to connect to the database via SSMS
  • I’ll be running the same query on both databases and then we will see how we can analyze the client statistics report.

 

So here is the client statistics report for query ran on SQL Azure DB located at West Europe datacenter:

 

And here is the client statistics report for same query ran on SQL Azure DB located at South Central US:

 

Now out of three kind of statistics shown by Client statistics namely Client Execution Time, Network Statistics and Time Statistics – We are interested in examining the TIME STATISTICS section. And in particular the “Wait on server replies” value.

Wait time of server replies is nothing but the total time in milliseconds the client spent in waiting for server reply. And as you can imagine, the value of the “wait time on server replies” would be directly proportional to the latency between client and SQL Azure. More the latency between client and SQL Azure, the more would be the value of “wait time on server replies”.

Now, Let’s examine the wait time on server replies for our scenario:

For West Europe, it was 116 ms

For South Central US, it was 70 ms

 

Since our aim was to have select the SQL Azure location for which we have minimum latency – we would choose South Central US in this case.

 

And You should consider running this test multiple times on different time for accurate observations. But running it once, do give us an overall idea of the latency between client and SQL Azure.

 

And as an end note:

If you have a windows azure app that accesses SQL Azure database, then the decision becomes easier because – it’s best to host the SQL Azure database and the windows azure app in same datacenter and it has two benefits:

  1. Minimal latency => better performance!
  2. And the finance guy in you would be delighted to know that no charge applies for data transfer in same data center! so no charge is applied on data moving out of SQL Azure which is then accessed by the windows azure app hosted in the same datacenter.

But again,

you should Know the location of the application that would access SQL Azure and once you know that, you can decide the optimal location of your SQL Azure server.