Documentation:SQL Server change tracking

Background

The MDrivenServer has built-in change tracking. It is implemented as a "dirty list" of objects that all clients can ask for. When the client gets this list, it marks 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 part of the SQL Server and 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 when there's legacy system reading and writing to the same SQL database. Because of this, it's 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.

Background About This on MS Site

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

Article with 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 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.

Applying Change Tracking

Preparing Your SQL-server

The suggested pattern to allow for MDriven Applications to effectively discover changes to data made by others is this. Turn on change tracking on the DB and on the tables you want to discover foreign changes to.

To turn change tracking on, do this:

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

Turn on change tracking for each table you want to track:

ALTER TABLE <table name>
ENABLE CHANGE_TRACKING

You do NOT need column track, we track whole rows/objects.

Model Additions

Add attributes in SysSingleton

• CurrentVersionNumberFromChangeTracking: Int64?

• LastHandledVersionNumberFromChangeTracking: Int64?

Viewmodel Additions

Read more on Serverside actions.

Create an MDrivenServer Server-side Job That Asks the SQL Server for the Current Change Track Version

• Create a ViewModel SS_GetCurrentTrackingVersion rooted in SysSingleton
• Add the CurrentVersionNumberFromChangeTracking attribute to the ViewModel.
• Add an action called GetCurrentVersion with this EAL.

self.CurrentVersionNumberFromChangeTracking := SysSingleton.sqlpassthrough( 
'-- Get the current tracking version number
  DECLARE @CurrentVersionNumberFromChangeTracking bigint;
  SET @CurrentVersionNumberFromChangeTracking = CHANGE_TRACKING_CURRENT_VERSION();
  select @CurrentVersionNumberFromChangeTracking', Int64 )->first.Part1

Add criteria for serverside execution to the ViewModel with a PS OCL like this:

SysSingleton.allInstances

Create an MDrivenServer Server-side Job That Invalidates Changed Objects

• Create a ViewModel SS_InvalidateExternalChanges rooted in SysSingleton

• Add the CurrentVersionNumberFromChangeTracking and LastHandledVersionNumberFromChangeTracking attributes to the ViewModel

• Add variable vHandleToVersionNumber as Int64

• Add an action called Invalidate with this EAL.

-- Move to variable avoiding change of version during execution
vHandleToVersionNumber := vCurrent_SS_InvalidateExternalChanges.CurrentVersionNumberFromChangeTracking;
-- Invalidate changed objects
selfVM.SuspectExternalUpdateInvalidate(<classname>.sqlpassthroughobjects( 'SELECT <primarykeyattributename> FROM CHANGETABLE (CHANGES <tablename>, ' + LastHandledVersionNumberFromChangeTracking.toString + ') AS C') );
selfVM.SuspectExte....
selfVM.SuspectExte....
selfVM.SuspectExte....
-- Update last handled change number
self.LastHandledVersionNumberFromChangeTracking := vHandleToVersionNumber

The selfVM.SuspectExternalUpdateInvalidate..... is the one you need to adapt to the tables you are tracking.

One row for each of the classes/tables you're tracking.

• Add criteria for serverside execution to the ViewModel with a PS OCL like this

SysSingleton.allInstances->select(ss|ss.CurrentVersionNumberFromChangeTracking <> ss.LastHandledVersionNumberFromChangeTracking)

This means that the job isn't even invoked if there's nothing to do.

Recommended Interval

Interval has to be adjusted depending on the need and load on the server.

It can be quite low because it's a very fast operation - for example, 5 seconds, or what suits your need of discovery.

The detection takes about 10 ms to run and gets changes for 40 tables (without changes) around 100 ms.

Generating OCL and SQL for Change Tracked Classes

You can use the model debugger to make it easier to keep your change-tracking OCL and SQL in sync with your model. Note! This OCL is for a database with reverse-engineered tables, having non-standard table names and primary keys.

Extend Your Model

If you add a tagged value to your classes, for example, called ChangeTracking. See TaggedValues for more information.

ChangeTracking tagged value with icon

Tagged value editor

SQL for Turning Change Tracking On

Using this tag called ChangeTracking, you can use this OCL to generate SQL for turning change tracking on for each table.

Class.allInstances->select(s | s.TaggedValue->select(tv | tv.Tag.toUpper='CHANGETRACKING')->notEmpty)->collect(c |
  String.format('ALTER TABLE {2} ENABLE CHANGE_TRACKING  -- For class {0}',
    c.Name,
    c.TaggedValue->select(tv | tv.Tag='Eco.PrimaryKey')->first.Value,
    c.TaggedValue->select(tv | tv.Tag='Eco.TableName')->first.Value
  )
)->orderBy(s|s)

This will create a list of rows like this:

ALTER TABLE [CV] ENABLE CHANGE_TRACKING  -- For class CV

Run this SQL on your SQL Server database.

OCL for Invalidating Objects

Class.allInstances->select(s | s.TaggedValue->select(tv | tv.Tag.toUpper='CHANGETRACKING')->notEmpty)->collect(c |
  String.format('selfVM.SuspectExternalUpdateInvalidate({0}.sqlpassthroughobjects( \'SELECT {1} FROM CHANGETABLE (CHANGES {2}, \' + LastHandledVersionNumberFromChangeTracking.toString + \') AS C\') );',
    c.Name,
    c.TaggedValue->select(tv | tv.Tag='Eco.PrimaryKey')->first.Value,
    c.TaggedValue->select(tv | tv.Tag='Eco.TableName')->first.Value
  )
)->orderBy(s|s)

This will create a list of rows like this:

selfVM.SuspectExternalUpdateInvalidate(CV.sqlpassthroughobjects( 'SELECT [CV_ID] FROM CHANGETABLE (CHANGES [CV], ' + LastHandledVersionNumberFromChangeTracking.toString + ') AS C') );

Insert this OCL in the SS_InvalidateExternalChanges action.

Helpful Queries for SQL Server

Here are a couple of helpful queries: Change tracking help SQL