مقایسه Temporal Tables با Change Data Capture و Change Tracking در SQL Server | قسمت اول

مسئله

Change Data Capture (CDC) و Change Tracking (CT) هر دو در SQL Server 2008 برای ردیابی داده‌ها معرفی شدند. در حالی که CDC فقط برای نسخه Enterprise بود، CT برای همه نسخه‌های SQL Server 2008 در دسترس بود. هدف این مجموعه مقاله ۳ قسمتی این است که ببینیم این دو ویژگی در SQL Server، چه شباهت‌ها و اختلاف‌هایی با Temporal Tables در SQL Server 2016 دارند.

راه‌حل

در قسمت اول این مجموعه مقالات، ما قصد داریم به طور عمیق به ویژگی Change Tracking در SQL Server بپردازیم. سپس در قسمت ۲ به بررسی عملکرد و کاربردهای Change Data Capture خواهیم پرداخت.

قبل از عمیق‌تر شدن در موضوع، توجه به این نکته مهم است که بدانید Temporal Tables جایگزین CDC یا CT نیست. CDC برای ذخیره تاریخچه داده‌ها برای مدت زمان کوتاه در نظر گرفته شده است. بسته به زمان‌بندی ETL اپلیکیشنی که روی آن کار می‌کنید، آن سوابق را که برای ذخیره، علامت‌گذاری شده‌اند را از لاگ تراکنش به برخی از جداول داده انبار داده منتقل می‌کنید. Temporal Tables تغییرات DML را در جدول temporal history ذخیره می‌کند و در نظر گرفته شده است که برای مدت زمان طولانی‌تری در آنجا بمانند.

Change Tracking یکی دیگر از ویژگی‌هایی است که تنها آخرین تغییر ردیف را ذخیره می‌کند. اگر چه استفاده محدودی دارد، برخی از برنامه‌ها ممکن است فقط به این قابلیت تازه‌سازی ساده نیاز داشته باشند و برای اهداف برنامه به Change Tracking داده‌ها یا Change Data Capture نیاز ندارند.

ویژگی Change Tracking در SQL Server چگونه کار می‌کند؟

• ویژگی Change Tracking ابتدا باید در سطح پایگاه داده و سپس برای هر جدولی که می‌خواهید تغییرات را ردیابی کنید فعال شود.
• Change Tracking تعداد دفعات تغییر در جدول را از زمانی که فعال شده است، پیگیری می‌کند.
• Change Tracking برایی ردیف بر اساس ستون کلید اصلی ردیابی می‌شود.
• هیچ تغییری در شِمای جدول مورد نیاز نیست، اما کد برنامه موجود برای استفاده از Change Tracking باید به روز شود.

دو روش وجود دارد که برنامه‌ها و پایگاه‌های داده می‌توانند تغییرات داده‌ها را همگام‌سازی کنند. در همگام‌سازی یک طرفه، برنامه وظیفه دارد تا حافظه cache را با تغییرات به روز شده در جدول، به روز نگه دارد.

در همگام‌سازی دو طرفه، تغییرات ایجاد شده از طریق برنامه می‌تواند به جدول منتقل شود و Change Tracking می‌تواند این تغییرات را علاوه بر تغییرات ایجاد شده در خود جدول، ردیابی کند.

باید از توابع Change Tracking بسیار پیچیده استفاده کنید تا آنچه را در جدولی که ردیابی می‌شود تغییر می‌کند، ردیابی کنید. در مثال زیر خواهید دید که این یک ویژگی مفید است، اما کمی پیچیده است و همچنین نیاز به تغییر کد برنامه دارد.

CREATE DATABASE TrackChange
GO
USE TrackChange
GO
CREATE TABLE Customer (
CustomerId INT IDENTITY (1,1)
,FirstName VARCHAR(30)
,LastName VARCHAR(30) NOT NULL
,Amount_purchased DECIMAL
)
GO
ALTER TABLE dbo.Customer ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerId, LastName)
GO
INSERT INTO dbo.Customer ( FirstName, LastName, Amount_Purchased)
VALUES ( 'Frank', 'Sinatra',20000.00),( 'Shawn', 'McGuire',30000.00),( 'Amy', 'Carlson',40000.00)
GO
SELECT * FROM dbo.Customer
-- Now enable change Tracking at Database Level
ALTER DATABASE TrackChange
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
-- Then enable change Tracking at Table Level
ALTER TABLE dbo.Customer
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
-- Verify the status of the change tracking
-- You will find that there is no version history yet.
SELECT CHANGE_TRACKING_CURRENT_VERSION () AS CT_Version
SELECT * FROM CHANGETABLE (CHANGES Customer,0) as CT ORDER BY SYS_CHANGE_VERSION
SELECT c.CustomerId, c.LastName , ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CONTEXT
FROM Customer AS c
CROSS APPLY CHANGETABLE (VERSION Customer, (customerId,Lastname), (c.CustomerId,c.LastName)) AS ct;

