Dynamics CRM in the Field

Information from the Microsoft Dynamics CRM PFE team working in the field

CRM 2011 Outlook Synchronization Process

CRM 2011 Outlook Synchronization Process

Rate This
  • Comments 6

Over the past few years, several Microsoft CRM 4.0 White Papers have been produced that cover both Outlook synchronization, as well as Offline synchronization.  You can find these white papers at the following locations:

The Outlook synchronization paper does a very nice job of covering the process that takes place when records are synchronized between Microsoft CRM and Outlook.  After reading the paper, you may walk away with a few questions around how the synchronization tables within the MSCRM database work with the client-side .sdf files....I know that I did anyways.  With the help of a few internal colleagues, I've put together the following information to hopefully clear up any confusion that you might have:

The CRM/Outlook synchronization process can occur in one of two ways:  1) manually clicking the "Synchronize with CRM" option, or 2) scheduled background synchronization.  The actual CRM/Outlook sync process looks like this when kicked off:

  1. Prepare sync is called to query the server to prepare all the entities which should be synchronized down from the CRM server.  There are a variety of tables used within the MSCRM database during this process:
    1. Subscription table = Specifies the SyncEntryTable name for each user that has been created.  There is a separate SyncEntry table for the outlook sync and the offline sync for each user.  A SubscriptionType of 0 = Offline and a SubscriptionType of 1 = Outlook.  You'll notice that a SubscriptionType of 2 for Address Book Provider will show as well.  The ABP SyncEntry table is no longer used.  The ABP sync is now handled exclusively by the Outlook MAPI sync.  Each SubscriptionId is tied to a unique SyncEntryTableName.
    2. SubscriptionClients table = Specifies which of the end user's machines is set to be the synchronizing client (based off of the IsPrimaryClient column).
    3. SubscriptionSyncInfo table = Specifies the sync metrics for each SubscriptionId (ex. StartTime, DataSize, InsertObjectCount, etc.).
    4. SyncEntry_<guid> = Specifies object being synchronized, the ObjectTypeCode (which tells you the entity), and the state of the sync.  Remember, there is a separate Sync_Entry table for each sync process per user
  2. After the information is prepared on the server end, it returns xml to the client with the number of records to be synchronized for each action type (create/update/delete) for each entity type (contact, appointment, etc).
  3. The CRM Outlook client then makes another sdk call, GetSyncData, according to the xml returned by the prepare sync to get the crmid and change timestamp for each changed record.  This information is then added to the OutlookSyncTable table in the OutlookSyncCache.sdf file.  This file is located at the following location on a Win7 machine:  C:\users\<user name>\AppData\Roaming\Microsoft\MSCRM\Client).
  4. The client also tracks the Outlook item changes and adds them to the OutlookSyncTable table in the .sdf file.  Now this table should contain all of the changed record information which comes from both CRM server and Outlook.
  5. The sync process will then process each changed record in the OutlookSyncTable table according to the change type and change timestamp.  For example, if the change type from crm server is create and there is no change from the outlook client, the action will be to create an item in Outlook.
  6. When one record is synced successfully, the entry in the OutlookSyncTable table will be removed and the IdMappingTable table entry will be inserted/updated/deleted accordingly. The IdMappingTable table contains the mapping between crmid and Outlook entryid.  When a crm record is synced down to Outlook, the table will help to find the corresponding Outlook item.  The only exception to the IdMappingTable table is for appointments where we cannot find an id mapping.  In that scenario, a search for an appointment with the same global object id will be done in Outlook. That is the only time that a search will be done in Outlook.
  7. If one entity fails and is not synchronized, then the corresponding entry in the OutlookSyncTable table will stay.  The next time a synchronization takes place, this entry will be synced again unless the user chooses to ignore the error (Select the “Ignore all errors” checkbox in the sync dialog in Outlook).

 

- Jon

  • Excellent information. I am in a role where I need to provide tech support for 1000+ Outlook clients and I am in constant need for further information about the CRM Outlook client synchronization process. This is because without having information how the synchronization should happen, it is impossible to provide technical support for this product WHEN sync issues happen (and they have happened a lot during the past years).

    Anyways, one question, how do the rows in the SyncEntry_<GUID> tables map to the user and sync process? By looking at the columns of this table, I do not see a reference to the user or the sync process (other sync tables)?

    Another question that I started wondering when I looked through some data in my MSCRM database tables that you described above: when I run the following query:

    select * from Subscription where SubscriptionId = <GUID found in SubscriptionSyncInfo table>

    I did find the corresponding record in the Subscription table. But running the following query:

    select * from SubscriptionClients where SubscriptionId = <same GUID found in SubscriptionSyncInfo table>

    did not return any rows. What does this indicate?

    Thanks.

  • Hi Jose,

    Each SyncEntry row has a linking record in the Subscription table (SyncEntryTableName).  The Subscription table contains the SystemUserId that the record relates to.  You can use a query similar to the following to get the user record for a specificy SyncEntry_<guid> (run against <org>_MSCRM):

    select b.fullname, b.systemuserid, a.syncentrytablename from Subscription a

    join SystemUserBase b on a.SystemUserId = b.SystemUserId

    where SyncEntryTableName = 'SyncEntry_<guid>'

    Below is an example of a query that you could use to locate all users who sync and the corresponding sync that they are performing (run against <org>_MSCRM):

    select c.FullName, a.SubscriptionId, case when b.SubscriptionType = 0 then 'Offline'

    when b.SubscriptionType = 1 then 'Outlook'

    when b.SubscriptionType = 2 then 'AddressBookProvider'

    end as 'Subscription_Type', COUNT (*) as NumOfSyncs,

    SUM (a.datasize) as 'DataSize_Sum',

    MAX (a.datasize) as 'DataSize_Max',

    MIN (a.datasize) as 'DataSize_Min',

    sum (a.insertobjectcount) as 'InsertOjbectCount'

    from SubscriptionSyncInfo a

    join Subscription b on a.SubscriptionId = b.SubscriptionId

    join SystemUserBase c on b.SystemUserId = c.SystemUserId

    group by c.FullName, a.SubscriptionId, b.SubscriptionType

    Thank you,

    Jon

  • Hi Jon,

    thank you for a great article, I have a few questions I don't know if you can help:

    Due to users changing machines, how can I clean these tables? I have loads of duplicates on the Subscription table, how can I clean the database from duplicates?

    I've also have loads of decommissioned machines, if I cold get a script I perhaps could put a where clause to check the last synch date to clean the table.

    Thanks

  • Is this for CRM 4.0 or 2011?  The links point to 4.0.

    Is there a 2011 equivalent white paper?

  • Corbett,

    The post is referencing CRM 2011.  At the current time, there aren't any white papers available specific to 2011 for this topic.

    Thanks,

    Jon

  • What happens when I desynchronize from a CRM 2011 Org?  Do my activities, meetings, appointments stay in both?

Page 1 of 1 (6 items)
Leave a Comment
  • Please add 4 and 2 and type the answer here:
  • Post