بررسی معماری و مفهوم و کاربرد CDC در SQL Server

CDC در SQL Server مفهوم Change Data Capture

توسط admin | گروه SQL Server | 1404/04/15

نظرات 0

CDC در SQL Server: مفهوم و معماری کلی

Change Data Capture (CDC) قابلیتی در SQL Server است که تغییرات داده (درج، به‌روزرسانی و حذف) در جداول را ثبت کرده و در جداول مخصوص خود نگهداری می‌کند تا سایر فرایندها (مانند ETL) بتوانند تغییرات را به شکل مؤثری پیگیری کنند. این ویژگی برای اولین بار در SQL Server 2008 معرفی شد و در نسخه‌های Enterprise (و بعداً در Developer) در دسترس بود؛ از SQL Server 2016 SP1 به بعد، نسخه استاندارد (Standard) نیز CDC را پشتیبانی می‌کند (در حالی که نسخه‌های Web و Express همچنان فاقد آن هستند).

CDC بر پایه‌ی خواندن لاگ تراکنش (transaction log) عمل می‌کند: هنگام هر تراکنش درج/به‌روزرسانی/حذف در جداول تحت نظارت، اطلاعات تغییرات ابتدا به لاگ افزوده می‌شود. سپس فرایند capture با خواندن لاگ (معمولاً توسط یک Job در SQL Agent)، تغییرات را استخراج کرده و در جدول تغییرات (change table) مربوطه وارد می‌کند. جداول تغییرات دارای ساختار شبیه به جدول مبدأ هستند و چند ستون متادیتا (مثل __$start_lsn, __$operation, __$update_mask) برای تشخیص زمان، نوع عملیات و مقادیر قبل/بعد از تغییر دارند. برای دسترسی به داده‌های ذخیره‌شده، SQL Server برای هر «نمونه‌ی ضبط» (capture instance) دو تابع جدولی ایجاد می‌کند:

  • cdc.fn_cdc_get_all_changes_<CaptureInstance> برای بازگشت تمام تغییرات در یک بازه‌ی LSN

  • cdc.fn_cdc_get_net_changes_<CaptureInstance> برای بازگشت تغییرات خالص (علاوه/حذف/به‌روزرسانی نهایی هر ردیف) در بازه
    همچنین توابع سیستمی دیگری مثل sys.fn_cdc_map_time_to_lsn و sys.fn_cdc_map_lsn_to_time وجود دارند که امکان تبدیل بازه‌های زمانی به LSN (و بالعکس) را فراهم می‌کنند.

شمای کلی فرآیند CDC در SQL Server (منبع: مایکروسافت)

فعال‌سازی CDC باعث ایجاد چند شیء داخلی می‌شود:

  • جداول تغییرات (cdc.<Schema_TableName>_CT) که سوابق تغییرات در آن‌ها ذخیره می‌شود.

  • توابع جدولی (تابع‌های cdc.fn_cdc_get_all_changes_... و cdc.fn_cdc_get_net_changes_...) برای خواندن تغییرات.

  • متادیتا در جدول‌های سیستمی مانند cdc.change_tables, cdc.captured_columns, cdc.ddl_history و… که پیکربندی و تاریخچه را نگهداری می‌کنند.

  • SQL Agent Jobs: دو Job ایجاد می‌شود – یکی برای فرآیند «ضبط» (capture) که لاگ را می‌خواند و تغییرات را در جدول تغییرات می‌نویسد، و یکی برای پاکسازی دوره‌ای (cleanup) که بر اساس سیاست نگهداری داده، ردیف‌های قدیمی را حذف می‌کند. به طور پیش‌فرض، Job پاکسازی هر روز راس ساعت ۲ بامداد اجرا شده و سوابق تغییرات بیش از ۷۲ ساعت (۴۳۲۰ دقیقه) را حذف می‌کند.

دستورهای اصلی فعالسازی و غیرفعالسازی CDC

