[Reprint] solve the timeout problem of JDBC connection to SQL azure

Recently, I was working on an azure Cloud project. The front-end is the Java code. The Java code uses the JDBC driver to access SQL azure.

After a JDBC connection string is used, it is found that the connection times out in about 2 minutes, and it can work normally on SQL Server.

JDBC: sqlserver: // xxxx.database.windows.net: 1433; database = miap; user = username @ xxxx; Password = mypassword; encrypt = true; hostnameincertificate = * .database.windows.net

 

In theory, SQL azure will automatically kill idle connection in 30 minutes, as recorded in the following document:

Http://msdn.microsoft.com/en-us/library/windowsazure/ee336245.aspx

Maximumallowable durations are subject to change depending on the resource usage. A logged-in session that hasbeen idle for 30 minutes will be terminated automatically. we stronglyrecommend that you use the Connection Pooling and always close the connectionwhen you are finished using it so that the unused connection will be returnedto the pool.

Currently, the ado.net connection complies with the preceding documents. However, some additional settings are required for the JDBC connection, as described in the following Forum URL:

Http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/69f2de2f-389b-4c4e-9497-471b8094b029/

I rewrote the Java program in C sharp, and predictably that worksfine and does not exhibit the connection drop problems.

I used Wireshark to compare the Java and. Net implementations at thetcp level and the problem becomes clear:

TCP keep-alivemessages are sent to SQL azure every 30 seconds from the. NET code, but thereare no such messages from JDBC, hence the JDBC connection times out at betweenabout 61 to 63 seconds.

The solution to this problem is here http://msdn.microsoft.com/en-us/library/hh290696 (V = SQL .110). aspx

 

Registry Setting	Recommended Value
HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services \ Tcpip \ Parameters \ KeepAliveTime	30000
HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services \ Tcpip \ Parameters \ keepaliveinterval	1000
HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services \ Tcpip \ Parameters \ tcpmaxdataretransmission	10

 

You must then restart the computer for the registry settings to take effect.

To accomplish this when running in Windows azure create a startup task to add the registry keys. For example, add the following startup task to the service definition file:

<Startup>    <Task commandLine="AddKeepAlive.cmd" executionContext="elevated" taskType="simple">    </Task></Startup>

Then add a addkeepalive. CMD file to your project. Set the "Copy to output directory" setting to copy always. The following is a sample addkeepalive. CMD file:

if exist keepalive.txt goto donetime /t > keepalive.txtREM Workaround for JDBC keep alive on SQL AzureREG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v KeepAliveTime /t REG_DWORD /d 30000 >> keepalive.txtREG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v KeepAliveInterval /t REG_DWORD /d 1000 >> keepalive.txtREG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v TcpMaxDataRetransmission /t REG_DWORD /d 10 >> keepalive.txtshutdown /r /t 1:done