همگام‌سازیChange Tracking برای جداول در SQL Server

نوشته شده توسط: تیم فنی نیک آموز

۱۶ اسفند ۱۴۰۰

زمان مطالعه: 8 دقیقه

مسئله

چگونه رکوردهای پایگاه داده SQL Server را که جدید هستند یا تغییر کرده‌اند به منظور کپی یا همگام‌سازی در جدول یا سیستم ثانویه، به عنوان بخشی از فرآیند بارگذاری داده، شناسایی می‌کنید؟ به طور معمول، ما از ستون‌هایی مانند LastModified با نوع داده datetime در SQL Server برای شناسایی رکوردهایی که تغییر کرده‌اند استفاده می‌کنیم.

اگر باید برخی از داده‌ها را مجدداً همگام‌سازی کنید، چطور عمل می‌کنید؟ اغلب اگر این سیستم‌ها از حالت همگام خارج شوند، ممکن است مقدار LastModified را به تاریخ فعلی به روز کنیم تا داده‌ها را با سیستم ثانویه همگام‌سازی مجدد کنیم. با این حال، تغییر مقدار تاریخ LastModified رکورد ممکن است ایده آل نباشد، زیرا انجام این کار، یک مقدار تاریخ LastModified جدید اختصاص می‌دهد که باعث می‌شود زمان صحیح همگام‌سازی‌های انجام شده روی رکوردها، قابل تشخیص نباشند.

بنابراین، در این مقاله به شما نشان خواهم داد که چگونه یک رکورد را علامت‌گذاری کنید تا بتوان آن را بدون تغییر مقادیر داده‌های رکورد اصلی مجدداً با یک سیستم ثانویه همگام‌سازی کرد و در نتیجه یکپارچگی داده‌ها را حفظ کرد.

راه‌حل

برای علامت‌گذاری رکوردها به‌گونه‌ای که بتوان آن‌ها را برای انتقال یا همگام‌سازی مجدد به یک سیستم ثانویه در یک فرآیند بارگذاری بدون تغییر مقادیر داده، شناسایی کرد، از ویژگی SQL Change Tracking (CT) و عبارت WITH CHANGE_TRACKING_CONTEXT برای علامت‌گذاری و شناسایی داده‌های منبع استفاده می‌کنم. من می‌خواهم همگام‌سازی مجدد انجام دهم. برای این مثال، من از یک کپی از پایگاه داده نمونه AdventureWorksLT (Lite) در Azure استفاده خواهم کرد. پایگاه داده من AWLTDB نام دارد. SQL Change Tracking در SQL Server 2008 معرفی شده است. شما می‌توانید آن را در هر نسخه از SQL Server 2008 یا جدیدتر تست کنید.

گام اول– فعال‌سازی Change Tracking در SQL Server در سطح پایگاه داده

ابتدا Change Tracking را در سطح پایگاه داده فعال کنید. در SQL Server Management Studio، روی پایگاه داده خود کلیک راست کرده و روی گزینه Properties کلیک کنید. در پنجره Properties بر روی Change Tracking کلیک کنید. در لیست سمت راست، Change Tracking را روی TRUE تنظیم کنید. مهم است که توجه داشته باشید که به طور پیش‌فرض پاک‌سازی خودکار با یک دوره نگهداری پیش‌فرض دو روز، فعال باشد. بعداً دوباره به این موضوع خواهیم پرداخت. تصویر زیر این روال را نشان می‌دهد.

گام دوم – فعال‌سازی Change Tracking در SQL Server در سطح جدول

برای این مورد، از جدول SalesLT.Customer استفاده خواهم کرد. روی جدولی که می‌خواهید تغییرات را ردیابی کنید کلیک راست کنید. روی گزینه Properties کلیک کنید، روی Change Tracking کلیک کنید، سپس در لیست سمت راست Change Tracking را روی TRUE قرار دهید. تصویر زیر این روال را نشان می‌دهد.

گام سوم – مرور توابع Change Tracking

در این تمرین از توابع Change Tracking زیر استفاده خواهیم کرد:

تابعCHANGETABLE (CHANGES): اطلاعات Change Tracking را از نسخه مشخص شده به بعد، برمی‌گرداند. نام جدول و شماره نسخه تغییر یافته، دو پارامتر این تابع است.
تابع CHANGETABLE (VERSION): آخرین اطلاعات Change Tracking را برای ردیف‌های فعلی در جدول شامل شماره نسخه تغییر یافته و محتوای تغییر یافته، برمی‌گرداند.
تابع WITH CHANGE_TRACKING_CONTEXT (context): برای علامت گذاری رکوردهای تغییر یافته استفاده می‌شود تا به راحتی شناسایی شوند یا بعداً انتخاب شوند. context یک string است که به یک (۱۲۸)varbinary تبدیل شده است.

گام چهارم – به روز رسانی رکوردها برای همگام‌سازی

