none
Sync completes when there still are batches left. RRS feed

  • Question

  • In a current project we use sychronization between SQL server 2008 and and SQLCE on a windows moblie device. The server runs a WCF service.
    Due to the possibility of large data to be synchronized we have implemented a batching.

    Later I've noticed that the device stops to stops to asks for more batches, even if it isn't done. The times I've counted it have ended after 6 batches, no matter the size of them.
    The SyncAgent.Synchronize() returns normal with a legit SyncStatistic. And if I do a new call on SyncAgent.Synchronize()  more it will resume the synchronization, with another 6 batches.

    Further down  are the group meta data from the actual communication sent between the client and server. As you can see in both the request and response the batchcount is greater than 1 and the the new anchor differs from the max anchor.

    Where should I start to look for errors?


    GetChanges:
    <groupMetadata><GroupName>WorkOrderDepending</GroupName><NewAnchor><Anchor>           	AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACUNDAwAAAAAA
    Cw==</Anchor></NewAnchor><MaxAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACW6eAwAAAAAA
    Cw==</Anchor></MaxAnchor><BatchCount>6</BatchCount><TablesMetadata><SyncTableMetadata><TableName>ArchiveJetasObjectType</TableName><SyncDirection>DownloadOnly</SyncDirection><LastSentAnchor /><LastReceivedAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACUNDAwAAAAAA
    Cw==</Anchor></LastReceivedAnchor></SyncTableMetadata><SyncTableMetadata><TableName>ArchiveJetasObjectColumn</TableName><SyncDirection>DownloadOnly</SyncDirection><LastSentAnchor /><LastReceivedAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACUNDAwAAAAAA
    Cw==</Anchor></LastReceivedAnchor></SyncTableMetadata><SyncTableMetadata><TableName>ArchiveDropDownListItem</TableName><SyncDirection>DownloadOnly</SyncDirection><LastSentAnchor /><LastReceivedAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACUNDAwAAAAAA
    Cw==</Anchor></LastReceivedAnchor></SyncTableMetadata><SyncTableMetadata><TableName>ArchiveJetasObject</TableName><SyncDirection>Bidirectional</SyncDirection><LastSentAnchor><Anchor>AQAAAAAAAAA=</Anchor></LastSentAnchor><LastReceivedAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACUNDAwAAAAAA
    Cw==</Anchor></LastReceivedAnchor></SyncTableMetadata><SyncTableMetadata><TableName>ArchiveJetasObjectData</TableName><SyncDirection>Bidirectional</SyncDirection><LastSentAnchor><Anchor>AQAAAAAAAAA=</Anchor></LastSentAnchor><LastReceivedAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACUNDAwAAAAAA
    Cw==</Anchor></LastReceivedAnchor></SyncTableMetadata><SyncTableMetadata><TableName>ArchiveJetasObject2Task</TableName><SyncDirection>Bidirectional</SyncDirection><LastSentAnchor><Anchor>AQAAAAAAAAA=</Anchor></LastSentAnchor><LastReceivedAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACUNDAwAAAAAA
    Cw==</Anchor></LastReceivedAnchor></SyncTableMetadata><SyncTableMetadata><TableName>ArchiveGroup</TableName><SyncDirection>DownloadOnly</SyncDirection><LastSentAnchor /><LastReceivedAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACUNDAwAAAAAA
    Cw==</Anchor></LastReceivedAnchor></SyncTableMetadata><SyncTableMetadata><TableName>MeterProperty</TableName><SyncDirection>DownloadOnly</SyncDirection><LastSentAnchor /><LastReceivedAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACUNDAwAAAAAA
    Cw==</Anchor></LastReceivedAnchor></SyncTableMetadata><SyncTableMetadata><TableName>WorkOrder</TableName><SyncDirection>Bidirectional</SyncDirection><LastSentAnchor><Anchor>AQAAAAAAAAA=</Anchor></LastSentAnchor><LastReceivedAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACUNDAwAAAAAA
    Cw==</Anchor></LastReceivedAnchor></SyncTableMetadata><SyncTableMetadata><TableName>WorkOrderContent</TableName><SyncDirection>Bidirectional</SyncDirection><LastSentAnchor><Anchor>AQAAAAAAAAA=</Anchor></LastSentAnchor><LastReceivedAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACUNDAwAAAAAA
    Cw==</Anchor></LastReceivedAnchor></SyncTableMetadata><SyncTableMetadata><TableName>WorkOrderStatusChange</TableName><SyncDirection>Bidirectional</SyncDirection><LastSentAnchor><Anchor>AQAAAAAAAAA=</Anchor></LastSentAnchor><LastReceivedAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACUNDAwAAAAAA
    Cw==</Anchor></LastReceivedAnchor></SyncTableMetadata></TablesMetadata></groupMetadata>
    

    GetChangesResponse:
    <GetChangesResult><GroupProgress><GroupName>WorkOrderDepending</GroupName>
    <TablesProgress><SyncTableProgress><TableName>ArchiveGroup</TableName><Inserts>0</Inserts><Updates>4</Updates><Deletes>0</Deletes><ChangesApplied>0</ChangesApplied><ChangesFailed>0</ChangesFailed><Conflicts/></SyncTableProgress>
    <SyncTableProgress><TableName>ArchiveJetasObjectType</TableName><Inserts>0</Inserts><Updates>8</Updates><Deletes>0</Deletes><ChangesApplied>0</ChangesApplied><ChangesFailed>0</ChangesFailed><Conflicts/></SyncTableProgress>
    <SyncTableProgress><TableName>ArchiveJetasObjectColumn</TableName><Inserts>0</Inserts><Updates>46</Updates><Deletes>0</Deletes><ChangesApplied>0</ChangesApplied><ChangesFailed>0</ChangesFailed><Conflicts/></SyncTableProgress>
    <SyncTableProgress><TableName>ArchiveDropDownListItem</TableName><Inserts>0</Inserts><Updates>0</Updates><Deletes>0</Deletes><ChangesApplied>0</ChangesApplied><ChangesFailed>0</ChangesFailed><Conflicts/></SyncTableProgress>
    <SyncTableProgress><TableName>ArchiveJetasObjectData</TableName><Inserts>0</Inserts><Updates>94</Updates><Deletes>0</Deletes><ChangesApplied>0</ChangesApplied><ChangesFailed>0</ChangesFailed><Conflicts/></SyncTableProgress>
    <SyncTableProgress><TableName>ArchiveJetasObject</TableName><Inserts>0</Inserts><Updates>19</Updates><Deletes>0</Deletes><ChangesApplied>0</ChangesApplied><ChangesFailed>0</ChangesFailed><Conflicts/></SyncTableProgress>
    <SyncTableProgress><TableName>ArchiveJetasObject2Task</TableName><Inserts>0</Inserts><Updates>19</Updates><Deletes>0</Deletes><ChangesApplied>0</ChangesApplied><ChangesFailed>0</ChangesFailed><Conflicts/></SyncTableProgress>
    <SyncTableProgress><TableName>MeterProperty</TableName><Inserts>0</Inserts><Updates>0</Updates><Deletes>0</Deletes><ChangesApplied>0</ChangesApplied><ChangesFailed>0</ChangesFailed><Conflicts/></SyncTableProgress>
    <SyncTableProgress><TableName>WorkOrder</TableName><Inserts>5</Inserts><Updates>0</Updates><Deletes>0</Deletes><ChangesApplied>0</ChangesApplied><ChangesFailed>0</ChangesFailed><Conflicts/></SyncTableProgress>
    <SyncTableProgress><TableName>WorkOrderContent</TableName><Inserts>19</Inserts><Updates>0</Updates><Deletes>0</Deletes><ChangesApplied>0</ChangesApplied><ChangesFailed>0</ChangesFailed><Conflicts/></SyncTableProgress>
    <SyncTableProgress><TableName>WorkOrderStatusChange</TableName><Inserts>0</Inserts><Updates>0</Updates><Deletes>0</Deletes><ChangesApplied>0</ChangesApplied><ChangesFailed>0</ChangesFailed><Conflicts/></SyncTableProgress>
    </TablesProgress></GroupProgress><OriginatorId>0</OriginatorId><NewAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACUlDAwAAAAAACw==</Anchor></NewAnchor><MaxAnchor><Anchor>AAEAAAD/////AQAAAAAAAAAEAQAAAAxTeXN0ZW0uSW50NjQBAAAAB21fdmFsdWUACW6eAwAAAAAACw==</Anchor></MaxAnchor><BatchCount>5</BatchCount>

    • Edited by Joel Steen Timle Thursday, January 28, 2010 10:46 AM Inserted linebreaks in the XML.
    Thursday, January 28, 2010 10:44 AM

