Synchronization steps

From Resco's Wiki
Jump to: navigation, search
Synchronization

This is a technical description of the steps that are performed during a synchronization.

Connection to the CRM server

Security note: All communication is over https, some users additionally use VPN.

Step 1: Authentication

  • Usually requires login and password. (OAuth2 is multi-factor authentication and can use e.g. supplementary email verification)
  • Result: Security token (tokens expire after a few hours)

Step 2: WhoAmI -> SystemUserId, OrganizationId, BusinessUnitId

Security token is included in every following server request. Based on it server decides

  • Which client actions are allowed
  • Which server records are visible to the server

...

Uploading local changes

The application logs all changes of the local database to two special tables:

  • rs_changelog - changes in entity tables
  • rs_changelognn - changes in relationship tables

Entities are sorted before upload, so that forward references are minimized. This is not always possible, for example in case of circular references. So, whenever possible, we upload parent records (quote, invoice...) before child records (quotedetail, invoicedetail...).

Tip By modifying ChildParentMap (i.e. adding new parent-child relation) you can influence the upload order.

Records within an entity are uploaded sorted either by creation date (Woodford Configuration: Chronological Upload Order) or by last modification date (default).

  • ExecuteMultipleRequests - 100 records in a batch
  • Questionnaires need increase to 500 records/batch

Special considerations

  • Lookups may present a problem. They may point to a record that does not exist on the server.
  • In some cases, the state of record cannot be updated, for example a closed quote. Status change must be performed at the end of synchronization.
  • We may create a new record on the server, but do not get any confirmation. Next time we try to re-create an existing record. Similar problem can happen for record update.
  • Some entities such as quote require special processing
  • Conflicts
    • We may update server record that was meanwhile deleted on the server.
    • Both server and client edited the same record.

The upload

The uploader performs two passes

  1. First pass executes "easy" updates (data, lookups - if possible).
    If the update fails, we try to remove lookups and repeat the update. (Log: Update NoLookups for: account[...])
  2. Second pass executes failed lookups & status changes

The information about the upload process ends up in the synchronization log

  • SyncLog contains info about all problems (including conflicts)
  • If you enable sync details (using the Setup form in Resco Mobile CRM app (parameter Diag. Sync Log) or via Woodford), you will see all upload actions.

Result

In optimal case, all changes are uploaded to the server.

Some changes may fail to upload, due to:

  • Communication problems
  • Server errors
  • Conflicts

In case of upload problems, SyncErrorsForm opens in the application:

  • It presents all upload problems (incl. conflicts).
  • It allows users to resolve the conflicts.

Consequences of upload failure

  • ForcedFullSync is refused (unless AppSettings.ForcedFullSyncDateAlways == true)
  • New customization is refused (except special case when the new customization leaves data schema and SyncFilter intact - such customization is applied)
  • Downloader skips changed records (so that the changes are not lost)

Example log files

Standard log

Upload:NoChangesLeft			// Or: SomeChangesNotUploaded + <Error>...</Error>
...
<UploadSync> Entitys=969ms Sent=3 SvrCalls=1 TotalTim=969</UploadSync>

Detailed log

<UploadSync>
<Upload entity=resco_mobileaudit>

1> Create resco_mobileaudit[applicationstate, 5ceaf7df-b283-4cff-8e09-a9e79b9e3bf7]
2> Create resco_mobileaudit[applicationstate, f2d10927-51a6-4db4-9f90-1c9df524b59e]
3> Create resco_mobileaudit[synchronization, 4f20ab68-744d-41bb-9393-40adb057dd6a]
MultiSend> 3 packets in 844ms
ClearLog 5ceaf7df-b283-4cff-8e09-a9e79b9e3bf7
ClearLog f2d10927-51a6-4db4-9f90-1c9df524b59e
ClearLog 4f20ab68-744d-41bb-9393-40adb057dd6a
</Upload>

</UploadSync>

Upload:NoChangesLeft
...
<UploadSync> Entitys=969ms Sent=3 SvrCalls=1 TotalTim=969</UploadSync>

Customization

In the next step of synchronization we check licenses and update customization.

License check

To check licenses, the app connects to Resco licensing service: iservices.resco.net.

The result of the license check is written into the synchronization log. The result can be either success (License: Valid) or one of the following errors:

Trial Expired | NOT FOUND | EXPIRED | INVALID ORGANIZATION | INVALID USER | EDITION NOT ALLOWED | URL NOT ALLOWED | INVALID SIGNATURE | INSUFFICIENT

Device configuration (remote device control)

The app sends device description to the CRM server, including the following parameters:

  • SystemUserId
  • Device ID, name, operating system, version of the mobile CRM app
  • Device state (Normal | Wiped | Locked)
Tip App users can see their device ID in About form, in the log file.

This information is stored on the CRM server (Dynamics or Resco CRM server), in the entity resco_mobiledevice (Dynamics) or mobiledevice (Resco). Installations with Salesforce store this information in Resco CRM server.

When a device registers at the server during its first synchronization, a new record is created. Each device has exactly one such record.

