Configuring bwagent for Microsoft SQL Server and TIBCO Enterprise Message Service

The bwagent can be configured to use Microsoft SQL Server database with TIBCO Enterprise Message Service (EMS) for persistence and transport.

Note: The database name must be unique per agent network if multiple networks share the same physical database. bwagent and bwengine supports sharing the same database, users and schemas.

Prerequisites

  • Install TIBCO Enterprise Messaging Server 8.1 and start the server.
  • The Microsoft SQL driver is available by default.
  • Ensure you have installed EMS client libraries. For more information, see the "Integrating with TIBCO Enterprise Message Service™" section in the TIBCO ActiveMatrix BusinessWorks™ Installation guide.
  • Optional. If you are upgrading to ActiveMatrix BusinessWorks™ 6.3.2, or later versions of the software, upgrade the database schema. For more information, see the "Updating the Database Schema to Enable bwagent to Use Database with TIBCO Enterprise Message Service" section in the TIBCO ActiveMatrix BusinessWorks™ Installation guide for additional details.

After installing Microsoft SQL Server, create a database bwadmindb and grant privileges to the default database owner root as described in the following steps:

Procedure

  1. Open Microsoft SQL Server Management Studio.
  2. From the Object Explorer pane, right-click Databases > New Database to create the database bwadmindb.
  3. Right-click Security > Logins > New Login....
  4. Make the following configurations from the Login-New window:
    1. Type bwuser in the Login name field.
    2. Select SQL Server Authentication.
    3. Optional. Unselect Enforce password policy, Enforce password expiration, or User must change password at next login.
    4. In the Default database field, select bwadmindb.
    5. From the Select a page pane, on the left side of the Login - New window, click on the Server Roles tab.
    6. To configure the bwagent with MS SQL Server, set the values for Minimum Server Role and Database Role required for a user, for a particular database. In MS SQL Server Management Server, navigate to Security > Logins. Right click Login Properties > Server Roles. The minimum server role required for a particular user is public. Under User Mapping, the minimum database role membership for the selected database for a user mapped to the login should be one of the following two combinations: public and db_owner OR public, db_datawriter, db_datareader, and db_ddladmin.
    7. Click OK.
  5. Stop the bwagent if it is running.
  6. Open the bwagent_db.json file located in BW_HOME\config (Windows) or ${BW_HOME}/config (Unix).
  7. Update the following properties for your environment:
    Property Name Microsoft SQL Value
    dbtype sqlserver
    dbdriver com.microsoft.sqlserver.jdbc.SQLServerDriver
    dbconnectionurl jdbc:sqlserver://localhost:1433;databaseName=bwadmindb
    dbuser bwuser
    dbpassword bwuser
  8. Run the bwadmin config command with the -cf option push the changes from the JSON file to the bwagent.ini file. 
    BW_HOME\bin>bwadmin config -cf ../config/bwagent_db.json agent
  9. Restart the bwagent.