Data Sync Time Duration Problem- Head office to Hub and Branch to Hub

Question

Dear Sir,

I set up Web base 1 GB Database on Azure Cloud which Name is CampusContact

In this database following Tables and records (Transactions)

No.	Table	Records
1	Contacts	3038
2	Contacts_Additional_Info	1444
3	Contacts_Address	3051
4	Contacts_Bank	396
5	Contacts_Category_List	727
6	Contacts_Division	805
7	Contacts_Entry_Categories	66
8	Contacts_Relation	0

I set up following Sync Groups :- Group No. Group Name Direction Conflict Resolution Define Sync Dataset Sync Schedule 1 Head Office to Hub Sync to Hub Client Wins Head office Table Auto Disable 2 Hub to Branch Sync from Hub HUB Wins Cloud Table Auto Disable 3 Branch to Hub Sync to Hub HUB Wins Branch Office Table Auto Disable 4 Hub to Head Office Sync from Hub Client Wins Cloud Table Auto Disable     In my Head Office & Branch Premises Windows 2008 server , SQL Server 2008 R2 and we use Data Sync Agent in both premises. In both Premises''s SQL Server 8 tables as per above name are same and configure in Data Sync Agent. BUT WHEN I INSERT NEW SINGLE RECORD IN HEAD OFFICE / BRANCH SERVER AND FOLLOW SYNC GROUP SEQUENCE AS PER ABOVE EVERY TIME WHEN I SYNC - HEAD OFFICE TO HUB AND BRANCH TO HUBIN THIS TWO SYNC PROCESS IT WILL TAKE MORE THAN 1 TO 2 HOURS.

Thanks

Answer 1

Hello,

How many changes to you see in the results for each Sync Group?

Also, I see that you have 4 sync groups - why not have just one with the Hub, Head Office and Branch as endpoints? It looks like you may want to have Hub Wins in one pair, but Client wins in the other pair - in this case you can do with two sync groups.

thanks

Answer 2

just to clarify the steps to reproduce the issue:

you insert a row in both Head Office and Branch Server?

You sync Sync Groups 1 to 4 and then sync Sync Group 1 and 3?

