در
قسمت قبلی با CDC و نحوه فعال‌سازی آن آشنا شدیم. در مقاله امروز رادکام به بررسی جداول CDC
ایجادشده و نحوه نگهداری
آن‌ها می‌پردازیم.
بررسی جداول CDC – جدول CDC مربوط به جدول هدف
در هر change table، پنج ستون اول meta data هستند و باقی ستون‌ها انعکاسی از ستون جدول هدف بوده و برای نمایش تغییری که
صورت گرفته ایجاد می‌شوند.
هر Insert، Update یا Delete که در جدول موردنظر صورت بگیرد، خود را در غالب یک رکورد در Change Table نشان می‌دهد که در
آن رکوردهای اینسرت شده به‌صورت زیر هستند:
- برای insert: ستون‌ها نشان‌دهنده مقادیر جدیدی که اینسرت شده‌اند هستند.
- برای delete: ستون‌ها نشان‌دهنده مقادیر پیش از حذف هستند.
- برای update: دو رکورد ثبت می‌شود که یکی نشان‌دهنده مقادیر پیش از آپدیت و دیگری نشان‌دهنده مقادیر پس از آپدیت
هستند.
توضیحات meta data در جدول CDC جدول هدف
- __$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
این جداول به‌راحتی قابلیت حجیم شدن را دارا هستند، پس باید برای خالی کردن آن نیز برنامه‌ای در نظر گرفت.
رکوردهای ثبت‌شده در این جداول به‌صورت پیش‌فرض پس از 3 روز حذف می‌شوند.
از جدول cdc.lsn_time_mapping می‌توانیم زمان‌بندی تغییرات را ببینیم و job مربوط به تمیزکاری نیز از مقادیر همین جدول
متوجه می‌شود که چه رکوردهایی را باید حذف کند.
برای تغییر مدت‌زمان پیش‌فرض 3 روز که رکوردهای قدیمی‌تر از آن حذف می‌شوند، از دستور زیر استفاده می‌کنیم. توجه کنید که
حداکثر مقدار قابل‌قبول 52494800 یا 100 سال است:
sp_cdc_change_job @job_type='cleanup', @retention=minutes
تغییرات صورت گرفته بر جداول
- اگر بعد از فعال کردن CDC برای یک جدول ستون‌هایی به آن اضافه شود، در CDC نادیده گرفته می‌شود.
- اگر بعد از فعال کردن CDC ستونی از جدول موردنظر حذف شود، در جدول CDC از این به بعد برای آن ستون Null ثبت می‌شود.
- اگر نوع داده یک ستون تغییر کند، تغییرات در جدول CDC هم منعکس می‌شود و به‌این‌ترتیب اطلاعات از دست نمی‌رود.
با توجه به این اطلاعات، اگر دیتای تغییر کرده از اهمیت برخوردار است، باید فکری جهت نگهداری این اطلاعات در data
warehouse کنیم. به‌این‌ترتیب اگر ساختار جدول هم تغییر کرد، می‌توانیم CDC را از ابتدا فعال کرده تا این تغییر ساختار
جدول موردنظر نیز در جداول مربوط به CDC منعکس شود.
می‌توانیم تغییرات صورت گرفته در ساختار جدول را به این صورت مشاهده کنیم:
EXECUTE sys.sp_cdc_get_ddl_history
@capture_instance = 'dbo_Person';
GO
منبع:
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15