Answers

  • if you can consistently repro this issue ( i.e. sync ends at the 3rd batch where you expect more than 3 ), I would suggest you also profile the sql server and enable tracing at the sync application to get the level 4 trace, those, together would give us more info on what might be going wrong here. you can follow this link (http://msdn.microsoft.com/en-us/library/cc807160.aspx) to enable the tracing on the sync client and the IIS side.

    thanks
    yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 5, 2010 6:45 AM
    Moderator

All replies

  • Would you please check the Get New Anchor command text and see how it was implemented.
    If there is a Stored Procedure associated with it, please check the TSQL as well.

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, January 29, 2010 1:49 AM
    Answerer
  • I've build my own GetNewAnchor command with a stored procedure. The idea behind it is to sync a fixed number of rows from our main table, workorder, at a time.
    This have lead to a quite more complicated Get New Anchor procedure than the one usually described.
    When I've check the bigints value of "LastReceivedAnchor, NewReceivedAnchor and MaxReceivedAnchor" they all have had different values on the last batch before the syncAgent.Synchronize() consider the synchronizatoin to be completed. Here is the code from the GetNewAnchor stored procedure:
    CREATE PROCEDURE [dbo].[usp_GetNewBatchAnchor] (
    	@sync_last_received_anchor bigint, 
    	@sync_batch_size int,
    	@jetas_user_id uniqueidentifier,
    	@sync_max_received_anchor bigint out,
    	@sync_new_received_anchor bigint out,            
    	@sync_batch_count int output)            
    AS            
    BEGIN
    	SET NOCOUNT ON;
    
          -- Set a default batch size if a valid one is not passed in.
           IF  @sync_batch_size IS NULL OR @sync_batch_size <= 0
    	     SET @sync_batch_size = 30 
    	   -- Before selecting the first batch of changes,
    	   -- set the maximum anchor value for this synchronization           
             -- session. After the first time that this procedure is 
    	   -- called, Synchronization Services passes a value for 
             -- @sync_max_received_anchor to the procedure. Batches of 
             -- changes are synchronized until this value is reached.
           IF @sync_max_received_anchor IS NULL
             SELECT  @sync_max_received_anchor = 
                        change_tracking_current_version()
                        
    
    	DECLARE @ChangeTrackings TABLE(SYS_CHANGE_VERSION bigint,WorkOrderID uniqueidentifier)
    	DECLARE @AssignedWOS TABLE(WorkOrderID uniqueidentifier)
    
    	INSERT @AssignedWOS
    		SELECT WorkOrderID
    			FROM
    				WorkOrder
    			WHERE
    				[Status] in (1,3,11)
    				AND ISNULL(WorkOrder.AssignedToUserID,'00000000-0000-0000-0000-000000000001') =  @jetas_user_id
    				AND IsAdministrativeWorkOrder = 0
    
    	
    	INSERT @ChangeTrackings
    		SELECT CT.SYS_CHANGE_VERSION,Wos.WorkOrderID
    			FROM 
    				@AssignedWOS AS WOs
    				INNER JOIN CHANGETABLE(CHANGES [WorkOrder],@sync_last_received_anchor) AS CT
    				ON WOs.WorkOrderID = CT.WorkOrderID
    
    	--Fetch the change tracing tracking numbers connected to workorder
    	--assigned to the user.
    	IF ISNULL(@sync_last_received_anchor,0) <=0
    	BEGIN
    				
    	--Add changetracking number 0 to Assigned WOs with no active change tracking
    	--to make sure all are synchronized during initial sync
    	INSERT @ChangeTrackings
    		SELECT 0, Wos.WorkorderID
    			FROM @AssignedWOS AS WOs
    				LEFT OUTER JOIN  @ChangeTrackings AS CT 
    				ON WOs.WorkOrderID = CT.WorkOrderID
    			WHERE
    				CT.SYS_CHANGE_VERSION IS NULL
    	END
    				
    	--Select the new received anchor as the @sync_batch_size:th 
    	--change tracking number
    	SELECT @sync_new_received_anchor = MAX(ChangeVersion)
    		FROM (SELECT TOP (@sync_batch_size) CT.SYS_CHANGE_VERSION AS ChangeVersion
    				FROM @ChangeTrackings AS CT
    				group by SYS_CHANGE_VERSION 
    				order by ISNULL(SYS_CHANGE_VERSION,0) ASC ) AS CV
    
    	SELECT @sync_batch_count = (COUNT(*)/@sync_batch_size) + 1
    		FROM @ChangeTrackings
    
    	--If the new received anchor isn't set or the batch count is one 
    	--or less this is the final batch.
    	IF (@sync_new_received_anchor IS NULL OR @sync_batch_count <= 1) 
    	BEGIN
    		SET @sync_new_received_anchor = @sync_max_received_anchor
    		SET @sync_batch_count = 1
    	END
    	--If the new received anchor still is zero or less we have to 
    	--force a new anchor value.
    	IF (@sync_new_received_anchor <= 0)
    		SET @sync_new_received_anchor = 1
    
    END


    Friday, January 29, 2010 10:27 AM
  • Hi Joel, Can you try replacing this:
    INSERT @ChangeTrackings
    		SELECT CT.SYS_CHANGE_VERSION,Wos.WorkOrderID
    			FROM 
    				@AssignedWOS AS WOs
    				INNER JOIN CHANGETABLE(CHANGES [WorkOrder],@sync_last_received_anchor) AS CT
    				ON WOs.WorkOrderID = CT.WorkOrderID
    
    	--Fetch the change tracing tracking numbers connected to workorder
    	--assigned to the user.
    	IF ISNULL(@sync_last_received_anchor,0) <=0
    	BEGIN
    				
    	--Add changetracking number 0 to Assigned WOs with no active change tracking
    	--to make sure all are synchronized during initial sync
    	INSERT @ChangeTrackings
    		SELECT 0, Wos.WorkorderID
    			FROM @AssignedWOS AS WOs
    				LEFT OUTER JOIN  @ChangeTrackings AS CT 
    				ON WOs.WorkOrderID = CT.WorkOrderID
    			WHERE
    				CT.SYS_CHANGE_VERSION IS NULL
    	END
    
    with this one:
    
    	INSERT @ChangeTrackings
    		SELECT ISNULL(CT.SYS_CHANGE_VERSION,0),Wos.WorkOrderID
    			FROM 
    				@AssignedWOS AS WOs
    				LEFT OUTER JOIN CHANGETABLE(CHANGES [WorkOrder],@sync_last_received_anchor) AS CT
    				ON WOs.WorkOrderID = CT.WorkOrderID
    
    
    I think, the first INSERT in your query may return NULL values for SYS_CHANGE_VERSION and that NULL maybe getting assigned in your SELECT MAX statement.
    Friday, January 29, 2010 5:17 PM
    Moderator
  • Update: This time when I run it it gave up after 3 batches.


    As show in th end of this post the values of the bigints that the stored procedures outputs are correct. It's only the client that doesn't treat them right, or it doesn't interpret them correctly from the XML.

    When I substitute my selection with your I still get the same result, or actually it will give me a @new_received_anchor that give me a batch size of 29  but that doesn't matter. The reason I've split it is that the outer join with the CHANGETABLE(CHANGES, ..) takes ~20 during the initial execution, instead of about half a second when you split it.
    You'll also need to take the into account that @ChangeTrackings always will include all the rows, even the one with lover SYS_CHANGE_VERSION values i.e. the one that belongs to former syncs/batches.

    The null values will only be inserted/replaced with 0 during the first batch, when @sync_last_received_anchor is 0 or null. The problem have only occurred during later batches.

     
    Here are some values form execution of the ups_GetNewBatchAnchor run in Management studio. Batch size set to 30 and there are 145 rows to be synchronized to the client in the database.



    Execution last received new received max received batch count
    1 0                     14801 14916 5
    2              14801              14831 14916 4
    3 14831 14861 14916 3
    4  14861  14891                14916  2
    5 14891 14916 14916 1




    ps. Can the CHANGETABLE(CHANGES ...) really return a row with SYS_CHANGE_VERSION equal to null? Doesn't that mean that it doesn't have any change tracking information about it and thus won't be included in the CHANGETABLE ?
    ds.
    Friday, January 29, 2010 6:38 PM
  • can you capture a trace using SQL Profiler?
    Friday, January 29, 2010 6:52 PM
    Moderator
  • Yes, what data are you interested in?
    Saturday, January 30, 2010 1:13 PM
  • just curious what SQL gets sent by SyncFx...
    Saturday, January 30, 2010 2:08 PM
    Moderator
  • if you can consistently repro this issue ( i.e. sync ends at the 3rd batch where you expect more than 3 ), I would suggest you also profile the sql server and enable tracing at the sync application to get the level 4 trace, those, together would give us more info on what might be going wrong here. you can follow this link (http://msdn.microsoft.com/en-us/library/cc807160.aspx) to enable the tracing on the sync client and the IIS side.

    thanks
    yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 5, 2010 6:45 AM
    Moderator
  • Hi Joel, any updates on your findings? hope you got this figured out ?

    thanks
    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 19, 2010 5:27 AM
    Moderator
  • Not yet unfortunately, and due do deadlines we haven't got the time yet to perform a deeper analysis with profiling and sync trace. The issue is clearly reproduceable, but sometimes it ends after 2 or 4 batches instead.

    I'll return with more info as soon as I have time to dig into it.

       Joel
    Monday, February 22, 2010 9:23 AM