Azure SQL Database Data Sync, Monitoring, Alerts & Scheduled Jobs
Dashboard Pricing         Docs         Support         Sign In

How to Synchronize Data from SQL Server to Azure SQL Database

Cotega Data Sync is a high performance method of synchronizing data one-way from a source SQL Server or Azure SQL database to another. The following document outlines how to synchronize data from a SQL Server database to an Azure SQL database where the source SQL Server is located in your company or in the cloud, although this same method could be used to synchronize to a hosted SQL Server database. The data sync process can either run continually or on a schedule.


This service is currently in beta and needs to be enabled for you. To enable Cotega Data Sync for your account, please contact us with your Cotega account name.




Requirements

In order to use Cotega Data Sync, you will require the following on the Cotega Data Sync client machine:

  1. SQL Server 2008 SP2 or higher on client machine.
  2. Windows machine to host Cotega Data Sync client with connectivity to SQL Server. Note: Cotega Data Sync client can alternatively be installed on SQL Server machine.
  3. Azure SQL database to synchronize your data to.
  4. .NET Framework 4.0
  5. CLR Types
  6. x86 Server Management Objects (SMO)

Installation

  1. From the machine you wish to install the Cotega Data Sync application, login to the Cotega Dashboard
    2. and ensure that you have added the source database you wish to synchronize from using the "Add Monitoring Agent" button
  2. If you have not already done so, you will need to allow the Cotega server with IP addresses 96.31.33.52 and 96.31.33.196 to access your Azure SQL server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for the IP address for 96.31.33.52 and 96.31.33.52
  3. Choose the "SQL Server Data Sync" button from the Cotega Dashboard.
  4. Choose "Add SQL Server to Azure SQL Data Agent" and choose a name for the Agent which uniquely defines the SQL Server that you will be synchronizing.
  5. Make note of this Data Sync Token.
  6. Choose “Download Data Sync Agent” to download a ZIP file containing Cotega Data Sync Client application. You can choose to unzip this directory on any machine that has connectivity to your SQL Server database.

Configuring Data Sync Client

  1. At this point you will have a directory containing the Cotega Data Sync Client downloaded from the Cotega dashboard.
  2. There are two files that you will need to configure, settings.json and synctables.json. Settings.json provides the details needed for synchronization such as the source and target database connection information as well as the specific sync parameters. SyncTables.json specifies which tables are to be synchronized.
  3. Before updating the settings.json file you will need to encrypt the passwords for your source and target database that are to be stored. To do open a command prompt to where you unzipped the Data Sync Client and run the command:

    4. datasynccmd.exe encrypt PASSWORD
    (where you will replace PASSWORD with your actual password)

  4. Make note of the resulting encrypted passwords that will be used in the settings.json file.
  5. Open the settings.json file in a text editor and update the values for each of the parameters. Here is some information on what each of these parameters are used for:
    • DataSyncToken: This value should be set to the Data Sync Token you made note of in Step 5 of "Installation"
    • SourceServer: This is the server name of the source SQL Server. Please note that if your SQL Server requires \ (backslashes), please use two \\ (backslashes). For example, if your SQL Server name is (local)\db1, you would enter a value of (local)\\db1
    • SourceTrustedConnection: This can be set to true or false and specifies whether to use a Trusted SQL Server connection or to use a user / password login.
    • SourceUser: If you specified false for SourceTrustedConnection this is the user name to use to connect to your SQL Server.
    • SourcePasswordEncrypted: This is the encrypted password for your source database that you generated from Step 3 of "Configuring Data Sync Client"
    • TargetServer: This is the Azure SQL server name and should include .database.windows.net
    • TargetDatabase: This is the Azure SQL database name
    • TargetUser: This is the user name to use to connect to your Azure SQL database.
    • TargetPasswordEncrypted: This is the encrypted password for your target database that you generated from Step 3 of "Configuring Data Sync Client"


      • The following parameters rarely need to be changed from their defaults and are only used for custom data synchronization requirements.

    • InsertOnly: This can be set to true or false and specifies if Cotega Data Sync should only upload Inserts (new rows) or if it should upload all changes (Inserts, Updates, Deletes) to the Azure SQL Database.
    • BatchSize: This is the number of data changes that are applied to your Azure SQL Database at one time. It is recommended to set this to 1000
    • SyncAgentID: This value should only be set if you are enabling SyncAgentTracking and when you have multiple SQL Server databases syncchronizing to a single Azure SQL database. It is a 3 letter value that will uniquely identify the data in the Azure SQL database so you can determine which database made the change.
    • SyncAgentTracking: This can be set to true or false and is only set to true in cases where multiple SQL Servers are synchronizing to a single Azure SQL Database.
    • TimestampTracking: This can be set to true or false and is used in the event that you want Cotega Data Sync to apply a timestamp column in the target tables that get updated whenever new data changes come in.
  6. Next open the synctables.json file and add all of the tables you wish to synchronize. The format of the tables should be Database.Schema.Table and you can use the examples in the existing synctables.json as template.
  7. Next you will need to enable Change Tracking in your SQL Server. To do this simply execute the command:

    datasynccmd.exe enablechangetracking

  8. Finally, you will want to create the necessary tables in your Azure SQL Database to receive the data changes. Please note that not only will there exist the tables that will host the data, but an associated Table Valued Parameter (TVP) will be created to help optimize data upload performance. Cotega Data Sync can generate the scripts needed by executing:

    datasynccmd.exe createscripts

  9. Once complete, you will see a new file called tables.sql that you can simply apply against your target Azure SQL Database.

