For example, Beginning in version 4.0 of the Microsoft JDBC Driver for SQL Server, it's no longer necessary to append to the UserId in the connection string. Prior to the 4.0 version of the Microsoft JDBC Driver for SQL Server, when connecting to an Azure SQL Database, you were required to append the server name to the UserId in the connection string. REG ADD HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v TcpMaxDataRetransmissions /t REG_DWORD /d 10 > keepalive.txtĪppending the server name to the userId in the connection string 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 KeepAliveTime /t REG_DWORD /d 30000 > keepalive.txt REM Workaround for JDBC keep alive on Azure SQL The following script is a sample AddKeepAlive.cmd file: if exist keepalive.txt goto done Set the "Copy to Output Directory" setting to Copy always. Then add a AddKeepAlive.cmd file to your project. For example, add the following Startup task to the service definition file: To perform this configuration when running in Azure, create a startup task to add the registry keys.
TcpMaxDataRetransmissions is not controllable on Windows Vista or Windows 2008 and higher. Depending on your environment, you may want to increase the KeepAliveInterval to accommodate known disruptions (like virtual machine migrations) that might cause a server to be unresponsive for longer than 10 seconds. If no response is received during that time, the client-side socket is disconnected. After a keep alive packet is sent, if no response is received, it will be retried every second up to 10 times. These settings will have the effect of disconnecting an unresponsive connection within 10 to 40 seconds. The KeepAliveTime and KeepAliveInterval values are in milliseconds. Restart the computer for the registry settings to take effect. HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services \ Tcpip \ Parameters \ TcpMaxDataRetransmissions HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services \ Tcpip \ Parameters \ KeepAliveInterval HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services \ Tcpip \ Parameters \ KeepAliveTime To address the first point and avoid dropping idle connections by a network component, the following registry settings (or their non-Windows equivalents) should be set on the operating system where the driver is loaded: Registry Setting This method is not recommended and should only be used if there are no other possible options. Keep connections active via lightweight activity. Use the Redirect connection policy when configuring your Azure SQL data source. To address the second point and avoid the Gateway terminating idle connections, you can: In this scenario, the Gateway will determine that the TDS connection is idle at 30 minutes and terminate the connection. Idle by the Azure SQL Gateway, where TCP keepalive messages might be occurring (making the connection not idle from a TCP perspective), but not had an active query in 30 minutes. Idle at the TCP layer, where connections can be dropped by any number of network devices. There are two types of idle connections, in this context: When connecting to an Azure SQL Database, idle connections may be terminated by a network component (such as a firewall) after a period of inactivity. Refer to the discussion of permissions in sys.databases (Transact-SQL) to understand SQLServerDatabaseMetaData.getCatalogs behavior on an Azure SQL Database. SQLServerDatabaseMetaData.getCatalogs use the sys.databases view to get the catalogs. When connecting to an Azure SQL Database, you should connect to the master database to call SQLServerDatabaseMetaData.getCatalogs.Īzure SQL Database doesn't support returning the entire set of catalogs from a user database. For more information about connecting to an Azure SQL Database, see:Ĭonnecting using Azure Active Directory Authentication This article discusses issues when using the Microsoft JDBC Driver for SQL Server to connect to an Azure SQL Database.