بررسی CDC یا Change Data Capture در SQL Server

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

برای این کار نیاز است که در ابتدا SQL Server Agent را Start کنید.

سپس برای فعال‌سازی این قابلیت کافی است کد زیر را اجرا کنید.

USE [MyDb]
GO

EXEC sys.sp_cdc_enable_db

و سپس برای فعال‌سازی این قابلیت در سطح دیتابیس خود، کد زیر را فراخوانی کنید.

USE [MyDb]
GO  

EXEC sys.sp_cdc_enable_table  
@source_schema = N'schema_name',  
@source_name   = N'tbl_name',  
@role_name     = NULL,  
@filegroup_name = NULL,  
@supports_net_changes = 0 

برای چک کردن موفقیت آمیز بودن کدهای بالا نیز می‌توانید از دستورهای زیر استفاده کنید.

USE master
GO

select name,
       is_cdc_enabled
from sys.databases
where name = 'db_name'

USE [MyDb]
GO

select  name,
        type,
        type_desc,
        is_tracked_by_cdc
from sys.tables
where name = 'tbl_name'

در صورتی که تمام موارد به‌درستی پیش برود، قسمت‌های زیر به دیتابیس شما اضافه می‌شود:

جاب‌های اضافه شده به سیستم کارهای زیر را انجام می‌دهند:

• Capture job: وظیفه جمع‌آوری اطلاعات را بر عهده دارد.
  • این job بعد از اجرای دستور بلافاصله آغاز به کار می‌کند.
  • پیوسته در حال اجراشدن است.
  • در هر اجرا نهایتاً 1000 تراکنش انجام می‌دهد.
  • بین هر اجرا 5 ثانیه تأخیر وجود دارد.
• Clean up job: وظیفه حذف رکوردهای قدیمی را بر عهده دارد.
  • هر شب ساعت 2 اجرا می‌شود.
  • به‌صورت پیش‌فرض رکوردهای قدیمی‌تر از 3 روز را پاک می‌کند.
  • با هر دستور delete در حدود 5000 رکورد را حذف می‌کند.

در صورت نیاز به تغییر مدت زمان پیش‌فرض 3 روز گفته شده، می‌توانید از کوئری زیر استفاده کنید و حداکثر مقدار 100 سال یا 52494800 را قرار دهید.

EXECUTE sys.sp_cdc_change_job @job_type='cleanup', @retention=minutes;

sys-sp-cdc-change-job-transact-sql

فانکشن‌های اضافه شده به دیتابیس، دارای نام ثابت fn_cdc_get_all_changes_ هستند که در انتهای آن نام دیتابیس شما می‌آید. بطور مثال fn_cdc_get_all_changes_dbo_MyTable.
برای دریافت لیست تغییرات انجام شده بر روی دیتایس می‌توانید از کوئری زیر استفاده کنید:

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

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

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_MyTable(@from_lsn, @to_lsn, N'all update old');

در کد بالا اگر مقدار all update old را قرار دهید، در دستور آپدیت مقادیر قبل از تغییر نیز که کد 3 دارند هم نمایش داده می‌شوند و اگر مقدار all را قرار دهید، مقادیر گفته شده نشان داده نمی‌شوند.

cdc-fn-cdc-get-all-changes-capture

در خروجی نشان داده شده:

• __$start_lsn: نشان‌دهنده شماره Commit است. عملیاتی که در یک تراکنش انجام‌شده باشند، مقادیر این ستون برایشان یکسان ثبت می‌شود (LSN مخفف Log Sequence Number است)
• __$seqval: شماره عملیات در یک تراکنش را نشان می‌دهد.
• __$operation: نشان‌دهنده نوع عمل صورت گرفته است:
  • شماره 1 نشان‌دهنده delete
  • شماره 2 نشان‌دهنده insert
  • شماره 3 نشان‌دهنده update (پیش از تغییر مقادیر)
  • و شماره 4 نشان‌دهنده update (پس از تغییر مقادیر) است
• __$update_mask: یک متغیر از جنس bit mask است که به ازای هر ستونی که مقدار گرفته باشد، 1 می‌گیرد. برای Insert و Update تمام بیت‌های آن 1 ثبت می‌شود ولی برای آپدیت، تنها ستون‌هایی که تغییر کرده‌اند مقدار 1 می‌گیرند.

ستون‌های دیگر جدول نشان داده شده، ستون‌های جدول مورد نظر شما است.
در رابطه با این ستون‌ها به موارد زیر نیز باید توجه کنید:

• اگر بعد از فعال کردن CDC برای یک جدول ستون‌هایی به آن اضافه شود، در CDC نادیده گرفته می‌شود.
• اگر بعد از فعال کردن CDC ستونی از جدول موردنظر حذف شود، در جدول CDC از این به بعد برای آن ستون Null ثبت می‌شود.
• اگر نوع داده یک ستون تغییر کند، تغییرات در جدول CDC هم منعکس می‌شود و به‌این‌ترتیب اطلاعات از دست نمی‌رود.

توسط کوئری زیر نیز می‌توانید لیست تغییرات بر روی ستون‌ها را مشاهده کنید:

 EXECUTE sys.sp_cdc_get_ddl_history  
    @capture_instance = 'dbo_Person';

sys-sp-cdc-get-ddl-history-transact-sql

چالش‌ها

روش گفته شده از عملیات I/O برو روی لاگ‌های دیتابیس برای مطلع شدن از تغییرات استفاده می‌کند و کمی سربار بر روی دیتابیس قرار می‌دهد. اگرچه هیچ قفل اضافه‌ای و یا بلاکی برای عملیات دیتابیسی قرار نمی‌دهد.
اگر Rate تغییرات بر روی دیتابیس شما زیاد باشد، روش گفته شده خیلی برای شما مناسب نیست.
عملیاتی کردن روش گفته شده نسبت به روش‌های دیگر کمی سربار اضافه دارد.

مطالعه بیشتر درباره موارد گفته شده راه جایگزین:

bryteflow

حواستان باشد که قابلیت دیگری به اسم Change Tracking نیز در SQL Server وجود دارد که با این قابلیت گفته شده تفاوت دارد.

توضیحات بیشتر:

about-change-data-capture

enable-and-disable-change-data-capture

administer-and-monitor-change-data-capture

work-with-change-data

change-data-capture-and-other-sql-server-features

cdc-fn-cdc-get-net-changes-capture