ویژگی Change Tracking در SQL Server | بررسی عملکرد و نحوه استفاده

ویژگی 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 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 بسیار پیچیده استفاده کنید تا آنچه را در جدولی که ردیابی می‌شود تغییر می‌کند، ردیابی کنید. در مثال زیر خواهید دید که این یک ویژگی مفید است، اما کمی پیچیده است و همچنین نیاز به تغییر کد برنامه دارد. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

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 صحبت کردیم. Change Tracking فقط آخرین تغییر ایجاد شده در ردیف را ذخیره می‌کند. تاریخچه تغییرات قبلی را حفظ نمی‌کند. اگر چه کاربرد محدودی دارد، برخی از برنامه‌ها ممکن است فقط به این قابلیت تازه‌سازی ساده نیاز داشته باشند و برای اهداف ردیابی داده‌ها به temporal table یا CDC نیاز ندارند.

در این قیسمت می‌خواهیم ببینیم که یکی دیگر از ویژگی‌های ردیابی تغییر، Change Data Capture در SQL Server  چگونه کار می‌کند.

ویژگی‌ Change Data Capture در SQL Server

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

هر بار که یک DELETE انجام می‌شود، جدول تغییرات شامل یک رکورد برای هر دستور DELETE خواهد بود که مقادیر هر ستون را قبل از DELETE نشان می‌دهد. هنگامی که یک UPDATE انجام می‌شود، در جدولی که قابلیت ذخیره داده‌های تغییر یافته را دارد، دو رکورد در جدول تغییرات ایجاد می‌شود، یکی با مقادیر ستون به ‌روز شده و دیگری با مقادیر ستون اصلی قبل از به روز رسانی، ذخیره می‌شود. با استفاده از ذخیره داده‌های تغییر یافته، می‌توانید تغییراتی را که در طول زمان رخ داده‌اند در جدول خود ردیابی کنید. این نوع عملکرد برای برنامه‌ها مفید است، مانند فرآیند بارگذاری انبار داده که نیاز به شناسایی تغییرات دارند، بنابراین می‌توانند به ‌درستی به ‌روز رسانی‌ها را برای ردیابی تغییرات تاریخی در طول زمان استفاده شوند.

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

یک پایگاه داده به نام DataCapture و یک جدول به نام Customer ایجاد می‌کنیم. سپس چند ردیف را در جدول Customer وارد می‌کنیم. پس از آن ویژگی CDC را در سطح پایگاه داده فعال می‌کنیم.

USE master
GO

CREATE DATABASE DataCapture
GO

USE DataCapture
GO

CREATE TABLE Customer (
CustomerId INT  PRIMARY KEY
,FirstName VARCHAR(30)
,LastName VARCHAR(30)
,Amount_purchased DECIMAL
)
GO
 
INSERT INTO dbo.Customer( CustomerId, FirstName,    LastName,    Amount_Purchased)
VALUES
(۱, 'Frank', 'Sinatra',20000.00),( 2,'Shawn', 'McGuire',30000.00),( 3,'Amy', 'Carlson',40000.00)
GO
 
SELECT * FROM dbo.Customer
 
-- Now enable CDC at the Database Level
EXEC sys.sp_cdc_enable_db  
GO 

هنگامی که سعی کردم ویژگی CDC را در پایگاه داده DataCapture فعال کنم، یک پیام خطای بسیار دقیق دریافت کردم.

Msg 22830, LEVEL 16, State 1, PROCEDURE sys.sp_cdc_enable_db_internal, Line 198 [Batch START Line 31] Could NOT UPDATE the metadata that indicates database DataCapture IS enabled FOR Change Data Capture. The failure occurred WHEN executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15404: 'Could not obtain information about Windows NT group/user 'NCalalani', error code 0x54b.'. USE the action AND error TO determine the cause OF the failure AND resubmit the request.

CDC مستلزم این است که owner پایگاه داده یک sysadmin باشد. به طور پیش‌فرض کاربر ایجاد کننده پایگاه داده owner خواهد بود. بنابراین تغییر owner به ‘ ‘saخطای فوق را برطرف می‌کند و یا اگر کاربر فوق را به نقش sysadmin اضافه کنید خطا برطرف می‌شود. البته دقت داشته باشید که اضافه کردن کاربر به sysadmin همچنین مجوزهای زیادی را به کاربر می‌دهد که ممکن است نخواهید به آنها بدهید.

EXEC sp_changedbowner 'sa'
GO
 
EXEC sys.sp_cdc_enable_db  
GO
 

اکنون CDC را در سطح جدول فعال می‌کنیم.

-- Enable on the table level
EXEC sys.sp_cdc_enable_table   
   @source_schema = N'dbo',
   @source_name   = N'Customer',
   @role_name     = NULL,
   @filegroup_name = N'Primary',
   @supports_net_changes = 0
