Switch JBossESB-4.5.GA from HSQL to MSSQL

To switch JBossESB from the default HSQL embedded database to a "real" database like MSSQL, the persistence configuration of JMS, jUDDI, ESB MessageStore and jBPM needs updating.

1. JMS
rm server/default/deploy/jms/hsqldb-jdbc2-service.xml
cp docs/examples/jms/mssql-jdbc2-service.xml server/default/deploy/jms
cp docs/examples/jca/mssql-ds.xml server/default/deploy/jms
mv server/default/deploy/jms/hsql-jdbc-state-service.xml server/default/deploy/jms/mssql-jdbc-state-service.xml

update the mssql-jdbc-state-service.xml to point to the MSSQLDS datasource

<depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=MSSQLDS</depends>

update the "Security domain for JBossMQ" in the server/default/conf/login-config.xml to java:/MSSQLDS

2. jUDDI
cp docs/examples/jca/mssql-ds.xml server/default/deploy/jbossesb.sar/juddi-ds.xml
update the juddi-ds.xml, make sure the <jndi-name>juddiDB</jndi-name>
update the server/default/deploy/jbossesb.sar/esb.juddi.xml to make sure the mssql sql is used to create the jUDDI tables

<entry key="juddi.sqlFiles">juddi-sql/mssql/create_database.sql,juddi-sql/mssql/import.sql</entry>

3. JBossESB MessageStore
rm server/default/deploy/jbossesb.esb/hsql-ds.xml
cp docs/examples/jca/mssql-ds.xml server/default/deploy/jbossesb.esb
update the mssql-ds.xml, make sure the <jndi-name>JBossESBDS</jndi-name>
update the server/default/deploy/jbossesb.esb/jbossesb-service.xml to point to the mssql sql for creating the message table

<attribute name="SqlFiles">
     message-store-sql/mssql/create_database.sql
</attribute>

4. jBPM
rm server/default/deploy/jbpm.esb/hsql-ds.xml
cp docs/examples/jca/mssql-ds.xml server/default/deploy/jbpm.esb/
update the mssql-ds.xml, make sure the <jndi-name>JbpmDS</jndi-name>
update the server/default/deploy/jbpm.esb/hibernate.cfg.xml to make sure the SQLServerDialect is used

<!-- hibernate dialect -->
<property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>

update the server/default/deploy/jbpm.esb/jbpm-service.xml so it points to the mssql sql file

<attribute name="SqlFiles">
     jbpm-sql/jbpm.jpdl.mssql.sql,
     jbpm-sql/import.sql
</attribute>

Not that the import.sql tries to set the primary key, which is an autoincrement column. MSSQL does not like this. Simply setting this column from this script will get you going.
5. Finishing up
To Finish up, make sure you add the mssql database driver jar to the server/default/lib directory, and make sure you added your credentials to the various mssql-ds.xml files.

<datasources>
 <local-tx-datasource>
   <jndi-name>JbpmDS</jndi-name>
<connection-url>jdbc:jtds:sqlserver://192.168.1.2:1433/jbossesb</connection-url>
   <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
   <user-name>jbossesb_admin</user-name>
   <password>admin</password>
     <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
     <metadata>
        <type-mapping>MS SQLSERVER2000</type-mapping>
     </metadata>
 </local-tx-datasource>

Make sure this user has table create rights, so that when you start up JBossESB it can create the tables.

MSSQL Drop all constraints from the database

Recently I had to drop all tables from a MSSQL Server Database, and this proved to be harder than expected because of foreign key contraints in the database. I found a post on how do disable them but that turned out to be not enough to allow to drop the tables. Instead I had to drop all the constraints from the database. The few lines below generate the commands to do this.

select
'ALTER TABLE ' + so.table_name + ' DROP CONSTRAINT ' + so.constraint_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS so