کنترل تغییرات در SQL Server بخش دوم Change Data Capture

کنترل تغییرات در SQL Server- بخش دوم Change Data Capture

در مقاله قبل که اولین مقاله این بلاگ بود به بررسی کنترل تغییرات دیتا در SQL Server توسط Change Tracking و اکنون در ادامه این سری مقالات قصد داریم به بررسی Change Data Capture یا CDC بپردازیم که تقریباً شبیه به Change Tracking می‌باشد، با این تفاوت که در این تکنیک ما به تاریخچه‌ی داده های تغییر کرده نیز دسترسی داریم. در ادامه مطلب با ما همراه باشید تا با هم به بررسی این تکنیک بپردازیم

IC156272

 

همانطور که اشاره شد Change Data Capture که از این پس CDC صداش خواهیم کرد از جهات زیادی شبیه به Change Tracking است با این تفاوت ها:

  • برخلاف Change Tracking که هیچ سابقه ای از داده های تغییر کرده را در اختیار کاربر قرار نمی‌داد، CDC تاریخچه تغییرات را نیز ذخیره می‌کند
  • یکی از معایب Change Tracking این بود که تغییرات را به صورت همزمان(Synchronous) با اجرای تراکنش‌ها درون Change Table ذخیره می‌کرد که منجربه اعمال Overhead زمانی برروی کوئری‌های ما می‌شود، در تکنیک CDC اینگونه نیست و تمامی تغییرات توسط روندی غیرهمزمان(Asynchronous) انجام می‌گیرد، در نتیجه تاثیری برروی زمان اجرای تراکنش های بانک اطلاعاتی ما نخواهد داشت.

فعال سازی change Data Capture:

برای این منظور یک دیتابیس تست با نام TestDB ایجاد می‌کنیم:

USE master
GO 

--Create a Test Database To Run This Sample
IF DB_ID('TestDB') >0
 BEGIN 
 ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 DROP DATABASE TestDB;
 END 
GO

CREATE DATABASE TestDB;
GO

همانند Change Tracking توصیه می‌شود که SNAPSHOT_ISOLATION را برروی دیتابیس مورد نظر فعال نماییم، که منجربه جلوگیری از ایجاد Blocking به هنگام دسترسی به داده های ChangeTable می‌شود، که همین امر کمی بار پردازشی برای دیتابیس ایجاد می‌نماید. بعد از فعال سازی SNAPSHOT_ISOLATION تمامی تغییرات داده ها در قالب مکانیزم Row versioning در دیتابیس tempdb ذخیره می گردد ، به جهت فعال سازی SNAPSHOT_ISOLATION از دستور زیر استفاده می‌کنیم:

--Enable SNAPSHOT_ISOLATION on Database
ALTER DATABASE TestDB SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

پس از آن نوبت به فعال‌سازی change data capture بروی دیتابیس می‌باشد(این کار توسط یک SP به نام sp_cdc_enable_db انجام می‌شود)

--Enable CDC on Database
USE TestDB
EXECUTE sys.sp_cdc_enable_db
GO

به محض اجرای دستور فوق یک schema به نام cdc به دیتابیس اضافه خواهد شد و در همان ابتدا ۵ جدول درون این schema موجود می باشد:

جداول مرتبط با Change Data Capture

همانطور که در تصویر فوق مشاهده می‌نمایید change data capture برای عملیات های مختلفی که ممکن است منجر به ایجاد تغییر شود جدول جداگانه‌ای در نظر گرفته است، این جداول عبارتند از:

  • cdc.captured_columns : همانطور که از نام آن مشخص است این جدول حاوی لیستی از فیلدهایی است که تغییر روی آنها صورت گرفته است.
  • cdc.change_tables : در این جدول لیستی از جداولی که cdc بروی آنها فعال شده است وجود دارد.
  • cdc.ddl_history:به ازای هر تغییر از طریق دستورات ddl که برروی یکی از جداول با cdc فعال صورت گرفته باشد یک رکورد در این جدول درج می‌گردد.
  • cdc.index_column : به ازای هر index column ی که در change table موجود می‌باشد یک رکورد در این جدول موجود می باشد
  • cdc.lsn_time_mapping : در این جدول لیستی از Log Sequence Number یا LSNها و زمان رخداد آنها وجود دارد

