Tracking when users go offline in Microsoft CRM


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.


 


SubscriptionSyncInfo


 


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


8/11/2006 18:30


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.


 


Ilana Smith and Andrei Smertin

Comments (10)

  1. John says:

    thanks that really helped.

    regards

    J-

    http://www.megasolutions.net

  2. SomewholikesCRM says:

    Excellent , Good job.. Thanks for taking my comments and coming up with excellent Article….

  3. Pete says:

    This is Awesome 🙂

    Thanks

  4. Last month, Ilana Smith and Andrei Smertin wrote a post about the SubscriptionSyncInfo table and how

  5. Angelina says:

    Great work thanx

  6. Katie says:

    I have had three users report that they are defaulted to Offline when they open Outlook each morning. Do you know why that could be happening?

  7. Sundar says:

    Hi

    Great work, But this table also holds sync entry when ever i imported data from data migration.Can i ignore these values

Skip to main content