برای استفاده از CDC ابتدا باید در سطح دیتابیس آن را فعال کنید، و سپس جداول موردنظر را مشخص کنید:

  • فعالسازی در سطح دیتابیس:

     
    -- بر طبق مستندات مایکروسافت و مراجع آموزشی 
    EXEC sys.sp_cdc_enable_db;

    این رویه (sys.sp_cdc_enable_db) پایگاه داده را برای CDC آماده می‌کند. پس از اجرای آن، ستون جدید is_cdc_enabled در نمای sys.databases به ۱ تغییر می‌کند. اجرای این رویه نیازمند عضویت در نقش db_owner (یا sysadmin در برخی موارد) است.

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

    EXEC sys.sp_cdc_enable_table 
    @source_schema = N'dbo',    
    @source_name   = N'MyTable',    
    @role_name     = N'cdc_Admin',    
    -- نقش برای دسترسی به تغییرات   
    @capture_instance  = N'dbo_MyTable', 
    -- نام پیش‌فرض؛ معمولا Schema_Table   
    @supports_net_changes = 1,        
    -- فعال‌سازی تابع net_changes   
    @index_name        = N'PK_MyTable', 
    -- ایندکس یکتایی (مثلا کلید اصلی) برای net_changes   
    @captured_column_list = N'id,name,amount', 
    -- ستون‌های مورد نظر   
    @filegroup_name    = N'MyDB_CDC', 
    -- فایل‌گروپی برای جدول تغییرات   
    @allow_partition_switch = N'1';  
    -- اجازه یا عدم اجازه جابجایی پارتیشن

    توضیحات پارامترها:

    • @supports_net_changes: اگر برابر ۱ باشد، تابع fn_cdc_get_net_changes تولید می‌شود (نیازمند ایندکس یکتا یا کلید اصلی). فعال کردن این گزینه باعث ایجاد ایندکس اضافی روی جدول تغییرات می‌شود که نگهداری آن سربار دارد.

    • @role_name: نام یک نقش پایگاه داده که دسترسی به اطلاعات تغییرات را محدود می‌کند. اگر مقدار NULL داده شود، هیچ کنترل سطح دسترسی اضافه نمی‌شود.

    • @captured_column_list: لیستی از ستون‌های جدول مبدا که باید در جدول تغییرات ذخیره شوند. اگر NULL باشد، تمام ستون‌ها در نظر گرفته می‌شوند. ستون‌های سیستم CDC (مانند __$start_lsn) در این لیست نمی‌توانند باشند.

    • @filegroup_name: در چه فایل‌گروپی جدول تغییرات ایجاد شود؛ پیشنهاد می‌شود برای جداول CDC فایل‌گروپ جداگانه‌ای ایجاد شود.

    • @allow_partition_switch: اگر جدول مبدا پارتیشن‌بندی شده باشد، مشخص می‌کند آیا می‌توان روی آن عملیات ALTER TABLE … SWITCH PARTITION انجام داد یا خیر. (به‌طور پیش‌فرض ۱ است.)

    پس از اجرای این رویه، جدول تغییرات (cdc.dbo_MyTable_CT) و توابع مربوط ایجاد می‌شوند و SQL Agent Jobs (ضبط و پاکسازی) راه‌اندازی می‌شوند. تا زمانی که SQL Agent در حال اجرا نباشد، فرآیند ضبط لاگ انجام نمی‌شود، اما فعال‌سازی امکان‌پذیر است.

  • غیرفعالسازی در سطح جدول:

     
    EXEC sys.sp_cdc_disable_table    
    @source_schema = N'dbo',    
    @source_name   = N'MyTable',    
    @capture_instance = N'dbo_MyTable';

    این رویه ( sys.sp_cdc_disable_table ) جدول تغییرات مربوط به آن نمونه ضبط را حذف کرده، توابع تولید شده را می‌ریزد و سطرهای مربوط در جداول سیستمی CDC را پاک می‌کند. اگر جدول چندین نمونه ضبط داشته باشد، می‌توان @capture_instance='all' را برای غیرفعالسازی همه استفاده کرد. عضویت در نقش db_owner مورد نیاز است.

  • غیرفعالسازی در سطح دیتابیس:

    EXEC sys.sp_cdc_disable_db;

    با اجرای این رویه، تمام جداول تحت پوشش CDC در این دیتابیس غیرفعال شده و تمامی اشیاء مرتبط (جداول تغییرات، توابع، Jobها و…) حذف می‌شود. ستون is_cdc_enabled در sys.databases برابر ۰ می‌گردد. قبل از آن، بهتر است همه جداول را با sys.sp_cdc_disable_table غیرفعال کنید تا از بروز خطا در تراکنش‌های طولانی جلوگیری شود.

  •  

رویه‌ها و توابع سیستمی مربوط به CDC

