راهنمای پیاده سازی Change Data Capture (CDC) در SQL Server

راهنمای جامع پیاده‌سازی Change Data Capture (CDC) در SQL Server 

SQL Server یک قابلیت کلیدی برای ردیابی تغییرات در جداول دارد که به Change Data Capture (CDC) معروف است. این ویژگی برای سناریوهایی که نیاز به ردیابی دقیق تغییرات داده در طول زمان دارید، بسیار کارآمد است، از جمله بارگذاری داده در انباره‌های داده (Data Warehouses)، حسابرسی (Auditing) یا سیستم‌های همگام‌سازی. در این مقاله به بررسی نحوه فعال‌سازی و استفاده از CDC می‌پردازیم و جزئیات فنی آن را مورد کاوش قرار می‌دهیم.

CDC چیست و چرا مهم است؟

Change Data Capture (CDC) مکانیزمی است که SQL Server برای ثبت تغییرات درج (INSERT)، به‌روزرسانی (UPDATE) و حذف (DELETE) در یک جدول منبع (Source Table) به کار می‌برد. این تغییرات در جداول مجزایی که به آن‌ها “جداول تغییرات” (Change Tables) گفته می‌شود، ذخیره می‌شوند. مزیت اصلی CDC این است که به شما امکان می‌دهد تا فقط داده‌هایی را که از آخرین بازرسی تغییر کرده‌اند، بازیابی کنید که به طور چشمگیری عملکرد عملیات ETL (Extract, Transform, Load) و Replication را بهبود می‌بخشد و بار روی سرور پایگاه داده را کاهش می‌دهد. این قابلیت اطمینان و دقت بالایی را در ردیابی تاریخچه تغییرات داده فراهم می‌کند.

فعال‌سازی CDC برای یک پایگاه داده

قبل از اینکه بتوانید CDC را برای جداول خاصی فعال کنید، ابتدا باید آن را در سطح پایگاه داده فعال کنید. این کار به وسیله فراخوانی یک رویه سیستمی (System Stored Procedure) در زمینه پایگاه داده مورد نظر انجام می‌شود. توجه داشته باشید که این عملیات باید توسط یک کاربر دارای نقش سیستمی sysadmin انجام شود.

USE TestDB;
GO
EXEC sys.sp_cdc_enable_db;
GO

پس از اجرای موفقیت‌آمیز این فرمان، sys.sp_cdc_enable_db یک شمای (Schema) جدید به نام cdc و جداول سیستمی مربوطه را در پایگاه داده شما ایجاد می‌کند. همچنین وظایف (Jobs) SQL Server Agent را برای پردازش و پاکسازی داده‌های تغییر یافته ایجاد می‌کند. این وظایف به صورت خودکار تغییرات را از لاگ تراکنش (Transaction Log) خوانده و آن‌ها را به جداول تغییرات منتقل می‌کنند.

فعال‌سازی CDC برای یک جدول

پس از فعال‌سازی CDC در سطح پایگاه داده، می‌توانید آن را برای جداول خاصی که می‌خواهید تغییراتشان ردیابی شود، فعال کنید. این کار نیز از طریق یک رویه سیستمی انجام می‌شود. در این مثال، فرض می‌کنیم جدولی به نام Employees در پایگاه داده TestDB داریم.

USE TestDB;
GO
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Employees',
    @role_name     = N'cdc_TestDB_Employees',
    @supports_net_changes = 1;
GO

در اینجا، پارامتر @source_schema شمای جدول منبع و @source_name نام جدول را مشخص می‌کند. پارامتر @role_name یک نقش پایگاه داده برای کنترل دسترسی به داده‌های تغییر یافته ایجاد می‌کند. تنظیم @supports_net_changes = 1 باعث می‌شود که SQL Server علاوه بر تغییرات لحظه‌ای (All Changes)، امکان بازیابی تغییرات خالص (Net Changes) را نیز فراهم کند، به این معنی که برای هر ردیف، فقط وضعیت نهایی آن پس از چندین تغییر را گزارش می‌دهد. این یک بهینه‌سازی مهم برای کاهش حجم داده‌های برگشتی است.

پارامترهای مهم `sys.sp_cdc_enable_table`

• `@captured_column_list`: اگر نمی‌خواهید تمام ستون‌ها ردیابی شوند، می‌توانید لیستی از ستون‌های مورد نظر را مشخص کنید.
• `@index_name`: اگر می‌خواهید از یک ایندکس خاص به جای ایندکس کلاستر شده (Clustered Index) برای شناسایی ردیف‌ها استفاده کنید.
• `@filegroup_name`: برای تعیین فایل‌گروپ (Filegroup) که جداول تغییرات در آن ذخیره شوند.

درک جداول CDC

هنگامی که CDC برای یک جدول فعال می‌شود، SQL Server یک جدول تغییرات جدید در شمای cdc ایجاد می‌کند. نام این جدول به طور پیش‌فرض به شکل cdc.dbo_Employees_CT خواهد بود (اگر نام نمونه کپچر dbo_Employees باشد).
این جدول حاوی ستون‌هایی است که وضعیت قبل و بعد از تغییر را برای هر عملیات (درج، به‌روزرسانی، حذف) نشان می‌دهند.