قبلاً برای بازگرداندن رکوردها به سیستم ثانویه، باید مقدار ModifiedDate را تغییر می‌دادیم. اما با SQL Change Tracking، می‌توانیم رکوردهای تغییر یافته را بدون به خطر انداختن یکپارچگی داده‌ها علامت‌گذاری کنیم. من از این روال به عنوان یک به روز رسانی ساختگی یاد می‌کنم. در زیر هر دو روش را نشان می‌دهم. من اینها را در پایگاه داده AdventureWorksLT در نمونه Azure SQL خود اجرا خواهم کرد.

--Option 1: Update the ModifiedDate to trigger the ETL process to pickup the records as changes.
UPDATE SalesLT.Customer
SET [ModifiedDate] = GETDATE()
WHERE CustomerID = 1;
GO

--Option 2: Dummy Update - Pick a column to update with itself thus not changing value and marking the
--transaction using CHANGE_TRACKING_CONTEXT as 'SpecialUpdate', maintaining the integrity of the record.
DECLARE @originator_id varbinary(128);
SET @originator_id = CAST('SpecialUpdate' AS varbinary(128)); -- Set Your value

WITH CHANGE_TRACKING_CONTEXT (@originator_id)
UPDATE SalesLT.Customer
SET [ModifiedDate] = [ModifiedDate]
WHERE CustomerID = 2;
GO

گام پنجم – شناسایی رکوردها برای انتقال به سیستم ثانویه با تابع

CHANGETABLE (CHANGES)

در این گام، نحوه شناسایی رکوردهای تغییر یافته را برای پردازش مجدد در سیستم ثانویه نشان می‌دهم. نتایج A با رویکرد سنتی است. در حالی که نتایج B از تابع CHANGETABLE (CHANGES) برای انتخاب رکوردهایی که برای تغییر علامت گذاری شده‌اند استفاده می‌کند. در این مثال، من شماره نسخه تغییر (۰) را به تابع CHANGETABLE می‌دهم تا تمام رکوردها در دوره نگهداری تغییرات که به ‌عنوان «SpecialUpdate» مشخص شده‌اند، بازیابی شوند.

نکته: قبلاً به تنظیمات ۲ روزه Clean Up خودکار برای Change Tracking اشاره کردم. باید تراکنش‌های علامت گذاری شده را قبل از اینکه رکوردها از دوره پاک‌سازی خودکار خارج شوند، بازیابی کنید. در یک سیستم تولید، پیش‌فرض ۲ روزه ممکن است نیاز به تنظیم داشته باشد تا نیازهای سیستم شما را برآورده کند.

--Results A: Retrieve Results based on change to ModifiedDate.
SELECT CustomerID, [EmailAddress], CompanyName, [ModifiedDate]
FROM SalesLT.Customer
WHERE [ModifiedDate] >= DATEADD(MINUTE,-5,GETDATE()); --Just Updates a few minute ago

--Results B: Retrieve Results with Join to the CHANGETABLE (CHANGES) Function.
SELECT
cust.CustomerID, cust.[EmailAddress], cust.CompanyName, cust.[ModifiedDate],
c.SYS_CHANGE_VERSION,
c.SYS_CHANGE_OPERATION,
CAST(c.SYS_CHANGE_CONTEXT as varchar(255)) as 'SYS_CHANGE_CONTEXT'
FROM CHANGETABLE (CHANGES SalesLT.Customer, 0) AS c
INNER JOIN SalesLT.Customer AS cust ON cust.CustomerID = c.CustomerID
WHERE c.SYS_CHANGE_CONTEXT = CAST('SpecialUpdate' AS varbinary(128));

نتایج A) نتایج بر اساس تغییرات ایجاد شده در مقدار ModifiedDate در زیر نشان داده شده است.

نتایج B) نتایج به ‌روز رسانی ساختگی انجام شده با CHANCE_TRACKING_CONTEXT در زیر نشان داده شده است. در اینجا هیچ مقداری تغییر نکرده است.

گام ششم – پیاده‌سازی گزینه‌ای جهت Process Sync Code

در این مثال، کد همگام‌سازی Load Process ممکن است همان طور که نشان داده شده است اصلاح شود تا همیشه در جستجوی به‌ روز رسانی‌های ad-hoc همراه با به ‌روز رسانی‌ها و درج‌های معمول رکورد با ترکیب منطق ModifiedDate Load Process با تابع CHANGETABLE (CHANGES) برای Change Tracking و استفاده از SYS_CHANGE_CONTEXT باشد.

-- Build into your ELT Process with Or condition and in the future if you have to resync data
-- Just do the Update WITH CHANGE_TRACKING_CONTEXT ('SpecialUpdate').
DECLARE @LastRunDate as Datetime = DATEADD(DAY,-1,GETDATE())
SELECT
cust.CustomerID, cust.[EmailAddress], cust.CompanyName, cust.[ModifiedDate],
c.SYS_CHANGE_VERSION, c.SYS_CHANGE_OPERATION,
CAST(c.SYS_CHANGE_CONTEXT as varchar(255)) as 'SYS_CHANGE_CONTEXT'
FROM CHANGETABLE (CHANGES SalesLT.Customer, 0) AS c
INNER JOIN SalesLT.Customer AS cust ON cust.CustomerID = c.CustomerID
WHERE [ModifiedDate] >= @LastRunDate OR c.SYS_CHANGE_CONTEXT = CAST('SpecialUpdate' AS varbinary(128));