اکنون توسط کدهای زیر یک جدول تست ایجاد کرده و change data capture را بروی آن فعال می‌نماییم:

USE TestDB
GO	
CREATE TABLE dbo.products
(
   ProductID INT IDENTITY PRIMARY KEY,
   ProductName VARCHAR(30) NOT NULL,
   ProductCost MONEY NOT NULL
)
GO	

در قطعه کد فوق یک جدول در TestDB ایجاد کردیم و در ادامه جهت فعال سازی cdc بروی این جدول از یک sp سیستمی به نام sys.sp_cdc_enable_tables استفاده می‌نماییم، این sp نه عدد پارامتر ورودی دارد که در این مقاله ما تنها به سه مورد آنها اشاره خواهیم کرد. قبل از فعال سازی cdc بروی این جدول باید اطمینان حاصل کنیم که سرویس SQL Agent بروی سرور بانک اطلاعاتی فعال باشد.

EXEC sys.sp_cdc_enable_table
   @source_schema = 'dbo',
    @source_name = 'Products',
    @role_name = NULL
GO

پارامترهای ورودی که در این مثال مورد استفاده قرار گرفته است به شرح زیر می‌باشد:

  • source_schema : یک پارامتر اجباری است که مشخص کننده schema جدول مورد نظر می‌باشد
  • source_name : این پارامتر نیز اجباری است و مشخص کننده نام جدول مورد نظر است
  • role_name: این پارامتر مشخص کننده database role خاصی است که میخواهیم فقط آن role به جداول cdc دسترسی داشته باشد، در صورتی که این پارامتر را برابر NULL قرار دهیم به طور پیش‌فرض برابر cdc_admin خواهد بود.

اجرای دستورات قطعه کد فوق بین ۱۵ تا ۳۰ ثانیه طول خواهد کشید و به محض پایان اجرای دستورات با خروجی زیر مواجه خواهیم شد.

02.CDCEnabledForATable

همانطور که مشاهده می‌کنید وقتی برای اولین بار cdc را فعال می‌کنیم دو job ایجاد می‌شود، علاوه بر اینها sp_cdc_enable_table سه عدد SP، دو function و یک جدول سیستمی نیز تولید می‌کند.

دو job ایجاد شده به شرح زیر می‌باشد:

  • capture job : وظیفه اصلی آن دریافت و ثبت تغییرات صورت گرفته در جداول مرتبط با CDC می‌باشد، این job توسط sp_MScdc_capture_job اقدام به اقدام به انجام این کار می‌کند که در روند اجرای sp مورد نظر فعالیت های زیر صورت می‌گیرد
    • در اولین مرحله sp_MScdc_capture اقدام به اجرای sp_cdc_scan میکند
    • sp_cdc_scan نیز توسط sp_replcmds محتویات لاگ فایل را برای یافتن تغییرات برروی جداول مرتبط با cdc اسکن می‌نماید.
  • Cleanup job : این job که وظیفه پاکسازی داده‌های قدیمی را برعهده دارد، اقدام به اجرای sp_MScdc_cleanup_job می‌نماید. که به ترتیب مراحل زیر را طی می‌نماید:
    • در این مرحله با فراخوانی sp_cdc_cleanup_change_tables با پارامتر ورودی Retention Period اقدام به پاکسازی داده‌های قدیمی می‌نماید.

با فعال سازی cdc بروی جدول Products، جدولی با نام cdc.dbo_Products_CT توسط SQL Server ایجاد می‌گردد که درون آن تمامی فیلدهای خود جدول به همراه ۵ فیلد دیگر ایجاد می‌گردد که حاوی تمامی تغییرات صورت گرفته برروی جدول Products خواهد بود.

بررسی صحت عملکرد:

در حال حاضر هیچ داده‌ای درون جدول Products وجود ندارد، از اینجا به بعد هر تغییری که بروی جدول Products صورت گیرد(ثبت و ویرایش اطلاعات) همگی در جدول سیستمی cdc.dbo_Products_CT لاگ خواهد شد.در واقع وقتی job ثبت تغییرات با نام cdc.TestDB_capture  به هنگام اجرا  لاگ فایل را جهت یافتن تغییرات مرتبط با جداول با cdc فعال اسکن کرده و به محض مشاهده تغییر آن را در جدول سیستمی cdc.dbo_Products_CT درج می‌نماید. حال این مورد را با درج یک رکورد در جدول Products تست می‌کنیم:

