解决JDBC 连接 SQL Azure 超时问题

最新推荐文章于 2024-01-31 19:56:17 发布

原创最新推荐文章于 2024-01-31 19:56:17 发布 · 3.6k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#jdbc #sql #parameters #system #sqlserver #sql server

云计算Azure 专栏收录该内容

19 篇文章

订阅专栏

本文档详细介绍了在Java项目中使用JDBC连接SQL Azure时遇到的超时问题。通过分析，发现JDBC连接缺少TCP Keep-Alive消息，导致连接在大约61到63秒后超时。为了解决这个问题，引用了微软官方文档提供的解决方案，即通过修改系统注册表设置启用TCP Keep-Alive，并在Windows Azure环境中通过启动任务来应用这些设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近在做一个Azure云项目，前台是Java代码，Java代码里使用JDBC驱动访问SQL Azure。

用了JDBC 连接串后，发现2分钟左右连接就超时timeout，原来在SQL Server上是能正常工作的。

jdbc:sqlserver://XXXX.database.windows.net:1433;database=miap;user=username@XXXX;password=myPassword;encrypt=true;hostNameInCertificate=*.database.windows.net

理论上SQL Azure会在30分钟后把idle connection自动踢掉，如下面文档所记载：

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.

目前ado.net 连接是符合上述文档的。但是，JDBC连接还需要一些额外设置，如下面的论坛网址中所描述：

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.

针对这个问题的解决方案在这里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 done
time /t > keepalive.txt
REM Workaround for JDBC keep alive on SQL Azure
REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v KeepAliveTime /t REG_DWORD /d 30000 >> keepalive.txt
REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v KeepAliveInterval /t REG_DWORD /d 1000 >> keepalive.txt
REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v TcpMaxDataRetransmission /t REG_DWORD /d 10 >> keepalive.txt
shutdown /r /t 1
:done