Dynamics CRM Outlook client: Organization DB table structures - Client side

About the series

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

Concepts & Rules

• Dynamics CRM Outlook client: Scenarios and Synchronization Rules

• Dynamics CRM Outlook client: Organization DB table structures - Server side

• Dynamics CRM Outlook client: Organization DB table structures - Client side

• Dynamics CRM Outlook client: User defined fields

• Dynamics CRM Outlook client: Settings that affect Synchronization & Tracking

Troubleshooting

• Dynamics CRM Outlook client: Highlights and Simple Methodology for troubleshooting

• Dynamics CRM Outlook client: Simple list of Troubleshooting Tools

• Dynamics CRM Outlook client: Synchronization Scenarios - Changes to items in Outlook (ACT)

• Dynamics CRM Outlook client: Synchronization Scenarios - Changes to items in Dynamics CRM (ACT)

• Dynamics CRM Outlook client: Synchronization Scenarios - Track an item in Outlook (ACT)

• Dynamics CRM Outlook client: Synchronization Scenarios - Merging overlapping changes

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.

(*) - 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.

(*) - 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.