Best practices for Azure SQL Data Sync

Alberto Jimenez 1 Reputation point
2021-11-22T19:53:33.827+00:00

Hello,

Why is a best practice to install the client agent (SQL Data Sync 2.0) on a computer that isn't the SQL Server computer?

I'm leading a project that has data on-premises and on Azure, the goal is to have the data on Azure, I can't migrate on-premises to the cloud at this point, so I have to sync data each hour to Azure, what's the best practice to do this? I'm using now Azure SQL Data Sync.

Thank you.

Azure SQL Database
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,886 Reputation points MVP
    2021-11-22T21:03:52.267+00:00

    It is said that reliability and security reasons, one shoukd install de agent using the least privilege account with network service access, it is best if the client agent is installed on a computer separate from your on premises SQL Server computer, and do not register an on-premises DB with more than one agent. Here you will find a good reason to install the agent on separate computer when troubleshooting issues with SQL Data Sync.

    In addition, try to avoid synchronization loops. A synchronization loop results from an overlapping of databases in two or more sync groups such that a change in a database in one sync group is re-written to the same database by another sync
    group. Synchronization loops are self-perpetuating and can result in large amounts of data repeatedly overwriting identical data in two of more databases.

    Any of the following can cause a synchronization loop.

    • A circular reference within a database or table.
    • A circular reference involving two or more sync groups.
    • A single database registered with different agents and added to a sync group under two of
    • more agents.

    Best Practices before first Data-Sync. When you create a sync group, be sure that only one of the databases contains data before the first synchronization. If multiple databases include data, each current row is treated as a data conflict, even if the data is identical. Data conflicts significantly slow down a synchronization. Depending on the size of your DB, if multiple databases are populated with data, the first sync can take days or weeks. Conflict resolution also requires numerous round trips between the databases. If your databases are in different data centers, these round trips can add significantly to your ingress and egress charges.

    More best practices are outlined on Microsoft documenattion here.