JPA SQLServer Connection Pool using Instance Name

Creating a connection pool in Glassfish is fairly simple, as I have described in my previous post. All you need to do is to specify the URL and the Port number where the database engine is listening for the incoming connections in the JPA configuration file, i.e. sun-resources.xml.

In my previous post I used the port number of the database server instance to connect to the database. Sometimes, specially in the clustered enviournment it is not possible for you to specify the port number and you need to use the instance name to communicate with the server.

Doing that is fairly simple. All you need to do is to replace the portNumber property with the instanceName property in the configuration file. You also need to change the Database Connection URL and replae the portNumber with the instanceName. The resulting configuration will look something like this:

<jdbc-connection-pool name="MSSQL_POOL_TEST" 
                       datasource-classname="com.microsoft.sqlserver.jdbc.SQLServerDataSource" 
                       lazy-connection-enlistment="false" match-connections="false" 
                       max-pool-size="32" pool-resize-quantity="2" res-type="javax.sql.XADataSource" 
                       statement-timeout-in-seconds="-1" steady-pool-size="8"> 
         <property name="serverName" value="localhost" /> 
         <property name="instanceName" value="DBInstance" /> 
         <property name="databaseName" value="TestDB" /> 
         <property name="User" value="sa" /> 
         <property name="Password" value="5QLPWD" /> 
         <property name="URL" 
                      value="jdbc:sqlserver://localhost;instanceName=DBInstance;databaseName=TestDB" /> 
         <property name="driverClass" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" /> 
</jdbc-connection-pool>

Your connection pool is now using the instance name and as a result if the cluster management engine ever need to make any changes to the port numbers, your connection pool will not be effected by that.