Now you ask, will missing deletes have any effect on the data convergence?  Well, the answer is yes, it does. But this effect can be prevented if only really old tombstones (older than a given time period, two weeks or a month for example) are deleted while fresh one are left intact. This way most of the sync clients would have seen the deletes already and won't be affected. So what about this guy who went on vacation for a month and just came back and wanted to synchronize with the server?  Well, that guy should not be allowed to synchronize at all. He must reinitialize his client application by basically deleting the local database and starting over.

In this demo, I will show you how to cleanup tombstones based on some aging criteria and how to detect and prevent stale clients from synchronizing.

Tombstone Cleanup

Let's first discuss tombstone cleanup logic. There are different ways to schedule a regular cleanup task. One would be through custom logic implemented in the application, another one would be through SQL Server Agent job that is kicked off at scheduled time for cleanup. In this demo, I've included a script that was generated automatically through adding a new agent job wizard to cleanup tombstones older than 14 days. It is rather simple, check the following code for cleaning up [orders] table:
 

USE [msdb]
GO

DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'Tombstone Cleanup Job',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=2,
            @notify_level_netsend=2,
            @notify_level_page=2,
            @delete_level=0,
            @category_name=N'[Uncategorized (Local)]',
            @job_id = @jobId OUTPUT

select @jobId
GO

EXEC msdb.dbo.sp_add_jobserver @job_name=N'Tombstone Cleanup Job'
GO

USE [msdb]
GO

EXEC msdb.dbo.sp_add_jobstep @job_name=N'Tombstone Cleanup Job', @step_name=N'Cleanup orders_tombstone Table',
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=3,
            @on_fail_action=2,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'TSQL',
            @command=N'delete from orders_tombstone where DATEDIFF(hh, create_datetime, GetDate()) > (24 * 14)', -- set the tombstone expire period
            @database_name=N'pub',
            @flags=0
GO

Rejecting Stale Clients

Rejecting clients that did not synchronize for long time will have to follow similar metric as used for the tombstone cleanup. The scheme I used was based on the age of the tombstones. Tombstones that are older than 14 days must go. With that, identifying bad clients will require recording when was the last time a given client synchronized with the server and if that period exceeded 14 days (in the code I used 10 days to avoid boarder line changes, you could use 13, 12 or anything that is less than or 14).

There are two approaches come to mind when thinking about recording the last sync time. First, you could write code to reference some table in the database that records the last sync time for each client. That logic could be in the database and called through SelectClientIdCommand. Better yet, the web service methods can do that check if you prefer not to put code in the database. Second, you could use events that are fired from the DbServerSyncProvider, the event handler could exercise the same logic as in the first case. The choice is yours, my friend.

But what is my choice? I don't really like to store state on the server side. After all, the anchor itself is stored on the client. Hang on, isn't the anchor an opaque type that the developer defines and the runtime transfers it back and forth? Well, how about piggy packing the datetime value of the last sync as part of the anchor. This way the server store will remain stateless. This is one of the powers of an opaque anchor. You can be as creative as you want in using it. Storing the last sync time is just one function that I thought about. So, let's see how we do that.

The anchor type is serialized prior to sending back to the client. The client stores it as is and returns it back to the server in the next sync. That said, if we can intercept the anchor at the beginning of the call to GetChanges and ApplyChanges and implement the following logic:

[Shrink Anchor]

• Detribalize the custom anchor for each table
• Inspect the value of the last sync time and if the client is stale throw exception
• Serialize the timestamp value back

After successfully enumeration or applying changes, intercept the anchor value returned from the DbServerSyncProvider and implement the following logic:

[Expand Anchor]

• Detribalize the simple timestamp anchor
• Create a new custom anchor type and populate it with the new timestamp and current datetime
• Serialize the custom anchor and send it to the client

The diagram below summarizes the above logic.

In this demo, I am using a web service for the server side. Both the Shrink and Expand anchor routines are easily implemented as follows:

[Serializable]
public class CustomAnchor
{
    public byte[] timestamp;
    public int version;
    public DateTime lastSyncTime;
};

private void ShrinkAnchor(SyncAnchor anchor)
{
       if (anchor != null && !anchor.IsNull())
       {
            MemoryStream memStream = new MemoryStream(anchor.Anchor);
            BinaryFormatter binFormatter = new BinaryFormatter();
            CustomAnchor customAnchor = (CustomAnchor)binFormatter.Deserialize(memStream);
            memStream.Dispose(); 

            if (customAnchor != null)
            {
                // fail if the last sync happened more than 10 days ago
                TimeSpan span = DateTime.Now - customAnchor.lastSyncTime;
                if (span.Days > 10) // replace with span.Seconds to see the failure at the client 
                {
                    throw new InvalidOperationException(
"Client had not synchronized with the server for more than 10 days, please   reinitialize the client local database and try again");
                }
            } 

            // shrink the anchor to timestamp only
            memStream = new MemoryStream();
            binFormatter.Serialize(memStream, customAnchor.timestamp);
            anchor.Anchor = memStream.ToArray();
            memStream.Dispose();
        }               
}

private void ExpandAnchor(SyncAnchor anchor)
{
        CustomAnchor customAnchor = new CustomAnchor();
        MemoryStream memStream;
        BinaryFormatter binFormatter = new BinaryFormatter(); 

        if (anchor != null && !anchor.IsNull())
        {
            memStream = new MemoryStream(anchor.Anchor);
            customAnchor.timestamp = (byte[])binFormatter.Deserialize(memStream);
            memStream.Dispose();
        }

        customAnchor.lastSyncTime = DateTime.Now;

        // Serialize the expanded anchor instead
        memStream = new MemoryStream();
        binFormatter.Serialize(memStream, customAnchor);         

        if (anchor == null)
        {
            anchor = new SyncAnchor(memStream.ToArray());
        }
        else
        {
            anchor.Anchor = memStream.ToArray();
        } 

        memStream.Dispose();       
}

The code for shrinking the custom anchor to just timestamp value and expand it to a custom anchor again is fairly straight forward. No we need to wire these routines with the GetChanges and ApplyChanges web methods as shown below:

[WebMethod]
public SyncContext GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)
{       
        foreach (SyncTableMetadata tableMetadata in groupMetadata.TablesMetadata)
        {           
            ShrinkAnchor(tableMetadata.LastReceivedAnchor);           
        }

        SyncContext context = _serverProvider.GetChanges(groupMetadata, syncSession);
        ExpandAnchor(context.NewAnchor); 

        return context;
}

[WebMethod]
public SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)
{
       foreach (SyncTableMetadata tableMetadata in groupMetadata.TablesMetadata)
       {
            ShrinkAnchor(tableMetadata.LastReceivedAnchor);
       } 

       SyncContext context = _serverProvider.ApplyChanges(groupMetadata, dataSet, syncSessio);

       return context;
}   

We are done. On the client side, when the exception is thrown by the web service, the client application will error out with this message box:

The client application should detect the error and reinitialize the client by deleting the local database and invoking a new sync session. Of course, you can backup the data (or just the pending client changes) prior to creating a new SqlCe file. The demo does not show this logic but I bet you got the idea.

In summary, the takeaways of this demo are two fold:

1- You need to clean those tombstones at one point
2- The opaque anchor architecture opens a lot of possibilities. Use it but don't abuse it.
 

Quick note on client side tombstone cleanup:

There is really no reason for the client database to keep tombstones lying around. Thus the SqlCeClientSyncProvider will delete all tombstone information once it successfully uploads the changes to the server. One thing less to worry about.

Happy synchronizing...

Install OfflineAppDemo Application Steps:

Version 1.2 [C#] : Download Now