Table – InstanceChanges

Purpose

This table is used to queue updates to Instance records for bulk transport to other data stores. It implements a simple decoupling architecture to ensure that failure of either the originating, or target, data storage is automatically recovered when connectivity is restored.

Definition

CREATE TABLE [dbo].[InstanceChanges](
	[InstanceGUID] [UNIQUEIDENTIFIER] NOT NULL,
	[ChangeType] [VARCHAR](40) NOT NULL,
	[ChangedOn] [DATETIME2](7) NOT NULL,
	[PullGUID] [UNIQUEIDENTIFIER] NULL,
	[ConceptGUID] [UNIQUEIDENTIFIER] NULL,
	[OldPrimaryKey] [VARCHAR](500) NULL,
 CONSTRAINT [PK_InstanceChanges] PRIMARY KEY CLUSTERED 
(
	[InstanceGUID] ASC,
	[ChangeType] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Column Definitions

This tables tracks at most the latest change on a single Instance record. The record being changed is identified by its InstanceGUID and the type of change being made is indicated by the ChangeType.

The timestamp of the change is also tracked in ChangedOn so that updates can be transferred and applied to a target data storage in time sequence order.

The ConceptGUID of the Instance is also tracked so that changes to different types of concepts can be sent to different target data stores.

The OldPrimaryKey (if any) of the Instance is also tracked so that correlation to existing records in the target data storage can be made. This is useful in data integration scenarios.

The PullGUID is used to track work in progress on sets of Instances as they are transferred to the target data store.

Tagging Changes

Rows are optionally added to the InstanceChanges table by the MaintainLookups trigger on the Instances table.

The trigger makes a call to GI_InstanceChanged for all of the records in the Deleted temporary table indicating that the ChangeType = ‘Instance Deleted’. It then makes a call to GI__InstanceChanged indicating that the ChangeType = ‘Instance Inserted’ for all of the records in the Inserted temporary table.

The net effect is that the InstanceChanges table contains a record of all Instance record Creation, Updates and Deletes.

GI_InstanceChanged

CREATE PROCEDURE [dbo].[GI_InstanceChanged]
(
	@InstanceGUID AS UNIQUEIDENTIFIER,
	@ConceptGUID AS UNIQUEIDENTIFIER,
	@ChangeType AS VARCHAR(40),
	@OldPrimaryKey AS VARCHAR(500)
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

	BEGIN TRY

		IF @ChangeType = 'Instance Deleted'
		BEGIN
			DELETE FROM dbo.InstanceChanges WHERE InstanceGUID = @InstanceGUID AND ChangeType = 'Instance Inserted'
		END
		IF @ChangeType = 'Instance Inserted'
		BEGIN
			DELETE FROM dbo.InstanceChanges WHERE InstanceGUID = @InstanceGUID AND ChangeType = 'Instance Deleted'
		END
		DELETE FROM dbo.InstanceChanges WHERE InstanceGUID = @InstanceGUID AND ChangeType = @ChangeType

		INSERT INTO dbo.InstanceChanges (InstanceGUID, ChangedOn, ChangeType, ConceptGUID, OldPrimaryKey)
		VALUES (@InstanceGUID, SYSUTCDATETIME(), @ChangeType, @ConceptGUID, @OldPrimaryKey)

		RETURN 0

	END TRY	
	BEGIN CATCH
	
		DECLARE @ReturnStatus AS INTEGER
		EXECUTE @ReturnStatus = dbo.GI_LogEvent '00000000-0000-0000-0000-000000000000', 'dbo.GI_InstanceChanged - Unknown error tagging changed instance data', 1, NULL
		RAISERROR ('dbo.GI_InstanceChanged - Unknown error tagging changed instance data.', 16, 1)
		RETURN 1
		
	END CATCH
	
END

In some implementations, we have extended the use of the GI_InstanceChanged stored procedure to include changes to an Instance’s Events, Milestones, Access.

Pulling Changes

Typical implementations of the technology are based on scheduled jobs, or background tasks, initiated from the target data storage. There is no limitation on the technology used to pull changes. In the past we have seen SSIS, T-SQL Stored Procedures and C# windows Services as common examples.

The preferred approach is to decouple the dependency between the two data storage systems.

The originating system is always able to write to its own InstanceChanges table and can queue the change list indefinitely. If the target data storage is offline, then changes will just build up until the target comes back online and is able to pull changes. This also copes with situations where a flood of changes exceeds the ability of the target to temporarily keep up.

The target system is always able to update its own data storage and can pull changes when the originating system is online. If the originating system is offline, the target system can simply poll until it becomes available. The target system can choose how many changes it wishes to pull at a time, which change types it needs, and on which concepts. It indicates that it is in the process of pulling changes by setting the PullGUID on the InstanceChanges record. When the pull has been successfully completed, the InstanceChanges record can be deleted.