SQL Server تعدادی رویه و تابع سیستمی برای کار با CDC ارائه می‌دهد که به مختصر با آن‌ها آشنا می‌شویم:

  • sys.sp_cdc_enable_db و sys.sp_cdc_disable_db: برای فعال و غیرفعال‌سازی CDC در سطح دیتابیس (پارامتر ندارند).

  • sys.sp_cdc_enable_table و sys.sp_cdc_disable_table: برای فعال/غیرفعالسازی CDC روی جداول مشخص. (در فعال‌سازی می‌توان پارامترهایی مانند نام ایندکس یکتا و ستون‌های مورد نظر را داد).

  • sys.sp_cdc_add_job و sys.sp_cdc_drop_job: رویه‌های داخلی که به ترتیب، ایجاد و حذف SQL Agent Jobهای مربوط به CDC را انجام می‌دهند. معمولاً نیاز نیست مستقیماً صدا زده شوند، چون sp_cdc_enable_table در اولین جدول فعال‌سازی آن‌ها را ایجاد می‌کند.

  • sys.sp_cdc_change_job: برای تغییر تنظیمات پیش‌فرض Jobها (مثلاً تعداد تراکنش در هر اسکن یا دوره نظرسنجی و مقدار نگهداری) استفاده می‌شود. مثال:

    USE MyDatabase;  
    EXEC sys.sp_cdc_change_job       
    @job_type = N'cleanup',       
    @retention = 10080;  -- نگهداری به دقیقه (مثلاً 7 روز)

  • sys.sp_cdc_help_jobs: نمایش پیکربندی فعلی Jobهای CDC (جداول msdb.dbo.cdc_jobs).

  • sys.sp_cdc_start_job و sys.sp_cdc_stop_job: برای متوقف و راه‌اندازی دوباره Job ضبط تغییرات (capture job) یا پاکسازی (با تعیین @job_type) به کار می‌روند. توجه شود که توقف Job ضبط، داده‌ها را از بین نمی‌برد، بلکه فقط موقتا مانع از اسکن لاگ می‌شود.

  • sys.sp_cdc_help_change_data_capture: گزارش وضعیت هر نمونه‌ی CDC در دیتابیس (از جمله LSNهای فعلی، نام جدول و…) را نمایش می‌دهد.

  • sys.fn_cdc_get_all_changes_<capture_instance> و sys.fn_cdc_get_net_changes_<capture_instance>: دو تابع جدولی که به ترتیب تمام تغییرات یا تغییرات خالص را در بازه‌ی LSN دلخواه برمی‌گردانند. پارامترهای ورودی این توابع، LSN آغاز و LSN پایان (باینری(۱۰)) و یک رشته برای تعیین حالت ('all' یا 'all update old') هستند.

  • sys.fn_cdc_map_time_to_lsn و sys.fn_cdc_map_lsn_to_time: توابعی برای تبدیل مقدار زمانی به LSN یا برعکس. مثلاً برای گرفتن کوچکترین LSN پس از زمان مشخص، یا یافتن زمان مرتبط با یک.

  • sys.fn_cdc_get_min_lsn و sys.fn_cdc_get_max_lsn: برگرداننده‌ کمینه/بیشینه LSN موجود در جدول تغییرات برای یک نمونه‌‌ی ضبط (معادل LSNهای مرز پایین و بالا).

  • sys.sp_cdc_get_ddl_history: نمایش تاریخچه DDLهای مرتبط با CDC (مثلاً در صورت تغییرات ساختاری جدول مبنا). تغییرات ساختاری جداول تحت CDC در جدول cdc.ddl_history ثبت می‌شوند و این رویه امکان مشاهده آن‌ها را می‌دهد.

  • sys.sp_cdc_cleanup_change_table: برای پاک‌سازی دستی جدول تغییرات بر اساس مقدار LSN (آستانه) استفاده می‌شود. این رویه با تنظیم مقدار @low_water_mark (LSN جدید پایین) می‌تواند به‌صورت دستی داده‌های قدیمی را حذف کند؛ اما باید با احتیاط استفاده شود، چون ممکن است بر مصرف‌کنندگان تغییرات تأثیر بگذارد.

بازه زمانی نگهداری داده‌ها (Retention) و پاکسازی

