Creating SQL Server databases and validating database connections

If you need new SQL Server databases, create the databases before creating your deployment environment. Even if you have no new databases, validate that all database connections are correctly configured before you continue.

Procedure

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

Create required databases.
If you are migrating to a Standard deployment environment:
- Create a database for Business Space if you did not have one in the source 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 database for Business Space if you did not have one in the source 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
```
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.
  - Use the backslash ("\") to replace the forward slash as the file separator.
  - @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
```
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
```
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 extracted information using the BPMConfig -migrate command. Update the properties in the file 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.
```

Results

After all connections are checked, you see a message that the BPMConfig -validate command completed successfully.