ستون‌های اصلی جدول تغییرات:

• `__$start_lsn`: شماره توالی لاگ (Log Sequence Number) شروع تراکنش.
• `__$end_lsn`: شماره توالی لاگ پایان تراکنش (فقط برای به‌روزرسانی‌های خاص).
• `__$seqval`: ترتیب تغییرات در یک تراکنش.
• `__$operation`: نوع عملیات (1 = حذف, 2 = درج, 3 = به‌روزرسانی (قبل از تغییر), 4 = به‌روزرسانی (بعد از تغییر)).
• `__$update_mask`: یک بیت‌ماسک (Bitmask) که نشان می‌دهد کدام ستون‌ها به‌روزرسانی شده‌اند.
• ستارهایی از جدول منبع: تمام ستون‌های جدول Employees به همراه داده‌های مربوط به تغییر.

کار با توابع CDC

SQL Server چندین تابع برای پرس‌وجو از داده‌های تغییر یافته ارائه می‌دهد. این توابع به شما کمک می‌کنند تا به روشی سازمان‌یافته به داده‌های تغییرات دسترسی پیدا کنید.

دریافت تمام تغییرات (`cdc.fn_cdc_get_all_changes_`)

این تابع تمام تغییرات (درج، حذف و هر دو تصویر قبل و بعد از به‌روزرسانی) را در یک محدوده LSN مشخص برمی‌گرداند.

DECLARE @from_lsn BINARY(10), @to_lsn BINARY(10);

-- Get the last LSN from the cdc.lsn_time_mapping table for a specific time range
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Employees');
SET @to_lsn   = sys.fn_cdc_get_max_lsn();

SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_Employees
(
    @from_lsn,
    @to_lsn,
    N'all' -- or 'all update old' to get old values only for updates
);
GO

در این کد، sys.fn_cdc_get_min_lsn و sys.fn_cdc_get_max_lsn برای تعیین محدوده LSN استفاده می‌شوند. N'all' به این معنی است که تمام تغییرات، از جمله تصاویر قبل و بعد از به‌روزرسانی، برگردانده می‌شوند. این یک ابزار قدرتمند برای بازسازی کامل تاریخچه تغییرات در جدول شماست.

دریافت تغییرات خالص (`cdc.fn_cdc_get_net_changes_`)

این تابع تنها آخرین تغییر خالص را برای هر ردیف در یک محدوده LSN مشخص برمی‌گرداند. اگر یک ردیف چندین بار در طول محدوده LSN به‌روزرسانی شود، فقط آخرین وضعیت آن نمایش داده می‌شود. این برای سناریوهای ETL که فقط به وضعیت نهایی داده‌ها اهمیت می‌دهید، بسیار مفید است.

DECLARE @from_lsn BINARY(10), @to_lsn BINARY(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Employees');
SET @to_lsn   = sys.fn_cdc_get_max_lsn();

SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_Employees
(
    @from_lsn,
    @to_lsn,
    N'all'
);
GO

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

پاکسازی CDC

داده‌های CDC می‌توانند به سرعت رشد کنند و فضای دیسک زیادی را اشغال کنند. SQL Server به طور خودکار وظایف پاکسازی (Cleanup Jobs) را برای مدیریت این موضوع ایجاد می‌کند. با این حال، می‌توانید پارامترهای نگهداری (Retention) را نیز تنظیم کنید.

برای مشاهده یا تغییر تنظیمات نگهداری:

USE TestDB;
GO
EXEC sys.sp_cdc_help_jobs;
GO

EXEC sys.sp_cdc_change_job
    @job_type = N'cleanup',
    @retention = 4320; -- retention period in minutes (e.g., 3 days = 3 * 24 * 60 = 4320)
GO

فرمان اول اطلاعات مربوط به وظایف CDC، از جمله زمان نگهداری فعلی را نمایش می‌دهد. فرمان دوم به شما اجازه می‌دهد تا دوره نگهداری را بر حسب دقیقه تنظیم کنید. نگهداری مناسب داده‌ها از اهمیت بالایی برخوردار است تا اطمینان حاصل شود که فضای دیسک بهینه مدیریت می‌شود و در عین حال تاریخچه تغییرات مورد نیاز شما در دسترس باقی می‌ماند.

غیرفعال کردن CDC

اگر دیگر نیازی به ردیابی تغییرات برای یک جدول خاص یا کل پایگاه داده ندارید، می‌توانید CDC را غیرفعال کنید.

غیرفعال کردن CDC برای یک جدول:

USE TestDB;
GO
EXEC sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name   = N'Employees',
    @capture_instance = N'dbo_Employees'; -- replace with your capture instance name
GO

غیرفعال کردن CDC برای یک پایگاه داده:

USE TestDB;
GO
EXEC sys.sp_cdc_disable_db;
GO

غیرفعال کردن CDC برای پایگاه داده باعث حذف شمای cdc، جداول سیستمی مرتبط و تمام وظایف SQL Server Agent می‌شود.

نتیجه‌گیری

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