Skip to main content

Jama Connect User Guide

Install and configure Microsoft SQL Server (upgrading traditional to KOTS)

If you are using Microsoft SQL Server for your database, follow these steps to install and configure it.

Important considerations

  • You must have full database admin permissions to the server hosting the SQL Server database.

Important

The following steps apply only if you are creating a new database instance to support the KOTS upgrade.

To install and configure SQL Server:

  1. Connect to the SQL Server using a SQL management application (such as SQL Server Management Studio).

  2. Replace the following values in the installation script: <JAMA_LOGIN_Psswd>, <SAML_LOGIN_Psswd> & <OAUTH_LOGIN_Psswd>.

  3. Copy and store the passwords you create here. You will need them later to configure the Admin Console settings.

  4. In a new query window, run this SQL query script:

    -- Fresh Install Preparation SCRIPT
/*

INSTRUCTIONS:
This script must be run prior to Jama installation or installation might fail to 
complete.
Modify login passwords as needed. 
Passwords must be enclosed in single quotes.
*/

USE master;
CREATE LOGIN jamauser with password = 'password';
CREATE LOGIN samluser with password = 'password';
CREATE LOGIN oauthuser with password = 'password';
GO

USE master;
CREATE DATABASE jama;
GO
ALTER DATABASE jama SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO

USE jama;
EXEC ('CREATE SCHEMA oauth');
EXEC ('CREATE SCHEMA saml');
GO

USE jama;
CREATE USER jamauser for LOGIN jamauser;
CREATE USER samluser for LOGIN samluser with DEFAULT_SCHEMA=saml;
CREATE USER oauthuser for LOGIN oauthuser with DEFAULT_SCHEMA=oauth;
GO

EXEC sp_addrolemember N'db_owner', jamauser;
EXEC sp_addrolemember N'db_owner', samluser;
EXEC sp_addrolemember N'db_owner', oauthuser;
GO

  5. Create a database schema for Quartz to support horizontal scaling in KOTS:

    USE master;      
CREATE LOGIN quartzuser with password = 'password';      
GO             

USE jama;      
EXEC ('CREATE SCHEMA quartz');      
GO             

USE jama;      
CREATE USER quartzuser for LOGIN quartzuser with DEFAULT_SCHEMA=quartz;      
GO             

EXEC sp_addrolemember N'db_owner', quartzuser;            
GO

  6. Confirm that these actions were successful:

    • Script completed — Check the Query Execution results for errors.

    • Users created — Run the following SQL script in a new query window.

      USE jama
SELECT * from master.sys.sql_logins
SELECT * from Jama.sys.sysusers

      The results include jamauser, samluser, and oauthuser in the "Name" column of the result panes.

    • Users granted the DB_owner role — Run the following SQL script in a new query window.

      USE jama
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

      The results show that db_owner role is granted to jamauser, samluser, and oauthuser.

  7. Keep the database from locking users' accounts while they are logging in or working in Jama Connect (you must have db_owner permissions):

    ALTER DATABASE jama SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

  8. Make sure the flag was successfully enabled:

    SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name='jama';

    If the returned value is 1, the flag is on.

Follow the instructions to Restore backup data from traditional Replicated to KOTS.

In this section: