SQL Server change tracking
Hans Karlsen (talk | contribs) No edit summary |
Hans Karlsen (talk | contribs) No edit summary |
||
Line 26: | Line 26: | ||
Implementation notes | Implementation notes | ||
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 | |||
# 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 | |||
# Store this Current version number in a Singleton of your model - I will call the property CurrentVersionNumberFromChangeTracking | |||
# Make the job run every 20 seconds or what suits your need of discovery | |||
# Have another property on your singleton - I will call this LatestHandledVersionNumberFromChangeTracking | |||
# Create another MDriven Server side job that triggers if LatestHandledVersionNumberFromChangeTracking<>CurrentVersionNumberFromChangeTracking | |||
# 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; | |||
# on the resulting objects call selfVM.SuspectExternalUpdateInvalidate(result) | |||
# and remember to update LatestHandledVersionNumberFromChangeTracking in this job as well | |||
# Your job must do this for each class that you need to handle this way | |||
DECLARE @ | |||
SET @ | |||
select @ | |||
SELECT | |||
selfVM. | |||
Revision as of 13:20, 21 April 2018
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 build in Turnkey database.
To turn change tracking On, do like this
ALTER DATABASE AdventureWorks2012 SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
More reading
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
Setting Context Information provides a way to know that the MDrivenServer is the source of the change
Maybe last_synchronization_version can be used to know if ANY change has occured
-- 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
Implementation notes
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
- 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
- Store this Current version number in a Singleton of your model - I will call the property CurrentVersionNumberFromChangeTracking
- Make the job run every 20 seconds or what suits your need of discovery
- Have another property on your singleton - I will call this LatestHandledVersionNumberFromChangeTracking
- Create another MDriven Server side job that triggers if LatestHandledVersionNumberFromChangeTracking<>CurrentVersionNumberFromChangeTracking
- 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;
- on the resulting objects call selfVM.SuspectExternalUpdateInvalidate(result)
- and remember to update LatestHandledVersionNumberFromChangeTracking in this job as well
- Your job must do this for each class that you need to handle this way
This page was edited more than 9 months ago on 03/26/2024. What links here