INSERT INTO dbo.Products 
        ( ProductName, ProductCost )
VALUES  ( 'Product 1',1000.00)
GO

حال با مشاهده تغییرات در جدول Product و cdc.dbo_Products_CT مشاهده می‌کنیم که یک سطر به هر دو جدول اضافه شده است:

تاثیر ثبت رکورد جدید

همانطور که مشاهده می‌کنید  رکورد ثبت شده درون جدول Products با فاصله زمانی ناچیزی پس از درج به همراه دیگر اطلاعات کنترلی مرتبط با cdc درون جدول cdc.dbo_Products_CT درج گردیده است. دلیل تاخیر زمانی بین ثبت رکورد اصلی و لاگ شدن آن در جداول cdc نیز اینه که اگر یادتون باشه در ابتدای بحث به این نکته اشاره کردم که مکانیز ثبت تغییرات در cdc به صورت Async انجام میشه و به همین دلیل مدت زمانی ناچیزی رو باید برای ثبت تغییرات در نظر بگیریم. فیلدهای مهمی که توی جدول cdc.dbo_Products_CT وجود داره اینا هستند:

  • start_lsn و end_lsn  : مفهوم lsn یا همون Log Sequence Number یک کد هگزا دسیمال با نوع داده ای binary هستش که به عنوان وجه تمایز رکوردها توی SQL Server Transaction Log مورد استفاده قرار می‌گیره، این مقدار به صورت صعودیه که توی ثبت تغییرات نشون میده کدوم عملیات زودتر یا دیرتر نسبت به تغییر دیگه‌ای انجام شده، در مورد این جدول هم Start_lsn و end_lsn به این اشاره دارند بازه انجام یک تغییر خاص از کجا تا کجاست؟
  • operation : مشخص کننده نوع عملیاتیه که انجام شده، مقدارش از نوع INT هست و مقادیری که می‌گیره :
    • یک : عملیات delete بوده
    • دو : عملیات insert بوده
    • سه : عملیات update بوده، که در این صورت مقداری که نمایش میده مقدار قدیمی رکورد مورد نظر خواهد بود
    • چهار: عملیات update بوده با این تفاوت که اینبار مقدار Committ شده‌ای که به اون اختصاص داده شده نمایش داده خواهد شد(دو مقدار سه و چهار رو در ادامه به طور کامل توضیح خواهم داد)

توی این مثال همونطور که مشاهده میکنید مقدار فیلد operation برابر ۲ هست که نشون میده رکورد به تازگی ثبت شده، حالا فیلد جدیدی اضافه می‌کنیم و مجدد از جداولمون خروجی میگیریم:

INSERT INTO dbo.Products
        ( ProductName, ProductCost )
VALUES  ( 'Samsung TV',1500.00)


SELECT * FROM dbo.Products
SELECT * FROM cdc.dbo_Products_CT

ثبت رکورد جدید و تغییرات cdc

خب با کوئری گرفتن از جداولمون می‌بینیم که الان دوتا رکورد توی cdc.dbo_Products_CT وجود داره که اون رکورد جدید هم مقدار ۲ رو برای فیلد operation داره که نشون دهنده نوع عملیات insert هستش، حالا میخوایم یک update روی جدولمون داشته باشیم

UPDATE dbo.Products SET ProductCost=2200.00 WHERE ProductID = 1

SELECT * FROM dbo.Products
SELECT * FROM cdc.dbo_Products_CT

 

تغییرات پس از update

همونطور که مشاهده می‌کنید بعد از دستور updateی که روی جدولمون اجرا کردیم، دوتا رکورد جدید به جدول cdc.dbo_Products_CT اضافه شد که رکورد اول وضعیت رکورد قبل از تغییر و رکورد دوم وضعیت اون رو بعد از انجام تغییرات نشون میده.

