Steps to Use MS-SQL DB on a different server and synchronizer application on a different server:
Server1: Synchronizer server.
Server2: MS-SQL server.
...
In this article, you can find instructions on setting up the environment to use the TFS4JIRA synchronizer app installed in a different machine/server of the database, in this case, MS-SQL DB.
Instructions:
The below keys are used to differentiate the two servers:
Server-1: TFS4JIRA Synchronizer Server.
Server-2: MS-SQL Database Server.
Initially, on Server-1, install Microsoft® SQL Server® Compact 4.0 SP1 - download link:https://www.microsoft.com/en-us/download/details.aspx?id=30709 in the synchronizer application server (Server1Server-1).
INSTALLATION OF TFS4JIRA STEPS HERE ON SERVER-1
Next, try to open tfs-jira-synchronizer using http://localhost/tfs-jira-synchronizer. If the synchronizer is opened, then Install the Export SQLCe tool in the synchronizer application server (Server1) on Server-1.
Stop the TFS4Jira Synchronizer application pool in IIS in the synchronizer application server (Server1)on Server-1.
Export SQL CE db into a .sql script (schema and data) in the synchronizer application server (Server1).
Run the following command in the directory where you downloaded the export sqlce tool(it requires opening the terminal as administrator).
...
on Server-1.
On Server-1run the command:
C:\Users\Administrator\Downloads\ExportSqlCe40.exe "Data Source=C:\inetpub\wwwroot\tfs-jira-synchronizer\App_Data\config.sdf" Synchronizer.sqlin the directory where you downloaded the export SQLCE tool (it requires opening the terminal as administrator).Once the command is executed, the Synchronizer.sql file is created in path
C:\inetpub\wwwroot\tfs-jira-synchronizer\App_Data\
...
.
Now on Server-2, create a new DB in MSSQL
...
using the below script.
The PASSWORD = 'YOUR_PASSWORD' should be changed as per your standards
...
.
| Code Block |
|---|
sp_configure 'contained database authentication', 1; GO RECONFIGURE; GO -- Create database for synchronizer CREATE DATABASE TFS4JIRA; GO -- enable contained db auth on the level of a db Use MASTER; GO ALTER DATABASE TFS4JIRA SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE TFS4JIRA SET containment=partial; ALTER DATABASE TFS4JIRA SET MULTI_USER; GO SELECT containment_desc FROM sys.databases WHERE name='TFS4JIRA' -- create user with password, contained in the db USE [TFS4JIRA] CREATE USER TFS4JIRA_Synchronizer_User WITH PASSWORD = 'B$3K94ynVQf7aDYOUR_PASSWORD'; GO -- add the user to the db_owner role - because EF will alter schema on migrations -- (otherwise db_data_reader and _writer would be enough) USE [TFS4JIRA] ALTER ROLE db_owner ADD MEMBER TFS4JIRA_Synchronizer_User GO |
Enable the “SQL Server and Windows Authentication mode”
...
on Server-2.
...
Open SSMS
...
Right-click the server and choose Properties.
...
Open the “Security” section.
...
Change the “Server Authentication” option to "SQL Server and Windows Authentication Mode" and click “OK”
...
Restart SQL Express instance to apply authentication changes
Copy the Synchronizer.sql file from
...
Command: in (server2) in command prompt
...
Server-1 to Server-2.
Run the following command in Server-2 command prompt:sqlcmd -S <your-device-name>\SQLEXPRESS,1433
...
-U
...
TFS4JIRA_Synchronizer_User
...
-P
...
B$3K94ynVQf7aD
...
-d
...
TFS4JIRA
...
-i
...
C:\Users\Administrator\Desktop\Synchronizer.sqlUpdated connection string in Web.config
...
on Server-1.
Update the “forced type” application setting by changing its value to “SyncProfileContextExpressConnection.”
Change the value for SyncProfileContextExpressConnection in <connectionStrings>
...
<add
...
name="SyncProfileContextExpressConnection"
...
connectionString="Data
...
Source=<your-device-name>\TFS4JIRA,1433;
...
Database=TFS4JIRA;
...
User
...
Id=TFS4JIRA_Synchronizer_User;
...
Password=B$3K94ynVQf7aD;
...
MultipleActiveResultSets=True"
...
providerName="System.Data.SqlClient"
...
/>Restart the TFS4Jira Synchronizer application pool in IIS in the synchronizer application serveron Server-1.