Creating the Microsoft SQL Server database





Creating the Microsoft SQL Server database

To create a WSRR database manually, you must edit and run the scripts that come with the WebSphere® Service Registry and Repository. This topic tells you how to edit and run scripts associated with the SQL Server database.

Before you begin

 You must create the database using the UTF-8 code set. No other code sets will function correctly.
Before you run the scripts to create the databases, you must first install the stored procedures for Java transaction API (see Installing stored procedures for Java Transaction API).

About this task

Before you can run scripts to create a SQL Server database manually, you must customize them for WebSphere Service Registry and Repository.
WSRR can be configured using up to three databases:
  • WSRR
  • Activity logging
  • Service integration bus
These databases can be separate or the activity logging and service integration bus tables can be created in the WSRR database
Note: WebSphere Service Registry and Repository databases must use the UTF-8 code page, have transactionality (XA) support, and be case sensitive.
Use the following values in the instructions on this page:
Table 1. Values used to create SQL Server database in WSRR
ValueDescription
$ACTDB_NAMEActivity logging database name.
$ACTDB_SCHEMAActivity logging database schema.
$ACTDB_USERActivity logging database user ID.
$DB_NAMEWSRR database name.
$DB_SCHEMAWSRR database schema.
$DB_USERUser in the MSSQL database.
$SQLSERVERPATHThe MSSQL Server installation directory
$SIBDB_NAMEService integration bus database name.
$SIBDB_SCHEMAService integration bus database schema.
$SIBDB_USERService integration bus database user ID.
$SQLPATHPath to the SQL files, for example: install_root/WSRR/dbscripts/sqlserver.

Procedure

  1. Create and configure the WSRR database.
    Note: If you are using an existing database, it must use the UTF-8 code set. No other code sets will function correctly.
    1. Edit $SQLPATH/createWsrrDb.sql
      • Replace all instances of __DBNAME__ with $DB_NAME
      • Replace all instances of __DBUSER__ with $DB_USER
      • Replace all instances of __DBPASSWORD__ with $DB_PASS
      • Replace all instances of __DBSCHEMA__ with $DB_SCHEMA
      • Replace all instances of __SQLSERVERHOME__ with $SQLSERVERPATH
      • If the database does not already exist, uncomment all llines starting -- db --
      • If $DB_USER does not already exist, uncomment all lines starting -- user --
      • If $DB_SCHEMA does not already exist, uncomment all lines starting -- schema --
      • If $DB_USER is authenticated using Windows authentication rather than SQL Server authentication, edit the CREATE USER line to read:
        CREATE USER __DBUSER__ FOR LOGIN domain\__DBUSER__;
        Where domain\__DBUSER__ corresponds to the existing SQL Server login for this user.
    2. Run the createWsrrDb.sql script to create the WSRR database, by entering the following command:
      sqlcmd.exe -i $SQLPATH/createWsrrDb.sql
  2. Optionally, create and configure the activity logging database.
    Note: If you are using an existing database, it must use the UTF-8 code set. No other code sets will function correctly.
    1. Edit $SQLPATH/createActDb.sql
      • Replace all instances of __DBNAME__ with $ACTDB_NAME
      • Replace all instances of __DBUSER__ with $ACTDB_USER
      • Replace all instances of __DBPASSWORD__ with $DB_PASS
      • Replace all instances of __DBSCHEMA__ with $ACTDB_SCHEMA
      • Replace all instances of __SQLSERVERHOME__ with $SQLSERVERPATH
      • If the database does not already exist, uncomment all llines starting -- db --
      • If $DB_USER does not already exist, uncomment all lines starting -- user --
      • If $DB_SCHEMA does not already exist, uncomment all lines starting -- schema --
      • If $DB_USER is authenticated using Windows authentication rather than SQL Server authentication, edit the CREATE USER line to read:
        CREATE USER __DBUSER__ FOR LOGIN domain\__DBUSER__;
        Where domain\__DBUSER__ corresponds to the existing SQL Server login for this user.
    2. Run the createActDb.sql script to create the activity logging database, by entering the following command:
      sqlcmd.exe -i $SQLPATH/createActDb.sql
  3. Optionally, create and configure the service integration bus database.
    Note: If you are using an existing database, it must use the UTF-8 code set. No other code sets will function correctly.
    1. Edit $SQLPATH/createSibDb.sql
      • Replace all instances of __DBNAME__ with $SIBDB_NAME
      • Replace all instances of __DBUSER__ with $SIBDB_USER
      • Replace all instances of __DBPASSWORD__ with $DB_PASS
      • Replace all instances of __DBSCHEMA__ with $SIBDB_SCHEMA
      • Replace all instances of __SQLSERVERHOME__ with $SQLSERVERPATH
      • If the database does not already exist, uncomment all llines starting -- db --
      • If $DB_USER does not already exist, uncomment all lines starting -- user --
      • If $DB_SCHEMA does not already exist, uncomment all lines starting -- schema --
      • If $DB_USER is authenticated using Windows authentication rather than SQL Server authentication, edit the CREATE USER line to read:
        CREATE USER __DBUSER__ FOR LOGIN domain\__DBUSER__;
        Where domain\__DBUSER__ corresponds to the existing SQL Server login for this user.
    2. Run the createSibDb.sql script to create the service integration bus database, by entering the following command:
      sqlcmd.exe -i $SQLPATH/createSibDb.sql
  4. Create the database tables for the WSRR database.
    1. In each of createWsrrTables1.sql, createWsrrTables2,sql and createWsrrProcs.sql:
      • Replace all instances of __DBSCHEMA__ with $DB_SCHEMA
      • Replace all instances of __DBUSER__ with $DB_USER
    2. Run:
      sqlcmd.exe -d $DB_NAME -i $SQLPATH/createWsrrTables1.sql

sqlcmd.exe -d $DB_NAME -i $SQLPATH/createWsrrTables2.sql

sqlcmd.exe -d $DB_NAME -i $SQLPATH/createWsrrProcs.sql
  5. Create the database tables for the activity logging database.
    1. In $SQLPATH/createActTables.sql:
      • Replace all instances of __DBSCHEMA__ with $ACTDB_SCHEMA
      • Replace all instances of __DBUSER__ with $ACTDB_USER
    2. Run:
      sqlcmd.exe -d $ACTDB_NAME -i $SQLPATH/createActTables.sql
  6. Create the database tables for the service integration bus database
    1. In $SQLPATH/createSibTables.sql:
      • Replace all instances of __DBSCHEMA__ with $SIBDB_SCHEMA
      • Replace all instances of __DBUSER__ with $SIBDB_USER
    2. Run:
      sqlcmd.exe -d $SIBDB_NAME -i $SQLPATH/createSibTables.sql

Results

The SQL Server database is created