Creating required databases in SQL Server

Before you install, create Microsoft SQL Server databases for IBM Business Automation Workflow, Workstream Services, or both, as well as for Application Engine, IBM FileNet® Content Manager, and IBM Business Automation Navigator.

About this task

Restriction: You cannot use the same database user for IBM Business Automation Workflow and Workstream Services, or for other components, such as Business Automation Studio. You can use a shared database but you must use a different database user for each component.
Tip: If you need to know which database versions are supported for your installation, use this IBM Support page to generate a software compatibility report.

For more details of the database privileges that are required to run Business Automation Workflow, see the Process Server component in SQL Server database privileges. The other components do not apply.

Procedure

  1. To configure XA transactions for SQL Server, see Configuring XA Transactions.
    Note that Windows Authentication is not supported.
  2. To create the database for Business Automation Workflow:
    1. Create a script named CREATE_BAW_INSTANCE1_DB.sql and copy it to your SQL Server server.
      CREATE_BAW_INSTANCE1_DB.sql script:
      -- create the database:
      CREATE DATABASE @DB_NAME@ COLLATE SQL_Latin1_General_CP1_CI_AS;
      ALTER DATABASE @DB_NAME@ SET READ_COMMITTED_SNAPSHOT ON;
      
      USE MASTER
      GO
      -- when using SQL authentication
      CREATE LOGIN @DB_LOGIN@ WITH PASSWORD='@DB_PASSWD@'
      GO
      CREATE USER @DB_USER@ FOR LOGIN @DB_LOGIN@ WITH DEFAULT_SCHEMA=@DB_USER@
      GO
      EXEC sp_addrolemember N'SqlJDBCXAUser', N'@DB_USER@';
      GO
      
      USE @DB_NAME@
      GO
      CREATE USER @DB_USER@ FOR LOGIN @DB_USER@ WITH DEFAULT_SCHEMA=@DB_USER@
      GO
      CREATE SCHEMA @DB_USER@ AUTHORIZATION @DB_USER@
      GO
      EXEC sp_addrolemember 'db_ddladmin', @DB_USER@;
      EXEC sp_addrolemember 'db_datareader', @DB_USER@;
      EXEC sp_addrolemember 'db_datawriter', @DB_USER@;
      
      GO
      
      To modify the @DB_NAME@, @DB_LOGIN@, @DB_USER@, and @DB_PASSWD@ in the Business Automation Workflow database, you must specify the database user and password in the Business Automation Workflow Server database secret.
    2. Optional: To ensure that all communications between Business Automation Workflow Server and SQL Server are encoded, import the database certificate authority (CA) certificate to the Business Automation Workflow Server.
      1. To create a secret to store the certificate, run the following command:
        oc create secret generic baw-sqlserver-ssl-secret --from-file=tls.crt=<path-to-sqlserver-certificate-file>
        where <path-to-sqlserver-certificate-file> is the path to the SQL Server database certificate file. Make sure the format of the certificate file is Base-64 encoded X.509 (.CER). Don't change tls.crt as the key name.
      2. Add the secret to the database configuration in the baw_configuration section of the custom resource file. For example, the name of the secret could be baw-instance1-sqlserver-ssl-secret.
        baw_configuration:
          - name: instance1
            ...
            database:
              enable_ssl: true
              db_cert_secret_name: " baw-instance1-sqlserver-ssl-secret"
              ...
        
  3. To create the database for Workstream Services:
    1. Create a script named CREATE_BAW_INSTANCE2DB.sql and copy it to your SQL Server server.
      CREATE_BAW_INSTANCE2_DB.sql script:
      -- create the database:
      CREATE DATABASE @DB_NAME@ COLLATE SQL_Latin1_General_CP1_CI_AS;
      ALTER DATABASE @DB_NAME@ SET READ_COMMITTED_SNAPSHOT ON;
      
      USE MASTER
      GO
      -- when using SQL authentication
      CREATE LOGIN @DB_LOGIN@ WITH PASSWORD='@DB_PASSWD@'
      GO
      CREATE USER @DB_USER@ FOR LOGIN @DB_LOGIN@ WITH DEFAULT_SCHEMA=@DB_USER@
      GO
      EXEC sp_addrolemember N'SqlJDBCXAUser', N'@DB_USER@';
      GO
      
      USE @DB_NAME@
      GO
      CREATE USER @DB_USER@ FOR LOGIN @DB_USER@ WITH DEFAULT_SCHEMA=@DB_USER@
      GO
      CREATE SCHEMA @DB_USER@ AUTHORIZATION @DB_USER@
      GO
      EXEC sp_addrolemember 'db_ddladmin', @DB_USER@;
      EXEC sp_addrolemember 'db_datareader', @DB_USER@;
      EXEC sp_addrolemember 'db_datawriter', @DB_USER@;
      
      GO
      
      To modify the @DB_NAME@, @DB_LOGIN@, @DB_USER@, and @DB_PASSWD@ in the Workstream Services database, you must specify the database user and password in the Workstream Services Server database secret.
    2. Optional: To ensure that all communications between Workstream Services Server and SQL Server are encoded, import the database certificate authority (CA) certificate to the Workstream Services Server.
      1. To create a secret to store the certificate, run the following command:
        oc create secret generic baw-sqlserver-ssl-secret --from-file=tls.crt=<path-to-sqlserver-certificate-file>
        Where <path-to-sqlserver-certificate-file> is the path to the SQL Server database certificate file. Make sure the format of the certificate file is Base-64 encoded X.509 (.CER). Don't change tls.crt as the key name.
      2. Add the secret to the database configuration in the baw_configuration section of the custom resource file. For example, the name of the secret could be baw-instance2-sqlserver-ssl-secret.
        baw_configuration:
          - name: instance2
            ...
            database:
              enable_ssl: true
              db_cert_secret_name: " baw-instance2-sqlserver-ssl-secret"
              ...
        
  4. Optional: To use your own JDBC driver, package your JDBC files into a compressed file and use the sc_drivers_url configuration parameter to download them from an accessible web server.
  5. To create the database for Application Engine, follow the instructions in Creating an SQL Server database.
  6. To create the databases for IBM FileNet Content Manager, create a database for the Content Platform Engine global configuration database (GCD) and databases for the content stores. Follow the instructions in Preparing the databases.

    Business Automation Workflow needs four different object stores (DOCS, TOS, DOS, and AEOS). If you are installing the Workflow capability, you must run the instructions to create the PostgreSQL database and table spaces four times. If you are installing only the Workstreams capability, you need two object stores (DOCS and AEOS).

  7. To create the database for Business Automation Navigator, follow the instructions in Creating the databases without running the provided scripts.

What to do next

To protect the configuration data you're going to enter, see Creating secrets to protect sensitive configuration data.