Setting up an SQL Server database

Setting up a database

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.
   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.
   
   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.