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
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
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:

JDBC Connection with SQL Server Port Number
JDBC Connection with SQL Server Port Number

Obviously, using the instance number is a much better than using the port number, because the instance name is less likely to be changed over time.

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

 Microsoft SQL Server Express Edition

Microsoft JDBC Driver for SQL Server

 Installing Microsoft JDBC Driver for SQL Server

 Loading Driver Class Automatically

 Loading Driver Class with Class.forName()

 DriverManager.getConnection() and Connection URL

 Enable TCP/IP with SQL Server Configuration Manager

 Specifying Port Number in Connection URL

Instance Name Better than Port Number

 Specifying Instance Name in Connection URL

 Closing the Database Connection - con.close()

 Specifying Database Name in Connection URL

 Incorrect Database Name in Connection URL

 Creating Connections with DataSource Class

 Microsoft JDBC Driver - Query Statements and Result Sets

 Microsoft JDBC Driver - DatabaseMetaData Object

 Microsoft JDBC Driver - DDL Statements

 Microsoft JDBC Driver - DML Statements

 SQL Server - PreparedStatement

 SQL Server CLOB (Character Large Object) - TEXT

 SQL Server BLOB (Binary Large Object) - BLOB

 JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver

 JDBC-ODBC Bridge Driver - Flat Text Files

 JDBC-ODBC Bridge Driver - MS Access

 JDBC-ODBC Bridge Driver - MS SQL Server

 Archived Tutorials

 References

 Full Version in PDF/EPUB