Instance Name Better than Port Number

JDBC for SQL Server - Herong's Tutorial Examples

∟Instance Name Better than Port Number

This section describes how SQL Server support JDBC client applications to use instance name instead of port number to connect to a specific instance.

In the last tutorial, I have resolved the connection issue to my SQLEXPRESS SQL Server instance by turning on TCP/IP support and looking up the port number in the ERRORLOG file of the instance.

But I am not very happy with the solution. Because if the SQL Server decides to a different port number for SQLEXPRESS at next startup time, my application that uses the old port number, 60782, will fail again.

A better solution is to run the SQL Server Browser service together with the SQL Server instance services. This allows client applications to request a connection with the instance name, which will be mapped to the dynamic port number used by the requested instance.

Earlier versions of SQL Server only supports a single database instance on a single host machine. JDBC client applications can connect directly to the database instance at the default port 1433 or a different port the instance is listening at, see the picture below:

JDBC Connection to Single-Instance SQL Server

Newer versions of SQL Server can support multiple database instance on a single host machine. Each database instance can configured to listen to a different dynamically selected port. This will give JDBC client application a hard time to keep track of which port is used by which instance. So Microsoft introduced the SQL Server Browser service to help by offering a port number look up service with UDP protocol on port 1434.

If you specify an instance name in the connection request, the JDBC Driver is smart to make a UDP call first to the SQL Server Browser with the instance name. The SQL Server Browser will return the port number of the given instance. Then the JDBC Driver will a TCP connection on that port number. see the picture below:

JDBC Connection with SQL Server Instance Name

But if you know the port number used by a specific database instance, you can still connect directly to that database instance by specifying the port number in the connection request, see the picture below: