Speeding Up SQL Server Connections

We’ve heard from some customers that initial connections to on-premise SQL servers using Windows Azure Connect sometimes takes a long time if the Azure machines are domain-joined. On investigating the issue, we’ve found out that all current versions of SQL Client attempt to connect via IPv4 before IPv6 regardless of system settings (more details here). Normally, when you connect to a machine using Windows Azure Connect, the Connect endpoint looks up the name and returns an IPv6 address. However, when your Azure VM is domain joined, it can look up the name in your on-premise DNS server as well, which returns an IPv4 address. When that happens, SQL client chooses to use IPv4 address first and needs to time out the IPv4 connection attempt before it can connect through IPv6.

We’ve identified a simple workaround to avoid the timeout and speed up connections: create a firewall rule on your Azure roles to block outbound connections to SQL over IPv4. That causes the incorrect IPv4 connection to fail immediately instead of timeout. The easiest way to accomplish that is to add a startup task to your role that runs a command like:

netsh advfirewall firewall add rule name="BlockIPv4SQL" dir=out action=block protocol=tcp remoteport=1433 remoteip=(your on-premise IPv4 range)

Note that if you use SQL Azure in addition to SQL over Windows Azure Connect, you will need to ensure the the remoteip range in the rule exempts traffic to your SQL Azure servers.

If you’re looking for other performance improvements, make sure you’re using a relay close to you.

 

--Morgan Brown

Comments

• Anonymous
  June 16, 2011
  The comment has been removed