ویژگی Change Data Capture در SQL Server

ویژگی Change Data Capture (CDC) در SQL Server

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

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

CDC چیست؟

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

CDC این مشکل را حل می‌کند.
این قابلیت تغییرات اعمال‌شده روی جداول را از Transaction Log استخراج کرده و در جداول مخصوصی ذخیره می‌کند.
به این ترتیب تاریخچه تغییرات قابل مشاهده و تحلیل خواهد بود.

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

نحوه عملکرد CDC  در SQL Server

CDC برای ثبت تغییرات از Transaction Log پایگاه داده استفاده می‌کند.
هر عملیاتی که روی جدول انجام شود ابتدا در لاگ تراکنش ثبت می‌شود. CDC این اطلاعات را از لاگ استخراج کرده و در جداول سیستمی ویژه‌ای ذخیره می‌کند.

فرآیند عملکرد CDC شامل مراحل زیر است:

• فعال‌سازی CDC روی پایگاه داده

• فعال‌سازی CDC روی جدول موردنظر

• ایجاد Jobهای سیستمی برای خواندن Transaction Log

• ثبت تغییرات در جداول Change Table

• امکان استخراج تغییرات توسط برنامه‌ها و ابزارهای ETL

از آنجا که CDC مستقیماً از Transaction Log استفاده می‌کند، سربار کمتری نسبت به Triggerها ایجاد می‌کند و عملکرد بهتری در محیط‌های بزرگ دارد.

اجزای اصلی CDC در SQL Server

پس از فعال‌سازی CDC، چندین شیء سیستمی در پایگاه داده ایجاد می‌شود.

1. Change Table

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

نام این جداول معمولاً به شکل زیر است:

cdc.<CaptureInstance>_CT

این جدول شامل اطلاعات زیر است:

• داده‌های تغییر یافته
• نوع عملیات
• شماره توالی لاگ
• اطلاعات تراکنش

2. Capture Job

این Job وظیفه خواندن Transaction Log و انتقال اطلاعات تغییرات به Change Table را بر عهده دارد.

3. Cleanup Job

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

4. Metadata Tables

SQL Server مجموعه‌ای از جداول سیستمی برای مدیریت اطلاعات CDC ایجاد می‌کند که شامل تنظیمات، وضعیت پردازش و اطلاعات Capture Instance هستند.

فعال‌سازی CDC در SQL Server

برای استفاده از CDC ابتدا باید آن را در سطح پایگاه داده فعال کرد.

• فعال‌سازی در سطح Database

EXEC sys.sp_cdc_enable_db;

پس از اجرای این دستور، پایگاه داده برای استفاده از CDC آماده می‌شود.

• فعال‌سازی روی جدول

EXEC sys.sp_cdc_enable_table

@source_schema = 'dbo',

@source_name = 'Customers',

@role_name = NULL;

در این مثال، CDC برای جدول Customers فعال می‌شود.

پس از اجرای دستور، SQL Server به طور خودکار Change Table و سایر اشیای موردنیاز را ایجاد می‌کند.

• مشاهده تغییرات ثبت شده

پس از اعمال تغییرات روی جدول، اطلاعات در Change Table ذخیره می‌شود و می‌توان آن‌ها را مشاهده کرد.

مثال:

SELECT *

FROM cdc.dbo_Customers_CT;

نتیجه شامل تمام تغییرات اعمال‌شده روی جدول خواهد بود.

ستون‌های مهم در CDC  در SQL Server

جدول تغییرات دارای چند ستون سیستمی مهم است.

• __$operation

  نوع عملیات انجام‌شده را مشخص می‌کند:

• __$start_lsn

شماره Log Sequence Number مربوط به تراکنش را ذخیره می‌کند.

• __$seqval

ترتیب اجرای عملیات را مشخص می‌کند.

• __$update_mask

ستون‌هایی که در عملیات Update تغییر کرده‌اند را مشخص می‌کند.

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

مزایای CDC  در SQL Server

• کاهش حجم پردازش

  در سیستم‌های بزرگ معمولاً میلیون‌ها رکورد وجود دارد.
  CDC این امکان را می‌دهد که فقط رکوردهای تغییر یافته پردازش شوند.

• عملکرد بهتر نسبت به Trigger

  Triggerها هنگام هر عملیات اجرا می‌شوند و ممکن است باعث کاهش سرعت سیستم شوند.
  CDC با استفاده از Transaction Log سربار کمتری دارد.

• مناسب برای ETL

  یکی از مهم‌ترین کاربردهای CDC در فرآیندهای ETL است.
  ابزارهای انتقال داده می‌توانند فقط تغییرات جدید را استخراج کنند.

• امکان ثبت تاریخچه تغییرات

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

• افزایش دقت در همگام‌سازی داده‌ها

  در سیستم‌هایی که چند پایگاه داده با یکدیگر تبادل اطلاعات دارند، CDC کمک می‌کند فقط داده‌های تغییر یافته منتقل شوند.

کاربردهای CDC  در SQL Server

• انبار داده (Data Warehouse)

  در پروژه‌های Data Warehouse معمولاً لازم است تغییرات سیستم عملیاتی به انبار داده منتقل شود.
  CDC این فرآیند را بسیار ساده می‌کند.

• سیستم‌های گزارش‌گیری

  گزارش‌هایی که نیاز به تحلیل تغییرات داده دارند می‌توانند از اطلاعات CDC استفاده کنند.

• همگام‌سازی بانک‌های اطلاعاتی

  CDC می‌تواند برای انتقال تغییرات بین سرورها و پایگاه‌های داده مختلف مورد استفاده قرار گیرد.

• Audit و نظارت

  در برخی سازمان‌ها لازم است تمام تغییرات داده‌ها ثبت شود. CDC می‌تواند بخشی از این نیاز را برطرف کند.

• انتقال داده بلادرنگ

  در معماری‌های مدرن و سامانه‌های Real-Time Data Processing، CDC به عنوان ابزاری برای تشخیص تغییرات و ارسال آن‌ها به سرویس‌های دیگر استفاده می‌شود.

• تفاوت CDC و Trigger

  بسیاری از توسعه‌دهندگان هنگام نیاز به ثبت تغییرات بین CDC و Trigger مردد می‌شوند.

ویژگی CDC Trigger  در SQL Server

در اکثر سناریوهای ثبت تغییرات، CDC گزینه مناسب‌تری محسوب می‌شود.

محدودیت‌های CDC در SQL Server

با وجود مزایای فراوان، CDC محدودیت‌هایی نیز دارد.

• افزایش حجم ذخیره‌سازی

  نگهداری تاریخچه تغییرات باعث مصرف فضای بیشتر در پایگاه داده می‌شود.

• وابستگی به SQL Server Agent

  CDC برای اجرای Jobهای Capture و Cleanup به SQL Server Agent وابسته است.

• عدم پشتیبانی برخی اشیا

  برخی انواع جداول و ویژگی‌های خاص ممکن است محدودیت‌هایی در استفاده از CDC داشته باشند.

• نیاز به مدیریت داده‌های قدیمی

  در صورت عدم تنظیم مناسب Cleanup Job، حجم اطلاعات CDC می‌تواند بسیار زیاد شود.

CDC در نسخه‌های جدید SQL Server

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

همچنین در سرویس‌های ابری مانند Azure SQL Database نیز پشتیبانی از CDC فراهم شده است که امکان پیاده‌سازی راهکارهای مدرن انتقال داده را فراهم می‌کند.

جمع‌بندی

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

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