ستون Sys_Change_Version مقدار Null را نشان می‌دهد زیرا پس از فعال کردن CT، هیچ تغییری در جدول ایجاد نشده است. حال اجازه دهید برخی تغییرات DML را در این جدول ایجاد کنیم و سپس دوباره مقدار ستون Sys_Change_Version را که توسط تابع ChangeTable برگردانده شده است بررسی کنیم.

-- Now make some changes in the table
-- insert a row
INSERT INTO Customer(FirstName, LastName, Amount_purchased)
VALUES('Ameena', 'Lalani', 50000)
GO
-- delete a row
DELETE FROM dbo.Customer
WHERE CustomerId = 2
GO
-- update a row
UPDATE Customer
SET Lastname = 'Clarkson' WHERE CustomerId = 3
GO
-- Let us query to see what it reports
SELECT CHANGE_TRACKING_CURRENT_VERSION () AS CT_Version
SELECT * FROM CHANGETABLE (CHANGES Customer,0) as CT ORDER BY SYS_CHANGE_VERSION
SELECT c.CustomerId, c.LastName , ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CONTEXT
FROM Customer AS c
CROSS APPLY CHANGETABLE (VERSION Customer, (customerId,Lastname), (c.CustomerId,c.LastName)) AS ct;

اکنون می‌بینیم که نسخه Change Tracking برابر با ۳ است. ما ۳ عملیات انجام دادیم. ۱ درج، ۱ حذف و ۱ به روز رسانی. ۲ ردیف در جدول change به روز رسانی می‌شود. یک حذف و یک درج، همان طور که از ستون SYS_CHANGE_OPERATION در تصویر زیر قابل مشاهده است.

بیایید ۳ تغییر دیگر در جدول Customer انجام دهیم.

-- Update the above row one more time
UPDATE Customer
SET Lastname = 'Blacksmith' WHERE CustomerId = 3
GO
-- Let INSERT few more rows
INSERT INTO Customer(FirstName, LastName, Amount_purchased)
VALUES('Sponge', 'Bob', 5000)
GO
INSERT INTO Customer(FirstName, LastName, Amount_purchased)
VALUES('Donald', 'Duck', 6000)
GO
-- Let us query to see what it reports now
SELECT CHANGE_TRACKING_CURRENT_VERSION () as CT_Version
SELECT * FROM CHANGETABLE (CHANGES Customer,0) as CT ORDER BY SYS_CHANGE_VERSION
SELECT c.CustomerId, c.LastName , ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CONTEXT
FROM Customer AS c
CROSS APPLY CHANGETABLE (VERSION Customer, (customerId,Lastname), (c.CustomerId,c.LastName)) AS ct;

ما دوباره همان ۳ عمل را انجام دادیم و اکنون شاهد هستیم نسخه Change Tracking برابر با ۶ است.

-- Let us make one more update
UPDATE Customer
SET Lastname = 'Cool' WHERE CustomerId = 6
GO
SELECT CHANGE_TRACKING_CURRENT_VERSION () as CT_Version
SELECT * FROM CHANGETABLE (CHANGES Customer,0) as CT ORDER BY SYS_CHANGE_VERSION
SELECT c.CustomerId, c.LastName , ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CONTEXT
FROM Customer AS c
CROSS APPLY CHANGETABLE (VERSION Customer, (customerId,Lastname), (c.CustomerId,c.LastName)) AS ct;

مشاهده کردیم که SYS_CHANGE_VERSION = 6 وقتی ردیف را به روز کردیم (customerId = 6) حذف شد و نسخه جدید برابر ۷ با همان اطلاعات ثبت شد. از این رو ما ثابت کردیم که ویژگی Change Tracking تنها آخرین تغییر را برای ردیف ذخیره می‌کند، بنابراین هیچ داده تاریخی واقعی با ویژگی Change Tracking در دسترس نخواهد بود. تابع CHANGE_TRACKING_CURRENT_VERSION () همیشه نسخه فعلی ردیف را برمی‌گرداند. همچنین توجه داشته باشید که هیچ کنترلی بر روی تغییر رفتار ویژگی Change Tracking ندارید.

خلاصه

Change Tracking در SQL Server یک ابزار ردیابی بسیار ساده است که کاربرد محدودی دارد و هیچ راه مستقیمی برای کوئری از جدول ردیابی داخلی که با فعال بودن CT ایجاد می‌شود وجود ندارد. شما می‌توانید دوره نگهداری داده‌های CT را در سطح پایگاه داده تعریف کنید. تنها ستون‌هایی که بخشی از کلید اصلی در جدول هستند، پس از فعال کردن Change Tracking برای تغییرات ردیابی می‌شوند. در مقاله بعدی، قسمت دوم، درباره Change Data Capture (CDC) بیشتر بحث خواهم کرد.

منابع

https://www.mssqltips.com/sqlservertip/5211/sql-server-temporal-tables-vs-change-data-capture-vs-change-tracking-part-1/