Initiating Data Synchronization

At this point you have the Cotega Data Sync Client configured and are ready to initiate data synchronizataion. To do this you can run:


datasynccmd.exe sync


For this first data synchronizataion, a full data upload will be executed. For each subsequent execution of this command, only changes will be uploaded. In the event you ever need to resynchronize your data, this can be done by executing:


datasynccmd.exe resync



Scheduling Data Sync

Cotega Data Sync can be run by creating a Windows Scheduled Task. You can either open the Windows Scheduled Task window to create this scheduled task or execute the creation from the command line. Here is an example that enables data synchronization to occur every 2 minutes (/mo) and to run the data sync command located in the directory 'c:\CotegaDataSync', regardless of whether the user is logged in to windows or not:


schtasks /create /sc minute /mo 2 /RU system /tn "Cotega Data Sync" /tr c:\CotegaDataSync\run_sync.cmd



Viewing Historical Data Sync Logs

After you have executed a sync, the results will be logged. To view these logs including total rows synchronizes and errors:

  1. Login to the Cotega Dashboard
  2. Choose “Data Sync”.
  3. Click on "View Logs" for the agent you wish to see historical data sync logs for

FAQ

Question: I see Cotega Data Sync is in Beta. When can I use it in production?

Answer: We have a number of customers using Cotega Data Sync in production with full support from Cotega. If you would like to use Cotega Data Sync for production, please contact us.


Question: Can I synchronize data to Azure SQL Data Warehouse?

Answer: Yes, Cotega Data Sync can be used to synchronize new data (Inserts) to Azure SQL Data Warehouse, however changes (Updates or Deletes) will not be synchronized. Please note, for Azure SQL Data Warehouse you will need to set the parameter "InsertOnly" to true, and if you use the command "datasynccmd.exe createscripts" to generate scripts, you will need to remove the primary keys since Azure SQL Data Warehouse does not need or support primary keys.


Question: Can I synchronize data to another SQL Server database?

Answer: Yes, Cotega Data Sync can be used to synchronize data to a SQL Server database as long as the Cotega Data Sync Client has connectivity to this database.


Question: I need a solution that does not make any changes to my SQL Server database schema, will Cotega make and changes?

Answer: No, Cotega Data Sync does not make any changes to the schema of your SQL Server database. The only thing that Cotega does is to enable an option in SQL Server called "Integrated Change Tracking" which is an internal process that does not alter the schema and will not impact existing applications.


Question: Is the Data Sync Client Application sending data to the Azure Database directly via outbound port 1433?

Answer: Yes, it sends data directly to the Azure Database directly via port 1433


Question: Do I need to enable the firewall in my Azure SQL Database for the machine that has my Data Sync Client?

Answer: Yes, this is the machine that will connect to your database and upload the changes. If you go to a search engine like Bing or Google, and type "What is my IP", you will get back the IP address that would need to be added. In some cases this value will different than what you would get if you type ipconfig from the machine command line.