GO

فعال کردن CDC در سطح جدول به سادگی در سطح پایگاه داده نیست. به این دلیل است که تمام اشیا CDC به عنوان اشیا سیستم ایجاد می‌شوند. همچنین وابستگی به پایگاه داده MSDB و سرویس SQL Server Agent وجود دارد. اگر دستور بالا را با موفقیت اجرا کنیم، پیام زیر را برمی‌گرداند:

Job 'cdc.DataCapture_capture' started successfully.
Job 'cdc.DataCapture_cleanup' started successfully.

یک تریگر DDL و تعدادی از پروسیجرهای سیستم نیز ایجاد می‌شود. اشیا CDC تماما در یک پایگاه داده هستند. اگر به اشتباه جدول را drop کنید، تاریخچه شما از بین می‌رود. برخلاف Temporal Table، در صورت فعال بودن CDC، هیچ مکانیسم ایمنی برای محدود کردن حذف جدول وجود ندارد.

تغییراتی به صورت زیر در جدول Customer ایجاد می‌کنیم.

-- insert a row
INSERT INTO Customer (Customerid, FirstName, LastName, Amount_purchased)
VALUES (4, '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 * FROM dbo.Customer
 
DECLARE @begin_lsn binary (10), @end_lsn binary (10)
 
-- get the first LSN for customer changes
SELECT @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_customer')
 
-- get the last LSN for customer changes
SELECT @end_lsn = sys.fn_cdc_get_max_lsn()
 
-- get individual changes in the range
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_customer(@begin_lsn, @end_lsn, 'all');

توجه کنید customerId = 2 که حذف شد، اکنون همه تغییرات ظاهر می‌شود. CDC اطلاعات تغییر DML را به صورت ناهم‌زمان می‌نویسد. ابتدا در لاگ تراکنش می‌نویسد و سپس لاگ های تراکنش را جستجو می‌کند و اطلاعات را با شروع و پایان Log Sequence Number (LSN) ذخیره می‌کند. برخلاف CT، CDC همه ستون‌ها را ذخیره می‌کند و نه فقط ستون‌های کلید اصلی. به ستون $operation در تصویر بالا نگاه کنید، عدد ۲ نشان دهنده یک Insert است، عدد ۱ برای Delete و عدد ۴ برای Update است. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

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

-- Update the above row one more time
UPDATE Customer
SET Lastname = 'Blacksmith' WHERE CustomerId = 3
GO
 
-- Let INSERT few more rows
INSERT INTO Customer (Customerid, FirstName, LastName, Amount_purchased)
VALUES (5, 'Sponge', 'Bob', 5000)
GO
 
INSERT INTO Customer (Customerid, FirstName, LastName, Amount_purchased)
VALUES (6, 'Donald', 'Duck', 6000)
GO
 
-- Let us query to see what it reports now
SELECT * FROM dbo.Customer
 
DECLARE @begin_lsn binary (10), @end_lsn binary (10)
 
-- get the first LSN for customer changes
SELECT @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_customer')
 
-- get the last LSN for customer changes
SELECT @end_lsn = sys.fn_cdc_get_max_lsn()
 
-- get individual changes in the range
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_customer(@begin_lsn, @end_lsn, 'all');
 

Temporal Tableها در ذخیره‌سازی داده‌های تاریخی کارآمدتر هستند در مقاله بعدی به این موضوع دقیق‌تر نگاه خواهیم کرد. از آنجایی که جدول فعلی دارای داده است و آنها محدود به زمان هستند، نیازی به نگه داشتن داده‌های اضافی در شی history  وجود ندارد. CDC آن داده‌های اضافی را همان طور که برای id های مشتری با شماره‌های ۵ و ۶ در مثال بالا می‌بینیم، نگه می‌دارد. CDC هیچ بُعد زمانی ندارد، تغییرات داده‌ها را بر اساس LSN (شماره توالی ورود به سیستم) پیگیری می‌کند در حالی که Temporal Table درSQL Server 2016  بعد زمانی را نیز در نظر می‌گیرد.

سخن پایانی

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

ویژگی Change Data Capture در SQL Server جایگاه خاص خود را دارد و Temporal Table در نسخه SQL Server 2016 جایگزین آنها نمی‌شوند. CDC را می‌توان برای ثبت تغییرات در Master Data در Master Data Management (MDM) با ثبت ناهم‌زمان تغییرات استفاده کرد. با مثال‌هایی در این مقاله، مزایا و معایب ویژگی CDC را مشاهده کردیم. سینتکس و اجرای کلی CDC بسیار پیچیده‌تر از Change Tracking  و Temporal Table است. 

ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.