Rob Blackwell home projects resumé archive

Java JDBC to SQLAzure Connection Drop Workaround

02 December 2010

UPDATE: The Microsoft JDBC Driver 4.0 for SQL Server is now available in CTP. The information below is now officially documented on MSDN.

A while ago, we encountered some problems with using Java JDBC to access SQL Azure. In a nutshell, connections were getting forcibly closed after about one minute, preventing any kind of sensible connection pooling strategy and hurting performance. The problem seemed to be caused because TCP keepalive messages were not being sent.

Anyway, there is now a workaround.

For on premises services, you need to change the registry and then reboot for the changes to take effect. You can use regedit or simply run this BAT file:

REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v KeepAliveTime /t REG_DWORD /d 30000
REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v KeepAliveInterval /t REG_DWORD /d 1000
REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v TcpMaxDataRetransmission /t REG_DWORD /d 10

With a little messing around, you can apply the same fix to a Windows Azure instances too, but you need the recently released Windows Azure SDK 1.3 with its elevated startup task to be able to change the registry.

Add a StartUp task to your service definition file, something like this:

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

Then add a Startup.cmd file to your Web or Worker Role project. Make sure that the "Copy to Output Directory" setting is set to Copy always.

The Startup.cmd should look something like this.

if exist startup.txt goto skip
time /t >> startup.txt
REM Workaround for JDBC to SQL Azure
REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v KeepAliveTime /t REG_DWORD /d 30000 >> startup.txt
REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v KeepAliveInterval /t REG_DWORD /d 1000 >> startup.txt
REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v TcpMaxDataRetransmission /t REG_DWORD /d 10 >> startup.txt
shutdown /r /t 1
:skip

The trick here is the reboot - The first time through this script, the startup.txt file won't exist, so the registry update block runs followed by the shutdown command. On subsequent invocations, the startup.txt file exists, so the code block isn’t run, preventing further reboots.

*The opinions expressed on this site are my own and do not necessarily represent those of Two10degrees Ltd.