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:
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?
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 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.
Is this for CRM 4.0 or 2011? The links point to 4.0.
Is there a 2011 equivalent white paper?
The post is referencing CRM 2011. At the current time, there aren't any white papers available specific to 2011 for this topic.
What happens when I desynchronize from a CRM 2011 Org? Do my activities, meetings, appointments stay in both?