Step by step process to connect jBPM-6.2.0 to Microsoft SQLSERVER2008R2

Version 1

    Hi All,

     

    I wanted to connect all my processes to our MS SqlServer2008 R2. I've tried and consolidate the whole process here. Hopefully it helps!!

     

    Step 1:

    In your jbpm-installer\build.properties

     

    comment out the H2 section :

    # default is H2

    #H2.version=1.3.168

    #db.name=h2

    #db.driver.jar.name=h2-${H2.version}.jar

    #db.driver.download.url=http://repo1.maven.org/maven2/com/h2database/h2/${H2.version}/h2-${H2.version}.jar

     

    and add

    #MSsqlserver

    db.name=sqlserver2008

    db.driver.module.prefix=com/sqlserver2008

    db.driver.jar.name=sqljdbc4-2.0.jar

     

    Step 2:

    In your jbpm-installer\standalone-full-wildfly-8.1.0.Final.xml

    comment out the default <datasources>... </datasources> and add this:


    <datasources>

    <datasource jta="false" jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-ccm="false">

                        <connection-url>jdbc:sqlserver://yourServerName:yourDBPortNo;DatabaseName=yourDbName</connection-url>

                        <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

                        <driver>sqlserver2008</driver>

                        <security>

                            <user-name>yourUserName</user-name>

                            <password>yourPassword</password>

                        </security>

                        <validation>

                            <validate-on-match>false</validate-on-match>

                            <background-validation>false</background-validation>

                        </validation>

                        <statement>

                            <share-prepared-statements>false</share-prepared-statements>

                        </statement>

                    </datasource>

                    <drivers>

                        <driver name="sqlserver2008" module="com.sqlserver2008">

                            <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>

                        </driver>

                    </drivers>

    </datasources>

     

    Step 3:

    In your jbpm-installer\standalone-wildfly-8.1.0.Final.xml

    comment out the default <datasources>... </datasources> and add this:


    <datasources>

    <datasource jta="false" jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-ccm="false">

                        <connection-url>jdbc:sqlserver://yourServerName:yourDBPortNo;DatabaseName=yourDbName</connection-url>

                        <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

                        <driver>sqlserver2008</driver>

                        <security>

                            <user-name>yourUserName</user-name>

                            <password>yourPassword</password>

                        </security>

                        <validation>

                            <validate-on-match>false</validate-on-match>

                            <background-validation>false</background-validation>

                        </validation>

                        <statement>

                            <share-prepared-statements>false</share-prepared-statements>

                        </statement>

                    </datasource>

                    <drivers>

                        <driver name="sqlserver2008" module="com.sqlserver2008">

                            <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>

                        </driver>

                    </drivers>

    </datasources>


    Step 4:

    In your \jbpm-installer\db\jbpm-persistence-JPA2.xml

    Comment out the h2dilect property and add this:


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


    Also, Comment out default <jta-data-source>..</jta-data-source> and add this:


    <jta-data-source>java:jboss/datasources/jBPMDS_MSSQL</jta-data-source>


    Step 5:

    Create sqlServer2008_module.xml file here: \jbpm-installer\db

    Add this:

    <module xmlns="urn:jboss:module:1.0" name="com.sqlserver2008">

      <resources>

        <resource-root path="sqljdbc4-2.0.jar"/>

      </resources>

    <dependencies>

          <module name="javax.api"/>

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

        </dependencies>

    </module>


    Make sure you have sqljdbc4-2.0.jar file here  :  \jbpm-installer\db\driver


    Now stop, clean and reinstall your demo using this commands in cmd:

    ant stop.demo

    ant clean.demo

    ant install.demo

     

    After everything is set, go to : http://localhost:9990/console and click on datasources adn make sure you have ExampleDS there, if not configure it witht he same credentials as in standalone files:

    Name:ExampleDS

    JNDI:java:jboss/datasources/ExampleDS

    Is enabled?:true

    Datasource Class:

    Driver:sqlserver2008

    Driver Class:com.microsoft.sqlserver.jdbc.SQLServerDriver

    Share Prepared Statements:false

    Statement Cache Size:0

    Connection URL:jdbc:sqlserver://yourServerName:yourDBPortNo;DatabaseName=yourDbName

    New Connection Sql:

    Transaction Isolation:

    Use JTA?:false

    Use CCM?:false

    Username:yourUserName

    Password:yourPassword

     

    After this step, just test your db connection by clicking "Test Connection" on Connections tab.

    Once successful, craete a process on web and build & deploy. You should see some logs on your db server using these statements:

    select * from ProcessInstanceLog

    select * from SessionInfo

    select * from NodeInstanceLog

     

    Hope it works for you guys!!

    Cheers