موضوع CDC (Change Data Capture) در SQL Server

راهنمای جامع فعال‌سازی و مدیریت CDC

قابلیت Change Data Capture یا به اختصار CDC، یکی از ابزارهای قدرتمند در SQL Server برای ردیابی تغییرات (Insert، Update، Delete) در جداول دیتابیس است. برخلاف روش‌های سنتی مانند تریگرها (Triggers) که بار پردازشی زیادی به سیستم تحمیل می‌کنند، CDC با خواندن مستقیم Transaction Log، تأثیر ناچیزی بر عملکرد دیتابیس دارد.

در این مقاله، به نحوه پیکربندی و مدیریت CDC می‌پردازیم. قبل از اینکه یادم بره بگم که موضوع فقط برای SQL SERVER نیست و برای دیگر سیستم ها وجود داره. مثلاً
PostgreSQL (با ابزار Logical Decoding)
MySQL (با استفاده از Binary Log)
Oracle (با استفاده از Oracle GoldenGate)
یا دیگر ابزار ها و مکانیزم ها. منتها در این مقاله، تمرکز، روی SQL SERVER هست.

۱. پیش‌نیازهای فعال‌سازی

قبل از شروع، باید اطمینان حاصل کنید که Agent سرویس SQL Server در حال اجرا است؛ چرا که CDC برای مدیریت لاگ‌های تغییرات از Capture Jobها استفاده می‌کند.

همچنین، برای فعال‌سازی CDC، ابتدا باید آن را در سطح دیتابیس فعال کنید:

                                                                    
USE YourDatabaseName;
GO
EXEC sys.sp_cdc_enable_db;
GO

نکته: برای اطمینان از فعال شدن، می‌توانید فیلد is_cdc_enabled را در جدول sys.databases بررسی کنید.

۲. نحوه فعال‌سازی CDC برای جداول

پس از فعال‌سازی در سطح دیتابیس، باید CDC را برای جدول مورد نظر خود روشن کنید. این کار با استفاده از پروسیجر sys.sp_cdc_enable_table انجام میشه.

                                            
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'YourTableName',
    @role_name     = NULL; -- در صورت نیاز به محدودیت دسترسی، نقش تعریف کنید
GO

پس از اجرای این دستور، SQL Server به صورت خودکار یک Schema جدید به نام cdc ایجاد کرده و جداول سیستمی برای ذخیره تغییرات (Change Tables) می‌سازد.

۳. نحوه غیرفعال‌سازی (Disable)

اگر دیگر نیازی به ردیابی تغییرات ندارید، غیرفعال‌سازی به دو صورت انجام می‌شود:

غیرفعال‌سازی برای یک جدول خاص:

                                            
EXEC sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name   = N'YourTableName',
    @capture_instance = N'all';
GO

غیرفعال‌سازی برای کل دیتابیس:

                                            
USE YourDatabaseName;
GO
EXEC sys.sp_cdc_disable_db;
GO

۴. دستورات و مدیریت تغییرات

وقتی CDC فعال است، داده‌های تغییر یافته در جداول جانبی ذخیره می‌شوند. برای کوئری گرفتن از این تغییرات، از توابع سیستمی استفاده می‌کنیم:

استخراج تغییرات

برای مشاهده تغییرات ثبت شده در یک بازه زمانی، از تابع cdc.fn_cdc_get_all_changes_... استفاده می‌شود:

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

-- دریافت محدوده LSN
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_YourTableName');
SET @to_lsn   = sys.fn_cdc_get_max_lsn();

-- نمایش تغییرات
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_YourTableName(@from_lsn, @to_lsn, 'all');

اگر نمیدونید LSN چی هست، به معنای Log Serial Number هست که SQL میده به لاگ

مدیریت Jobها

مدیریت CDC بر عهده دو Job اصلی در SQL Server Agent است:

1. cdc.YourDB_capture: داده‌ها را از لاگ می‌خواند و در جداول CDC می‌نویسد.

2. cdc.YourDB_cleanup: داده‌های قدیمی (بیش از دوره نگهداری پیش‌فرض که ۷۲ ساعت است) را پاک می‌کند.

برای تغییر دوره نگهداری (مثلاً به ۴۸ ساعت):

EXEC sys.sp_cdc_change_job 
    @job_type = 'cleanup', 
    @retention = 2880; -- زمان به دقیقه

نکات کلیدی برای محیط‌های عملیاتی (Production)

• Storage Space: جداول CDC فضای دیسک را اشغال می‌کنند. حتماً تنظیمات cleanup را متناسب با نیاز خود بهینه‌سازی کنید.

• Transaction Log: از آنجایی که CDC به Log وابسته است، در دیتابیس‌هایی که تغییرات بسیار سنگینی دارند، ممکن است باعث رشد سریع Log File شود (در مدل Recovery Full، حتماً Log Backup منظم داشته باشید).

• Performance: اگرچه CDC بهینه است، اما فعال کردن آن برای جداول بسیار حجیم با نرخ تراکنش بالا، نیاز به پایش دقیق منابع دارد.

امیدوارم موفق باشید.