Page Comparison

Prerequisites:

• Install SQL Express (steps below)
• (optional) Install SQL Server Management Studio(SMSS)
• Install Export SQLCe tool

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 via Group Policy require strong passwords

MIGRATION

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):

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

   
   

   Info
   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
   1. 
4. (optional) open generated file using SSMS and make sure that file is not empty, you should see SQL commands creating schema and adding data in file.
   1. 

Create new DB in SQLExpress

1. download script file: DB_creation.sql

2. in folder containing above script please create a BAT file or run command:

   Code Block
   language actionscript3 theme RDark
   sqlcmd -S localhost\tfs4jira -d MASTER -i DB_creation.sql -E -e > DB_creation.txt

   
   

3. DB_creation.txt will be created with in the  same folder with script result, example entry:
   1. 

Populate TFS4JIRA database with SQL

...

CE database schema and content

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

   Code Block
   language actionscript3 theme RDark
   sqlcmd -S localhost\TFS4JIRA -d TFS4JIRA -i Synchronizer_SQLCE_DB.sql -E -e -x > DB_migration.txt

   
   

2. Result in DB_migration.txt

   
   

   Warning

   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 -b : on error batch abort - process will stop on error -e : echo input -x : disable variable substitution

   
   

Update migrations history

1. Downlod SQL script: Update_migrations_history_for_SQL_Express.sql

2. Execute above sccript 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):

   Code Block
   language actionscript3 theme RDark
   sqlcmd -S localhost\TFS4JIRA -d TFS4JIRA -i Update_migrations_history_for_SQL_Express.sql -E -e > DB_migration_history.txt

   
   

3. Result in DB_migration_history.txt

...

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" />
   3. Image Added

Start TFS4Jira Synchronizer application pool in IIS

...

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

NOTES

...