Configuring a Microsoft SQL Server database

You can use Microsoft™ SQL Server as a supporting database with IBM® UrbanCode™ Release server. Create the database before you install IBM UrbanCode Release. Typically the database is installed on a computer other than the one where the server is located.

When you install IBM UrbanCode Release, you need the database connection information and the JDBC JAR file. The user account used to create the database must have table creation privileges and database owner (DBO) privileges. You can remove the DBO privileges after the server runs for the first time, but you must re-enable this permission when you upgrade.

The IBM UrbanCode Release database consists of approximately fifty tables. While storage requirements vary by installation, database storage of 10-20 GB is sufficient for most installations. The Microsoft SQL Server documentation provides several utilities that you can use to determine storage usage. Many users see a performance boost when the data files and log files are located on separate drives.

To configure the Microsoft SQL Server database complete the following procedure:

  1. Create a Microsoft SQL Server database named ibm_ucr. The following commands are an example of how you might create this database: 
    CREATE DATABASE ibm_ucr
;

USE ibm_ucr
;
CREATE LOGIN ibm_ucr
 WITH PASSWORD = 'password';
CREATE USER ibm_ucr
 FOR LOGIN ibm_ucr
 WITH DEFAULT_SCHEMA = ibm_ucr
;
CREATE SCHEMA ibm_ucr
 AUTHORIZATION ibm_ucr
;
GRANT ALL TO ibm_ucr
;
  2. Obtain the SQL Server JDBC driver JAR file from the Microsoft site. The JDBC JAR file is not included with the installation files. This file must be available when you run the install program.
  3. Record the values of the database parameters so that you can supply them during the installation of IBM UrbanCode Release. The following parameters are typical for an installation of Microsoft SQL Server used to support IBM UrbanCode Release:
    Parameter Value
    Database user name ibm_ucr
    Note: This account must have table creation privileges and database owner (DBO) privileges. You can remove the DBO privileges after the server runs for the first time.
    Database password user_account_password
    Database driver class com.microsoft.sqlserver.jdbc.SQLServerDriver
    Database connection string

    jdbc:sqlserver://database_server:1433;databaseName=ibm_ucr

    If your database server uses integrated security, include the integratedSecurity parameter, as in the following example:
    jdbc:sqlserver://database_server:1433;databaseName=ibm_ucr;integratedSecurity=true;
IBM UrbanCode Release creates the database schema the first time it starts.
After you install the database, you can install the IBM UrbanCode Release server.
If you need to reinstall the database, follow these steps:
  1. Shutdown the IBM UrbanCode Release server.
  2. Drop the database schema.
  3. Assign DBO permissions to the database user account.
  4. Restart the server. IBM UrbanCode Release recreates the schema. You can remove DBO privileges from the database user account after the server restarts for the first time.