نکته: مجدداً در این مثال شماره نسخه تغییر (۰) را به تابع CHANGETABLE ارسال می‌کنم. اگر Load Process شما با استفاده از Change Tracking Versioning پیاده‌سازی شده باشد، شماره نسخه واقعی مورد استفاده برای Change Tracking داده از آخرین همگام‌سازی داده را به جای (۰) ارسال کنید.

نتایج) در تصویر زیر رکوردهایی را نمایش می‌دهد که ممکن است از طریق DML سیستم معمولی و به ‌روز رسانی‌های ساختگی دستی با CHANGE_TRACKING_CONTEXT اصلاح شده باشد.

گام هفتم – سناریو مستثنی کردن در Process Sync Code

این مثال در موردی است که می‌خواهید داده‌ها را تغییر دهید اما نمی‌خواهید ردیف‌ها در فرآیند بارگزاری شما جمع‌آوری شوند. در اینجا نحوه استفاده از WITH CHANGE_TRACKING_CONTEXT نشان داده شده است که یک به‌ روز رسانی را به ‌عنوان «DONOTProcess» علامت‌گذاری کرده و چگونگی مستثنی کردن آن را در عبارت Select در کوئری، نشان می‌دهد.

--Option 3: Use CHANGE_TRACKING_CONTEXT to Mark a Record Not to be Processed.
DECLARE @originator_id varbinary(128);
SET @originator_id = CAST('DONotProcess' AS varbinary(128)); ---Your value

WITH CHANGE_TRACKING_CONTEXT (@originator_id)
UPDATE SalesLT.Customer
SET [Title] = 'Sr.',
[ModifiedDate] = GETDATE()
WHERE CustomerID = 130;
GO

--Retrieve all Updates except for ones where CHANGE_TRACKING_CONTEXT as 'DONotProcess'.
DECLARE @LastRunDate as Datetime = DATEADD(DAY,-1,GETDATE())
SELECT
cust.CustomerID, cust.[EmailAddress], cust.CompanyName, cust.[ModifiedDate],
c.SYS_CHANGE_VERSION, c.SYS_CHANGE_OPERATION,
c.SYS_CHANGE_COLUMNS, CAST(c.SYS_CHANGE_CONTEXT as varchar(255)) as 'SYS_CHANGE_CONTEXT'
FROM CHANGETABLE (CHANGES SalesLT.Customer, 0) AS c
INNER JOIN SalesLT.Customer AS cust ON cust.CustomerID = c.CustomerID
WHERE ([ModifiedDate] >= @LastRunDate OR c.SYS_CHANGE_CONTEXT = CAST('SpecialUpdate' AS varbinary(128)) )
AND (c.SYS_CHANGE_CONTEXT <> CAST('DONotProcess' AS varbinary(128)) OR c.SYS_CHANGE_CONTEXT IS NULL );
GO

نتایج) در زیر نشان داده شده است که به ‌روز رسانی‌های انجام‌شده با CHANGE_TRACKING_CONTEXT که روی «DONotProcess» تنظیم شده است، مستثنی شده‌اند.

گام هشتم – تابع CHANGETABLE (VERSION)

مثال آخر استفاده از تابع CHANGETABLE (VERSION) را نشان می‌دهد که همه ردیف‌ها را از جدول فعال شده برای Change Tracking برمی‌گرداند و شماره نسخه تغییر برای درج‌ها و به‌ روز رسانی‌ها و SYS_CHANGE_CONTEXT را هنگام استفاده نشان می‌دهد. برای این کار از Cross Apply، نام جدول تغییر یافته و ستون کلید اصلی استفاده می‌کنیم.

--Retrieve All rows with CROSS APPLY Join to the CHANGETABLE (VERSION) Function, returning the row
--VERSION number and the SYS_CHANGE_CONTEXT if used for updates.
SELECT c.*, CT.*, CAST(ct.SYS_CHANGE_CONTEXT as varchar(255)) as 'SYS_CHANGE_CONTEXT'
FROM SalesLT.Customer AS c
CROSS APPLY CHANGETABLE(VERSION SalesLT.Customer, (CustomerID), (c.CustomerID)) AS CT
Order by 1

نتایج) پارامترهای SYS_CHANGE_VERSION و SYS_CHANGE_CONTEXT را برای هر یک از تغییرات در این نسخه نمایشی نشان می‌دهد.

جمع‌بندی

Change Tracking یک ویژگی خوب است که دقیق‌تر از روال ستون last modified برای تشخیص زمان تغییر داده‌ها، کار می‌کند. می‌توان آن را فعال و در تمام جداول بدون نیاز به تغییر این طرح، اجرا کرد. استفاده از Change_Tracking_Context تنها راهی است که برای علامت‌گذاری رکوردها در یک به‌ روز رسانی ساختگی استفاده می‌شود تا بعداً بتوانیم بدون تغییر مقادیر داده، آنها را شناسایی کنیم. همچنین، به یاد داشته باشید که این عملکردها تحت تأثیر تنظیم حافظه Change Tracking یا Clean up خودکار قرار می‌گیرند.