Setup procedure for SQL server database to work with Jazz Team Server

Content

If you have already have SQL Server setup on your computer please continue with section Jazz Setup. Otherwise, continue reading the Microsoft SQL Server Setup section.

Microsoft SQL Server Setup

Installation.

Note: If you do not have already a version of Microsoft SQL Server installed, you can use a free version of Microsoft SQL Server 2005 Express Edition (http://www.microsoft.com/sql/editions/express/default.mspx). Please note the content and the installation instructions are provided for that specific version. For general information regarding SQL Server 2005 please visit http://www.microsoft.com/sql/editions/express/default.mspx.

Download the SQL Server 2005 Express Edition and all the pre-requisites and follow these instructions http://www.microsoft.com/express/2005/sql/download/

Note: You need to have certain credentials on target machine in order to install SQL Server 2005.

In order to properly install SQL Server 2005 for Jazz we need to properly specify collation options that would support case sensitivity for Jazz data and SQL Server default installation is not case sensitive.

You can specify the collation options on entire server during installation or if the server is shared or Jazz is getting installed onto the existing server , you can specify collation for the specific database.

In order to set the proper collation for the entire server during the install you need to un-check “Hide Advanced Configuration Options”.

Choose “Mixed Mode” for Authentication.

Choose a case sensitive collation sequence such as "Latin1_General” with case sensitivity option.

To see all collation options you can refer to SQL Server documentation

Add the user account used for SQL Server installation to have SQL Server Administration role so we can create a special user for special Jazz database.

Once installed start SQL Server Configuration Manager and make sure that both SQL Server Service and SQL Server Browser both are running.

Jazz Setup

In order to satisfy Jazz server requirements you now need to create Jazz database and Jazz user. One can do it by hand executing SQL statements or using visual tool like SQL Server Management Studio Express (http://www.microsoft.com/DownLoads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en )

We will first provide instructions on how to complete Jazz Setup using visual SQL Server Management Studio Express following by command line SQL statements.

Creating Jazz User and Jazz Database

Once installed, start the application, go to Security/Logins, right click and choose “New Login” option. On the resulting page choose the login name that will be used by the Jazz server.

Note: The default login used in the teamserver.properties file in JazzInstallDir/server directory for SQL Server database is “jazzDBuser” and the password is “jazzDBpswd”.

Note: By default the teamserver.properties file in JazzInstallDir/server directory is not setup for the SQL Server. All settings are captured in the teamserver.sqlserver.properties file in the same directory. So start with renaming teamserver.sqlserver.properties into teamserver.properties.

So unless you plan to use different login and password that need to be propagated back to the teamserver.properties file in JazzInstallDir/server directory you can input jazzDBuser/ jazzDBpswd as the login and password.

Note: Uncheck “User must change password at next login “

Next you need to create database “Jazz” with the owner being a login that we just created.

Getting JDBC Drivers

The JDBC drivers for SQL Server need to be downloaded separately from

http://msdn.microsoft.com/en-us/data/aa937724.aspx and installed according to provider instructions.

Setting Up TCP Protocol and Ports

You need make sure that the TCP protocol is enabled for Jazz server to be able to connect to SQL Server.

Last thing is to check and set is the ports for network communication with SQL Server. In the teamserver.properties file in JazzInstallDir/server directory the port specified for SQL Server is 1433.

So you need to set it up or verify that it is setup in 2 places.

Note: By default the teamserver.properties file in JazzInstallDir/server directory is not setup for the SQL Server. All settings are captured in the teamserver.sqlserver.properties file in the same directory.

In SQL Server Configuration manager under SQL Native Client Configuration the Client Protocol option and under SQL Server 2005 Network Configuration the Protocols for SQLEXPRESS option.

Server Manual Setup executing SQL statements

Use this section only if you have not used the SQL Server Management Studio Express and installed Jazz DB using Command line tool.

To create a database use this command:

CREATE DATABASE jazz
GO
To learn more about create database options please refer to the documentation (http://msdn.microsoft.com/en-us/library/aa258257.aspx)

To create user and password use this command and change the ownership of the jazz database:

CREATE LOGIN jazzDBuser
WITH PASSWORD = 'jazzDBpswd';
USE jazz;
exec sp_changedbowner 'jazzDBuser'
GO
To learn more about create login options please refer to the documentation (http://msdn.microsoft.com/en-us/library/ms189751.aspx)

To change the collation of Jazz database

ALTER DATABASE jazz COLLATE SQL_Latin1_General_CP437_CS_AS
GO

To learn more about alter database options please refer to the documentation (http://msdn.microsoft.com/en-us/library/aa275464(SQL.80).aspx)
To learn more about collation options please refer to the documentation (http://msdn.microsoft.com/en-us/library/aa174903(SQL.80).aspx)

Running Repotools Command

If you followed all installation instructions from before then the only thing remaining in order to run the repotools command in order to generate Jazz-related data in the database you need to specify the location of JDBC driver to the repotools.bat file in JazzInstallDir/server directory.

Locate the file teamserver.properties in the directory JazzInstallDir/server and rename it to teamserver.<Other DB Provider>.properties.

Note: It is not critical to follow this direction. Choosing the name of teamserver .properties files with DB Provider makes it easier to restore to the previously working version. By default the DB Provider is “derby”.

Then rename teamserver.sqlserver.properties into teamserver.properties.

To specify the jar files location for JDBC:

setup the environment variable SQLSERVER_ABSPATH to point to the directory holding the jar file

Note: If the path has a space character somewhere in it, you need to include them with quotation

If you used different user name, password, and db name or installed SQL Server on the non-local machine make sure to change this information in the teamserver.properties file in JazzInstallDir/server directory.

com.ibm.team.repository.db.vendor = SQLSERVER

com.ibm.team.repository.db.jdbc.location=//<SQL SERVER MACHINE IP>:<SQL SERVER TCPIP PORT>;databaseName=<JAZZ DATABASE NAME>;user=<JAZZ USER NAME>;password={password}

com.ibm.team.repository.db.jdbc.password=<JAZZ LOGIN PASSWORD>

Troubleshooting

There could be numerous issues resulting in the repotools command not running properly. First check the log file named repotools_createTables.log located in JazzInstallDir/server directory.

Verify the following:

1. The SQL Server is using TCPIP connection, SQL Server machine host name and the port is correctly reflected in the teamserver.properties file
2. The login name, password, database name are properly reflected in the teamserver.properties file
3. Firewall does not prevent you from accessing SQL Server. Use telnet command to telnet to the machine using hostname and port from teamserver.properties
4. Created Jazz user has proper credentials in the Jazz database