I have been using JBPM5.3 for some months and then I switch to JBPM5.4.

After I download JBPM5.4.Final full installer, I thought the DB Set up will be exactly the same with JBPM5.3 full installer https://community.jboss.org/wiki/SetUpJBPM53ToUseMSSQLServer2008

But I realized that some things has changed and at the time when I write this, the guide for changing Database in JBPM5.4 full installer is still outdated http://docs.jboss.org/jbpm/v5.4/userguide/ch.installer.html#d0e609

A. What I Am Going To Do

     1. I will change the Database setting of jbpm5.4 final full installer and point it to use MS SQL Server 2008

     2. The Database driver used is jtds-1.2.4

     3. Although it is possible to have separate database for Task and Process data, in this setup I will use only one Database for all the Task database tables and all the Process database tables.

     4. I do this set up using Windows XP 64 Bit

     5. apache ant version used is Apache Ant 1.8.4

     6. jdk used is jdk 1.6 (set in Windows Environment variable as JAVA_HOME)

     7. I use 7-zip to help me modify files inside a .war file

B. The Setup Steps

1. JBPM5.4 final full installer comes with almost every needed component, except eclipse (optional). But I want the eclipse, so I download eclipse-java-helios-SR2-win32-x86_64.zip and put it into

     jbpm-installer\lib folder

2. Because my OS is 64 bit version, I have to do a modification to the build.xml file found in jbpm-installer folder. So I changed this part

<!-- Download Eclipse -->
  <condition property="download.type" value="win32">
    <os family="windows" />
  </condition> 

to

<!-- Download Eclipse -->
  <condition property="download.type" value="win32-x86_64">
    <os family="windows" />
  </condition>

3. Now its time to set up the Hibernate settings, mostly we will make changes to the hibernate dialect, lets open jbpm-installer\db folder.

4. I am not sure how each file is used here in jbpm5.4 since the official guide still reflect jbpm5.3 files, so here is what I do, I start with opening jbpm-installer\db\jbpm-persistence-JPA1.xml

I changed:

<properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>
      <property name="hibernate.max_fetch_depth" value="3"/>

to

<properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>
      <property name="hibernate.max_fetch_depth" value="3"/>

5. Then I open jbpm-installer\db\jbpm-persistence-JPA2.xml

I changed:

 <properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />
      <property name="hibernate.max_fetch_depth" value="3" />

to

 <properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect" />
      <property name="hibernate.max_fetch_depth" value="3" />

6. Lets continue, and open jbpm-installer\db\task-persistence-JPA1.xml

I changed:

<properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>
      <property name="hibernate.connection.driver_class" value="org.h2.Driver"/>
      <property name="hibernate.connection.url" value="jdbc:h2:tcp://localhost/runtime/task" />
      <property name="hibernate.connection.username" value="sa"/>
      <property name="hibernate.connection.password" value=""/>

to

<properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>
      <property name="hibernate.connection.driver_class" value="net.sourceforge.jtds.jdbc.Driver"/>
      <property name="hibernate.connection.url" value="jdbc:jtds:sqlserver://DBName:1433/MyJBPMDB" />
      <property name="hibernate.connection.username" value="myuser"/>
      <property name="hibernate.connection.password" value="mypassword"/>

7. Open jbpm-installer\db\task-persistence-JPA2.xml

I changed:

 <properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>          
      <property name="hibernate.max_fetch_depth" value="3"/>

to

 <properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>          
      <property name="hibernate.max_fetch_depth" value="3"/>

8. Lets navigate to other file in other folder, lets open jbpm-installer\task-service\resources\META-INF\persistence.xml

I changed:

<properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
      <property name="hibernate.connection.driver_class" value="org.h2.Driver"/>
      <property name="hibernate.connection.url" value="jdbc:h2:tcp://localhost/runtime/task" />
      <property name="hibernate.connection.username" value="sa"/>
      <property name="hibernate.connection.password" value=""/>

to

<properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>
      <property name="hibernate.connection.driver_class" value="net.sourceforge.jtds.jdbc.Driver"/>
      <property name="hibernate.connection.url" value="jdbc:jtds:sqlserver://DBName:1433/MyJBPMDB" />
      <property name="hibernate.connection.username" value="myuser"/>
      <property name="hibernate.connection.password" value="mypassword"/>

9. Ok, lets dig into .war files in the lib folder, (I use 7-zip to help me modify files inside .war files), lets open jbpm-installer\lib folder

10. Open jbpm-installer\lib\jbpm-gwt-console-server-5.4.0.Final-EE6.war\WEB-INF\classes\META-INF\persistence.xml

I changed:

<properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" />
      <property name="hibernate.max_fetch_depth" value="3" />

to

<properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect" />
      <property name="hibernate.max_fetch_depth" value="3" />

11. Next, Open jbpm-installer\lib\jbpm-human-task-war-5.4.0.Final-EE6.war\WEB-INF\classes\META-INF\persistence.xml

I changed:

<properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>          
      <property name="hibernate.max_fetch_depth" value="3"/>

to

<properties>
      <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>          
      <property name="hibernate.max_fetch_depth" value="3"/>

12. Allright, seems like we are done changing dialects, lets provide our jtds jar. put jtds-1.2.4.jar into jbpm-installer\db\driver folder

13. next, we need to provide an xml file that will be used by jboss AS to recognize our jtds later, so Create a file called sqlserver_module.xml and put it in jbpm-installer\db folder

The file content is:

