Slow Initial Sync for SQL Azure Data Sync

BtempletonSSI 96 Reputation points
2021-06-22T17:45:31.163+00:00

We have and On Prem DB syncing to an Azure SQL DB with 5 Sync Groups -- that works pretty well -- We have a need to pull a copy of the Azure SQL DB back down to another server, I have tried to establish another sync group and some of the tables do not do BULK INSERTS for the intial sync, so they are very slow. Other tables work fine. All destination tables are empty before starting. (The data in Azure SQL DB has additional tables and and some of the on-prem to Azure is one-way with data being allowed to change in Azure sQL DB, this is why we need locally) I am wondering if the issues is the tables that are not able to do BULK INSERTs if it si because they also participate in another sync group?

Azure SQL Database
0 comments No comments
{count} votes

Accepted answer
  1. BtempletonSSI 96 Reputation points
    2021-06-30T19:18:55.237+00:00

    So to summarize -- to have a fast initial sync, a table cannot be in multiple sync groups. A sync Group cannot have some tables in a 2-way since and others in a 1-way sync. So this desired state is not something Azure Datasync can currently handle.

    If this is not the case, please correct me.

    Thanks

    Brent


2 additional answers

Sort by: Most helpful
  1. Alberto Morillo 32,791 Reputation points MVP
    2021-06-22T21:06:33.587+00:00

    It seems synchronization loops are the cause of the poor performance on the initial sync. Design to avoid synchronization loops. A synchronization loop results when there are circular references within a sync group so that each change in one database is replicated through the databases in the sync group circularly and endlessly. Any row that is synchronized by one sync group cannot be synchronized by another sync group. You want to avoid synchronization loops as they degrade performance and can significantly increase your costs.

    Another suggestion, make sure all tables you put on a sync group are related to each other.


  2. BtempletonSSI 96 Reputation points
    2021-06-28T20:51:05.967+00:00

    MemberDB1 and HubDB some tables are 2-way and some are 1-way, there are not duplicated tables

    Then From HubDB to MemberDB2 we want to push to another server in a 1-way sync --

    MemberDB1.TableABC -> HubDB.TableABC -> MemberDB2.TableABC
    MemberDB1.TableXYZ <-> HubDB.TableXYX -> MemberDB2.TableXYZ
    etc

    There are some tables that are not in MemberDB1 but present in HubDB that we will want on MemberDB2, but I don't see those as a problem.

    Hope that clears up what we are trying to do.

    Thanks

    Brent