Synchronization Strategies¶
Updating a class table is not automatically called on a change of a record in M-Files. There are various methods that can be used to action or schedule an update. The methods used will depend on the use case, the volume of data and the performance requirements.
This section outlines the various strategies that can be used to keep the class tables in sync with M-Files for reporting and data exchange purposes. The next section Integration connector provides more details on strategies that involve integration with other systems and advanced use cases.
Synchronization-strategies¶
The Connector is often used interactively using SSMS to perform updates on demand. However, in production scenarios it is common to schedule updates at regular intervals or to trigger updates based on events in M-Files or other systems.
When using the Connector interactively in SSMS, the procedures outlined in Update Approaches can be called directly. For production scenarios, the following automation strategies are commonly used:
Using an agent to schedule updates at regular intervals.
Using a PowerShell script to schedule updates at regular intervals.
Updates on demand triggered by an external application.
Updates triggered by an M-Files event.
Updates as part of a workflow script.
Before making changes in SQL.
Using the context menu in M-Files.
If SQL Server Standard (or higher) edition is used for MFSQL Connector, use a SQL Agent job. Refer to Using Agent for automated updates for more details.
If SQL Express edition is used, then use the PowerShell utility to schedule a task.
In either case the procedure spMFUpdateAllncludedInAppTables will allow for both incremental and full updates.
We recommend scheduling an incremental update at an appropriate interval for your needs, as well as a full update at least once a day.
Note
Records destroyed in M-Files is not picked up by the incremental update. It requires a full update to be picked up. Records deleted or undeleted will be picked up by the incremental update.
Updates on demand¶
Updates can be triggered on demand in various ways: spMFUpdateTable with update method 1 can be called to update the class table from M-Files. The procedure spMFUpdateMFilesToMFSQL should be used in scenarios where higher volumes of data are to be processed as this procedure is optimized for performance by batching the updates and allows for incremental updates.
spMFUpdateTableWithLastModifiedDate can be used to update records that have changed since a specified date and time. This is particularly useful when the last modified date and time of the records is known.
spMFUpdateTableinBatches can be used to update records in batches. This is useful when dealing with large dataset takeons or trouble shooting.
Updates triggered by an M-Files event¶
Before making changes in SQL¶
The general rule is to always update from M-Files before a record is changed in SQL. This is done by first calling the spMFUpdateTable with method 1 for the records that will be updated and then to call spMFUpdateTable with update method 0 after the the SQL record is update to push the update back to M-Files. The procedure spMFUpdateMFilesToMFSQL is particularly valuable in this regard. Depending on the requirement and volume of data to be taken into account other update procedures can also be used as outline in section Insert Update of Class Records.
Using an agent¶
If the requirement is to refresh the class tables at intervals (for instance overnight, or every 2 hours) an agent can be used with
On demand in an external application¶
An action button in an external application can call spMFUpdateTable with update method 1; alternatively spMFUpdateTableWithLastModifiedDate can be used.
Action as part of a workflow script¶
We are currently working on a method that will allow for calling the procedure by using the context menu vault application from a workflow. This will trigger the update from the workflow state change.
Process Logging¶
Each synchronization can (optionally) attach to a ProcessBatch. See: spMFProcessBatch_Upsert.
Validation Queries¶
SELECT ClassTableName, PropertyCount = COUNT(*)
FROM dbo.MFClassProperty
GROUP BY ClassTableName
ORDER BY PropertyCount DESC;