In versions 1.0 and 1.2 of Microsoft CRM, we used SQL’s merge replication technology as the basis for our offline sync process. Merge replication is great, but it ended up being too heavy-weight for our very simple needs, and in 3.0, we built a synching solution that re-used some of CRM’s existing components, along with SQL’s BCP feature for packaging the data for transport.
One of the advantages of SQL replication is the extensive tracking that occurs as part of the process. To allow tracking of Go Offline (and investigation of issues), some of our larger customers were able to mine SQLrepl’s tracking data and create reports that gave an overview of sync regularity and success. They were also generous enough to share these with us, so we could get an idea of what was of interest.
In 3.0, we had originally hoped to be able to ship a report that any admin could access that would give this information. Unfortunately, this didn’t make the grade, but we were able to ensure that syncs could be tracked. In the CRM database, the table SubscriptionSyncInfo tracks each time a user goes offline. The start and end times are tracked, along with the amount of data that is copied and whether or not the sync was successful.
Each row in SubscriptionSyncInfo represents one “Go Offline” by one user, and looks something like this (if you were to say, run a first time sync on a test machine):
EndTime Subscription SyncInfoId DeleteObject Count SubscriptionId SyncResult StartTime DataSize InsertObject Count 1 0 F5E705BD-6629- DB11-A9C7- 00087424AE25 1 8/11/2006 18:25 6631515 9783
- EndTime and StartTime are in UTC
- DeleteObjectCount tracks the number of objects that were deleted from the client database during the sync. This was an initial sync, so this is 0.
- SubscriptionId is a GUID and identifies the user and machine. More on that below.
- SyncResult lets you know if the sync was successful or not. 1 indicates success, 0 indicates failure.
- DataSize is the size of the changes in bytes downloaded by client.
- InsertObjectCount tracks the number of objects that were created in the client database during the sync. This is an initial sync so it is fairly high.
- This only tracks the Go Offline process. Go Online is a completely different (much more light-weight) process and is not tracked.
Identifying The User
The SubscriptionId can be cross-referenced with the Subscription table to identify the user. Each user can have up to three subscriptions per Outlook client machine – one subscription for each of the three types of sync (offline sync, sync to Outlook and Address Book sync). Only offline syncs are tracked in SubscriptionSyncInfo table.
Using This Data
We’re never particularly happy to talk about directly accessing the CRM database, because we may (and probably will) change the structure in future releases, but should sync tracking be required, this table provides the raw data. Luckily, Offline Sync is far less problematic in 3.0 than in previous versions and many customers have not found the need to track syncs.
This is a logging table only in the current release, so if database size is a concern, a regular job could be created that would clean out this table.
We’re not sure if this information is interesting for admins, so we don’t know how much future investment we’ll make in this area. If you think tracking syncs is useful, please let us know.