So what happens in the app during synchronization:

  1. App downloads its resco_mobiledevice record from the server.
  2. App performs the necessary management actions (for example lock device) or replaces the security policy. (Administrator can plan these actions using Woodford's Device control function. Technically, Woodford updates the resco_mobiledevice record for that device.)
  3. App updates the record on the server.

Download mobile project from CRM server

  1. Download resco_mobileproject for this user (settings.SystemUserId).
    • If the project is not found (log "Customization NOT FOUND") => Switch to DEMO mode (delete customization, WWW...) & exit.
    • If the project did not change (log "Customization NOT MODIFIED") => Exit.
  2. Check whether the version of the app supports the customization. Users that use an obsolete version of the app receive the error Your Server customization requires new version of the Application. Please Update your Application.
  3. If this is a background synchronization, the synchronization will end and app's UI will indicate that an update is waiting and a foreground synchronization is necessary.
  4. Download (up to) three customization files. We store hashes of the previously downloaded files; only changed files are downloaded.
  5. Unpack customization.zip
  6. Check license: license.ValidateEntities(). This can result in an exception "LICENSE: INSUFFICIENT".
    At this point we have two customizations - old one & new one. The old is active. The new is unpacked except WWW/Images data.
  7. Adapt database schema to the new customization:
    • Drop tables that were removed.
    • Create new tables.
    • Drop columns that were removed.
    • Add columns that were added. (This forces full synchronization for the table.)
    AppSettings.DatabaseSchemaHash <- new database schema
  8. Apply server settings (= Woodford Configuration):
    The configuration is in the file ServerConfig.generated.bin.
    UseFlexiForms,636661280237246393,1,True		// Setting, version, readonly, value
    MaxImageSize,636665620336850414,1,Max_2048x1536
    RecordQuality,636661324791448237,1,Low
    ...
    
  9. Unpack WWW/Images data. This overwrites old customization.

Summary: At this moment new customization is fully activated except one thing: The app remembers that it is in process of changing the customization. Later, when the sync completes (successfully or not) and updated user interface is loaded, this transitional state will be terminated.

In the opposite case (when the app was terminated during synchronization or the new user interface fails to load), at the next app start MCRM deletes the database and returns to its previous customization. However, the WWW/Images folders are not reverted.

Forced full synchronization

Full synchronization essentially means one thing: delete data. This can be done by the user. However, it can be also initiated by admin via Woodford.

How it works: When the Woodford admin enables Full Sync (using Device control), the respective attribute (resco_forcefullsyncon) in the resco_mobiledevice record belonging to the client's device is set to current date and time. This value is passed to the client during synchronization. The client receives this date/time and compares it with the time of the last synchronization. If the last synchronization started prior to the Woodford device control action, client attempts a full synchronization.

In addition to the date/time value in ForcedFullSyncDate (can be also null), there's also the flag ForcedFullSyncDateAlways. When enabled, full synchronization continues even if there are unsynchronized local changes. The changes are deleted and everything is redownloaded from the server.

Caveats:

  • If this is a background synchronization, it is aborted and an error is displayed: "Please Start Sync Manually: Administrator Forced Full Sync".
  • If ForcedFullSyncDateAlways is false and there are unsynchronized local changes, synchronization is aborted with error "Can't execute Full Sync forced by Administrator because upload of local changes failed".

Download permissions

This step is performed:

  • during full synchronization
  • when the new customization contains new entities
  • at first synchronization since app start
  • {Dynamics) when Field Level Permissions is set to true in Woodford configuration

Permissions include

  • Entity privileges (Create, Read, Write, Delete, Append, AppendTo)
  • Field-level permissions (if AppSettings.SyncFieldLevelPermissions is enabled; CRM5 only)
  • List of teams the current user is member of (if AppSettings.Teams is enabled)

Besides, a number of other global settings and information items is downloaded from the server, for example in case of CRM5:

  • OrganizationId, BusinessUnitId
  • CurrencyDecimalPrecision, CurrencyDisplayOption, CurrencyFormatCode, UserDefaultCurrencyId
  • FullNameConventionCode
  • DiscountCalculationMethod

Salesforce-specific information

  • Download permissions step is executed for each customization update.
  • Field-level permissions are always applied (AppSettings.SyncFieldLevelPermissions is ignored).
  • Few extra server actions are executed during each sync:
    • Layout configurations are updated (unless multi-layout support is disabled).
    • Custom actions required for content document management.

DeletePlugin

DeletePlugin is a Resco module that can be installed on the Dynamics server. It collects information about deleted records, as well as records where the ownership changed (this info is missing for Salesforce).

We recommend that you activate the DeletePlugin on the Dynamics Server. If not, server deletions or ownership changes are not propagated to the client. See Plugins for more information.

Why do we need it?

We need information about deleted records because of the mechanic how incremental synchronization works: Give me records that were changed since this date. Deleted records disappear from the server, hence they cannot be returned. Note that the same argument applies for record ownership changes: If a record was assigned to me, it wasn't changed, and the normal incremental synchronization request does not return this record.

This problem only applies to Dynamics CRM. Salesforce maintains a RecycleBin, which has its own set of problems, such as limited expiration duration.

See also:

Incremental and full synchronization

Full synchronization fetches all records that match the Sync Filter.

Incremental synchronization fetches all records changed since the last synchronization. Additionally, it performs a cleanup: records that do not match Sync Filter are deleted.

For more information about the differences between full and incremental sync, see Types of synchronization. For information about how to force full sync for an entity, see Managing entities.

Example: Why is this needed?

Download all accounts except inactive:

  <fetch version="1.0">
    <entity name="account">
      <filter type="and"> <condition attribute="statuscode" operator="ne" value="2" /> </filter>
    </entity>
  </fetch>
  1. Sync1: Account A is downloaded to the client using FullSync-fetch.
  2. Account A is deactivated on the server.
  3. This record won't be downloaded using SyncFilter-fetch.
  4. This record will be downloaded using IncSync-fetch. It will be later deleted in the cleanup phase.

SyncAnalyzer

This step is used during incremental synchronization, unless AppSettings.DisableSyncAnalyzer==true.

It provides an answer to the question, for each entity, is it better to to download all records changed since the last synchronization, or to download all records that match SyncFilter at this instant?

The results are written to the log:

<SyncAnalyzer SkipEntitys=35 Tim=1375ms/>
<SyncAnalyzer <FullSync sf_contentdocumentlink sf_leadstatus sf_opportunitystage/> SkipEntitys=24 Tim=640ms/>

Logged details (optional):

*** SyncAnalyzer ***
contact Inc:15 -> IncSync
annotation Inc:1166 Full:34 -> FullSync (750ms)
koo_herbagetestresult: CantAnalyze(SyncLinked)
koo_soiltestresult: CantAnalyze(SyncLinked)
duplicaterule: ForcedFullSync
resco_chatpost: UploadOnly
resco_chatcomment: UploadOnly
resco_mobileaudit: UploadOnly
duplicaterulecondition: ForcedFullSync
resco_mobilereport: FirstSync
salesorderdetail: FirstSync
businessprocessflowinstance: FirstSync
transactioncurrency Inc:0 -> Skip
pricelevel Inc:0 -> Skip
businessunit Inc:0 -> Skip

Advantages and disadvantages

  • Helps you avoid excessive download
  • It is fast (uses 5 threads simultaneously)
  • One of the problems when SyncAnalyzer is that it increases the number of API calls. (Pricing policy of CRM servers is often directly related to the number of API calls.)

RowVersion versus LastSyncDate

Database tables often have a hidden column RowID - unless the database admin explicitly requested that this column is not created. For each database operation, this column is filled from the global database counter: table.RowID = ++GlobalRowID

Initially (during full synchronization) we request all records that match SyncFilter condition. Fetch response usually contains the so-called MinActiveRowVersion.

<b:MinActiveRowVersion>3329419</b:MinActiveRowVersion>

If we receive this value, we store it for each table. For our subsequent (incremental) synchronization, we request all records where RowVersion > MinActiveRowVersion.

If the response does not contain MinActiveRowVersion

<b:MinActiveRowVersion>-1</b:MinActiveRowVersion>

we store the current date and time instead. This is less exact, but good enough.

We store these values in the rs_sync2 table (one row per table). The table is updated after the table download successfully completes.

[rs_sync2]
table			LastSyncDate		maxrowversion
rs_delete_tracking	12/4/2018 9:38:41 AM
progres_country		12/4/2018 9:38:41 AM	569257891
businessunit		12/4/2018 9:38:42 AM	569257891
systemuser		12/4/2018 9:38:44 AM	569257891
resco_mobileaudit	12/4/2018 9:38:44 AM
team			12/4/2018 9:38:45 AM	569257893
...

The parameters LastSyncDate and maxrowversion are sometimes called anchors.

Synchronization can be interrupted. In such case:

  • Some anchors may be missing. They will be full-synced next time.
  • Some tables may have old anchors. Only part of changes was downloaded - they will be re-downloaded next time.

SyncEntities

In this step we download and update the entity changes for normal tables and relationship tables (NN).

foreach "normal" entity
{
	Start transaction
		Download server records (Either FullSync or IncSync fetch is used)
		If this is activity-entity: Download ActivityParty's for this entity
		For entity "list": Download "listmember" records
	Commit transaction
}

Remarks:

  • This module uses multi-downloader: three simultaneous download threads (each has a separate document SyncSetup.txt).
  • Page-size = 500 except emails (100) // Can be changed
  • FullSync drops indexes before the download and re-creates them at the end. This optimizes performance.
  • Huge tables: Always after 20,000 records there is an intermediate commit. This prevents huge (>> 1 GB) WAL log files.
  • BkSync checks if there is a waiting writer. If so, it temporarily pauses the transaction. (Commit, pause... re-open transaction)
  • Records changed locally (if any) are never overwritten.
foreach NN-entity
{
	Download server records (FullSync or IncSync)
}

Remarks:

  • NN-entities use page-size 2500.
  • Transaction is opened for short periods - while storing one page. => Basically non-blocking.

Cleanup

Cleanup step is performed for each entity. We delete records that do not match SyncFilter.

Special cleanup actions:

  • Delete old audit records that were uploaded to server.
  • Delete activityparty records that do not belong to any activity
  • Delete orphaned annotations.
<Cleanup ... Sum:140ms, Deleted 2869+0nn />
<Cleanup 265ms, Deleted 1+0nn />
<Cleanup ActivityParty*419 annotation.orphans*529 Sum:141ms, Deleted 1016+0nn />
<Cleanup 
	contact*413/3625ms 
	atx_dlsrequest*0/344ms 
	atx_visittopic*0/344ms 
	email*0/313ms 
	activitymimeattachment*0/359ms 
	atx_additionalinstrumentimplant*41857/1188ms 
	annotation*0/407ms 
	atx_navnotes*3743/765ms 
	ActivityParty*22630/672ms 
	annotation.orphans*11556/2172ms 
	listmember*24866/421ms 
	Sum:13719ms, Deleted 80246+24987nn />

Notes:

  • Cleanup of a large database may take long.
  • We avoid partial cleanup actions if we know that they are not needed. (For example, the entity was not changed and the SyncFilter is not time-dependent.)
  • There is no cleanup if the sync was interrupted.

If there are too many deletes, try to find out why.

  • In case of incremental synchronization: Wouldn't be better to set ForcedFullSync for given entity?
  • In case of full synchronization: Can you improve SyncFilter?

There is additional cleanup related to things we did not discuss yet, for example documents.

Interrupted synchronization

Some errors are fatal in the sense they interrupt synchronization. Typical reasons for interruptions are:

  • Dropped connection
  • Program error

Synchronization is a step-by-step process. If it is interrupted during certain step, the following steps are not executed.

Hence, if the sync is interrupted when syncing some entity:

  • Some entities may be completely updated, while the remaining ones may not be updated at all.
  • Entity that was interrupted may be partially updated. (Downloads are committed in steps of 20000 records.)
  • Cleanup was not executed.

Hence the data is workable, but be careful. Your next synchronization will resume where the last sync stopped. For partially updated entities, the records downloaded during the last sync will be re-downloaded.

If you have a huge database with millions of records, there is nothing wrong if the sync executes in several steps.

Special actions

Attachments

What are attachments?

In case of Dynamics CRM:

  • activitymimeattachment.body
  • annotation.documentbody
  • salesliteratureitem.documentbody

In case of Salesforce:

  • sf_attachment.body
  • sf_document
  • sf_quotedocument.document
  • sf_contentversion.versiondata
  • *__kav.*__body__s

All these attributes can contain large data. Hence they are processed separately and stored as record attachments.

Remark for annotations (notes): An annotation can contain a text note or an attachment or both.

CREATE TABLE [annotation] (
    [annotationid]   UNIQUEIDENTIFIER PRIMARY KEY,
    [createdon]      DATETIME,
    [documentbody]   TEXT,		// Null or blob that is stored as attachment (filename content)
    [filename]       TEXT,		// Label for documentbody
    [filesize]       INT,
    [isdocument]     BIT,		// True if documentbody!=null
    [mimetype]       TEXT,
    [modifiedon]     DATETIME,
    [notetext]       NTEXT,		// Note text, stored as normal attribute in the DB
    [objectid]       UNIQUEIDENTIFIER,  // Record to whom this note belongs; 1 record can have multiple notes
    [objecttypecode] TEXT,
    [ownerid]        UNIQUEIDENTIFIER,  // Note title
    [subject]        TEXT,
    [objectidTarget] TEXT,
    [owneridTarget]  TEXT
);
How are attachments downloaded during Sync?
  1. Collection: Download of annotation (activitymimeattachment, salesliteratureitem) records. Attribute documentbody is excluded from the download.
    For each downloaded record
    {
    	if( isdocument == true && 
    		fileSize <= AppSettings.MaxAttachmentSize && 
    		BlobStoreFilter.AttachmentFilter.Matches("annotation", fileSize, fileName, objectidTarget)
    	{
    		Store (annotationid, "annotation", fileSize) -> AttachmentQueue table
    	}
    }
    
  2. Attachments download. This is a separate synchronization step executed once the download of "normal" entity records is over. In case of a foreground synchronization, the app shows "Attachments" in the synchronization progress bar.
    Download jobs (*) stored in AttachmentQueue table are executed:
    • Small attachments are downloaded in parallel (10 threads)
    • Large attachments are downloaded sequentially one by one.
    (*) This is actually a fetch of annotation record with given ID, but this time only 1 column is requested - document body.
Azure attachments

Dynamics 365 users may store note and email attachments using Azure Blob storage. This storage is much cheaper than storing large files directly in Dynamics.

Using this external storage requires adjustment of the fetches that MCRM normally uses. To support Azure blob storage, set AppSettings.SyncAzureAttachments=true.

Note This is rather undocumented area. MCRM so far supports annotation attachments. Possibly activitymimeattachment attachments should be included, too.
How are the attachments stored?
  • Blob\annotation\{recordID}
  • Blob\activitymimeattachment\{recordID}
  • ...
What happens to non-downloaded (refused) attachments?
(For example those exceeding MaxAttachmentSize)
These attachments are downloaded on-demand, when the respective form is opened. The app knows that the attachments exist and it displays a download button.
What happens when the sync is aborted during the attachment download?
  • Downloaded attachments were periodically (~once per second) removed from AttachmentQueue.
  • Non-downloaded attachments remain in the queue and will be downloaded during the next sync.
Asynchronous attachment download
(If AppSettings.AsyncAttachmentsDownload==true; only applies to foreground synchronization)
The synchronization engine initiates the attachment download, but does not wait until it completes. Synchronization is gracefully terminated (attachment download is usually the last major step), progress switches to background and users can continue working with MCRM.

BlobStoreFilter

Blob store filters allow you to restrict files that should be downloaded from external storage, for example a SharePoint server.

See Document filters for more details.

<?xml version="1.0" encoding="utf-8"?>
<BlobStoreFilter>
  <FilesFilter>
    <Quota>3000000000</Quota>
    <MaxFileSize>30000000</MaxFileSize>

    <Filters>
      <BlobStoreFileTypeFilter>
        <FileSuffix>jpg</FileSuffix> <MaxFileSize>1000</MaxFileSize>
      </BlobStoreFileTypeFilter>

      <BlobStoreFileTypeFilter>
        <FileSuffix>doc</FileSuffix> <MaxFileSize>1000000</MaxFileSize>
      </BlobStoreFileTypeFilter>

      <BlobStoreFileTypeFilter>
        <FileSuffix>rtf</FileSuffix> <MaxFileSize>0</MaxFileSize>
      </BlobStoreFileTypeFilter>

      <BlobStoreFileTypeFilter>
        <FileSuffix>*</FileSuffix> <MaxFileSize>1000</MaxFileSize>
      </BlobStoreFileTypeFilter>
    </Filters>
  </FilesFilter>

  <AttachmentsFilter>...</AttachmentsFilter>
</BlobStoreFilter>
<AttachmentsFilter>
    <MaxAttachmentSize>1000000</MaxAttachmentSize>
    <NotSyncingAttachmentsFor>
      <string>contact</string>
      <string>campaign</string>
    </NotSyncingAttachmentsFor>
</AttachmentsFilter>
<AttachmentsFilter>
    <MaxAttachmentSize>1000000</MaxAttachmentSize>
    <OnlySyncingAttachmentsFor>
      <string>contact</string>
      <string>campaign</string>
    </OnlySyncingAttachmentsFor>
</AttachmentsFilter>
<AttachmentsFilter>
    <EntityFilters>

      <EntityAttachmentsFilter Name="annotation" AttachedTo="account">
        <Quota>1000000</Quota>				// Quota is ignored (Not implemented) for attachments
        <MaxFileSize>100001</MaxFileSize>
        <Filters>
          <BlobStoreFileTypeFilter>
            <FileSuffix>jpg</FileSuffix> <MaxFileSize>5000</MaxFileSize>
          </BlobStoreFileTypeFilter>
          <BlobStoreFileTypeFilter>
            <FileSuffix>doc</FileSuffix> <MaxFileSize>10000</MaxFileSize>
          </BlobStoreFileTypeFilter>
        </Filters>
      </EntityAttachmentsFilter>

      <EntityAttachmentsFilter Name="annotation">
        <Quota>-1</Quota>
        <MaxFileSize>12345</MaxFileSize>
        <Filters />
      </EntityAttachmentsFilter>

      <EntityAttachmentsFilter Name="activitymimeattachment">
        <Quota>-1</Quota>
        <MaxFileSize>-1</MaxFileSize>
        <Filters />
      </EntityAttachmentsFilter>
    </EntityFilters>
</AttachmentsFilter>

DownloadEntityImages

In Dynamics, entity images are icon-sized images that can be defined for each record of selected entities. For example, you can associate a photo to a person record.

Only some entities support images. These entities must have the following attributes:

The following system entities support images (however, only some of them are enabled by default):

  • Account, Contact
  • Product, Resource
  • Lead, Competitor
  • SalesOrder, Invoice, OpportunityProduct
  • Incident, Contract, Goal, Campaign
  • KbArticle, SalesLiterature
  • Connection, TransactionCurrency
  • EmailServerProfile, Mailbox
  • Queue, Territory
  • SystemUser, Publisher, Organization

During the image download phase of the synchronization process, the app shows "Images" in the progress bar.

We execute the following for each entity that has "entityimage" attribute:

{
    Fetch all record IDs with image timestamp > TimeWhenThisFetchWasExecuted
    {
        Add record ID to the AttachmentQueue
    }
    Store permanently TimeWhenThisFetchWasExecuted.
}

Actual images are downloaded later together with attachments.

The images are stored in the same way attachments; as Blob\Account\{recordID}. There's no conflict with attachments because attachment entities cannot have images.

SyncSharedEntities

In Dynamics CRM, users can share records to others. For example, when user A shares the record R to user B:

  • R is added to so-called POA (PrincipalObjectAccess) table. (In this context, principal means a user, or a team, etc.)
  • R becomes "visible" to user B.
  • R itself was not modified through the act of sharing.

This mechanic can have the following impact on Resco Mobile CRM app:

  • Full synchronization: No impact. Shared records are downloaded together with other records the mobile user is entitled to see.
  • Incremental synchronization: Newly shared records (that were not modified) won't be downloaded. This is a problem.
If AppSettings.SyncShared == true
{
    Sync progress bar shows "Shared".
    foreach normal entity (not NN)
    {
        Fetch id's of all entity records that were shared since the last sync    // Fetch POA table records
        If there are such records
        Fetch those of them that match the SyncFilter
    }
}

Similar process also runs for records that were unshared since the last sync.

Notes on POA:

  • This table can be important for Technical Support. This table is known to deteriorate server performance. (Possible server timeouts if POA has multi-million entries.)
  • Any fetch executed on some table incorporates implicitly also shared records:
Original fetch
 SELECT * FROM email WHERE condition
is replaced by:
WITH emailSecurity as (SELECT * FROM email UNION SELECT shared emails from POA)
SELECT * FROM emailSecurity WHERE condition

SyncLog

This synchronization step saves information to the sync log.

<SharedEntities Recv=1234 [Del=123 Conflicts=12] TotalTim=12345ms />
<FullSync Recv='14719' TotalTime='15828ms' Entitys='2312ms' ManyToMany='578ms' Shared='469ms' MarketingLists='11953ms' />

Cloud document servers

Multiple solutions exist for saving and sharing documents online, for example: SharePoint, OneDrive, GoogleDrive, DropBox, Box, etc. Users may use any number of them.

Basic idea: A CRM record may have a cloud folder (or multiple folders in case of SharePoint) containing 1 or more attachments. These attachments are listed in the Documents tab in the record DetailView. A single attachment cannot be assigned to multiple CRM records.

Cloud servers must keep on specific folder structures that makes possible to determine the document location.

SharePoint

SharePoint is a web-based collaborative platform that integrates with Microsoft Office. Launched in 2001, SharePoint is primarily sold as a document management and storage system, but the product is highly configurable and usage varies substantially among organizations. SharePoint supports hundreds of protocols for every possible use.

Logical storage

  • Site collections
    • Sites (top level site)
      • Sites (optional sites)
      • Lists
        • Lists items organized in a folder hierarchy (task, announcement, contact)
      • (Document) libraries
        • Documents (with their associated metadata) organized in a folder hierarchy

List items and documents (basic units of content) have unique URL.

Users can create lists (1) and customize them with new columns, forms, and views.

SharePoint integration with Microsoft Dynamics

Dynamics CRM sees SharePoint as a set of document libraries that store documents related to CRM records; other SharePoint aspects are ignored.

  • Cloud folders (subfolders of document libraries) are assigned to CRM records.
  • Files in these subfolders are record attachments.

In standard integration, CRM admin selects entities (account, contact…) that can have SharePoint attachments. In turn, respective SharePoint document libraries are created: One library per one entity with the library names identical to the entity names.

Formally, the Dynamics server contains multiple entities that describe the locations of SharePoint documents that are managed by Dynamics CRM:

  • sharepointsite entity records describe SharePoint sites.
  • sharepointdocumentlocation records (“locations”) contain a URL (subfolder of some SharePoint document library) and a reference to the CRM record (regardingobjectid) to which the documents are attached.
  • sharepointdocument entity: From Resco CRM point of view, this entity contains redundant information; do not use it in your projects.
CREATE TABLE sharepointsite (
    sharepointsiteid UNIQUEIDENTIFIER PRIMARY KEY,       // PK
    name TEXT,
    absoluteurl TEXT, relativeurl TEXT,                  // Absolute/Relative URL of the SharePoint site.
    parentsite UNIQUEIDENTIFIER, parentsiteTarget TEXT,  // Parent SharePoint site
);
CREATE TABLE sharepointdocumentlocation (
    sharepointdocumentlocationid UNIQUEIDENTIFIER PRIMARY KEY,
    name TEXT,
    regardingobjectid UNIQUEIDENTIFIER, regardingobjectidTarget TEXT, // Points to CRM record that "owns" attachments in this location
    absoluteurl TEXT, relativeurl TEXT,
    parentsiteorlocation UNIQUEIDENTIFIER,               // FK sharepointdocumentlocation|sharepointsite. Relation sharepointdocumentlocation_parent_sharepointsite uses cascading delete.
    parentsiteorlocationTarget TEXT,
);

Default folder structure:

  • <entity_type_name> (Document location RootFolder)
    • <record_name>_<record_id> (Subfolder for specific entity record)
      • <file_name> (Cloud document attached to the record)

SharePoint synchronization

a) Using old List service API
  1. For each site: GetListCollection (Does not return list RootFolder, hence we need additional GetList calls)
  2. For each possibly interesting list: GetList (get list details)
  3. Get list content (file names and attributes): GetListItems
  4. New files that match BlobStoreFilter are stored in the download queue
b) Using new REST API (enable SharePoint REST API parameter in Woodford configuration)

