About the series

This series will introduce you to two major areas related to Outlook Synchronization in Microsoft Dynamics CRM for Outlook:

Concepts & Rules

Troubleshooting

 

Introduction

In order to further understand the Outlook Synchronization, let’s look at the organization database table structures of the Outlook Synchronization. 

Microsoft Dynamics CRM for Microsoft Outlook stores and relies on tables structures from:

  • Server-side: there are a couples of tables into the ordinary SQL Server organization database.

  • Client-side: the add-in also writes information to the client-side database files.

For this article, let’s review the main tables for the client-side perspective.

 

Files and location

The client side database structure is stored a SQL CE database (SDF files) and the location of these files are:

%LocalAppData%\Microsoft\MSCRM\Client

  • CrmCache.sdf

    This file contains non-metadata platform caches.

  • {Guid}[true|false].sdf

    This file contains MAPI store internal storage. Address Book data.

    Each organization can have two caches, one for online and one for offline

  • MetadataCache-{Guid}.sdf

    This file contains Metadata cache that minimizes the amount of traffic that will be send once you publish your customization.

     

%AppData%\Microsoft\MSCRM\Client

  • EmailCache.sdf

    This file contains mappings between CRM and MAPI items.

  • OutlookSyncCache.sdf

    This file contains internal state for sync.

     

Focusing on OutlookSyncCache.sdf, we have four tables:

  • EmailSyncTable

    Contains change information for emails, which are only promoted from Outlook to CRM.

  • EmailTrackingTokenTable

    The EmailTrackingTokenTable stores a cache of TrackingTokenId for the specific user.

  • OutlookSyncTable

  • idMappingTable

 

The last two tables will be described in details in next topics.

 

 

OutlookSyncTable

It holds the change requests between synchronization cycles.

The OutlookSyncTable can be thought of as a queue that holds the change requests between synchronization cycles for any changes made on the client to synchronized records. Then, when the sync process occurs, this table is updated to contain the record data for changes.

Column

Description

Id

unique identifier and Primary Key for each row in the table

MapID

foreign key identifier to the idMappingTable

OutlookTimestamp

this time/date value reflects when the item was last changed in Outlook

OutlookChange

this integer value reflects the type of change action that occurred in Outlook:

1 – Create

2 – Delete

3 – Update

CrmTimestamp (*)

this time/date value reflects when the item was last changed in CRM

CrmChange (*)

this integer value reflects the type of change action that occurred in CRM:

1 – Create/Update

2 – Delete

Priority (**)

this integer column determines if the synchronization of the object should occur immediately or during the next manual or automatic sync

 

(*) - These columns are only populated during the synchronization process.

(**) - Doing a manual track, a row is first inserted into the OutlookSyncTable with a Priority of 1, which is processed immediately.

 

 

idMappingTable

Acts as a pointer table for the OutlookSyncTable.

The idMappingTable acts as a pointer table for the OutlookSyncTable to correlate the unique Outlook entry ID to the unique CRM ID during the synchronization process.

In other words, this table can be thought of as an index.

Column

Description

ID

this is the primary key for each row in the table. This key is related back as the foreign key (MapId) in the OutlookSyncTable

CrmId

this relates to the GUID of the record in CRM

EntryId

this is the Outlook Entry ID

Type

this defines the entity type code for the tracked object

IsDeletedLocally (*)

this bit column helps the client and server keep track of which tracked items the user has deleted locally to prevent them from resynchronizing to the client

IsLinked

this bit column determines whether the item is still linked to a CRM record: True/False

Visited

this bit column shows whether the user has visited the item in Outlook

VerifiedTime (**)

this column indicates the last time the Outlook client verified the existence of the record

 

(*) - This field also plays a role in the SubscriptionManuallyTrackedObject table on the server.

(**) - The add-in runs a periodic job to determine if any tracked objects in Outlook have been deleted. If the add-in detects that an object is now missing in Outlook, a delete insert is created in the OutlookSyncTable, which is carried out in the next synchronization.