you may be running into a sync loop (see: http://msdn.microsoft.com/en-us/library/windowsazure/hh667312.aspx)

Note that the sync knowledge between sync groups is independent of one another. What has been synched by Sync Group 1 is not known to Sync Group 2 and so on. so even if its the same row, the are detecting it as a change.

For example:

Sync Group 1 Syncs and a row insert is detected in Head Office and this is uploaded to Hub

Sync Group 2 Syncs and detects a change in Hub (the same row from Head Office from Sync Group 1) and downloads it to Branch

Sync Group 3 Syncs and detects a change in Branch (the same row originally from Head Office in Sync Group 1) and uploads it to Hub

Sync Group 4 Syncs and detects a change in Hub (the same row originally from Head Office in Sync Group 1) and downloads it to Head Office, the original row from Head Office actually came back to itself.

and the cycle goes on.

Answer 3

Hello June T,

Thanks for your reply. Please reproduce / design my sync group. Here I try to explain work flow so you can do it.

Branch and Head office can work locally without internet. Currently following step for one branch only but require to set one Head office and multiple branch.

Step 1 - Head Office insert record in his local server eg. Contact Id - 5001 and other Child Tables ID - 100001

Step 2 - Records upload on HUB with Sync Group 1 (from Head office to HUB)

Step 3 - New records inserted by Head Office require to Download from HUB to Branch that's why Sync Group 2. It is necessary that branch have head office records to view/see only before they insert new records. 

Step 4 - BRANCH OFFICE Also Insert new record in there local server. 

(BUT BRANCH HAVE NOT USER RIGHTS TO UPDATE OR DELETE ANY RECORDS JUST INSERT NEW RECORDS)

eg. Contact ID - 50001 and other Child Tables ID - 200001.

(It is interesting that never conflict primary key because primary key range allotted to HO and Branch so independent ranges ID will generate by our unique store procedure set in each HO and Branch server.)

Step 5 - Records upload on HUB with Sync Group 3(from Branch to HUB)

Step 6 - Records download from HUB with Sync Group 4 (from HUB to Head office)

Answer 4

Can you paste the Log Viewer logs for the syncs? I want to see how much time it is taking and how many records it is syncing each time.

Answer 5

I can not get detail log so need to talk with you. Can you give me your email address so I can send you my phone number or send me your number so I can reach to you.

Answer 6

my email address [e-mail]

Answer 7

-------------------

Sync completed successfully in 2062.90 seconds.
    Upload:   9529 changes applied
    Download: 0 changes applied

For more information, provide tracing id ‘e600d25c-1ace-442d-9613-8b6d2f29d5dc’ to customer support.

-------------------------------

Sync completed successfully in 2122.70 seconds.
    Upload:   9527 changes applied
    Download: 0 changes applied

For more information, provide tracing id ‘ef2f67b8-16a2-4fe6-8dc4-1aaf4bdbf2b9’ to customer support.

Answer 8

i'll try to reproduce later.

just some more questions though:

do you have filtering?

can a branch record be updated on other branches as well?

or a branch can only update/delete its own records?

can headoffice update branch records?

i'm asking because from what i can see, you are trying to create sync groups to cater to different conflict resolution approaches. if branches have their own ranges of PK, then its very unlikely you will have insert conflicts. if a branch cannot update head office records, then its very unlikely to get an update/delete conflict as well. as Sid suggested, you may actually just need one sync group or two at most.

Answer 9

do you have filtering?

---I do not have filtering .

can a branch record be updated on other branches as well?

---No , other branch not update.

or a branch can only update/delete its own records?

--- If you can set sync group then I require to give permission to update only branch its own records (currently due to lake of data sync knowledge we not give permission to update its own records also)

can headoffice update branch records?

-- Yes as per my above comment we set policy that only head office update branch records.

Our humble request if you can spare some time and reproduce as soon as earlier. 

Answer 10

Hari, the total number of items in your tables comes to 9527. With the two additional items you insert, it is 9529. This is the same number of items that are synced (from your log):

Sync completed successfully in 2062.90 seconds.
    Upload:   9529 changes applied
    Download: 0 changes applied

Just to make sure - you have already synced all the endpoints twice, so that all the enpoints have all the items, and are now trying to only sync the additional 2 items, right?

The log entries are not for the first time sync, correct?

I'd like to make sure we understand what you are trying to do - I don't want to repro yet, since this is likely expected behaviour.

Answer 11

Hello June T,

Thanks for your reply. Please reproduce / design my sync group. Here I try to explain work flow so you can do it.

Branch and Head office can work locally without internet. Currently following step for one branch only but require to set one Head office and multiple branch.

Step 1 - Head Office insert record in his local server eg. Contact Id - 5001 and other Child Tables ID - 100001

Step 2 - Records upload on HUB with Sync Group 1 (from Head office to HUB)

Step 3 - New records inserted by Head Office require to Download from HUB to Branch that's why Sync Group 2. It is necessary that branch have head office records to view/see only before they insert new records. 

Step 4 - BRANCH OFFICE Also Insert new record in there local server. 

(BUT BRANCH HAVE NOT USER RIGHTS TO UPDATE OR DELETE ANY RECORDS JUST INSERT NEW RECORDS)

eg. Contact ID - 50001 and other Child Tables ID - 200001.

(It is interesting that never conflict primary key because primary key range allotted to HO and Branch so independent ranges ID will generate by our unique store procedure set in each HO and Branch server.)

Step 5 - Records upload on HUB with Sync Group 3(from Branch to HUB)

Step 6 - Records download from HUB with Sync Group 4 (from HUB to Head office)

do you have filtering?

---I do not have filtering .

can a branch record be updated on other branches as well?

---No , other branch not update.

or a branch can only update/delete its own records?

--- If you can set sync group then I require to give permission to update only branch its own records (currently due to lake of data sync knowledge we not give permission to update its own records also)

can headoffice update branch records?

-- Yes as per my above comment we set policy that only head office update branch records.

Our humble request if you can spare some time and reproduce as soon as earlier. 

Answer 12

ok here's my testing result.

1. Insert row at Head Office

2. Sync Head Office to Hub - 1 row uploaded

3. Synch Hub to Branch - 1 row downloaded

4. Insert new row at Branch

5. Sync Branch to Hub - 2 rows uploaded (this proves the row from Head Office in step 1 was echoed back to Hub)

6. Sync Hub to Head Office - 2 rows downloaded (original row from Head Office was echoed back, new row from branch downloaded)

7. Did Head Office to Hub sync without changing any row on Head Office - 2 rows uploaded, again proves the point that the changes are echoed back.

so you have a case of a sync loop.

now going back to you scenario:

1. there is less likelihood of having an insert conflict between branches and between branch and head office because they have different PK ranges.

2. there is less likelihood of having an update or delete conflict between branches and between branch and head office since updates/deletes is only done on head office.

3. you only have one Head Office, so there is less likelihood as well of having a conflict between Head Office and Hub.

I think you should be able to have one sync group only and it wont matter what conflict resolution you set if there is no potential for conflict.

Answer 13

Thank you for your all support . From HO to 1 Branch successfully work now less then 1 minutes sync perform now. We are pleased to convey special Blessings from god and thanks to you from our organization.

Please email me once [e-mail] or please give me your email id. We are from Gujarat, India's big no profit organization. You may also get a valuable organization like us.  

Thanking you once again for your generosity.

We remain, Waiting for your reply.

Answer 14

Hi,

I have problem registering my Azure Data Sync Agent on my on-premise server running: Windows Server 2008 R2 Enterprise x86, Microsoft SQL Server 2008 R2 and Azure Data Sync Agent Preview. I get the message below.

------------------------
Connection failed. Please check database credentials along with permissions for Local Sync Service logon account to connect to database with specified credentials.

Other reasons for failure:

SQL Azure databases have to be added on the Azure Sync Service portal.
SQL Server databases lower than SQL Server 2005 SP2 are not supported on the Azure Sync Service.
---------------------------

1. I can connect to my on-premise sql server database with same specified credentials (SQL and Windows authentication) but can not connect in Azure Data Sync Agent. I tried both authentication - SQL & Windows but same message.

2. I already submit agent key and Ping sync service is also successfully pinged, so Agent is configured successfully.

3. As per above message my database is also higher then SQL Server 2005 SP2 so this is not happen in my case.

SO PLEASE HELP ME TO SOLVE THIS ISSUE.

Answer 15

Hi,

I have problem registering my Azure Data Sync Agent on my on-premise server running: Windows Server 2008 R2 Enterprise x86, Microsoft SQL Server 2008 R2 and Azure Data Sync Agent Preview. I get the message below.

------------------------
Connection failed. Please check database credentials along with permissions for Local Sync Service logon account to connect to database with specified credentials.

Other reasons for failure:

SQL Azure databases have to be added on the Azure Sync Service portal.
SQL Server databases lower than SQL Server 2005 SP2 are not supported on the Azure Sync Service.
---------------------------

1. I can connect to my on-premise sql server database with same specified credentials (SQL and Windows authentication) but can not connect in Azure Data Sync Agent. I tried both authentication - SQL & Windows but same message.

2. I already submit agent key and Ping sync service is also successfully pinged, so Agent is configured successfully.

3. As per above message my database is also higher then SQL Server 2005 SP2 so this is not happen in my case.

SO PLEASE HELP ME TO SOLVE THIS ISSUE.