解决JDBC 连接 SQL Azure 超时问题

原创 2012年04月01日 15:48:01

最近在做一个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

Java JDBC连接SQL Server2005错误:通过端口 1433 连接到主机 localhost 的 TCP/IP 连接失败

错误原因如下: Exception in thread "main" org.hibernate.exception.JDBCConnectionException: Cannot open conn...
  • njnu_mjn
  • njnu_mjn
  • 2011-08-08 13:56:36
  • 34843

通过Java访问国内Azure服务

随着Azure服务在国内的开展,不少Java用户也开始在国内Azure部署应用。不过,目前有一个问题,是关于证书。国内Azure服务HTTPS端点的证书,是由CNNIC颁发的,而CNNIC作为一个根证...
  • ShaunFang
  • ShaunFang
  • 2013-12-13 20:48:46
  • 2889

java通过JDBC链接SQLServer2012

下面请一字一句地看,一遍就设置成功,比你设置几十遍失败,费时会少得多。   首先,在连接数据库之前必须保证SQL Server 2012是采用SQL Server身份验证方式而不是windows身份验...
  • stewen_001
  • stewen_001
  • 2014-02-20 14:20:38
  • 214153

JDBC 连接SQLServer 错误总结

这是本人遇到的清苦样,本来没打算写下来的,可到目前位置让我纠结了整整两天的问题解决  这真的是让我有点喜出望外,这也不是什么大问题,也就是我这种新手才会遇见的,因为刚开始接触到javaEE 本来原来学...
  • javaxiaochouyu
  • javaxiaochouyu
  • 2011-03-18 21:25:00
  • 4605

如何调整Azure HDInsight 的时区

HDInsight 在创建后,默认Name Node /Data Node 的时区都是世界标准时区(UTC). 当客户把应用迁移到HDInsight, 有些情况客户可能需要调整集群节点的时区为中国时区...
  • JerryLu2000
  • JerryLu2000
  • 2015-11-02 15:53:13
  • 642

java通过JDBC链接SQLServer2012【转载!!!超详细】

http://blog.csdn.net/stewen_001/article/details/19553173/
  • hon_3y
  • hon_3y
  • 2017-05-30 17:13:26
  • 1778

Microsoft JDBC Driver 3.0 for SQL Server and SQL Azure

  • 2011年06月28日 16:32
  • 3.61MB
  • 下载

JDBC连接超时,导致数据库操作失败

Solution: 数据库连接字符串加上 String url ="jdbc:mysql://10.32.212.22:3306/tdsdb?autoReconnect=true&failOverRe...
  • kielin
  • kielin
  • 2017-07-23 11:08:15
  • 305

花了几个小时JDBC连接sql server 2012出现的异常com.microsoft.sqlserver.jdbc.SQLServerException: 通过端口 1433 ...解决了!

下面是连接sql server 2012的代码: import java.sql.*; import java.io.*; import java.util.*; public cla...
  • u014107093
  • u014107093
  • 2014-05-14 12:27:28
  • 3620

JDBC连接Sqlserver

首先配置好环境这里直接给出链接java通过JDBC链接SQLServer2012 接着直接上代码 import java.sql.Connection; import java.sql.SQLEx...
  • ww2041
  • ww2041
  • 2016-07-16 08:41:35
  • 207
收藏助手
不良信息举报
您举报文章:解决JDBC 连接 SQL Azure 超时问题
举报原因:
原因补充:

(最多只允许输入30个字)