How to migrate from SQLCE to SQL Express

Please reach out to Appfire support in case of any questions or issues concerning the below manual.

Contact details: Need support

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. DB_creation.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)

Export SQL CE database into an .sql script (schema and data)

  1. Download Export SQLCe tool (unzip to find .exe file)
  2. Run the following command in the directory where you have downloaded Export SQLCe tool (remember to start terminal as the administrator):

    ExportSqlCe40.exe "Data Source=C:\<tfs4jira synchronizer installation directory>\App_Data\config.sdf;Max Database Size=4000;" Synchronizer_SQLCE_DB.sql

    When the resulting script size exceeds 18,528 KB, ExportSqlCe40.exe tool automatically breaks down the output files into chunks with index numbers. Check the table below for the expected output and run time:

    config.sdf file size

    # of chunks / .sql file count

    Total size of .sql scripts

    Data migration duration

    787 MB

    75

    1.31 GB

    ~1.5 hour

    1.53 GB

    149

    2.62 GB

    ~2.5 hour

    3.05 GB

    298

    5.25 GB

    ~5 hours

  3. Outcome example
  4. (optional) Ppen generated file using SSMS and make sure that file is not empty, you should see SQL commands creating schema and adding data in file.

Create new DB in SQLExpress

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

    sqlcmd -S localhost\tfs4jira -d MASTER -i DB_creation.sql -E -e > DB_creation.txt
  3. DB_creation.txt will be created in the  same folder with script result, example entry:

Populate TFS4JIRA database with SQL CE database schema and content

  1. In the directory where you have exported SQL CE Database content to (location of Synchronizer_SQLCE_DB.sql file), execute the following command:

    sqlcmd -S localhost\TFS4JIRA -d TFS4JIRA -i Synchronizer_SQLCE_DB.sql -E -e -x > DB_migration.txt
  2. Result will be saved into DB_migration.txt

    In order to import all the chunks in to SQL express database with single command please run below. Note that all chunks are placed in the EXPORT folder in this example:

    • for /f %f in ('dir /b .\EXPORT') do sqlcmd -S localhost\tfs4jira -d TFS4JIRA -i .\EXPORT\%f -E -b -e -x > DB_migration_%f.txt
    • if used in BAT file: for /f %%f in ('dir /b .\EXPORT') do sqlcmd -S localhost\tfs4jira -d TFS4JIRA -i .\EXPORT\%%f -E -b -e -x > DB_migration_%%f.txt

    -b : on error batch abort - process will stop on error
    -e : echo input
    -x : disable variable substitution

Update migrations history

  1. Download SQL script: Update_migrations_history_for_SQL_Express.sql
  2. Execute the script on SQL Express database, either by using the command below or by opening this file using SSMS and executing it there (run the command from the script location):

    sqlcmd -S localhost\TFS4JIRA -d TFS4JIRA -i Update_migrations_history_for_SQL_Express.sql -E -e > DB_migration_history.txt
  3. Result is saved into DB_migration_history.txt

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:

ROLLBACK

In case something goes wrong or there will be problems with SQLExpress database, you can always go back to use SQL CE database. In order to do so follow this steps:

  1. Stop TFS4Jira Synchronizer application pool in IIS
  2. Change "forceDbType" application settings in Web.config back to "SyncProfileContextCeConnection"
  3. Start TFS4Jira Synchronizer application pool in IIS

Please reach out to Appfire support in case of any questions or issues concerning below manual.

Contact details: Need support

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. DB_creation.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)

Export SQL CE database into an .sql script (schema and data)

  1. Download Export SQLCe tool (unzip to find .exe file)
  2. Run the following command in the directory where you have downloaded Export SQLCe tool (remember to start terminal as the administrator):

    ExportSqlCe40.exe "Data Source=C:\<tfs4jira synchronizer installation directory>\App_Data\config.sdf;Max Database Size=4000;" Synchronizer_SQLCE_DB.sql

    When the resulting script size exceeds 18,528 KB, ExportSqlCe40.exe tool automatically breaks down the output files into chunks with index numbers. Check the table below for the expected output and run time:

    config.sdf file size

    # of chunks / .sql file count

    Total size of .sql scripts

    Data migration duration

    787 MB

    75

    1.31 GB

    ~1.5 hour

    1.53 GB

    149

    2.62 GB

    ~2.5 hour

    3.05 GB

    298

    5.25 GB

    ~5 hours

  3. Outcome example
  4. (optional) Ppen generated file using SSMS and make sure that file is not empty, you should see SQL commands creating schema and adding data in file.

Create new DB in SQLExpress

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

    sqlcmd -S localhost\tfs4jira -d MASTER -i DB_creation.sql -E -e > DB_creation.txt
  3. DB_creation.txt will be created in the  same folder with script result, example entry:

Populate TFS4JIRA database with SQL CE database schema and content

  1. In the directory where you have exported SQL CE Database content to (location of Synchronizer_SQLCE_DB.sql file), execute the following command:

    sqlcmd -S localhost\TFS4JIRA -d TFS4JIRA -i Synchronizer_SQLCE_DB.sql -E -e -x > DB_migration.txt
  2. Result will be saved into DB_migration.txt

    In order to import all the chunks in to SQL express database with single command please run below. Note that all chunks are placed in the EXPORT folder in this example:

    • for /f %f in ('dir /b .\EXPORT') do sqlcmd -S localhost\tfs4jira -d TFS4JIRA -i .\EXPORT\%f -E -b -e -x > DB_migration_%f.txt
    • if used in BAT file: for /f %%f in ('dir /b .\EXPORT') do sqlcmd -S localhost\tfs4jira -d TFS4JIRA -i .\EXPORT\%%f -E -b -e -x > DB_migration_%%f.txt

    -b : on error batch abort - process will stop on error
    -e : echo input
    -x : disable variable substitution

Update migrations history

  1. Download SQL script:  Update_migrations_history_for_SQL_Express.sql
  2. Execute the script on SQL Express database, either by using the command below or by opening this file using SSMS and executing it there (run the command from the script location):

    sqlcmd -S localhost\TFS4JIRA -d TFS4JIRA -i Update_migrations_history_for_SQL_Express.sql -E -e > DB_migration_history.txt
  3. Result is saved into DB_migration_history.txt

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:

ROLLBACK

In case something goes wrong or there will be problems with SQLExpress database, you can always go back to use SQL CE database. In order to do so follow this steps:

  1. Stop TFS4Jira Synchronizer application pool in IIS
  2. Change "forceDbType" application settings in Web.config back to "SyncProfileContextCeConnection"
  3. Start TFS4Jira Synchronizer application pool in IIS