Documentation:SQL Server change tracking

Background

The MDrivenServer has build in change tracking. It's implemented as a "dirty list" of objects that all clients can ask for. When the client gets this list, it markes all matched objects in memory as "dirty" and discards them. If any of these objects are present in a user interface (that is currently shown), these objects will be immediately reloaded from the MDrivenServer/ SQL Server.

MDrivenServer's dirty list is not a part of SQL Server and out of the box the MDrivenServer won't find out if some other client or service changes the underlying tables directly in the SQL Server.

The need for external dirty list handling (aka. change tracking) is often needed only when there's legacy system reading and writing to the same SQL database. Because of this, it's usually not a good idea to have it on all tables in the database. You probably only want change tracking on the tables that can be affected by the non-MDriven system.

MDriven implementation

Support for change tracking in SQL Server

Tracking of changes is available from SQL Server 2008 and in Azure SQL. It's available in the Express editions, so you can use it in the built in Turnkey database.

To turn change tracking on, do like this

ALTER DATABASE <database name>
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) 

Background about this on MS site

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server

See also when implementing

Article with a lot of details: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-tracking-sql-server

How we propose you use the MDriven features to do change tracking

We want to put as little extra load as possible on the SQL Server. We therefore use to different jobs to detect that something changed and then what has changed

• Something changed: The stored procedure CHANGE_TRACKING_CURRENT_VERSION() can be used to know if ANY change has occurred. We compare this version value with the value when we last processed change information.

• What has changed: If there's a change, we iterate change information for all tables we're interested in.

Both of these methods use sqlpassthroughobjects to directly use SQL to return information.

It then uses SuspectExternalUpdateInvalidate to "invalidate", make dirty, the changes objects.

Support with other database servers

The MDriven implementation is not dependent on HOW you find out what has changed. As long as you can find the primary key of changed rows, you can use this functionality.

Suggested pattern and usage notes

The suggested pattern to allow for MDriven Applications to effectively discover changes to data made by others is this:

1. Turn on change tracking on the db and on the tables you want to discover foreign changes to
2. Create a MDrivenServer server side job that asks sql server for the current change track version. Do this by using selfVM and SQLPassthrough and send in sql like this:

DECLARE @CurrentVersionNumberFromChangeTracking bigint;  
SET @CurrentVersionNumberFromChangeTracking = CHANGE_TRACKING_CURRENT_VERSION(); 
select @CurrentVersionNumberFromChangeTracking 

1. Store this Current version number in a Singleton of your model - I will call the property CurrentVersionNumberFromChangeTracking
2. Make the job run every 20 seconds or what suits your need of discovery
3. Have another property on your singleton - I will call this LatestHandledVersionNumberFromChangeTracking
4. Create another MDriven Server side job that triggers if LatestHandledVersionNumberFromChangeTracking<>CurrentVersionNumberFromChangeTracking
5. In this server side job ask the SQLServer for changes since your last request by using selfVM and SQLPassthroughObjects using this sql:

SELECT <YourClass>ID as context FROM CHANGETABLE (CHANGES <YourClass>, @LatestHandledVersionNumberFromChangeTracking) AS C;

1. on the resulting objects call selfVM.SuspectExternalUpdateInvalidate(result)
2. and remember to update LatestHandledVersionNumberFromChangeTracking in this job as well
3. Your job must do this for each class that you need to handle this way

Added handling for server restart

-- Check all tables with change tracking enabled  
IF EXISTS (
    SELECT COUNT(*) FROM sys.change_tracking_tables
    WHERE min_valid_version > @last_synchronization_version )
  BEGIN
    -- Handle invalid version & do not enumerate changes
    -- Client must be reinitialized
  END