Creating SQL Server databases

You can create the required databases before you create profiles.

1. Install Microsoft SQL Server.
2. Use the following commands to create the databases for SQL Server:
   1. To create the Process Server (BPMDB) and Performance Data Warehouse (PDWDB) databases:

      osql -b -S hostname -U db_user_account -P db_user_password 
      -Q "CREATE DATABASE database_name COLLATE SQL_Latin1_General_CP1_CI_AS"

      where hostname is the host name of the system that hosts SQL Server, db_user_account and db_user_password are the user name and password that are used to log in to create the database, and database_name is the name of the database that you are creating. COLLATE SQL_Latin1_General_CP1_CI_AS is included because these databases must be case-insensitive.
   2. To create the Common database:

      osql -b -S hostname -U db_user_account -P db_user_password 
      -Q "CREATE DATABASE database_name COLLATE SQL_Latin1_General_CP1_CS_AS"

      The difference is the COLLATE clause. CMNDB requires case-sensitive collation.
   Important: Ensure that the databases you create for Process Server and Performance Data Warehouse are case insensitive. The letters CI in the COLLATE attribute value specifies this. Ensure this variable looks like this: SQL_Latin1_General_CP1_CI_AS (not SQL_Latin1_General_CP1_CS_AS). Failure to do so can result in an error like the following:

   org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'message.routingCache' 
   defined in class path resource [registry.xml]: Instantiation of bean failed; nested exception is 
   org.springframework.beans.BeanInstantiationException: Could not instantiate bean class 
   [com.lombardisoftware.bpd.runtime.engine.message.DefaultMessageRoutingCache]: Constructor threw exception; 
   nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; 
   bad SQL grammar [select "value" from lsw_system where "key"=?]; nested exception is 
   com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'lsw_system'.
   
   Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'lsw_system'.
   at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
   at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:648)
   at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
   [...]

   When using Microsoft SQL Server as the database for the Performance Data Warehouse, the following error message might be displayed during report generation using optimizer or portal for an existing process application. This prevents the report from being displayed.

   com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'slathresholdtraversals'.
       at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteCursored(SQLServerStatement.java:1824)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:760)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:685)

   In Microsoft SQL server, the default schema name associated with a user must be the same as the user name. For example, if the Performance Data Warehouse database user name is perfDB then the default schema name associated with the user perfDB must also be named perfDB. You must create an ordinary database user and assign the required rights to the user instead of using a super user, such as sa. This is because the default schema for the super user is dbo and this cannot be changed.

   You can complete the following steps if existing tables are not associated with a schema that is the same as the user name.

   1. In SQL Server Management Studio Object Explorer, right-click the table name and then click Design.
   2. From the Design view, press F4 to view the Properties window.
   3. From the Properties window, update the schema name.
   4. Right-click the tab and select Close to close the Design view.
   5. Click OK when prompted to save. The selected table is transferred to the schema.
   6. Repeat the previous steps for all the tables in the Performance Data Warehouse database.