Skip to main content

Jama Connect User Guide

Upgrade and configure Microsoft SQL Server

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

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

If you want to install the Microsoft SQL server for the first time, see Install and configure Microsoft SQL Server.

Before upgrading

Before upgrading to Jama Connect 8.62.x, you must:

  • Be running a version of Jama Connect that's earlier than 8.62.x and using Microsoft SQL 2016-2019 for the database server.

  • Create two additional database schemas for the installation/upgrade to succeed.

Organizations using Microsoft SQL Server must enter database users in Replicated. Without these entries, the upgrade will fail.

The new schema must be created for a successful installation. Otherwise, the system continues to attempt to connect to the databases and produces log failures. After you create the database schemas, you must restart Jama Connect.

For more information, go to Supported software, environments, and system requirements and select your version of Jama Connect.

To upgrade and configure Microsoft SQL Server:

  1. Use a SQL management application (such as SQL Server Management Studio) to connect to the SQL Server.

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

    Note

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

  3. In a new query window, run the following SQL query code:

    -- UPGRADE SCRIPT
/*
Jama Connect Upgrade Commands. Required to run these on the 
Microsoft SQL Server BEFORE running the Jama Connect 8.62.x Upgrade 
for ON-PREM installation using Microsoft SQL Server
DATE: 05/10/2021
NOTES: This script assumes it is running against a pre-existing installation 
with the JAMA Database already present on the Server.
The Script will add 2 new schemas (empty) to the Jama Database, 2 
new DB Logins and Database users to support the Multi-Auth 
functionality released in Jama Connect 8.6.2.
This script must be run prior to actual Jama Upgrade or the upgrade
installation may fail to complete.
INSTRUCTIONS: Ensure a Full backup of the Jama database is made before execution.
Modify the <SAML_LOGIN_Password> & <OAUTH_LOGIN_Password> values in the 
script below before Execution. Passwords must be enclosed in single 
quotes.

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

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

USE jama;
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', samluser;
EXEC sp_addrolemember N'db_owner', oauthuser;
GO

  4. Confirm that these actions were successful:

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

    2. 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 samluser and oauthuser in the "Name" column of the result panes.

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

      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 will show that db_owner is granted to oauthuser and samluser.

  5. Run the following query to 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;

  6. Run this question to 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.

In this section: