Java – JDBC – Microsoft Drivers – Named Instance/ “Un-Default” Port Issues

Perplexingly, there are various issues one may run into while attempting to connect to a MS SQL Server Instance, especially the partner node of a mirrored-database.

This problem seems to be more common when MS SQL Server is running on something other than its default port 1433.

The problem is also well chronicled

  1. SQL 2005 JDBC Driver and Database Mirroring
    http://blogs.msdn.com/psssql/archive/2008/12/31/sql-2005-jdbc-driver-and-database-mirroring.aspx
  2. Running SQL Server ‘Default’ instance on a non-default (or non-standard) TCP port: : tips for making application connectivity work
    http://blogs.msdn.com/dataaccesstechnologies/archive/2010/03/03/running-sql-server-default-instance-on-a-non-default-or-non-standard-tcp-port-tips-for-making-application-connectivity-work.aspx
  3. The Failover Partner connection string attribute cannot connect to a mirror partner server by using the SQL Native Client OLE DB provider and the SQL Native Client ODBC driver in a .NET Framework-based application (http://support.microsoft.com/kb/941526)

 

Sample JDBC URL

Assumptions:

a) MS SQL Server Name is EPH
b) MS SQL Server Failover Partner is EphMirr
c) Database Name is intra

a)
DB_CONN_STRING=jdbc:sqlserver://Eph:2060;databaseName=intra;failoverPartner=EphMirr;
( works well)

b)DB_CONN_STRING=jdbc:sqlserver://Eph:2060;databaseName=intra;failoverPartner=EphMirr\\MSSQL2008;
( works well)

c)
DB_CONNECTION_STRING=jdbc:sqlserver://BETHEL:2060;databaseName=intra;failoverPartner=HOLYSPIRIT,2060;
( does not work)

d)DB_CONNECTION_STRING=jdbc:sqlserver://BETHEL:2060;databaseName=intra;failoverPartner=DBMIRR\\MSSQL2008
(
works — if DBMirr is resolvable thru DNS or
on MS Windows c:\windows\system32\drivers\etc\hosts

note that is is not enough to have used cliconfg.exe to create an alias
)

Issues:

The big problem seems to revolve around the fact that MS SQL Server does not properly resolve the port number for the partner node

Review of our various connection strings:

a) DB_CONN_STRING=jdbc:sqlserver://Eph:2060;databaseName=intra;failoverPartner=EphMirr;

LIFE IS GOOD

  • Since the partner is listening on MS SQL Server default port of 1433, no need to specify or indicate otherwise

b)

DB_CONN_STRING=jdbc:sqlserver://Eph:2060;databaseName=intra;failoverPartner=EphMirr\\MSSQL2008;

LIFE IS GOOD

  • It appears that the JDBC Driver (running on the client) correctly attempts to  engage the “SQL Server Browser” service on the database server – Note that this service, by default is listening on UDP Port 1434, and so Server\ DBA\Firewall Admins should ensure that this service is set to running and service mode set to “Automatic”
  • Also note that we have “escaped” the \ (the separator between the server and alias name) and have it as \\ (two backslashes)

c)

DB_CONNECTION_STRING=jdbc:sqlserver://BETHEL:2060;databaseName=intra;failoverPartner=HOLYSPIRIT,2060;


LIFE IS NOT SO GOOD

d) If one were try to create a SQL Client Alias

DB_CONNECTION_STRING=jdbc:sqlserver://BETHEL:2060;databaseName=intra;failoverPartner=DBMIRR\\MSSQL2008

  • Note that creating a SQL Client alias is not useful — Especially, as SQL Client Alias are MS OS Specifics, and does not translate to Unix
  • Also any alias created will have to resolvable at the Network\OS level (DNS or c:\windows\system32\drivers\etc\hosts)
  • Also, note that if MS SQL Server is not listening on its default port or its a named instance then make sure that “SQL Server Browser” is running on the server

One thought on “Java – JDBC – Microsoft Drivers – Named Instance/ “Un-Default” Port Issues

  1. Thanks for some other magnificent article. The place else
    could anyone get that kind of information in such
    a perfect method of writing? I’ve a presentation subsequent week, and I’m at the search for such info.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s