Same process except step 2 is skipped. Faster.

c) FileDownloadQueue

Download requests are stored in rs_documentQueue table. Download starts with the first request enqueued. Since that time next requests are added to the queue, while they are parallelly executed one by one using 10 threads. The queue runs asynchronously and it is used also by other file engines (if any). However, after the file engines sync is over, SyncEngine waits for the queue completion. If the sync is interrupted, queue download is resumed at the next sync.

Log examples
// Nice case - using REST API
<SharePointDownload REST=1 Tim=3015ms GetListCollection=3x/1438ms GetListItems=9x/1390ms #FileInfosSaved=256/>
<FileDownloadQueue Threads=10 Tim=12625ms Downloaded=256 TotalDownloadSize=56712K Speed=35.094Mbps/>
// Nice case without REST API (Using REST API would save ~860ms)
<SharePointDownload Tim=3016ms GetListCollection=1x/1515ms GetList=31x/860ms IgnoredLists=7 GetListItems=2x/563ms #FileInfosSaved=62 #NotDownloaded=11/>
<FileDownloadQueue Threads=10 Tim=4922ms Downloaded=51 TotalDownloadSize=18714K Speed=29.704Mbps/>
// Nothing downloaded because of BlobStoreFilter
<SharePointDownload REST=0 Tim=2438ms GetListCollection=1x/891ms GetList=6x/438ms IgnoredLists=19 GetListItems=3x/1000ms #FileInfosSaved=30 #NotDownloaded=30/>
// BlobStore quota in action
<SharePointDownload Tim=10328ms GetListCollection=4x/3719ms GetList=41x/1172ms GetListItems=15x/4392ms SaveListItems=548ms #FileInfosSaved=1662 #NotDownloaded=6/>
<FileDownloadQueue Threads=10 Tim=205750ms RefusedForQuotaExceeded=1301 Downloaded=355 TotalDownloadSize=1034536K Speed=39.282Mbps/>
// Slow download (reason unknown)
<SharePointDownload Tim=59547ms GetLists=6954ms/41webcalls GetListItems=16x/52312ms SaveListItems=2499ms #FileInfosSaved=24873 #NotDownloaded=22459/>
<FileDownloadQueue Threads=10 Tim=327531ms Downloaded=2414 TotalDownloadSize=66104K Speed=1.577Mbps/>
// Slow because of many sites (192). At least 1/2 of them could be eliminated in setup. (sharepointsite table)
// Using REST API would help marginally.
<SharePointDownload REST=0 Tim=73094ms GetListCollection=192x/44304ms GetList=81x/3688ms IgnoredLists=1240 GetListItems=173x/16294ms #FileInfosSaved=0/>
// Extreme case - many lists
<SharePointDownload Tim=1215375ms GetListCollection=282x/161684ms GetList=60145x/995196ms IgnoredLists=1332 GetListItems=175x/44298ms #FileInfosSaved=10 #NotDownloaded=10/>

