Creating SQL Server databases and validating database connections

For each deployment environment that you are creating, complete the following steps:

1. Create required databases.
   If you are migrating to a Standard deployment environment:

   • Create a messaging engine database if you want to use a single messaging engine bus in the new environment (recommended). If you want to use multiple buses, you can reuse the previous messaging engine database and schema.

   If you are migrating to an Advanced deployment environment:

   • Create a Business Process Choreographer database.
   • Create a messaging engine database if you want to use a single messaging engine bus in the new environment (recommended). If you want to use multiple buses, you can reuse the previous messaging engine database and schema.

   For each database, make any required changes and run the following script:

   install_root/BPM/dbscripts/SQLServer/Create/createDatabase_CaseSensitive.sql 

2. Optional: For case management, you must create at least one additional database. You can create either one database (CPEDB) or three databases, one for IBM Content Navigator (ICN), one for the design object store (DOS), and one for the target object store (TOS). This step assumes you are creating three databases and three users. To use the same user for all three databases, go to the next step instead.
   1. Find the files to create the databases in install_root/BPM/dbscripts/SQLServer/Create.
   2. To create the IBM Content Navigator database, replace the parameters in the following files:

      createDatabase_ICN.sql
      createUser.sql

      where:

      • @ECMClient_DBNAME@ is the name of the ICN database, for example, ICNDB.
      • @DB_LOGIN@ is the login name of the database user.
   3. Run the files to create the IBM Content Navigator database.

      createDatabase_ICN.sql
      createUser.sql

      Note: If you would like to use the same user as for the Process database, manually run the following SQL to attach the same user to the ICN database:

      USE @DB_NAME@
      GO
      CREATE USER @DB_USER@ FOR LOGIN @DB_USER@ WITH DEFAULT_SCHEMA=@DB_USER@
      GO

   4. To create the databases for IBM Enterprise Content Management, using the embedded Content Platform Engine, find the following database creation SQL files and make two copies of them.

      createDatabase_ECM.sql
      createUser.sql

      Name the files:

      createDatabase_ECM_DOS.sql
      createUser_ECM_DOS.sql
      createDatabase_ECM_TOS.sql
      createUser_ECM_TOS.sql

      Replace the parameters with the design object store (DOS) parameters in one file and with the target object store (TOS) parameters in the other file.

      • @DB_NAME@ is the name of the database. For example, the names could be DOSDB and TOSDB.
      • @DB_DIR@ is the data directory of your database.
        Important: Make sure that the @DB_DIR@/@DB_NAME@ path already exists on your database computer.
      • @DOS_SCHEMA@ is the schema used by DOS, for example, DOS.
      • @TOS_SCHEMA@ is the schema used by TOS, for example, TOS.
   5. In the createUser.sql file, uncomment the following lines:

      --EXEC sp_addrolemember 'db_securityadmin', @DB_USER@;
      --EXEC sp_addsrvrolemember @DB_USER@, 'bulkadmin';

      by removing the dashes:

      EXEC sp_addrolemember 'db_securityadmin', @DB_USER@;
      EXEC sp_addsrvrolemember @DB_USER@, 'bulkadmin';

   6. Run the files to create the Enterprise Content Management databases.
      For example:

      createDatabase_ECM_DOS.sql
      createUser_ECM_DOS.sql
      createDatabase_ECM_TOS.sql
      createUser_ECM_TOS.sql

3. Optional: For case management, if you want to use a single user who already exists, use this step instead of the previous step. You must do either step 2 or step 3.
   1. Find the files to create the databases in install_root/BPM/dbscripts/SQLServer/Create.
   2. To create the IBM Content Navigator database, replace the parameters in the following files:

      createDatabase_ICN.sql
      createSchema_ECM.sql

      In createDatabase_ICN.sql:

      • @ECMClient_DBNAME@ is the name of the ICN database, for example, ICNDB.
      • @DB_LOGIN@ is the login name of the database user.

      In createSchema_ECM.sql:

      • @DBNAME@ is the name of the ICN database that you created in createDatabase_ICN.sql.
      • @SCHEMA@ is the login name of the database schema, for example, ICN.
      • @ECMClient_DBUSER@ is the user you created.
   3. Run the files to create the IBM Content Navigator database.

      createDatabase_ICN.sql
      createSchema_ECM.sql

      Note: If you would like to use the same user as for the Process database, manually run the following SQL to attach the same user to the ICN database:

      USE @DB_NAME@
      GO
      CREATE USER @DB_USER@ FOR LOGIN @DB_USER@ WITH DEFAULT_SCHEMA=@DB_USER@
      GO

   4. To create the databases for IBM Enterprise Content Management, using the embedded Content Platform Engine, find the following database creation SQL files and make two copies of them.

      createDatabase_ECM.sql
      createSchema_ECM.sql

      Name the files:

      createDatabase_ECM_DOS.sql
      createSchema_ECM_DOS.sql
      createDatabase_ECM_TOS.sql
      createSchema_ECM_TOS.sql

      Replace the parameters with the design object store (DOS) parameters in one file and with the target object store (TOS) parameters in the other file.

      In createDatabase_ECM.sql:

      • @DB_NAME@ is the name of the database. For example, the names could be DOSDB and TOSDB.
      • @DB_DIR@ is the data directory of your database.
      • @DOS_SCHEMA@ is the schema used by DOS, for example, DOS.
      • @TOS_SCHEMA@ is the schema used by TOS, for example, TOS.

      In createSchema_ECM.sql:

      • @DB_NAME@ is the name of the database that you created in createDatabase_ECM.sql.
      • @SCHEMA@ is the login name of the database schema. It should be the same as @DOS_SCHEMA@ or @TOS_SCHEMA@.
      • @ECMClient_DBUSER@ is the user you created.
   5. Run the files to create the Enterprise Content Management databases.
      For example:

      createDatabase_ECM_DOS.sql
      createSchema_ECM_DOS.sql
      createDatabase_ECM_TOS.sql
      createSchema_ECM_TOS.sql

4. On the target deployment manager computer, validate that all database connections are correctly configured by running the BPMConfig -validate command.
   Use the following syntax:

   target_install_root/bin/BPMConfig -validate -db configuration_properties_file

   where

   • configuration_properties_file is the full path and name of the properties file that you copied over to the target environment after you migrated the configuration using the BPMConfig -migrate command. The BPMConfig -validate -db command uses the database properties and the database-related authentication alias properties (including passwords) from the configuration properties file. If necessary, you can update these database properties in the file, such as adding the properties for any new databases that you created.
   The command checks each connection and displays a message similar to the following message:

   A JDBC connection to the 'BusinessSpaceDb' (CMNDB.bpmadmin) has been successfully established.