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. JMSrm 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.xmlupdate 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:/MSSQLDS2. jUDDIcp docs/examples/jca/mssql-ds.xml server/default/deploy/jbossesb.sar/juddi-ds.xmlupdate 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 MessageStorerm server/default/deploy/jbossesb.esb/hsql-ds.xml
cp docs/examples/jca/mssql-ds.xml server/default/deploy/jbossesb.esbupdate 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. jBPMrm 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 upTo 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.