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 instroduced the SQL Server Brower 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.

Last update: 2015.

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

 Installing and Running Java DB - Derby

 Derby (Java DB) JDBC Driver

 Derby (Java DB) JDBC DataSource Objects

 Java DB (Derby) - DML Statements

 Java DB (Derby) - ResultSet Objects of Queries

 Java DB (Derby) - PreparedStatement

 MySQL Installation on Windows

 MySQL JDBC Driver (MySQL Connector/J)

 MySQL - PreparedStatement

 MySQL - Reference Implementation of JdbcRowSet

 MySQL - JBDC CallableStatement

 MySQL CLOB (Character Large Object) - TEXT

 MySQL BLOB (Binary Large Object) - BLOB

 Oracle Express Edition Installation on Windows

 Oracle JDBC Drivers

 Oracle - Reference Implementation of JdbcRowSet

 Oracle - PreparedStatement

 Oracle - JBDC CallableStatement

 Oracle CLOB (Character Large Object) - TEXT

 Oracle BLOB (Binary Large Object) - BLOB

 Microsoft SQL Server 2005 Express Edition

Microsoft JDBC Driver for SQL Server - sqljdbc42.jar

 Installing Microsoft JDBC Driver for SQL Server

 Loading Driver Class Automatically

 Loading Driver Class with Class.forName()

 DriverManager.getConnection() and Connection URL

 Enabling 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

 Summary of JDBC Drivers and Database Servers

 Additional Tutorial Notes to Be Added

 Outdated Tutorials

 References

 PDF Printing Version