Search code examples
javasql-serverjdbctcpmssql-jdbc

JDBC Connection to SQL server with Dynamic TCP Ports

My sql server engine is using dynamic TCP port, e.g., 52358. I can do JDBC query without any problem if I specified the port number 52358 in my JDBC DB url connection string, i.e.,

jdbc:sqlserver://serverName:52358;databaseName=myDB

However, since this is dynamic port, sql server might choose another port if this port (52358) becomes unavailable after you restart sql server next time.

So what's best way to configure my DB URL in the connection string?

Approach I have tried:

Omitting the port number and using only the instance name, i.e.,

jdbc:sqlserver://serverName;databaseName=myDB

This doesn't work. By the way, sql server browser service is already enabled.

Solution found (thanks to Gord Thompson). The complete form of DB URL is: 

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

where either the portNumber or instanceName may be used to connect to serverName. However,

If both a portNumber and instanceName are used, the portNumber will take precedence and the instanceName will be ignored.

Therefore, for the case of dynamic port, we should only use instanceName for connection and keep the SQL Browser service running (SQL Server provides the SQL Server Browser Service, to monitor the ports, and direct incoming connections to the current port for that instance). Thus, in my case, the correct form is: 

jdbc:sqlserver://serverName\instanceName;databaseName=myDB

Also, we should keep in mind that

For optimal connection performance, you should set the portNumber when you connect to a named instance. This will avoid a round trip to the server to determine the port number

Then this will call for a static port number.

Solution

  • If you really cannot count on the port number remaining constant then you will have to connect using the SQL Server instance name.

    Default instance:

    jdbc:sqlserver://servername\MSSQLSERVER;...

    Named instance

    jdbc:sqlserver://servername\instancename;...

    Of course, this means that the SQL Browser service must be running on the server so the instance name can be resolved to the actual port number currently being used.