Setting up an SQL Server database for Rational Engineering Lifecycle Manager

Set up an SQL Server database to work with the Jazz™ Team Server, Rational® Engineering Lifecycle Manager, and the Configuration Management application.

Before you begin

This procedure requires that the following prerequisites are met for the SQL Server:

Note: If you are using SQL server in a language other than English, you must change the language to English before you create the data warehouse:
  1. In SQL Server Management Studio, right-click the connection to the database server.
  2. From the pop-up window, select Properties.
  3. In the Server Properties window, select Advanced and set the Default language entry to English.
  4. Save your changes.

Procedure

  1. Create a database for each instance of the Jazz Team Server, Rational Engineering Lifecycle Manager, and the Configuration Management application. Create a database for the data warehouse associated with the Jazz Team server. For example, if you have one Rational Engineering Lifecycle Manager, one Configuration Management application, one Jazz Team Server, and one data warehouse, you create four databases. Use the following example for creating the Jazz Team Server database. To create the Rational Engineering Lifecycle Manager, and the Configuration Management application databases, substitute jts with your database name, for example relm, and vvc:
    CREATE DATABASE jts 
    GO 
  2. Create a user and password for each database, and change the ownership of each database.
    Run these commands:
    CREATE LOGIN jtsDBuser
    WITH PASSWORD = 'jtsDBpswd';
    USE jts;
    exec sp_changedbowner 'jtsDBuser'
    GO 
  3. Change the collation of the Jazz Team Server, Rational Engineering Lifecycle Manager, and the Configuration Management application databases. From the command-line tool, run this command for the Jazz Team Server. For Rational Engineering Lifecycle Manager, and the Configuration Management application databases, replace jts with relm, or vvc.
    ALTER DATABASE jts COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
  4. Change the row version system for Jazz Team Server, Rational Engineering Lifecycle Manager, and the Configuration Management application databases. From the command-line tool, run this command for the Jazz Team Server. For Rational Engineering Lifecycle Manager, and the Configuration Management application databases, replace jts with relm, or vvc.
    Note: Ensure that the connection to the database is open before you run this command.
    ALTER DATABASE jts SET READ_COMMITTED_SNAPSHOT ON
    GO
  5. Change the collation of the data warehouse database. From the command line, type:
    ALTER DATABASE dw COLLATE SQL_Latin1_General_CP1_CS_AS
    GO
  6. Create an environment variable that is named SQLSERVER_JDBC_DRIVER_FILE and point to the sqljdbc4.jar JDBC driver. The location of the driver varies depending on your installation.
    Windows
    1. Click Start > Control Panel > System.
    2. Click the Advanced tab, and then click Environment Variables.
    3. In the System variables list, click New.
    4. In the Variable name enter SQLSERVER_JDBC_DRIVER_FILE, and in the Variable value enter C:\Path_to_JDBC_driver\sqljdbc4.jar.
    Linux On UNIX systems, add the following environment variable declaration:
    SQLSERVER_JDBC_DRIVER_FILE =.:Path_to_JDBC_driver/sqljdbc4.jar
    Note: Rational Engineering Lifecycle Manager does not support JRE 5.0. The supported JREs are version 6.0 and 7.0.

    If you are using WebSphere® Application Server, you must also add a custom property that points to the sqljdbc4.jar JDBC driver. For more information, see Setting up WebSphere Application Server for Rational Engineering Lifecycle Manager.

  7. To configure your databases connections, and create database tables, complete one of these steps:
    1. If you are using Tomcat, or if you are deploying WebSphere Application Server, start the server, and continue with Running the Jazz Team Server setup wizard for Rational Engineering Lifecycle Manager applications.
    2. If you prefer to manually modify the teamserver.properties file for database connection, run the repotools commands to create database tables. For more information, read about Manually configuring an SQL Server database.
    3. If you are upgrading from a previous version, continue to run the appropriate upgrade scripts, which migrate your existing database connections. For more information, read about upgrading Rational Engineering Lifecycle Manager.

Manually configuring an SQL Server database

Running the setup wizard is the recommended method to configure the database connections and create the database tables. If you prefer to do this procedure manually, edit the teamserver.properties file to set up an SQL Server database connection and run the -createTables command to create database tables.