تا اینجا عملیات های insert و update رو انجام دادیم و تغییراتش رو هم توی جدول اصلی و همچنین جداول cdc مشاهده کردیم، حالا می‌خوایم تاثیر عملیات delete رو بررسی کنیم.

DELETE FROM dbo.Products WHERE ProductID=1

SELECT * FROM dbo.Products
SELECT * FROM cdc.dbo_Products_CT
GO

تاثیر دستور delete بروی cdc

همونطور که توی تصویر بالا مشاهده می‌کنید رکورد مورد نظر از جدول اصلی حذف شده و بلافاصله یک رکورد با operation =1 که معادل دستور delete هست به جدول cdc اضافه شده که مقادیر فیلدهای رکورد حذف شده رو قبل از عملیات حذف درو خودش نمایش میده، از این جدول استفاده های خوبی میشه کرد مثلاً توی همین مثال می@خوایم بررسی کنیم که محصولی با نام Product1 چه تغییراتی رو در طول زمان داشته میتونیم از دستور زیر استفاده کنیم

SELECT 
   ProductID,
   ProductName,
   ProductCost,
   CASE [__$operation] WHEN 1 THEN 'Deleted' WHEN 2 THEN 'Inserted' WHEN 3 THEN 'Updated: Values Before Update' WHEN 4 THEN 'Updated: Values After Update' END AS Operation
FROM cdc.dbo_Products_CT WHERE ProductID=1

مشاهده تاریخچه تغییرات

تو این تصویر داریم می‌بینیم که دقیقا در تمام طول عمر این رکورد چه اتفاقاتی براش رخ داده، این تنها استفاده مفید از این مکانیزم نیست و خیلی سناریو های متفاوتی رو میشه با cdc پیاده سازی کرد برای مثال اگر با Disconnected Caching آشنا باشید که توی  اون دو تا سیستم داریم که این سیستم ها کانکشن همیشگی با همدیگه ندارند و تنها در زمان های خاصی در روز یا حتی به صورت نامنظم در بازه های زمانی مختلف به همدیگه کانکت میشن و تغییرات رو دریافت میکنند، توی همچین سناریویی برای مثال دو تا سیستم داریم که سیستم A سیستم مرجع ماست و سیستم B بخش های خاصی از سیستم A رو نیاز داره و میخواد هر موقع کانکت شد این تغییرات رو با تاریخچه دقیق داشته باشه که در این حالت cdc به خوبی میتونه تمام نیازهامون رو برآورده کنه به این شکل که هر بار از آخرین lsnی که دفعه قبل داده ها رو دریافت کرده شروع به برداشتن داده‌ها بکنه.

خب صرفاً هم اینطوری نیست که حتماً باید خودمون برای رسیدن به این داده ها اقدام به نوشتن کوئری بکنیم، ماکروسافت این کار رو هم برامون انجام داده بطوری که بلافاصله بعد از اینکه cdc رو روی یک جدول خاص فعال می‌کنیم دو عدد فانکشن با نام های :

  • cdc.fn_cdc_get_all_changes_dbo_Products که تمام تغییرات جدول مورد نظر رو نمایش میده
  • cdc.fn_cdc_get_net_changes_dbo_Products که از این فانکشن میتونیم تنها برای دریافت آخرین تغییرات استفاده کنیم، سه پارامتر ورودی دارد:
    • from_lsn : مقدار شروع lsn برای برداشت اطلاعات
    • to_lsn : مقدار پایانی lsn برای برداشت اطلاعات
    • row_filter_option : این پارامتر هم به ما اجازه میده مشخص کنیم که کدام رکوردها نمایش داده بشن

اجازه بدید با یک مثال این رو تست کنیم، در درجه اول اگر بخواهیم یک Mapping بین lsn ها و زمان داشته باشیم یعنی اینکه بفهمیم یک lsn چه زمانی رخ داده از دستور زیر استفاده می‌کنیم:

select sys.fn_cdc_map_time_to_lsn('largest less than or equal', getdate())

و یا از جدول cdc.lsn_time_mapping که تمام lsn  های تولید شده را لیست می‌کند استفاده می‌کنیم، خوبه این نکته رو بدونید که توی این جدول اون lsn هایی که به همراه خودشتون تغییرات هم دارند مقدار فیلد tran_begin_lsn اونها چیزی غیر از 0x00000000000000000000 خواهد بود

