Installing MDM Workflow with Microsoft SQL Server

This article explains how to install MDM Workflow with Microsoft SQL Server 2012 R2.

Overview

MDM Workflow requires XA transactions enabled with Microsoft SQL Server. However, there is a known issue between Bitronix, the Transaction Manager shipped by Bonitasoft for the Tomcat bundle and inside Deploy bundle for Tomcat, and Microsoft SQL Server driver. For more information, see Understanding XA Transactions.

To solve this problem, you need to install MDM Workflow using the JBoss bundle provided by Talend. Contact Talend Support to receive the JBoss bundle.

Prerequisites

MDM Workflow Server requires MS SQL Server to use UTF8 and case insensitive charset.
JBoss Bundle 7.1.1 will only works with Java JDK 7. Install a JDK 7 and configure the JAVA_HOME for the JBoss Bundle appropriately.
Only Microsoft SQL Server 2012 R2 is supported by Bonitasoft 6.5. For more information, see Hardware and software requirements.

Clearing the MDM Workflow Server installation in the Talend installer

This section explains how to skip the installation of the MDM Workflow Tomcat bundle by the Talend installer

Procedure

Clear the Install MDM Workflow Server check box.
Since you are skipping the MDM Workflow Server installation, clear Install MDM Workflow Server as a service check box.

Configuring Microsoft SQL Server For XA Transactions

This section explains how to install XA Transaction.

MDM Workflow Server (Bonitasoft) uses XA Transactions when working with Microsoft SQL Server. For more information, see Understanding XA Transactions.

Procedure

Download Microsoft SQL Server JDBC Driver 4.0 here. Select the file named sqljdbc_4.0.2206.100_enu.exe
Double-click the file sqljdbc_4.0.2206.100_enu.exe and click Unzip.
Copy sqljdbc_xa.dll from %JDBC_DRIVER_INSTALL_ROOT%\sqljdbc_4.0\enu\xa\x64\ to %SQLSERVER_INSTALL_ROO%\Instance_root\MSSQL11.MSSQLSERVER\MSSQL\Binn\.
In this case, with SQL Server Express, the path is C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Binn
Open SQL Server Management Studio Query Editor and connect to the SQL Server instance.
Open a New Query window.
Copy/paste the content of the xa_install.sql file, located at %JDBC_DRIVER_INSTALL_ROOT%\sqljdbc_4.0\enu\xa, to the New Query Editor, then execute it.
Open Object Explorer and go to: Master > Programmability > Extended Stored Procedures to confirm the successful execution of the script.
You should have twelve new procedures with names starting with dbo.xp_sqljdbc_xa_.

Assign the new role SqlJDBCXAUser to the user who owns the Bonita database by executing the following commands in the SQL editor:

USE master;
GO
CREATE LOGIN bonita WITH PASSWORD = 'secret_password';
GO
CREATE USER bonita FOR LOGIN bonita;
GO
EXEC sp_addrolemember [SqlJDBCXAUser], 'bonita';
GO

In the Windows Start menu select Administrative Tools > Services.
In the Services window, make sure that the Distributed Transaction Coordinator service is set to start automatically. If it has not yet started, start it.
Also make sure that the other services it depends on, namely Remote Procedure Call and Security Accounts Manager are also set to start automatically.
Run the dcomcnfg command or use the Start menu to navigate to Administrative Tools > Component Services.
In the left navigation panel, navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator.
Select and right-click Local DTC and then Properties.
Click the Security tab. Ensure that the checkbox for Enable XA Transactions is checked.
Click Apply then OK.
Stop and restart SQLServer.
Create the bonita_journal database.
Set bonita as owner of the bonita_journal database using the Management Studio.

Run the script below to avoid deadlock in Microsoft SQL Server:

ALTER DATABASE bonita_journal SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
ALTER DATABASE bonita_journal SET ALLOW_SNAPSHOT_ISOLATION ON 
ALTER DATABASE bonita_journal SET READ_COMMITTED_SNAPSHOT ON 
ALTER DATABASE bonita_journal SET MULTI_USER

Installing Bonitasoft JBoss Bundle for Microsoft SQL Server

This section explains how to install an MDM Workflow Server, Bonitasoft JBoss Bundle, for Microsoft SQL Server.

Procedure

Download the Bonitasoft JBoss bundle called Bonita_JBoss_6.5.4_For_MDM_Workflow_Server.zip from the links provided by Talend Support.
If you do not have the links, create a support request to obtain them.
Copy the file Bonita_JBoss_6.5.4_For_MDM_Workflow_Server.zip to C:\Talend\6.1.1, for example.
Right-click and unzip the file, this creates a C:\Talend\6.1.1\bonita folder.
Edit the file C:\Talend\6.1.1\bonita\standalone\configuration\standalone.xml.
Find the <datasources> and update the jdbc URL to point to the bonita_journal database.
Edit the mdm.conf file and check that all settings are correct. They can be overwritten by the file from your original MDM installation, usually at C:\Talend\6.1.1\mdm\conf
The mdm.conf file is located in three folders, C:\Talend\6.1.1\bonita\bonita\client\conf, C:\Talend\6.1.1\bonita\bonita\mdm and C:\Talend\6.1.1\bonita\mdm.
Run standalone.bat in C:\Talend\6.1.1\bonita\bin to start the JBoss Bonitasoft.

Configuring MDM Workflow Server URL in MDM

This section explains how to configure an MDM Workflow Server URL in MDM.

Procedure

Edit the file C:\Talend\6.1.1\mdm\bonita\client\conf\bonita-client.properties.

Make sure the Server URL to the MDM Workflow Server is correct and change localhost to the correct value.

# HTTP
org.bonitasoft.engine.api-type = HTTP
                    
server.url = http://localhost:8280
application.name = bonita

Save the file.
By default, the MDM Workflow Server application listens on port 8280. This is configured in bonita\bin\standalone.conf.bat. You will see a line as follows.
```
REM NEW LINE HERE Alter Port Settings Offset
set "JAVA_OPTS=%JAVA_OPTS% -Djboss.socket.binding.port-offset=200"
```
This line tells JBoss to offset the binding ports by 200, meaning that instead of using 8080, it will use 8280.
Edit the bonita\bin\service.bat file and change all the C:\Talend\6.1.1 paths to reflect your path.
Run the service.bat install to install JBoss 7.1.1 as a Windows service called Talend MDM Workflow - JBoss 6.5.4.