This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(240, 44%, 33%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EB%3C/text%3E%3C/svg%3E)
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?
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
Azure SQL Data sync can handle it but the performance will be impacted (circular reference) due to conflicts on the rows which changes are stored on the DSS schema. If you want an acceptable performance you need to avoid one table on different sync groups. The initial sync on the first sync group will use BULK INSERT, but the inital sync for other sync groups using the same table created later will nout use BULK INSERT for first load, as it will find conflicts on the DSS schema
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.
sorry I didn't get notification of your reply. So my question would be what is the proper way to synchronize multiple tables to multiple databases?
Here is what I would like to do?
MemberDB1 (on prem) <Bidirectional sync & One way to Hub> HubDB <One Way sync to MemberDB2> MemberDB2
I tried also putting select table in same sync group and that didn't seem to make a difference.
Thanks
Brent
Let me see if I understand correctly, between MemberDB1 y Hub DB you have 2 types of sync (2-way & 1-way)? If that is correct then you have rows that are sync twice. There you have what is called a sync loop. Why you need that?
see response below --not sure why I added a new comment instead of reply.. :-)
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
What about the DTUs or CPU/Bandwitdh/RAM? Do you reach 100% usage while doing the sync?
yes, frequently max out DTU. -- but this occurred before trying to add the new 1-way to the memberdb2.
We have for now removed the 1-way to memberdb2 -- so am I correct that our desired state is not something currently possible with Datasync?
Thanks
Brent
You can sync one-way hub to member-database or member-to-hub. Both scenarios are supported
Are the databases on the same data center or not? that could also slow the performance of the initial sync
so how would I do that without causing the circular sync issue?
-> == one way sync direction
<-> == Bidirectional sync
repeating above example:
MemberDB1.TableABC -> HubDB.TableABC -> MemberDB2.TableABC
MemberDB1.TableXYZ <-> HubDB.TableXYX -> MemberDB2.TableXYZ
etc
A single sync group cannot be both bi-directional and 1-way.
thanks
no databases are not in same datacenter -- but issue is initial sync is NOT using bulk inserts for tables that are in multiple sync groups. Could tell be looking at commands being executed by sync agent account.
Memberdb1 (OnPrem) <-> HubDB (AzureSQLDB) -> MemberDB2 (OnPrem -- different server)
you can have 2 differrent sync groups. One one-way and the other bi-directional. You cannot set both optioncs for a sync member
When they are nnot in the same data center the performance gets affected.
So it does not appear that our desired sync setup is possible.
MemberDB1.TableABC [2way sync with] HubDB.TableABC [One Way to ] MemberDB2.TableABC
MemberDB1.TableXYZ [One Way Sync to] HubDB.TableXYZ [One Way Sync To] MemberDB2.TableXYZ
Am I correct?
AS
Thanks
Brent
Understood -- but they should still be able to do Bulk Inserts -- I tested by selecting a table that is not synced from MemberDB1, only exists in HubDB and made one way sync to MemberDB2 -- this table did bulk inserts.
INo matter if you separate things of different sync groups, all changes go to the DSS schema created by SAL Data Sync on that database. All sync groups will access the same schema DSS with all the changes. That makes slow the sync
If the table is used in other sync groups, there will be conflicts on rows on the DSS schema, that will slow the sync. The DSS schema stores all the changes for rows on each table