lsn_time_mapping

همونطور که توی تصویر مشاهده می‌کنید پنج عدد از این LSN ها حاوی تغییرات بودن و حالا اگر بخواهیم تغییرات بازه زمانی ۱۲:۱۵ تا ۱۲:۵۸ رو مشاهده کنیم به راحتی ابتدا lsn ابتدا و انتهای بازه مورد نظر رو انتخاب می‌کنیم و با استفاده از دستور زیر تغییرات رو نمایش میدیم

SELECT * from cdc.fn_cdc_get_net_changes_dbo_Products(0x00000029000001890019,0x0000002A000001CD0003,'all')

get_net_changes

کنترل تغییرات از نوع DDL

تا اینجا تمامی تغییراتی که لاگ کردیم از نوع DML بود که صرفاً با دیتا ها در ارتباط بودن، اما cdc این قابلیت رو هم داره که دستورات DDL یا همون دستورات مرتبط با تغییرات ساختاری جداول رو هم لاگ کنه. برای اینکار هم نیاز به انجام تنظیم خاصی یا اجرای دستور خاصی برای فعال کردن اون نداریم و با فعال کردن cdc روی دیتابیس به طور خودکار فعال میشه، اجازه بدید با یک مثال بررسیش کنیم: می‌خواهیم به جدول Products یک فیلد اضافه کنیم، بعد همون فیلد رو حذفش کنیم و مجدد اضافش کنیم و نتیجه رو با هم بررسی کنیم :

ALTER TABLE	dbo.Products ADD tempColumn INT ;
GO	
ALTER TABLE	dbo.Products DROP COLUMN tempColumn;
GO	
ALTER TABLE	dbo.Products ADD tempColumn INT ;
GO

و حالا برای اینکه ببینیم اون تغییرات لاگ شدن یا خیر لازمه که یک کوئری از جدول cdc.ddl_history  بگیریم که خرجی زیر رو مشاهده خواهیم کرد

کنترل تغییرات ساختاری

همونطور که مشاهده می‌کنید cdc تمام دستورات مرتبط با تغییرات ساختاری جداولمون رو هم لاگ می‌کنه، یک راه دیگه برای پیاده سازی این مکانیزم استفاده از DDL Triggerها هست که سعی میکنم در آینده نزدیک حتماً یک پست اختصاصی در مورد DDL Trigger هم داشته باشیم.

پاکسازی تاریخچه تغییرات

خب تا به اینجا روند لاگ کردن تغییرات رو به طور کامل بررسی کردیم، اما اگر توی یک سیستم OLTP با حجم تراکنش‌های بالا تکلیف چیه؟ حجم دیتایی که داره تولید میشه رو کی کنترل کنه؟ دستی پاکش کنیم؟ براش job تعریف کنیم ؟

اگر یادتون باشه وقتی داشتیم Change Data Capture رو روی جدول Products فعال می‌کردیم دو تا job ایجاد شد که اولیش که وظیفه لاگ کردن تغییرات رو داشت به طور کامل بررسی کردیم، اما دومین job که همون Cleanup_job باشه دقیقاً همین کار رو برامون انجام میده، کارش اینه که به صورت دوره‌ایاقدام به حذف داده های با عمر بیش از حد Retention Period که به طور پیش‌فرض برابر با سه روز هستش میکنه، مقدار Retention Period توی جدولی با نام dbo.cdc_jobs توی دیتابیس msdb ذخیره شده که دوتا از مهمترین فیلدهایی که توی این جدول وجود داره

  • retention که میزان این فاکتور رو به واحد دقیقه مشخص میکنه
  • threshold که تعداد ماکزیمم رکورد هایی که در هر بار اجرای این فرایند امکان پاک شدن رو دارن مشخص می‌کنه

اگر نیاز به اطلاعات بیشتری در این زمینه دارید پیشنهاد میکنم ی سر به اینجا بزنید.

در پایان میخوام یک سری کوئری های کاربردی که توسط اونها می‌تونید اطلاعات کاملتری نسبت به تنظیمات cdc بدست بیارید بهتون معرفی کنم

