TFS4JIRA Self-Hosted | Empty SQL express database creation and usage

Owned by Damian Kilkowski (Inactive) (Deactivated)
Last updated: May 31, 2023
3 min read

This instruction will only work with TFS4JIRA Self-Hosted 10 or higher.

When TFS4JIRA Self-hosted is newly setup, users may choose to use SQL express database instead of the default SQL Compact Edition database. This article explains how to setup and start using SQL express database with TFS4JIRA.

Prerequisites:

SQL EXPRESS 2019 installation

  1. Download the installer for your system: SQL Express

  2. Start CUSTOM installation

  3. Choose: New SQL Server stand-alone installation or add features to an existing installation

    1. (optional) - do not install Machine Learning Services

    2. Named instance: TFS4JIRA

    3. Collation: SQL_Latin1_General_CP1_CI_AS

    4. Authentication mode: Mixed mode

    5. SA password: <password>

      1. please setup a custom, strong password

      2. some Windows systems require strong passwords via Group Policy 

MIGRATION

This manual operates on the default user credentials used in TFS4JIRA (provided out of the box). Users may choose to setup different user credentials, especially password that will be used by the application. Here are the password locations that must be corrected:

  1. empty_db.sql script

  2. web.config, <connectionString> section for "SyncProfileContextExpressConnection" 

 

Users may choose to run the scripts provided below using SQL Server Management Studio (SMSS) instead of SQLCMD commands.

Stop TFS4JIRA Synchronizer application pool in IIS using IIS Manager (cmd: inetmgr)

Create new DB in SQLExpress

  1. Download the script file: empty_db.sql

  2. In the folder containing above script please create a BAT file or run this command:

    sqlcmd -S localhost\tfs4jira -d MASTER -i empty_db.sql -E -e -x > empty_db.txt

  3. Empty_db.txt will be created in the  same folder with script result.

Update connection string in web.config (default location: C:\inetpub\wwwroot\tfs-jira-synchronizer)

  1. Update "forceDbType" application setting by changing its value to "SyncProfileContextExpressConnection".

    1. Search for: <add key="forceDbType" value="SyncProfileContextConnection" />

    2. Replace with: <add key="forceDbType" value="SyncProfileContextExpressConnection" />

Start TFS4Jira Synchronizer application pool in IIS

MIGRATION VALIDATION

In order to validate that TFS4JIRA Synchronizer uses SQL Express you can add a new profile or rename one of the existing ones. After that please query "SyncProfiles" using below command

sqlcmd -S localhost\TFS4JIRA -d TFS4JIRA -q "select * from SyncProfiles" -E -e > DB_migration_validation.txt

You should see your changes in DB_migration_validation.txt. Example content: