How to synchronize data changes in an application.

Problem: How to synchronize DML changes in an application.

Solution: Enable and Disable Change Tracking (SQL Server)

This features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database.

There are two types of Synchronizations:

• One-way Application synchronizations:

In a real word scenario, think of an online shopping website where the stock is shown of a particular item using a table called quantity, orders, availability. So as these three tables are updating infrequently on the application layer, where the changes are not yet brought back from the database. So to enable the changes that occurred on the tables to show in the application layer this change tracking feature would be helpful

• Two-way synchronizations:

Another scenario would be in data warehousing application where you need to pull incremental data (net changes only) from your source. In these kind of scenarios, Change Tracking is very helpful.

Before enabling change tracking there are few takeaways:

•       Change data tracking must be enabled on the database.
•       Change tracking must be enabled on the tables as well.
•       Tables need to have primary key for enabling change tracking.
•       Tracking will perform when any DML changes occur on the table.
•       Truncate operations won’t be tracked on change tracking tables
•        This view stores the information of change tracking (sys.change_tracking_tables)

Example:

In this article I am going to show you we can use change tracking.

Example 1: I this example, I would like to show how we can enable change tracking on a table.

--change tracking enable on the database.

ALTER DATABASE DB SET CHANGE_TRACKING =ON

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  -- rentention period is store the changes

--enable change tracking on tables .

ALTER TABLE dbo.Orders

ENABLE CHANGE_TRACKING 

WITH (TRACK_COLUMNS_UPDATED = ON)

--to view change tracking on the tables.

select s.name+'.'+tt.name as table_name, t.* from sys.change_tracking_tables T

                inner join sys.tables TT on TT.object_id = T.object_id

                inner join sys.schemas S on S.schema_id = TT.schema_id