Connect to Azure SQL Database V12 via Redirection

[Kun Cheng]

First published on MSDN on Sep 08, 2016

Authored by Kun Cheng

Reviewed by: Vince Curley, Saurabh Singh, Joe Ponce-Galindo, Murugan Ayyappan, Dimitri Furman, Denzil Ribeiro, Arvind Shyamsundar, Murshed Zaman, Sanjay Mishra, Mike Weiner

Introduction

In the old days of Azure SQL Database (prior to V12 ), SQL Database used what is called a gateway to proxy all connections and communications between clients and user databases. With V12, the gateway is still there, but it helps to establish the initial connection, and then gets out of the way in some cases. In the cases where direct connection can be established, subsequent communication happens directly between client and user database without going through the gateway anymore. This feature is also known as client “redirection”. The benefit of this “redirection” is faster response time for each database call, and better performance.

So how do you know if your application is taking advantage of the “redirection”?

The first restriction is that “redirection” by default is only supported for connections originating within Azure IP address space, so your application and Azure SQL database must both be deployed in Azure. However, an application outside Azure can also use “redirection” when a server connection policy is properly created (connectionType should be set as “Redirect” to enable “redirection”) against the target Azure SQL Database server. Keep in mind though the latency/perf benefit of redirection is very much diminished in the latter scenario since internet connection latency from outside the Azure data center would be much higher.

Second , your application must be using a SQL Server driver that supports TDS 7.4. Those drivers include (not a comprehensive list):

• ADO.Net 4.5 or above


• Microsoft SQL Server JDBC 4.2 or above (JDBC 4.0 actually supports TDS 7.4 but does not implement “redirection”)


• Microsoft SQL Server ODBC 11 or above

-- Note: Tedious for Node.js and JDBC 4.0 don’t implement redirection.

A simple way to find out what version of TDS the application is using is by querying:

SELECT session_id, protocol_type, protocol_version = SUBSTRING(CAST(protocol_version AS BINARY(4)),1,1)

FROM sys.dm_exec_connections



Sample output:

session_id           protocol_type   protocol_version

89                           TSQL                      0x74

105                         TSQL                      0x74



If protocol_version is equal to or greater than 0x74 then the connection would support “redirection.”

Third , as documented here , even applications using the right SQL Server drivers aren’t guaranteed to make successful connections via “redirection”. You also need to make sure the following ranges of outbound TCP ports (in addition to 1433) are open on the application instance: 11000-11999, 14000-14999. This is the reason why “redirection” is not enabled by default for connections originating outside of Azure – in some on-premises environments, network administrators may be unwilling to open these additional outbound port ranges, causing connection attempts to fail.

Use Wireshark to look deeper how redirection works

Now let’s use Wireshark (a network tracing tool) to examine the network traffic of a sample application running on an Azure VM that connects to an Azure SQL database, so we can see how it works. (If your application is deployed in a VM or cloud service, you can RDP into your app instance and install 3 ^rd -party tools like Wireshark. Azure App Service doesn’t allow RDP.)

Sample application connection step through:

1. Open a new connection to an Azure SQL database


2. Execute command to run Ad-hoc query 1


3. Execute command to run Ad-hoc query 2

In step #1, when new connection is being established, we can see in Wireshark the TCP connection handshake pre-login as shown below (starting at time 2.702112). 10.5.0.4 is local VM IP address where the application is running. 191.235.193.75 is the gateway IP address, used for inbound traffic on default port 1433.



To finish establishing the connection, a dynamically identified port, in this case 11142, was sent to the application (time 2.790811). The application used that port and connected to the target user database (time 2.791394), with the IP address 191.235.193.77. The application then executed the first command (time 2.792376+).



Let’s proceed with executing the 2 ^nd Ad-hoc query command. Remember that the connection is still open at this point, so when the application sends the command, it doesn’t need to go through the gateway (191.235.193.75) anymore. Instead it uses the “redirection” to communicate with the user database (191.235.193.77) directly (time 8.891064+).

Recap

To summarize, for an application running in the same data center as SQL database to leverage “redirection” capability, it needs to:

1. Use SQL Server driver version that supports TDS 7.4 or above (ADO.Net 4.5, JDBC 4.2, ODBC 11, or above).


2. Make outbound TCP ports open on the application instance: 1433, 11000-11999 and 14000-14999.