پیش‌فرض، CDC داده‌ها را به مدت سه روز نگه می‌دارد (۴۳۲۰ دقیقه). اگر داده‌ای قدیمی‌تر شود، Job پاکسازی دوره‌ای آن را حذف می‌کند. این کار بر اساس یک بازه زمانی نگهداری (retention) انجام می‌شود: ابتدا نقطه‌ پایین (low watermark) اعتبار (LSN مربوط به سه روز قبل) به‌روزرسانی می‌شود، سپس کلیه ردیف‌هایی که پایین‌تر از آن LSN هستند حذف می‌شوند.

برای تغییر این مقدار می‌توان از sys.sp_cdc_change_job استفاده کرد. به طور مثال، برای افزایش نگهداری تا ۷ روز:

 
EXEC sys.sp_cdc_change_job      
@job_type = N'cleanup',      
@retention = 10080; -- معادل 10080 دقیقه = 7 روز

(حداکثر مقدار قابل تنظیم ۵۲۴۹۴۸۰۰ دقیقه یا ۱۰۰ سال است.) همچنین sys.sp_cdc_help_jobs می‌تواند مقدار فعلی نگهداری را گزارش کند. اگر بخواهید بدون محدودیت زمانی (بی‌نهایت) داده را نگهداری کنید، می‌توانید مقدار بسیار بزرگ یا مقدار ۰ را تنظیم کنید (اما توجه داشته باشید که حذف ردیف‌ها را به کلی غیرفعال می‌کند و فضای دیسک مصرفی افزایش می‌یابد).

برای پاکسازی فوری یا کنترل شده‌تر نیز می‌توان از sys.sp_cdc_cleanup_change_table استفاده کرد. در این حالت با تعیین LSN پایین جدید (@low_water_mark) می‌توانید دیتای قدیمی تا آن LSN را پاک کنید. به عنوان مثال:

DECLARE @failed bit = 0; 
EXEC sys.sp_cdc_cleanup_change_table    
@capture_instance = N'dbo_MyTable',     
@low_water_mark = 0x001234ABCD,      
@threshold = 1000,     
@fCleanupFailed = @failed OUTPUT;

این رویه پس از اعمال، هر رکورد با __$start_lsn کمتر از مقدار تعیین‌شده را حذف می‌کند.

تفاوت CDC با تریگرها و سایر روش‌های ثبت تغییر

در مقابل روش تریگرها (Trigger) که تغییرات را در همان زمان تراکنش روی جدول اصلی ثبت می‌کنند، CDC مبتنی بر خواندن غیرهمزمان لاگ است. یعنی تغییرات ابتدا در لاگ ثبت می‌شوند و بعداً توسط یک Job پردازش شده و در جداول تغییرات ذخیره می‌شوند. بنابراین عملیات نوشتن تاریخچه‌ی تغییرات از تراکنش اصلی جداست و تاخیر ناچیزی دارد (معمولاً چند ثانیه یا کمتر). این به معنی کارایی بهتر تراکنش‌های عملیاتی است، چرا که آن‌ها تحت تأثیر سربار تریگرها قرار نمی‌گیرند. اما از سوی دیگر، در CDC امکان افزودن خودکار ستون‌های دلخواه به تغییرات وجود ندارد (برخلاف تریگرهای دلخواه) و ساختار جدول تغییرات ثابت است؛ هرگونه تغییر ساختاری در جدول مبنا باعث ثبت null یا نیاز به ساخت نمونه ضبط جدید می‌شود.

به طور خلاصه، مزایا و معایب CDC نسبت به تریگرها:

  • مزایا: توسعه سریع‌تر (تنظیمات کم‌تر از نوشتن تریگرها)، عدم سربار سنگین روی تراکنش‌ها (زیرا اسکن لاگ ناهمگام است)، پشتیبانی بومی از SQL Agent و مدیریت خودکار. CDC گزینه مناسبی برای بارگذاری تدریجی داده‌ها در انبار داده است.

  • معایب: نیاز به SQL Agent و سرویس‌گیرش دیتابیس (لاگ) دارد، فقط در نسخه‌های خاصی در دسترس است (نسخه‌های Enterprise/Developer و Standard جدید) و حجم بالاتر (جداول تغییرات و فایل‌گروه) می‌تواند فضای دیسک بیشتری مصرف کند. همچنین اگر تعداد جداول CDC‌شده یا نرخ تغییرات زیاد باشد، فشار بر پردازش لاگ و CPU/IO افزایش می‌یابد. به طور کلی تأثیر CDC بر عملکرد مشابه سایر سیستم‌های CDC است؛ باید منابع کافی (مانند CPU، حافظه و فضای دیسک) برای پایگاه داشته باشید و در زمان‌های پیک می‌توانید Job ضبط را موقتاً متوقف کنید تا بار کاهش یابد.