Other file engines

Other cloud storage servers (such as OneDrive, GoogleDrive, DropBox, Box, ...) must keep a specific folder structure that makes possible to determine the document location automatically.

File engines have to use this folder structure (inspired by the folder structure used by Dynamics CRM / SharePoint integration):

  • <root_folder> (setup)
    • <entity_name> (account, contact...)
      • <record_name>_<record_id> (John_Smith_12aaa7f78a7f41deaf4bcaccb43df2ce)
        • files (Capture_170627_171139.jpg, note.txt...)

Common setup error: Users forget to specify root folder in Woodford Configuration. In this case, nothing is downloaded.

Log examples
WARN: GoogleDrive: RootFolder=null, sync skipped
<OneDriveDownloadSync TotalTim=58641ms #FileInfosSaved=3573 #FilterIgnoredFiles=2892 SaveChangesCalls=21x/595ms/>
<FileDownloadQueue TotalTim=57375ms FilesRefusedForQuotaExceeded=531 FilesDownloaded=150 TotalDownloadSize=4882K Speed=4.741Mbps/>

Closing remarks

These are the app settings that control synchronization:

General
bool LogSyncDetails; Whether the synchronization log should include low level messages
bool MultiThreadSync; Whether to use multi-threaded synchronization. (Both upload+download) Default true.
SyncDownloader - IncSync fetches
bool IncSyncUsesVersionNumberSorting; Whether incremental sync fetches use "order by versionnumber" wherever possible. Since 10.2, default true.
bool UseRowVersionChangeTracking; Whether to use RowVersion or ModifiedOn in IncSync fetches. Default=true, i.e. RowVersion.
bool UseLinkedSyncFilter; Whether to use OR grouped incremental sync filter created from linked parents
SyncDownloader - all fetches
int EmailFetchPageSize; Download page size for email entity used in fetches. Must be between 20-500, default is 100.
bool SyncNoLock; whether to use "no-lock" in FetchXML during sync. Default=false. Consequence: SQL Server uses READ UNCOMMITTED for sync fetches. Faster fetches (on high transaction oriented tables), risk of reading data that will be discarded with Rollback.
SyncDownloader - other settings
bool SyncEmailAsText; Whether to strip Html from email body during sync. Since 6.4, default false.
int MaxSyncCount; maximum number of records (per entity) to download; ActivityPartys use 5*MaxSyncCount limit
SyncUploader
int MaxExecuteMultiple; Max #records to upload in one request. Default=100, Max=1000. (Current questionnaire design needs higher values.)
bool UploadRecordsInChronologicalOrder; Whether sync uploads recs in the order in which they were created/deleted/edited for the 1st time. Default=true
Attachments
int MaxAttachmentSize; maximum attachment size to download sync (in bytes)
bool AsyncAttachmentsDownload;
bool SyncAzureAttachments; Whether to use special web method to download note attachments stored in azure blob storage
Sync modules
bool DisableSyncAnalyzer; Whether the synchronization should use the SyncAnalyzer step
bool SyncShared; Whether to download shared entities. (Extra sync pass).
bool UseRestApiForSharePoint; Whether to use (more efficient) REST API for SharePoint server calls, default false. So far implemented only for 1 web call.
Customization
bool UpdateObjectTypeCodes; Whether to update entity objectypecodes (ObjectTypeMap.csv) as part of DownloadPermissions(). Dynamics only, default=false. Overrides objecttype codes stored in EntitySchema.csv.
bool FullSchemaSync; Whether to do DeleteData for new customization instead of incremental DB schema update
bool SyncFieldLevelPermissions; whether to download field-level permission as part of DownloadPermissions(). Crm5 only. Default=false.
bool EnableCustomizationSecurity; Enables the Offline HTML and customization files content verification. Default=false.
N/A yet
bool ShowSyncWarnings; Whether the SyncEngine should return warnings (if any) in the same way as errors. ALWAYS TRUE.
Exotic settings
bool IsDatabaseAuditEnabled; Special server log that contains SYNCSTART/SYNCEND commands & DB read commands for selected entities. Used for single customer only!
bool UseDatabaseEncryption; Whether the database is encrypted. Only used when the database is created (full sync"). Default true.
bool UseDatabaseBlobStore; Whether to store attachment blobs in database or in files
int SyncEmailOnSend; Whether to sync emails when new email is sent. 0 - enabled

Advanced SyncSetup

  • BlobStoreFilter.xml - see Document filters
  • SyncSetup.xml - see Advanced sync setup
    Finetuning of the SyncDownloader (# of threads, cache size, fetch page size, order of entities). This tool might speed up (mainly) FullSync. Look for the sync log item: <Downloader CacheSize=100MB UsedCache=81MB Paused=18% />. If there are large pauses or UsedCache approaches CacheSize, then it makes sense to apply special sync setup.

Synchronization result

SyncEngine = Code name for the synchronization module.

SyncEngine.Execute() = Method executing the synchronization.

Returns:

  1. Fatal error (error that interrupted sync process.)
  2. Otherwise warning (*)
    • "Warning:\nAccount entity has more records than allowed in the configuration; not all records were downloaded."
    • "Warning:\nBackground Sync cannot reliably synchronize contact entity. Consider using Foreground Sync.\n(Go to Setup > Accounts > CRM and tap the sync button.)"
    • "Warning:\nSharePoint synchronization failed.\n(The reason is logged.)""
    • "Application can't be updated because of Sync Errors.\nPlease resolve them and synchronize again."
    • "Sync generated multiple warnings. Please see the log."
  3. Null (everything ok)

(*) AppSettings.ShowSyncWarnings is the setting that could be used to exclude warnings. So far not used.

Email synchronization

Synchronization of emails is not included in SyncEngine. Even the log is separate (available from the About form). We support the following email servers: Exchange and Gmail.

Email synchronization is launched after SyncEngine.

errMsg = Data.Synchronization.SyncEngine.Execute()
if( errMsg == null &&           // THIS IS WHERE AppSettings.ShowSyncWarnings MATTERS
    && !Aborted                 // Abort is done by OS when the app runs on background and OS needs more resources. iOS gives us a chance to react, Android/W10 not.
    && !AppLocked && !AppWiped) // App can be locked/wiped from Woodford. (The check is done during customization check.)
{
    errMsg = EmailEngine.Execute();
}
if (errMsg != null)
    ShowError(errMsg);

Email synchronization can run also without SyncEngine. Users can initiate the action by clicking a dedicated button for email synchronization, or it can run automatically when they create a new email (assuming AppSettings.SyncEmailOnSend==true).