Procedure

  1. For each application, open and modify a separate teamserver.properties. These files are in their own directories (jts, relm, and vvc) in the <JazzInstallDir>/server/conf directory. Open each teamserver.properties file and specify the database and connection details.
    1. Comment out the following lines under Derby Configuration by placing a number sign (#) before each line. The example is for JTS teamserver.properties file:
      # com.ibm.team.repository.db.vendor = DERBY
      # com.ibm.team.repository.db.jdbc.location=conf/jts/derby/repositoryDB
    2. Uncomment the following lines under SQL Server Configuration by removing the number sign (#) before each line:
      com.ibm.team.repository.db.vendor = SQLSERVER
      com.ibm.team.repository.db.jdbc.location=//localhost:1433;databaseName=jazz;user=jazzDBuser;password={password}
      com.ibm.team.repository.db.jdbc.password=jazzDBpswd
    3. Edit the following values in the com.ibm.team.repository.db.jdbc.location and com.ibm.team.datawarehouse.db.jdbc.location properties:
      • //localhost:1433: Type the fully qualified host name of the database server.
        Note: You must manually set the static port to 1433 by using the SQL Server Configuration tool.
      • jazz: Type the database name.
      • jazzDBuser: Type the user name
      Note: Do not change the password={password} text in the com.ibm.team.repository.db.jdbc.location and com.ibm.team.datawarehouse.db.jdbc.location properties.
    4. Specify the user password in the com.ibm.team.repository.db.jdbc.password and com.ibm.team.datawarehouse.db.jdbc.password properties.
    5. Change the value of the com.ibm.team.fulltext.indexLocation=conf/jts/indices/workitemindex line to the full path location of the full text index. For example, enter com.ibm.team.fulltext.indexLocation=c:/Progra~1/IBM/JazzTeamServer/server/conf/jts/indices/workitemindex.
    6. Change the value of the com.ibm.team.jfs.index.root.directory=indices line to the full path location of Jazz Foundation Service index root directory. For example, enter com.ibm.team.jfs.index.root.directory=c:/Progra~1/IBM/JazzTeamServer/server/conf/jts/indices.
  2. After each database is created, you must run a repotools command to create database tables.
    Important: Run the createTables command only if this is a new installation.
    Note: When you run the repotools command, the teamserver.properties file is updated, and the original file with the original content is saved in the same directory. You might see a warning that the Public URI was not set. You can ignore this warning now because the Public URI is set when running the Jazz Team Server setup wizard for Rational Engineering Lifecycle Manager applications.
    1. To create the database tables for Jazz Team Server, go to the JazzInstallDir/server directory and run this command:
      repotools-jts -createTables 
    2. To create the database tables for the Rational Engineering Lifecycle Manager application, go to the RELMInstallDir/server directory and run this command:
      repotools-relm -createTables
    3. To create the database tables for the Configuration Management application, go to the VVCInstallDir/server directory and run this command:
      repotools-vvc -createTables 

Troubleshooting

Procedure

  • If the repotools command does not work, check the appropriate log files, which are in the JazzInstallDir/server directory.
  • Verify that these conditions are correct:
    • The SQL Server is configured to allow connections that use the TCP/IP protocol. If required, restart the SQL service.
    • The SQL Server computer host name, and the port is correctly reflected in the teamserver.properties files.
    • The login name, password, and database name are correctly reflected in the teamserver.properties files.
    • A firewall does not prevent you from accessing the SQL Server. Use a Telnet command to connect to the server. Enter the host name and port from the teamserver.properties file.
    • The created user has the correct permissions in the Jazz Team Server database.
    • The JDBC driver environment variables are properly configured for your environment.
    Note: Jazz Team Server runs on multiple database platforms; however, differences between the enterprise databases exist in syntax, optimization strategies, and locking semantics. The server was tested on SQL Server using a combination of automated tests and simulations of normal workloads and stress testing. The simulation of normal workloads showed acceptable performance. In some instances, database deadlocks occurred in automated concurrency tests; however, these errors were not consistently reproduced on different server hardware.

    The server is designed to handle a large number of simultaneous requests. In exceptional cases, deadlocks occur in the repository database, as similar or equal objects are updated concurrently in transactions that are associated with the requests. These errors are rare, but typical events. The system is defensive against deadlocks, and data integrity is maintained; there is no loss of data. Occasionally, deadlocks are logged by tasks, such as build engine execution. Generally, the condition is detected, and the task is performed again.

    All deadlocks are logged on the server to provide information about what happened when the deadlock occurred. The log entry has text similar to the following entry: com.ibm.team.repository.common.RetryableDatabaseException: Serialization failure.

    If your system encounters deadlocks, see the error log, which is available at https://servername:9443/jts/service/com.ibm.team.repository.common.internal.IFeedService?category=SystemLog.

    If you encounter frequent deadlocks, contact your product support, or check the forums on jazz.net for the latest information, updates, or mitigating techniques.


Feedback