روش‌های معمول پیاده‌سازی CDC در دیتابیس (روش‌های مبتنی بر ستون Audit، دلتاها، تریگرها یا لاگ). تفاوت اصلی CDC در استفاده از لاگ است (بخش پایین تصویر).

نگهداری نتایج و مدیریت عملکرد

بهتر است جداول تغییرات CDC را در فایل‌گروپ جداگانه و جدا از جدوال عملیاتی قرار دهید تا مدیریت بهتری روی آن‌ها داشته باشید. همچنین باید توجه کرد که CDC به طور پیش‌فرض با مسئله نگهداشتن لاگ همراه است: وقتی CDC فعال است، لاگ تراکنش تا زمان پردازش آخرین تغییرات توسط job ضبط، تراکنش‌گیری نمی‌شود. یعنی اگر SQL Agent متوقف باشد یا کند عمل کند، لاگ بیشتری روی سرور انباشته می‌شود. با این وجود اگر SQL Agent به طور معمول در حال کار باشد و پارامترهای Job بهینه باشند، می‌توان تأثیر بر تراکنش‌های عملیاتی را کاهش داد. ابزارهای مانیتورینگ و DMVهای مرتبط (مثل sys.dm_cdc_log_scan_sessions) برای پیگیری وضعیت و عملکرد CDC در دسترس است.

نسخه‌های پشتیبانی‌شده

همان‌طور که گفته شد، CDC در SQL Server 2008 و بالاتر معرفی شده است. در نسخه‌های اولیه تنها در Enterprise (و نسخه‌ی Developer که ویژگی‌های Enterprise را دارد) موجود بود. از SQL Server 2016 SP1 به بعد، نسخه Standard نیز CDC را پشتیبانی می‌کند. (نسخه‌های Web و Express فاقد CDC هستند.) بنابراین در هنگام طراحی سیستم باید نسخه SQL Server را در نظر گرفت.

نتیجه‌گیری

در این مقاله CDC را به طور جامع بررسی کردیم: CDC یک راهکار ثبت تغییرات مبتنی بر لاگ در SQL Server است که با فعال‌سازی ساده می‌تواند تغییرات جداول را رصد و نگهداری کند. دستورات sp_cdc_enable_db/table برای فعالسازی، sp_cdc_disable_db/table برای غیرفعال‌سازی، توابع fn_cdc_get_* برای خواندن تغییرات، و Jobهای SQL Agent برای ضبط و پاکسازی، از مهم‌ترین اجزای آن هستند. CDC به کاربران امکان می‌دهد بدون نوشتن تریگرهای متعدد، تغییرات را جمع‌آوری کرده و به شکل جدول درآورند (برای ETL یا تطبیق داده). هرچند مصرف منابع در CDC باید مدیریت شود و اندازه‌ی نگهداری داده‌ها را با تنظیم ریتنشن و پاکسازی کنترل کرد. در مجموع CDC ابزاری پایدار و مقیاس‌پذیر برای پیگیری تغییرات داده در SQL Server است که با اجرای جزئی تنظیمات، راهکاری آماده ارائه می‌کند.

 

0 نظر

نظر محترم شما در مورد مقاله های وب سایت برنامه نویسی و پایگاه داده

نظرات محترم شما در خدمات رسانی بهتر ما را یاری می نمایند. لطفا اگر مایل بودید یک نظر ما را مهمان فرمائید. آدرس ایمیل و وب سایت شما نمایش داده نخواهد شد.

حرف 500 حداکثر

اطلاعات تماس

  • آدرس:اصفهان-خیابان ام کلثوم غربی - بعد خیابان تخم چی - بیست متر بعد از پیتزا ننه شب - کوچه تعمیر گاه سمار زغالی - پلاک 354 - درب مشکی - طبقه هفتم
  • آدرس ایمیل:najafzade@gmail.com
  • وب سایت:http://www.a00b.com/
  • تلفن ثابت:(+98)9131253620
  • تلفن همراه:09131253620