Overview

DataSync is a service designed for Dispatch Science customers, enabling seamless data synchronization between the Dispatch Science cloud environment and any SQL Server database, hosted either on-premises or in the cloud. Please note, due to database size limitations, SQL Server Express edition is not supported.

DataSync securely supports incremental synchronization over HTTPS, using Azure Entra ID for robust authentication and access control.

Initial Synchronization

When executing the DataSync client for the first time, a full synchronization must be performed. This process involves two primary steps:

1. Snapshot Creation: A full database snapshot is created on the server if needed. After creation, this snapshot is downloaded locally and used to populate the destination database.
2. Initial Incremental Sync: Following snapshot creation, a larger incremental synchronization is performed, capturing all data changes since the snapshot was created. If a recent snapshot already exists, it will be reused, resulting in a larger incremental synchronization instead of a new snapshot.

The initial synchronization, particularly snapshot creation, can be time-intensive (over 10 hours for larger databases). However, once completed, subsequent synchronizations are typically swift, generally concluding within a few minutes.

Ongoing Synchronization

For ongoing incremental synchronizations, DataSync identifies and tracks all data changes in the Dispatch Science application. Each synchronization cycle only processes modified data. When synchronization runs, batch files with recent changes are generated server-side, downloaded, and processed by the client.

DataSync comprises two primary components:

• Web Service: Operates in the Azure Cloud environment.
• Windows Service Client: Connects to the web service, retrieves data updates, and applies them locally.

Supported Tables

This initial release offers limited table synchronization to validate architecture. Additional tables will be included swiftly upon successful validation. 

Client installation

Follow these steps to install and configure the DataSync client:

Download the latest DataSync client files from: https://datasyncexports.blob.core.windows.net/client/datasync.zip 

Unzip the downloaded archive to a machine with network access to your SQL Server database. The archive includes all files necessary for client operation.

In the folder containing the unzipped files, locate the file called Setup.ps1. To execute the file, you first need to unblock the file by checking the unblock option in the properties of the file. 

Unblock the script: Right-click the file → Properties → Check Unblock → Apply.

• (Optional) Depending on your policy, you may encounter this message when executing the script:

Simply press Y or A to run the script.

Open a PowerShell window in admin mode and navigate to the folder containing the installation script. Execute the script by running the following command: .\Setup.ps1

The installation script will open a browser allowing you to authenticate using the data sync user provided.  The service will be using that user to run the service.

After authentication, the installation wizard will launch (may appear behind the browser window).

Follow the wizard instructions to complete installation.

Provide the local database connection string for the database created in step 1, without any quotes.  The connection string supports all the options of the Microsoft.Data.SqlClient V3 format. 

Example Configuration: 

Data Source=MyLocalSQLServer;Database=DataSyncDb;Integrated Security=false;User ID=dbUserName;Password=dbPassword;TrustServerCertificate=True;Encrypt=True;

* Replace MyLocalSQLServer, DataSyncDb, dbUserName, and dbPassword with your actual server details. No other configuration changes are required.

                Press the Finish button when installation is complete to carry on with the rest of the setup.

The windows service files will be installed in this folder:  C:\Program Files\Dispatch Science\Datasync Service

You can validate that the service is running by opening the windows services utility services.msc. The service name is Dispatch Science DataSync Service.

Troubleshooting

For troubleshooting, review logs files located in the windows service directory. If you encounter issues, include log file along with a detailed description of the problem when seeking support.

In case you are using Integrated Security, which is not recommended, ensure the Windows service account has SQL database access.

Simply press CTRL-C on the script window and run it again.