EXECUTE	sys.sp_cdc_help_change_data_capture 

اولین مورد sp سیستمی sys.sp_cdc_help_change_data_capture که لیستی از تمامی جداولی که cdc روی اونها فعال شده به همراه اطلاعاتی  از قبیل تاریخ فعال سازی و لیست فیلدهایی که capture میشن رو برمی‌گردونه و اگیر تنها نیاز داشته باشیم که لیستی از نام جداول داشته باشیم از دستور زیر استفاده می‌کنیم

SELECT name,is_tracked_by_cdc 
FROM sys.tables 
WHERE is_tracked_by_cdc=1 
ORDER BY name

خب دوتا قطعه کد بالا کاملاً واضح بود و نیاز به توضیح بیشتری نداره، بیاید سناریویی بررسی کنیم که میخواهیم بدون دونستن lsn تغییرات مرتبط با دو روز اخیر رو توسط function cdc.fn_cdc_get_all_changes_dbo_Products به دست بیاریم. خب همونطور که قبلاً هم اشاره کردیم میتونیم از طریق function سیستمی sys.fn_cdc_map_time_to_lsn یک رابطه متناظر بین lsn های تولید شده در Transaction Log و زمان داشته باشیم، حلا با استفاده از همین دو function میخواهیم این سناریو رو پیاده سازی کنیم:

DECLARE @startDate DATETIME = DATEADD(dd,-2,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)); --Last Two Days
DECLARE @EndDate DATETIME=GETDATE();
DECLARE @StartLSN BINARY(10);
DECLARE	@EndLSN BINARY(10);
SELECT @StartLSN = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@startDate);
SELECT @EndLSN = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@EndDate);

SELECT 
   'All Changes' AS [Filter Option],
   sys.fn_cdc_map_lsn_to_time([__$start_lsn]) AS [Start LSN Date],
   CASE [__$operation]
         WHEN 1 THEN	'Delete'
         WHEN 2 THEN	'Insert'
         WHEN 3 THEN	'Update_OldValue'
         WHEN 4 THEN	'Update_NewValue'
         ELSE 'Big Mess'
   END AS [Operation],
   ProductID,ProductName,ProductCost
FROM cdc.fn_cdc_get_all_changes_dbo_Products(@StartLSN,@EndLSN,'all');

که با اجرای کد بالا با خروجی زیر مواجه خواهیم شد

تغییرات دو روز اخیر

در مورد function سیستمی sys.fn_cdc_map_time_to_lsn آخرین پارامتر آن یک رشته از حروف برای تعیین مرز دریافت lsn می‌باشد که در زیر مقادیر مختفی که می‌توان به آن نسبت داد را مشاهده می‌کنید:

  • largest less than : بزرگترین مقدار کوچکتر از x باشد
  • largest less than or equal : بزرگترین مقدار کوچکتر یا مساوی x باشد
  • smallest greater than : کوچکترین مقدار بزرگتر از x باشد
  • smallest greater than or equal : کوچکترین مقدار بزرگتر یا مساوی x باشد

حالا اگر قصد بررسی دقیقتری این اطلاعات روداشته باشیم، می‌تونیم بگیم مثلاً آیا طی دو روز اخیر تغییر قیمت داشتیم یا نه؟ اگر داشتیم نمایشش بده. برای این کار میتونیم از function سیستمی sys.dn_cdc_has_column_changed استفاده کنیم که دقیقاً کارش همینه که بگه یک فیلد خاص تغییر داشته یا نه؟ کدهای مرحله قبل رو برای این مورد تغییر میدیم

DECLARE @startDate DATETIME = DATEADD(dd,-2,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)); --Last Two Days
DECLARE @EndDate DATETIME=GETDATE();
DECLARE @StartLSN BINARY(10);
DECLARE	@EndLSN BINARY(10);
SELECT @StartLSN = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@startDate);
SELECT @EndLSN = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@EndDate);

