Creating SQL Server databases

Edit online

You must create the required databases before you install IBM® Business Automation Workflow. Usually you require the Process database, the Performance Data Warehouse database, the Common database, and the Content database. In the case of an AdvancedOnly deployment environment, you need only the Common database.

Before you begin

You cannot share databases across multiple deployment environments.
The Process and Performance Data Warehouse components require their own separate databases and cannot be configured on the same database as the other IBM Business Automation Workflow components.
The Process and Performance Data Warehouse components require the databases to be case insensitive for SQL Server.
The CommonDB (and legacy WebSphere® Process Server) components require the databases to be case sensitive for SQL Server.
The user who creates the databases cannot be the same user that you plan to assign as the login user for accessing the databases. If you deviate from this requirement, the default schema dbo will be used.
The schema name used for each component should match the user.

About this task

The default database names are BPMDB for the Process database, PDWDB for the Performance Data Warehouse database, CMNDB for the Common database, and CPEDB for the Content database. In the case of an Advanced deployment environment or AdvancedOnly deployment environment, the Common database has two parts: one is scoped to the cell and the other is scoped to the deployment environment. Both parts can be defined to use CMNDB (which is the default) or they can use separate databases. For details about databases and schemas, see Planning the number of databases.

Procedure

Use one of the following methods to create the BPMDB and PDWDB databases:
- Create and run the createDatabase_CaseInsensitive.sql file as described in the following substeps:
  1. Save the following SQL statement into a file named createDatabase_CaseInsensitive.sql (where CI in the COLLATE attribute value is applicable for the case-insensitive databases):
```
CREATE DATABASE @DB_NAME@ COLLATE SQL_Latin1_General_CP1_CI_AS;
GO
ALTER DATABASE @DB_NAME@ SET READ_COMMITTED_SNAPSHOT ON;
GO
```
  2. In the SQL statement, replace @DB_NAME@ with the name of the database that you want to create.
  3. Run the following command to create the database:
```
sqlcmd -i createDatabase_CaseInsensitive.sql
```
- Run the following command from the command line (where @DB_NAME@ is the name of the database that you want to create):
```
sqlcmd -Q "CREATE DATABASE @DB_NAME@ COLLATE SQL_Latin1_General_CP1_CI_AS;"
sqlcmd -Q "ALTER DATABASE @DB_NAME@ SET READ_COMMITTED_SNAPSHOT ON;"
```
Use one of the following methods to create the CMNDB database:
- Create and run the createDatabase_CaseSensitive.sql file as described in the following substeps:
  1. Save the following SQL statement into a file named createDatabase_CaseSensitive.sql (where CS in the COLLATE attribute value is applicable for the case-sensitive databases):
```
CREATE DATABASE @DB_NAME@ COLLATE SQL_Latin1_General_CP1_CS_AS;
GO
ALTER DATABASE @DB_NAME@ SET READ_COMMITTED_SNAPSHOT ON;
GO
```
  2. In the SQL statement, replace @DB_NAME@ with the name of the database that you want to create.
  3. Run the following command to create the database:
```
sqlcmd -i createDatabase_CaseSensitive.sql
```
- Run the following command from the command line (where @DB_NAME@ is the name of the database that you want to create):
```
sqlcmd -Q "CREATE DATABASE @DB_NAME@ COLLATE SQL_Latin1_General_CP1_CS_AS;"
sqlcmd -Q "ALTER DATABASE @DB_NAME@ SET READ_COMMITTED_SNAPSHOT ON;"
```
Create the content database (CPEDB) and the schemas for the design object store (DOS), target object store (TOS), and IBM Content Navigator (ICN):
1. Save the following SQL statements into a file named createDatabase_ECM.sql:
```
CREATE DATABASE @DB_NAME@
ON PRIMARY
(  NAME = @DB_NAME@_DATA,
   FILENAME = '@DB_DIR@\@DB_NAME@\@DB_NAME@_DATA.mdf',
   SIZE = 5GB,
   FILEGROWTH = 1GB ),

FILEGROUP @DOS_SCHEMA@_DATA_FG
(  NAME = @DOS_SCHEMA@_DATA,
   FILENAME = '@DB_DIR@\@DB_NAME@\@DOS_SCHEMA@_DATA.ndf',
   SIZE = 2GB,
   FILEGROWTH = 512MB),
  
FILEGROUP @DOS_SCHEMA@_IDX_FG
(  NAME = @DOS_SCHEMA@_IDX,
   FILENAME = '@DB_DIR@\@DB_NAME@\@DOS_SCHEMA@_IDX.ndf',
   SIZE = 1GB,
   FILEGROWTH = 128MB),
   
FILEGROUP @TOS_SCHEMA@_DATA_FG
(  NAME = @TOS_SCHEMA@_DATA,
   FILENAME = '@DB_DIR@\@DB_NAME@\@TOS_SCHEMA@_DATA.ndf',
   SIZE = 2GB,
   FILEGROWTH = 512MB),
  
FILEGROUP @TOS_SCHEMA@_IDX_FG
(  NAME = @TOS_SCHEMA@_IDX,
   FILENAME = '@DB_DIR@\@DB_NAME@\@TOS_SCHEMA@_IDX.ndf',
   SIZE = 1GB,
   FILEGROWTH = 128MB)
  
LOG ON
(  NAME = '@DB_NAME@_LOG',
   FILENAME = '@DB_DIR@\@DB_NAME@\@DB_NAME@_LOG.ldf',
   SIZE = 1024MB,
   FILEGROWTH = 100MB )
GO

ALTER DATABASE @DB_NAME@ SET RECOVERY SIMPLE 
GO

ALTER DATABASE @DB_NAME@ SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE @DB_NAME@ SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE @DB_NAME@ SET READ_COMMITTED_SNAPSHOT ON
GO
```
2. Replace @DB_NAME@ with the name that you want to use for the database (such as CPEDB). Replace @DOS_SCHEMA@ with DOSSA and replace @TOS_SCHEMA@ with TOSSA. Replace @DB_DIR@ with the location where you want to store your database (for example, the default SQL Server store location is C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA). Manually, create a folder with the name of the @DB_NAME@ under @DB_DIR@.
  Remember the name you choose for the database. You will enter it in the launchpad if you are using SQL Server authentication.
3. Run the createDatabase_ECM.sql command to create the database.
4. To create the schemas, save the following SQL statements into a file named createSchema_ECM.sql:
```
-- MSSQL SQL Script for creating schema

USE @DB_NAME@
GO
CREATE SCHEMA @SCHEMA@;
GO
```
5. Run the createSchema_ECM.sql command three times, replacing @DB_NAME@ with the name of the content database that you specified before, and replacing @SCHEMA@ with first TOSSA, then DOSSA, and finally ICNSA.
6. Change the schema owner for the three schemas (DOSSA, TOSSA, and ICNSA) to the user you created in the previous step instead of the default user (dbo).
  Open the SQL Server Management Studio and right-click each schema name. On the General tab, click Search for the Schema owner and choose the user name.