<module xmlns="urn:jboss:module:1.0" name="net.sourceforge.jtds">
   <resources>
     <resource-root path="jtds-1.2.4.jar"/>
   </resources>

   <dependencies>
      <module name="javax.api"/>
      <module name="javax.transaction.api"/>
    </dependencies>
</module>

14. Wondered how the ant build will recognize our sqlserver_module.xml later? Happens to me too at first, lets open jbpm-installer\build.properties file

Look at this section:

# data base related properties
db.driver.jar=${install.home}/db/driver/${db.driver.jar.name}
db.driver.module.dir=${jboss.home}/modules/${db.driver.module.prefix}/main/
# default is H2
db.name=h2
db.driver.jar.name=${db.name}.jar
db.driver.download.url=http://repo1.maven.org/maven2/com/h2database/h2/${H2.version}/h2-${H2.version}.jar
#other options are mysql
#  db.name=mysql
#  db.driver.module.prefix=com/mysql
#  db.driver.jar.name=${db.name}-connector-java.jar
#  db.driver.download.url=https://repository.jboss.org/nexus/service/local/repositories/central/content/mysql/mysql-connector-java/5.1.18/mysql-connector-java-5.1.18.jar
#postresql
#  db.name=postresql
#  db.driver.module.prefix=org/postgresql
#  db.driver.jar.name=${db.name}-jdbc.jar
#  db.driver.download.url=https://repository.jboss.org/nexus/content/repositories/thirdparty-uploads/postgresql/postgresql/9.1-902.jdbc4/postgresql-9.1-902.jdbc4.jar

lets comment out the h2 setting and make our own sqlserver setting like this:

# data base related properties
db.driver.jar=${install.home}/db/driver/${db.driver.jar.name}
db.driver.module.dir=${jboss.home}/modules/${db.driver.module.prefix}/main/
# default is H2
#db.name=h2
#db.driver.jar.name=${db.name}.jar
#db.driver.download.url=http://repo1.maven.org/maven2/com/h2database/h2/${H2.version}/h2-${H2.version}.jar
#other options are mysql
#  db.name=mysql
#  db.driver.module.prefix=com/mysql
#  db.driver.jar.name=${db.name}-connector-java.jar
#  db.driver.download.url=https://repository.jboss.org/nexus/service/local/repositories/central/content/mysql/mysql-connector-java/5.1.18/mysql-connector-java-5.1.18.jar
#postresql
#  db.name=postresql
#  db.driver.module.prefix=org/postgresql
#  db.driver.jar.name=${db.name}-jdbc.jar
#  db.driver.download.url=https://repository.jboss.org/nexus/content/repositories/thirdparty-uploads/postgresql/postgresql/9.1-902.jdbc4/postgresql-9.1-902.jdbc4.jar
#sqlserver
db.name=sqlserver
db.driver.module.prefix=net/sourceforge/jtds
db.driver.jar.name=jtds-1.2.4.jar

because or db.name is sqlserver, our sqlserver_module.xml will be recognized by the ant build later on.

If you really want to know the reason, open jbpm-installer\build.xml and look at this section

    <copy file="db/${db.name}_module.xml" tofile="${db.driver.module.dir}/module.xml" />

15. ok, now lets deal with the jboss AS 7 standalone setting, open jbpm-installer\standalone.xml

we need to do two things here, modify data source and make jboss as recognize our jtds driver, so change this section:

<datasource jta="true" jndi-name="java:jboss/datasources/jbpmDS" pool-name="H2DS" enabled="true" use-java-context="true" use-ccm="true">
                    <connection-url>jdbc:h2:~/jbpm</connection-url>
                    <driver>h2</driver>
                    <pool>
                        <min-pool-size>1</min-pool-size>
                        <max-pool-size>4</max-pool-size>
                        <prefill>false</prefill>
                        <use-strict-min>false</use-strict-min>
                        <flush-strategy>FailingConnectionOnly</flush-strategy>
                    </pool>
                    <security>
                        <user-name>sa</user-name>
                    </security>
                    <validation>
                        <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                        <validate-on-match>false</validate-on-match>
                        <background-validation>false</background-validation>
                    </validation>
                </datasource>
                <drivers>
                    <driver name="h2" module="com.h2database.h2">
                        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                    </driver>
                </drivers>

to

               <datasource jndi-name="java:jboss/datasources/jbpmDS" pool-name="H2DS" enabled="true" jta="true" use-java-context="true" use-ccm="true">
                    <connection-url>jdbc:jtds:sqlserver://DBName:1433/MyJBPMDB</connection-url>
                    <driver>JTDS</driver>
                    <pool>
                        <min-pool-size>1</min-pool-size>
                        <max-pool-size>4</max-pool-size>
                        <prefill>false</prefill>
                        <use-strict-min>false</use-strict-min>
                        <flush-strategy>FailingConnectionOnly</flush-strategy>
                    </pool>
                    <security>
                       <user-name>myuser</user-name>
                       <password>mypassword</password>
                    </security>
                    <validation>
                       <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
                       <validate-on-match>false</validate-on-match>
                       <background-validation>false</background-validation>
                    </validation>
                </datasource>
                <drivers>
                   <driver name="JTDS" module="net.sourceforge.jtds">
                            <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>  
                    </driver>
                                         <driver name="h2" module="com.h2database.h2">
                                                 <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                                        </driver>
                </drivers>

16. Phew, quite a lot files that we have configured. at this point you may want to copy and backup the whole jbpm-installer folder, just in case you missed some configuration and caused error in the installation process later, who knows, things like that can happen, so just to be safe...