SELECT 
   'All Changes' AS [Filter Option],
   CASE sys.fn_cdc_has_column_changed('dbo_Products','ProductCost',[__$update_mask])
         WHEN 1 THEN 'Yes'
         ELSE 'No' END AS [Is ProductCost Updated],
   sys.fn_cdc_map_lsn_to_time([__$start_lsn]) AS [Start LSN Date],
   CASE [__$operation]
         WHEN 1 THEN	'Delete'
         WHEN 2 THEN	'Insert'
         WHEN 3 THEN	'Update_OldValue'
         WHEN 4 THEN	'Update_NewValue'
         ELSE 'Big Mess'
   END AS [Operation],
   ProductID,ProductName,ProductCost
FROM cdc.fn_cdc_get_all_changes_dbo_Products(@StartLSN,@EndLSN,'all')
WHERE [__$operation] =4
ORDER BY [Start LSN Date];

دستور دقیقا همون کد قبلیه با این تفاوت که توسط functionی که بالاتر بهش اشاره کردیم مشخص کردیم که قیمت تغییر داشته طی این دو روز اخیر یا خیر، و همچنین برای اطمینان بیشتر توسط شرط  [__$operation] =۴ تنها تغییرات مرتبط با عملیات update رو بررسی کردیم

تغییرات روی یک فیلد خاص

غیرفعال سازی Change Data Capture:

اگر به هر دلیل قصد غیرفعال کردن cdc روی یک جدول رو داشتید میتونید از sp سیستمی sys.sp_cdc_disable_table استفاده کنید

EXEC sys.sp_cdc_disable_table 
@source_schema = 'dbo',
@source_name = 'Products',
@capture_instance='dbo_Products'

و همچنین برای غیرفعال کردن cdc از روی دیتابیس، دستور زیر

EXEC sys.sp_cdc_disable_db

و در نهایت برای پاکسازی کامل دیتابیس و حذف اون :

USE master
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE TestDB

معایب Change Data Capture

cdc خوبیهای زیادی داشت و توی خیلی از موارد کار رو برای ما آسون تر میکرد اما مثل همیشه در کنار هر مزیتی عیبی هم ممکنه وجود داشته باشه، که در نهایت تصمیم نهایی به مدیر سیستم برمیگرده که با توجه به work load سیستم تحت مدیریتش بهترین انتخاب‌ها رو انجام بده، در پایان قصد داریم برخی از معایب و مشکلات احتمالی cdc رو با هم بررسی کنیم

  • با فعال کردن cdc میزان بار IO سیستم افزایش خواهد یافت، چرا که نیاز به درج تغییرات در جداول مربوط به cdc داره، این درج ها در سه مرحله انجام میشه
    • مرحله اول ثبت تغییرات درون لاگ فایل هست (که حتی اگر cdc هم فعال نباشه سر جاشه و overhead خودش رو داره)
    • دومین I/O اضافی ثبت تغییرات توی جداول Change Table مرتبط با cdc هست
    • و در نهایت سومین I/O اضافی درج مجدد در لاگ فایل
  • با فعال کردن cdc روی دیتابیس و استفاده حریصانه از اون روی جداول مختلف میتونه باعث افزایش حجم بیش از حد سایز بانک‌اطلاعاتی ما بشه و ما رو با مشکل مواجه کنه (به طور کلی استفاده حریصانه و بدون تحلیل از هر امکانی میتونه نتیجه عکس روی سیستم داشته باشه)
  • یکی از ایراداتی که واقعا به cdc وارده اینه که، cdc تغییرات ساختاری ما رو با ddl_history داره ذخیره میکنه(دستش درد نکنه) اما اگر یک فیلد جدید به جدولی که cdc رو روی اون فعال کردیم و شروع به درج مقادیر توی اون جدول بکنیم، cdc مقادیری که برای اون فیلد درج میکنیم رو capture نمی‌کنه و لازمه که یک بار cdc اون جدول رو غیر فعال و مجدد فعالش کنیم

 

خب دوستان این بود بخش دوم از سری مقالات کنترل تغییرات در SQL Server، در بخش سوم به سراغ SQL Audit میریم و اونو بررسی می‌کنیم

شاد باشید 🙂

دانلود اسکریپت

برنامه نویس و توسعه دهنده و علاقه مند به هر چیزی که با دیتا در ارتباط باشه
SQL Server , Big Data, Hadoop, R,Statistical Programming

Categories: SQL Server

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *