SQL Server ردیابی تغییرات پیشرفته با Change Tracking و CDC

ردیابی پیشرفته تغییرات در SQL Server: راهنمای کامل Change Tracking و Change Data Capture (CDC)

ردیابی تغییرات در پایگاه داده یک نیاز رایج در بسیاری از کاربردهاست. سناریوهایی مانند حسابرسی (auditing)، انبار داده (data warehousing) و همگام‌سازی کش (cache synchronization) همگی از جمله مواردی هستند که ردیابی تغییرات در آنها بسیار مفید است. SQL Server دو راهکار اصلی برای مدیریت و ردیابی تغییرات داده‌ها ارائه می‌دهد: Change Tracking و Change Data Capture (CDC). در ادامه، به بررسی تفاوت‌ها و نحوه پیاده‌سازی هر یک از این دو ابزار قدرتمند در T-SQL می‌پردازیم.

Change Tracking یک راهکار سبک‌وزن و کارآمد برای ردیابی تغییرات است که مکانیزم مؤثری را برای برنامه‌ها فراهم می‌کند. این ابزار صرفاً ردیابی می‌کند که آیا یک ردیف تغییر کرده است یا خیر، اما جزئیات مربوط به تعداد دفعات تغییر یک ردیف یا مقادیر قدیمی و جدید آن را ذخیره نمی‌کند. کاربرد اصلی آن در همگام‌سازی کش (cache synchronization) است، جایی که یک برنامه نیاز دارد بداند کدام ردیف‌ها تغییر کرده‌اند تا بتواند کش خود را به‌روزرسانی کند. Change Tracking تنها آخرین نسخه ردیف را ذخیره می‌کند و یک API برای بازیابی آسان آخرین تغییرات ارائه می‌دهد.

برای فعال‌سازی Change Tracking در سطح پایگاه داده، از دستور زیر استفاده کنید:

ALTER DATABASE AdventureWorksLT
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

پارامتر CHANGE_RETENTION مدت زمان نگهداری اطلاعات ردیابی تغییرات در پایگاه داده را مشخص می‌کند، در حالی که پارامتر AUTO_CLEANUP تعیین می‌کند که آیا فرآیند پاکسازی به‌صورت خودکار آغاز شود یا خیر. این تنظیمات برای بهینه‌سازی عملکرد و مدیریت فضای دیسک در SQL Server حیاتی هستند.

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

ALTER TABLE SalesLT.Product
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

پارامتر TRACK_COLUMNS_UPDATED مشخص می‌کند که آیا SQL Server باید ستون‌های به‌روزرسانی‌شده را ردیابی کند یا خیر. فعال کردن این گزینه به شما امکان می‌دهد تا متوجه شوید کدام ستون‌ها در یک ردیف تغییر کرده‌اند.

برای دریافت اطلاعات تغییرات ثبت‌شده توسط Change Tracking، از تابع CHANGETABLE استفاده می‌شود. این تابع می‌تواند دو نوع اطلاعات را بازگرداند:

• CHANGETABLE(CHANGES ...) برای دریافت تمام تغییرات یک جدول از یک نسخه خاص.
• CHANGETABLE(VERSION ...) برای دریافت اطلاعات آخرین نسخه ردیف یک ستون.

برای مثال، برای دریافت تمام تغییرات از یک نسخه خاص (که برای همگام‌سازی داده‌ها بسیار مفید است):

SELECT
    CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_OPERATION,
    CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT,
    P.ProductID, P.Name, P.ProductNumber
FROM
    SalesLT.Product AS P
RIGHT OUTER JOIN
    CHANGETABLE(CHANGES SalesLT.Product, 0) AS CT
ON
    P.ProductID = CT.ProductID

شماره نسخه تغییر فعلی پایگاه داده را می‌توان با استفاده از تابع CHANGE_TRACKING_CURRENT_VERSION() به دست آورد. هرگاه ردیفی در یک جدول فعال برای Change Tracking تغییر کند، این شماره نسخه افزایش می‌یابد. این قابلیت برای مدیریت وضعیت همگام‌سازی و اطمینان از صحت ردیابی تغییرات حیاتی است.

(CHANGE_TRACKING_CURRENT_VERSION())

Change Data Capture (CDC) یک راهکار دقیق‌تر است که تغییرات DML (شامل درج، به‌روزرسانی و حذف) را در پایگاه داده ردیابی می‌کند. CDC یک تاریخچه جامع از تغییرات برای یک جدول کاربر را فراهم می‌آورد. برای هر عملیات DML، تغییر را ثبت می‌کند، آن را در جداول تغییرات (change tables) خاصی ذخیره می‌کند و فراداده (metadata) مربوط به تغییر را ارائه می‌دهد. این قابلیت با استفاده از SQL Server Agent Jobs و با خواندن لاگ تراکنش (transaction log) پیاده‌سازی می‌شود. کاربردهای اصلی CDC در سناریوهای انبار داده (data warehousing)، حسابرسی (auditing) و همگام‌سازی (replication) است.

برای فعال‌سازی Change Data Capture (CDC) در سطح پایگاه داده، از دستور زیر استفاده کنید:

