Configuring a SQL Server data source within JBoss 7.1

Configuring a SQL Server data source is a two step process if performed on a new installation.

  1. Configure the SQL Server module
  2. Configure the data source pool

To create the SQL Server module, create the following folder structure under the modules directory.

<JBOSS INSTALLATION>\modules\com\microsoft\sqlserver\main

Copy the Microsoft SQL Server JDBC jar into the main folder.

Create a module.xml file in the main folder and copy the following.

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.1" name="com.microsoft.sqlserver">
 <resources>
  <resource-root path="sqljdbc4-3.0.jar"/>
 </resources>
 <dependencies>
  <module name="javax.api"/>
  <module name="javax.transaction.api"/>
 </dependencies>
</module>

Next step is to configure the data source within the JBoss configuration of the system. Locate the data source subsystem

<subsystem xmlns="urn:jboss:domain:datasources:1.0">
 <datasources>
  ...

And copy the following

<datasource
        jndi-name="java:/yellowfire/ds"
        pool-name="yellowfire.pool"
        enabled="true"
        jta="true"
        use-ccm="false">
 <connection-url>jdbc:sqlserver://localhost\SQLSERVER2008R2:0;DatabaseName=xxx</connection-url>
 <driver>sqlserver</driver>
 <security>
   <user-name>user</user-name>
   <password>pwd</password>
 </security>
 <validation>
   <validate-on-match>false</validate-on-match>
   <background-validation>false</background-validation>
   <background-validation-millis>0</background-validation-millis>
 </validation>
 <statement>
   <prepared-statement-cache-size>0</prepared-statement-cache-size>
   <share-prepared-statements>false</share-prepared-statements>
 </statement>
 </datasource>

Now the you’ll notice that in the <driver> node the value sqlserver, this is because this is not the actual driver class name but rather a link to a driver definition. The driver definition will make use of the module that was created earlier to actually load the database driver.  Copy the following driver definition to below or before the definition of the h2database database driver definition.

<driver name="sqlserver" module="com.microsoft.sqlserver">
 <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
</driver>

The zip containing the SQL Server module can be downloaded here.

About these ads

Tags: ,

9 responses to “Configuring a SQL Server data source within JBoss 7.1”

  1. Broderick Salowitz says :

    Its wonderful as your other articles : D, thankyou for posting .

  2. vender ouro says :

    Aw, this was a really nice post. In concept I wish to put in writing like this moreover – taking time and actual effort to make an excellent article… however what can I say… I procrastinate alot and certainly not appear to get one thing done.

  3. Rohan Emmanuel says :

    how to create data source without creating a module?

    • mpashworth says :

      Good day Rohan,

      From the JBoss documentation and configuration XML it seemed that creating a module for 3rd party databases JDBC drivers was the approach when integrating with the JBoss config.

      It depends on what you are trying to accomplish, which unfortunately you have not specified.

      For example EclipseLink can resolve the SQL Server JDBC driver that is in the persistence.xml but the JDBC driver must be bundled in the WAR file and you loose the configuration options provided by the JBoss admin console.

      • Rohan Emmanuel says :

        hi there,

        i am trying to create a datasource using an -ds.xml . main motive is not to restart the server or modify the config files of jboss when i deploy any bean which uses the datasource.
        how will u package the driver jar within your application jar/ear?

  4. mpashworth says :

    Hi Rohan,

    To be honest, I am not sure about the -ds.xml files, maybe this will help https://community.jboss.org/en/tools/blog/2012/02/28/excited-about-jboss-as-71-part-i-deployable-datasources

    To answer your second question there is an WAR example of how to connect to SQL Server from JPA/EclipseLink available at http://code.google.com/p/yellowfire/downloads/list.

    NOTE: The persistence.xml and Maven pom is setup for Jetty but the same concept applies to JBoss / Glassfish / Weblogic.

    Also note that the persistence.xml is using the RESOURCE_LOCAL transaction type if a JTA transaction type is desired then a container data source needs to be configured.

  5. Jordan says :

    Next step is to configure the data source within the JBoss configuration of the system. Locate the data source subsystem

    Please explain this in much more detail. Where is this folder located? Lost me at this point.

    • Mark P Ashworth says :

      @Jordan

      When I configured the datasource it was in the standalone.xml file in the configurations directory of JBoss 7.1 because the standalone server instance was the one I was using. Of course if you are using the standalone-ha.xml or standalone-full.xml configurations then you’ll make the changes in those files. What I meant by locate the datasource section is that there is a subsystem node

      <subsystem xmlns=”urn:jboss:domain:datasources:1.0″>

      with a sample H2 database data source configuration to help with the configuration of the SQL Server datasource.

Leave a Reply

Fill in your details below or click an icon to log in:

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

Java Magic

Blog about Tapestry5, Plastic and related technologies

Steps & Leaps

Thoughts and Comments on (Mainly) Creativity, Innovation and Management

Facilitating Agility

Scrum and Agile Facilitation

Jan's Blog

Development and technology stuff I haven't easily found

WatirMelon

A 93% Software Testing Blog by Alister Scott

Dan Haywood

domain driven design, restful objects, apache isis, the naked objects pattern, agile and more

Marko A. Rodriguez

Supporting the Emerging Graph Landscape

A developer's journal

On Oracle, JEE, SOA and whatmore

RedStack

Musings on Integration with Oracle Fusion Middleware

oracle-stack-support

Oracle Stack Support (One Window Support)

Struberg's Blog

Yet another blog site?

Exit Condition

Andrew Lee Rubinger

Antonio's Blog

A blog mainly about Java

Sematext Blog

Monitoring, Log Management, Search, Big Data Analytics

WordPress.com News

The latest news on WordPress.com and the WordPress community.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: