Setting up an SQL Server database

Set up an SQL Server database to work with Jazz™ Team Server, CLM applications, and Data Warehouse.

This procedure requires that the following prerequisites have been 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.

Setting up a database

Set up the SQL Server database by using the sqlcmd command line tool, which is provided with the SQL Server installation.
To activate the sqlcmd tool for Microsoft SQL Server Express edition:
  1. From the Start menu, locate Microsoft SQL Server, and click Configuration Tools > SQL Server Configuration Manager.
  2. In the SQL Server Configuration Manager window, in the left pane, click SQL Server Network Configuration > Protocols for SQLEXPRESS.
  3. Double-click the protocol Named Pipes.
  4. In the Name Pipes Properties dialog box, select Yes for the Enabled field.
  5. Change the Pipe Name as follows: \\.\pipe\sql\query.
  6. Click Apply; then, restart the SQL Server Services.
You can also use a visual tool, such as SQL Server Studio Management, which is also available for the Express® version as SQL Server Studio Management Express. See the SQL Server documentation or a SQL Server database administrator (DBA) for more information.
Important: If you install Jazz Team Server with the CLM applications either on the same computer or distributed platforms, a separate database and a database user who is associated with that database must be created.
  1. Create a database for each instance of the Jazz Team Server, Change and Configuration Management, Quality Management, Requirements Management, Data Collection Component, Lifecycle Query Engine, Link Index Provider, Rational Engineering Lifecycle Manager, Design Management, Global Configuration Management applications. Also create a database for each data warehouse associated with a Jazz Team server. For example, if you have one CCM application, one QM application, one RM application, one Jazz Team Server, one DCC application, one LQE application, one LDX application, one RELM application, one DM application, one GC application, and one data warehouse, you will need to create 11 databases.
    CREATE DATABASE jts 
    GO 
    CREATE DATABASE ccm 
    GO 
    CREATE DATABASE qm 
    GO 
    CREATE DATABASE rm 
    GO 
    CREATE DATABASE dcc 
    GO 
    CREATE DATABASE lqe 
    GO 
    CREATE DATABASE ldx 
    GO 
    CREATE DATABASE relm 
    GO 
    CREATE DATABASE dm 
    GO 
    CREATE DATABASE gc 
    GO 
    CREATE DATABASE dw 
    GO 
  2. Create a user and password and change the ownership of the database by entering these commands:
    CREATE LOGIN jtsDBuser
    WITH PASSWORD = 'jtsDBpswd';
    USE jts;
    exec sp_changedbowner 'jtsDBuser'
    GO 
    CREATE LOGIN ccmDBuser
    WITH PASSWORD = 'ccmDBpswd';
    USE ccm;
    exec sp_changedbowner 'ccmDBuser'
    GO 
    CREATE LOGIN qmDBuser
    WITH PASSWORD = 'qmDBpswd';
    USE qm;
    exec sp_changedbowner 'qmDBuser'
    GO 
    CREATE LOGIN rmDBuser
    WITH PASSWORD = 'rmDBpswd';
    USE rm;
    exec sp_changedbowner 'rmDBuser'
    GO 
    CREATE LOGIN dccDBuser
    WITH PASSWORD = 'dccDBpswd';
    USE dcc;
    exec sp_changedbowner 'dccDBuser'
    GO 
    CREATE LOGIN lqeDBuser
    WITH PASSWORD = 'lqeDBpswd';
    USE lqe;
    exec sp_changedbowner 'lqeDBuser'
    GO 
    CREATE LOGIN ldxDBuser
    WITH PASSWORD = 'ldxDBpswd';
    USE ldx;
    exec sp_changedbowner 'ldxDBuser'
    GO 
    CREATE LOGIN relmDBuser
    WITH PASSWORD = 'relmDBpswd';
    USE relm;
    exec sp_changedbowner 'relmDBuser'
    GO 
    CREATE LOGIN dmDBuser
    WITH PASSWORD = 'dmDBpswd';
    USE dm;
    exec sp_changedbowner 'dmDBuser'
    GO 
    CREATE LOGIN gcDBuser
    WITH PASSWORD = 'gcDBpswd';
    USE gc;
    exec sp_changedbowner 'gcDBuser'
    GO 
    CREATE LOGIN dwDBuser
    WITH PASSWORD = 'dwDBpswd';
    USE dw;
    exec sp_changedbowner 'dwDBuser'
    GO 
  3. Change the collation of the databases. CLM databases require case-sensitivity and the default for SQL Server is case insensitive collation. From the command line tool, enter these commands. Note that SQL_Latin1_General_CP437_CS_AS is an example and any CS_AS collation can be used:
    ALTER DATABASE jts COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE ccm COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE qm COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE rm COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE dcc COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE lqe COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE ldx COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE relm COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE dm COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE gc COLLATE SQL_Latin1_General_CP437_CS_AS 
    GO
    ALTER DATABASE dw COLLATE SQL_Latin1_General_CP1_CS_AS
    GO
  4. Change the row version system for the JTS, CCM, QM, RM, DCC, LQE, LDX, RELM, DM, GC, and DW databases. From the command line tool, run these commands:
    Note: Ensure that the connection to the database is open before running these commands.
    ALTER DATABASE jts SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE ccm SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE qm SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE rm SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE dcc SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE lqe SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE ldx SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE relm SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE dm SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE gc SET READ_COMMITTED_SNAPSHOT ON
    GO
    ALTER DATABASE dw SET READ_COMMITTED_SNAPSHOT ON
    GO
  5. Create an environment variable named SQLSERVER_JDBC_DRIVER_FILE and point to the sqljdbc_4.1.jar JDBC driver. The location of the driver varies depending on your installation. For Windows operating system
    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_dirver\sqljdbc_4.1.jar.
    For Unix operating systems
    1. On Unix systems, add the following environment variable declaration:
      SQLSERVER_JDBC_DRIVER_FILE =.:Path_to_JDBC_dirver/sqljdbc_4.1.jar
    Note: If you are using WebSphere Application Server, you must also add a custom property that points to the sqljdbc_4.1.jar JDBC driver. For more information, see Setting up WebSphere Application Server.
  6. To configure your databases connections and create database tables complete one of these steps:
    1. If you are using Liberty or Tomcat, start the server and continue with Running the setup by using Custom setup in the setup wizard.
    2. If you are deploying WebSphere Application Server, see Deploying and starting WebSphere Application Server, and then continue with Running the setup by using Custom setup in the setup wizard.
    3. If you prefer to manually modify the teamserver.properties file for database connection and run the repotools commands to create database tables, see Manually configuring an SQL Server database.
    4. If you are upgrading from a previous version, continue to run the appropriate upgrade scripts which will migrate your existing database connections. For the data warehouse, you can modify the information to connect to the database while configuring the server after the upgrade.

Troubleshooting

If the repotools command does not work, check the appropriate log files, which are located in the JazzInstallDir/server directory.

Also, 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. For the LQE and LDX applications the properties file is dbconnection.properties.
  • 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 using 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: The database code for Jazz Team Server was designed to be generic and standard in order to run on multiple database platforms; however, differences between the enterprise databases exist in syntax, optimization strategies, and locking semantics. The server has been 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 have been observed in automated concurrency tests; however, these have not been consistently reproduced on different server hardware.

The server is designed to handle a large number of simultaneous requests. In exceptional cases, deadlocks can occur in the repository database, as similar or equal objects are updated concurrently in transactions that are associated with the requests. These are rare, but typical events. The system is designed to be 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 retried. A deadlock can result in a failed build. In very rare cases, a user action, such as working with work items or source code, is stopped to resolve a deadlock. In that case, the user action fails and an error message with details regarding the deadlock is displayed to the user. The user can then try the action 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.


video icon Watch videos

CLM playlist
Jazz.net channel
User Education channel

learn icon Learn more

CLM learning circle
Agile learning circle
Learning circles

ask icon Ask questions

Jazz.net forum
developerWorks forums

support icon Get support

Support Portal
Deployment wiki
Support blog