USE AdventureWorksLT
GO
EXEC sys.sp_cdc_enable_db
GO

پس از فعال‌سازی CDC برای پایگاه داده، باید آن را برای هر جدولی که می‌خواهید تغییرات داده‌ها را ردیابی کند، فعال کنید. هنگام فعال‌سازی CDC روی یک جدول، شما ستون‌هایی را که می‌خواهید ردیابی شوند، گروه فایل (filegroup) که جداول تغییرات در آن ذخیره می‌شوند و سایر گزینه‌ها را مشخص می‌کنید. این انعطاف‌پذیری به شما امکان می‌دهد تا ردیابی تغییرات را دقیقاً بر اساس نیازهایتان پیکربندی کنید.

EXEC sys.sp_cdc_enable_table
@source_schema = N'SalesLT',
@source_name   = N'Product',
@role_name     = N'cdc_SalesLT_Product',
@supports_net_changes = 1

هنگامی که CDC را روی یک جدول فعال می‌کنید، SQL Server به‌طور خودکار یک “capture instance” ایجاد می‌کند که شامل یک جدول تغییر (change table) و مجموعه‌ای از توابع پشتیبانی و رویه‌های ذخیره‌شده (stored procedures) است. همچنین، SQL Server یک SQL Server Agent Job ایجاد می‌کند تا تغییرات را از لاگ تراکنش (transaction log) ثبت کرده و جدول تغییر را پر کند. این فرآیند خودکار مدیریت داده‌ها و ردیابی تغییرات را بسیار ساده می‌کند.

Job پاکسازی (cleanup job) CDC مسئول پاک کردن داده‌های قدیمی تغییرات است. به صورت پیش‌فرض، این Job هر ۱۰ دقیقه یک‌بار اجرا می‌شود و داده‌ها را برای ۳ روز نگه می‌دارد. شما می‌توانید دوره نگهداری و فرکانس Job پاکسازی را پیکربندی کنید. این تنظیم برای مدیریت فضای دیسک و عملکرد پایگاه داده در SQL Server بسیار مهم است.

EXEC sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 2880 -- in minutes

در این مثال، ما دوره نگهداری را به ۲ روز (۲۸۸۰ دقیقه) تغییر می‌دهیم. این به شما کنترل بیشتری بر روی حجم داده‌های ردیابی تغییرات ذخیره‌شده می‌دهد.

برای بازیابی داده‌های ثبت‌شده توسط CDC، از توابع مخصوص CDC استفاده می‌شود. به عنوان مثال، برای دریافت تمام تغییرات خالص (net changes) بین دو LSN (شماره‌های دنباله لاگ) می‌توانید از تابع cdc.fn_cdc_get_net_changes_ استفاده کنید که تغییرات نهایی یک ردیف در طول یک دوره را نشان می‌دهد.

DECLARE @begin_time DATETIME, @end_time DATETIME, @begin_lsn BINARY(10), @end_lsn BINARY(10);
SET @begin_time = GETDATE() - 1;
SET @end_time = GETDATE();
SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);
SELECT
    *
FROM
    cdc.fn_cdc_get_net_changes_SalesLT_Product(@begin_lsn, @end_lsn, N'all');

این کوئری تمام تغییرات خالص (net changes) را برای جدول SalesLT.Product در ۲۴ ساعت گذشته بازیابی می‌کند. پارامتر 'all' مشخص می‌کند که تمام ستون‌ها بازگردانده شوند. این قابلیت CDC برای بازسازی وضعیت داده‌ها در یک نقطه زمانی خاص یا انجام تحلیل‌های عمیق بر روی تغییرات بسیار ارزشمند است.

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

اما اگر نیازمند ثبت دقیق “چه چیزی تغییر کرده است، چگونه تغییر کرده است، و چه زمانی تغییر کرده است؟” با جزئیات کامل تاریخچه و مقادیر قدیم و جدید هستید، Change Data Capture (CDC) انتخاب قدرتمندتری است. CDC با استفاده از SQL Server Agent و خواندن لاگ تراکنش (transaction log)، جزئیات کامل هر تغییر را در جداول سیستمی خاصی ثبت می‌کند. این روش برای سناریوهایی مانند انبار داده (data warehousing)، audit trail، یا replications که به تاریخچه جامع تغییرات نیاز دارند، ایده‌آل است. اگرچه CDC سربار بیشتری نسبت به Change Tracking دارد و فضای دیسک بیشتری مصرف می‌کند، اما اطلاعات بسیار غنی‌تری را ارائه می‌دهد که برای تجزیه و تحلیل عمیق‌تر و بازیابی دقیق تغییرات، ضروری است.

هر دو قابلیت SQL Server راهکارهای کارآمدی برای ردیابی تغییرات در پایگاه داده ارائه می‌دهند، اما انتخاب صحیح به دقت به نیازهای عملکردی و داده‌ای شما بستگی دارد. بررسی دقیق الزامات پروژه و مقایسه سربار و خروجی هر یک، به شما کمک می‌کند تا بهترین راه‌حل را برای مدیریت و نظارت بر داده‌های خود انتخاب کنید.