Set up a SQL Server database to work with the Jazz™ Team Server.
This procedure assumes that the following prerequisites have been
met:
- You have SQL Server installed
- SQL Server service is started
- You have the SQL Server JDBC drivers installed. For more information,
see http://msdn2.microsoft.com/en-us/data/aa937724.aspx
- The TCP protocol is enabled for SQL Server
- The user who creates the DB table must have one of the following
permissions:
- A membership in the sysadmin fixed server role, or ownership
of the database (dbo)
Remember: The default login name defined in JazzInstallDir/server/teamserver.properties file
for a SQL Server database is jazzDBuser and
the default password is jazzDBpswd. These values
are used in the steps below for creating a database. You can substitute
these values with the user name and password for your database.
Setting up a database
This section describes
how to set up the SQL Server database by using the command line tool sqlcmd,
which is provided with the SQL Server installation. 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 an SQL Server database
administrator (DBA) for more information.
To set up an SQL Server
database by using the command tool sqlcmd:
- Create a Jazz Team Server database.
From the command line tool, type:
CREATE DATABASE jazz
GO
- Create a user and password and change the ownership of the Jazz Team Server database.
From the command line tool, type:
CREATE LOGIN jazzDBuser
WITH PASSWORD = 'jazzDBpswd';
USE jazz;
exec sp_changedbowner 'jazzDBuser'
GO
- Change the collation of the Jazz Team Server database.
From the command line tool, type:
ALTER DATABASE jazz COLLATE SQL_Latin1_General_CP437_CS_AS
GO
Configuring the server
Remember: By
default, the teamserver.properties file in JazzInstallDir/server/conf/jazz directory
is not set up to connect to a SQL Server database. All default settings,
along with SQL Server-specific settings, are contained in the teamserver.sqlserver.properties file
in the same directory.
To configure the server:
- Rename the following files:
- Rename the JazzInstallDir/server/conf/jazz/teamserver.properties file
to teamserver.OtherDBProvider.properties
- Rename the teamserver.sqlserver.properties file
to teamserver.properties
Tip: Renaming the original teamserver.properties file
enables you to restore the file to the previous working version. By
default, the DB Provider is derby.
- Edit the server configuration file if you used a different user
name, password, or db name, installed SQL server on a different machine
than Jazz Team Server,
or are using a port other than the default. Open the file teamserver.properties in JazzInstallDir/server/conf/jazz directory,
and change the information listed below to match your setup.
com.ibm.team.repository.db.vendor = SQLSERVER
com.ibm.team.repository.db.jdbc.location=//SQL SERVER MACHINE IP:SQL SERVER TCIP PORT;databaseName=JAZZ DATABASE NAME;user=JAZZ USERNAME;password={password}
com.ibm.team.repository.db.jdbc.password=JAZZ LOGIN PASSWORD
Important: Do not change the password={password}
text in the property com.ibm.team.repository.db.jdbc.location.
The user password must be specified in the property com.ibm.team.repository.db.jdbc.password.
Important: For Windows only. Ensure that your server
security is in mixed mode. You can verify this by checking the server
security. It should be set to "SQL Server and Windows Authentication
Mode".
To specify the jar file location for JDBC:
- In the same directory as the server scripts (JazzInstallDir/server),
create a directory named sqlserver.
- Locate the SQL Server JDBC driver JAR file, named sqljdbc.jar.
The location can vary depending on where the drivers were installed,
as noted in the prerequisites section. Copy the file into the newly
created sqlserver directory.
- Specify the JDBC JAR file location for JDBC by completing the
following task:
- Set up the environment variable SQLSERVER_ABSPATH to
point to the directory that contains the JAR file. If the path contains
space characters, you must include them with quotations.
Tip: If
you use WebSphere® Application Server, configure
a property named SQLSERVER_JDBC; then set it to
the absolute directory path (not URL) that contains the SQL Server
JDBC drivers, for example, if your sqljdbc.jar is
located under JazzInstallDir/server/sqlserver.
For more information, see the WebSphere Application Server setup instructions.
Note the file path for later use in these instructions.
Troubleshooting
If the repotools command
does not succeed, check the log file named repotools_createTables.log,
located in the JazzInstallDir/server directory.
Also,
verify the following:
- The SQL Server is configured to allow connections using the TCP/IP
protocol. If required, restart the SQL service.
- The SQL Server machine host name, and the port is correctly reflected
in the teamserver.properties file.
- The login name, password, and database name are properly reflected
in the teamserver.properties file.
- A firewall does not prevent you from accessing the SQL Server.
Use a telnet command to telnet to the machine using hostname and port
from the teamserver.properties file.
- The created user has proper permissions in the Jazz Team Server database.
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 database vendors
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 service 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 associated with the requests. These are
rare, but normal events. The system is designed to be defensive against
this, 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 re-tried.
The deadlock can result in a failed build. In very rare cases, it
can be a user action, such as working with work items or source code,
that is aborted to resolve a deadlock. In that case, the user action
fails and an error with details regarding a deadlock is displayed
to the user. The user can then retry the action.
All deadlocks
encountered are logged on the server to provide information about
what happened when the deadlock occurred. The log entry has text similar
to the following: com.ibm.team.repository.common.RetryableDatabaseException:
Serialization failure.
If your system encounters
deadlocks, see the error log, which is available at https://servername:9443/jazz/service/com.ibm.team.repository.common.internal.IFeedService?category=SystemLog.
If
you encounter frequent deadlock exceptions, contact your product support,
or check the forums on jazz.net for the latest information, updates,
or mitigating techniques.