آشنایی با (CDC (Change Data Capture در Sql Server 2008

[Galawij]

سلام،
چندی پیش در این تاپیک (http://barnamenevis.org/showthread.php?308703-%D8%B9%D9%85%D9%84%DB%8C%D8%A7%D8%AA-%D8%AF%D8%B1%D8%AC-%D8%B1%D9%88%DB%8C-n-%D8%AC%D8%AF%D9%88%D9%84-%D8%A8%D8%A7%D9%86%DA%A9-%D8%A7%D8%B7%D9%84%D8%A7%D8%B9%D8%A7%D8%AA%DB%8C) سوالی را مطرح کردم که با راهنمایی مهندس صادقیان (http://barnamenevis.org/member.php?4504-%D8%AD%D9%85%DB%8C%D8%AF%D8%B1%D8%B6%D8%A7%D8%B5%D 8%A7%D8%AF%D9%82%DB%8C%D8%A7%D9%86) به نتیجه رسیدم.
در این تاپیک قصد دارم توضیحاتی را در مورد این قابلیت که در SQL Server 2008 ارائه شده است، بدهم و در نهایت ابزاری را برای این کار معرفی می کنم که کار کردن با این قابلیت را بسیار راحت تر کرده و نیاز به دستورات T-SQL (اجرای روال های در نظر گرفته شده برای این منظور) را حذف می کند.
امیدوارم مورد توجه و استفاده دوستان و کاربران محترم قرار گیرد.



مقدمه:
در ورژن قبلی SQL Server (ورژن 2005) تریگرهای جدیدی با نام After Update و After Insert و After Delete اضافه شده بود، که به منظور رفع مشکل نگهداری تغییرات داده ای در بانک اطلاعاتی در نظر گرفته شده بود. راه حل بهتری که در ورژن 2008 معرفی شده، استفاده از CDC است. این ویژگی جدید روی جداولی که توسط کاربر ایجاد می شود، اعمال می شود.
پياده‌سازي این قابلیت به صورت يك مؤلفه در هسته پايگاه ‌داده رابطه‌اي انجام شده ‌است. CDC براي جمع‌آوري تغييرات ايجاد شده در جدول‌ها، فايل ثبت تراكنش‌هاي مرتبط با جدول‌ها (Transaction log) را مي‌خواند. در صورت استفاده از SQL Server 2008، با استفاده از CDC مي‌توان داده‌هاي تغييريافته را در پايگاه‌داده OLTP مرجع شناسايي كرد، بدون آنكه به پياده‌سازي راه‌حل‌هايي با هزينه زياد،‌ نظير استفاده از Stamp ،Flagها يا Triggerها و ساير راه‌ حل‌هاي مبتني بر منطق صفر‌ و يك براي شناسايي داده‌هاي تغيير‌يافته، نيازي باشد.
این قابلیت بیشتر برای اهداف امنیتی استفاده می شود، به این صورت که در بانک اطلاعاتی و جداولی که این قابلیت برایش فعال شود، اجرای هر گونه دستورات DML روی آن جداول به صورت اتوماتیک در جداول سیستمی نگهداری می شود.
نکته: براي استفاده از CDC، بايد به يكي از نگارش‌هاي Enterprise ،Developer يا Evaluation از پايگاه‌داده SQL Server 2008 دسترسي داشته‌ باشيد.


CDC چگونه کار می کند؟
وقتي كه يك جدول مرجع را فعال مي‌كنيد تا از CDC استفاده كند، به‌ طور مؤثر عملكردي به نام Capture Instance را فعال كرده‌ايد.
نکته: شما مي‌توانيد به ازاي هر جدول مرجع حداكثر دو Capture Instance داشته باشيد.
فرآيند اصلي دريافت اطلاعات از طريق نمونه‌هايي (Agentهايي) از jobهاي SQL Server انجام مي‌شود كه اين وظايف ساير روتين‌ها را نيز براي پياده‌سازي عملكرد درخواست‌ شده‌ فراخواني مي‌كنند. با شناسايي هر گونه تغيير در داده، به ازاي هر يك از جدول‌هايي كه امكان ذكر شده براي آن‌ها فعال شده ‌باشد، اين تغييرات در يك جدول مجزا و متناظر با آن جدول به نام Change Table ذخيره مي‌شوند. هنگامي ‌كه داده‌هاي تغييريافته در اختيار يك پردازش مصرف‌كننده اين داده‌ها قرار گيرد معمولاً يك فرآيند از نوع (ETL) اتفاق افتاده است، اين پردازش مي‌تواند از طريق توابع توليد شده توسط خود سيستم به اين داده‌ها دسترسي داشته ‌باشد كه دسترسي به داده‌ها يا از طريق تعيين شماره توالي ذخيره‌ شده براي داده در جدول (log (LSN يا از طريق تعيين فاصله زماني مرتبط با داده‌ها، انجام مي‌شود. توابع توليد شده‌ و مرتبط با CDC اين امكان را فراهم مي‌آورد كه به راحتي و از طريق ارسال درخواست‌هايي در قالب پرس‌وجو، بتوانيم به داده‌ها دسترسي داشته‌ باشيم.
جدول‌هاي ثبت تغييرات چه زماني تخليه مي‌شوند؟
بايد توجه داشته باشيم كه اگر جدول‌هاي ثبت تغييرات هرگز خالي نشوند، مي‌توانند به شدت رشد كرده و حجم زيادي را اشغال كنند. قابليت CDC به طور پيش‌فرض داده‌ها را بعد از سه روز پاك مي‌كند. همه جدول‌هاي ثبت تغييرات در حالت پيش‌فرض، از اين راهبرد تخليه اطلاعات پيروي مي‌كنند. برای تغییر دادن این راهبرد می توانید از SP هایی که در نظر گرفته شده است، استفاده کنید.
نکته: اگر شما از پایگاه داده با قابلیت CDC، یک Backup تهیه کنید و آن را بازگردانید، پایگاه داده بازگردانده شده به صورت پیش فرض از قابلیت CDC پشتیبانی نمی کند.
CDC به طور پيش فرض امكان شناسايي و انتشار تغييرات نوع DDL را روي پايگاه ‌داده مرجع فراهم مي‌كند. البته CDC به طور پيش‌فرض تغييرات نوع DDL ايجاد شده روي پايگاه‌ داده مرجع را براي جدول‌هاي ثبت تغييرات، ارسال نمي‌كند. تنها استثنا براي اين قانون زماني است كه شما نوع داده يكي از ستون‌هاي جدول مرجع را تغيير دهيد كه در اين حالت در جدول ثبت تغييرات نيز به طور خودكار نوع ستون متناظر با ستون فوق نيز تغيير مي‌كند. مديريت تغييرات در ستون‌هاي حذف‌ شده نيز به اين صورت است كه در جدول ثبت تغييرات، بعد از حذف ستون مرجع، در ستون متناظر با آن مقدار Null درج مي‌شود و به ازاي ستون‌هاي اضافه شده به جدول مرجع، اين ستون‌ها در جدول ثبت تغييرات، ناديده‌گرفته مي‌شوند. براي فعال كردن صحيح فرآيند انتشار تغييرات از نوع DDL، بايد Capture Instance دوم را نيز بعد از اعمال تغييرات DDL در جدول مرجع، فعال كرد. اين فرآيند با استفاده از يك شماي جدول جديد شروع به استخراج داده‌هاي تغيير يافته مي‌كند. بعد از اين مرحله بايد همه برنامه‌هاي مصرف كننده از اين اطلاعات را به ‌روزرساني كنيد تا از Capture Instance جديد استفاده كنند.

برکت باشد، برکت باشیم...

[Galawij]

پیکر بندی CDC:

فعال کردن CDC روی پایگاه داده:
نکته:برای استفاده از این قابلیت باید عامل (Sql Server (SQL Server Agent در حالت اجرا باشد.
در اصل CDC ویژگی در سطح جداول هست، در مرحله اول باید این قابلیت را برای بانک اطلاعاتی مد نظرمان فعال کنیم و سپس برای جداولی که می خواهیم سابقه تغییرات را روی آنها در آن پایگاه داده نگهداری کنیم، این امکان را فعال می کنیم. برای مشاهده لیست پایگاه داده های که این قابلیت برایشان فعال شده است Query زیر را اجرا می کنیم:
Use master
GO
Select name,database_id,is_cdc_enabled
From sys.databases
GOنتیجه اجرای دستور بالا، لیست تمام بانک های اطلاعاتی و یک ستون است که نشان می دهد آیا قابلیت CDC برای آن بانک فعال شده است یا خیر؟
80025
برای اینکه بتوان قابلیت CDC را برای یک بانک خاص فعال کرد، روی بانک اطلاعاتی مورد نظر یک New Query ایجاد می کنیم و کد زیر را اجرا می کنیم:
Use YourDatabaseName
GO
EXEC sys.sp_cdc_enable_db
GOبا اجرای دستور بالا روی بانک مورد نظر شما یک Schema با نام cdc و یک سری جداول سیستمی اضافه می شود. این تغییرات را می توانید در شکل زیر مشاهده کنید:
80026

بررسی جداول ایجاد شده:



cdc.captured_columns: این جدول لیستی از ستونها و مشخصات مربوط به آنها را که برای CDC در نظر گرفته ایم، نگهداری می کند.

cdc.change_tables: این جدول لیستی از جداولی که برای CDC در نظر گرفته ایم، را نشان می دهد.

cdc.ddl_history: این جدول سابقه ای از دستورات DDL انجام شده روی جداولی را که قابلیت CDC برای آنها فعال شده، نگهداری می کند.

cdc.index_columns: این جدول شامل اندیکس های تعریف شده روی ستون های جداول است.

cdc.lsn_time_mapping: این جدول شماره های LSN و زمان مربوط به تراکنش های انجام شده روی جداول CDC را نگاشت می کند.

[Galawij]

فعال کردن CDC روی جداول پایگاه داده:
برای هر پایگاه داده ای که قابلیت CDC برای آن فعال شده است، می توان در سطح جدول نیز این قابلیت را فعال کرد. اجرای Query زیر نشان می دهد که کدام جداول، از پایگاه داده مورد نظر قابلیت CDC برای آنها فعال شده است؟
Use YourDatabaseName
GO
Select name,is_tracked_by_cdc
From sys.tables
GO
نتیجه اجرای Query بالا روی بانک اطلاعاتی، نمایش لیستی ازجداول بانک است که صفر به معنای غیر فعال بودن و یک به معنای فعال بودن CDC برای آن جدول است.
برای فعال کردن CDC روی جداول از طریق فراخوانی یک روال داخلی اقدام می کنیم. فراخوانی و اجرای این روال دو Job مختص به آن بانک اطلاعاتی را ایجاد می کند. برای اجرای این Job ها باید عامل Sql Server در حال اجرا باشد.
اسکریپت زیر CDC را روی جدول TbTest فعال می کند:
Use YourDatabase
GO
EXEC sys.sp_cdc_enable_table
@source_schema=N'dbo',
@source_name=N'TbTest',
@role_name=NULL
GO
که نتیجه اجرای آن به صورت زیر است:
80371
Job های ایجاد شده به صورت زیر ایجاد می شوند، که به صورت اتوماتیک فعال می باشند:
80372
این Job ها برای انجام عملیات CDC، روال های داخلی مرتبط را با CDC را فراخوانی می کنند. روال های همچون:
sys.sp_MScdc_capture_job، sys.sp_MScdc_cleanup_job و ...
نکته: با فراخوانی روال بالا به صورت پیش فرض تمام ستون های جدول تعیین شده را مورد رسیدگی قرار می دهد، برای محدود کردن ستون های خاصی از یک جدول از پارامتر زیر استفاده می کنیم:@captured_column_list = 'Field1,Field2,...'

[Galawij]

ارائه چند مثال از نحوه عملکرد CDC با اجرای دستورات DML بر روی پایگاه داده:


دستور Insert:

برای اینکه تأثیر این دستور را بر روی جداول CDC ببینیم، ابتدا Query زیر را برای عملیات درج بر روی جدولی که CDC بر روی آن فعال شده است، اجرا می کنیم:
INSERT INTO [TestForCDC].[dbo].[TbTest]
([Name])
VALUES
('Galawij')
GOنتیجه اجرای Query بالا اضافه کردن سطری با محتویات Galawij به جدول TestForCDC است. برای مشاهده محتویات جداول CDC از جدولی که معادل با نام جدولمان توسط CDC ایجاد شده است (در اینجا با نام dbo_TbTest_CT)، اطلاعات را نمایش می دهیم:
Select * From [TestForCDC].[cdc].[dbo_TbTest_CT]
نتیجه اجرای Select بالا به صورت زیر است:
81306
فیلد operation$__ نشان دهنده نوع عملیات انجام شده بر روی جدول است، عدد 2 به معنای عملیات Insert است.


عدد 1 معادل Delete
عدد 2 معادل Insert
عدد 3 معادل Before Update
عدد 4 معادل After Update



دستور Delete:

Delete From [TestForCDC].[dbo].[TbTest] Where Name='Ali'
نتیجه اجرا:
81307

[Galawij]

غیر فعال کردن CDC:

برای غیر فعال کردن این قابلیت باید ابتدا آن را از روی جدول یا جداولی که این قابلیت برای آنها فعال است، برداشته شود و در ادامه از روی سطح بانک اطلاعاتی.
غیر فعال کردن CDC روی جداول:
برای این کار ما نیاز به سه مقدار برای ارسال به پارامترها داریم: Source Schema,Source Table Name,Capture Instance. نام Schema، نام جدول و نام نمونه.
از طریق اجرای SP زیر روی بانک مورد نظرتان به راحتی می توان نام نمونه CDC را به دست آورد:
Exec sys.sp_cdc_help_change_data_capture
اجرای روال بالا اطلاعات مفیدی را در رابطه با CDC به ما خواهد داد.
81660
برای غیر فعال کردن CDC روی جداول، روال زیر را با پارامترهای نام برده فراخوانی می کنیم:
Exec sys.sp_cdc_disable_table
@Source_schema=N'dbo',
@Source_name=N'TbTest',
@capture_instance=N'dbo_TbTest';
GO
اجرای روال بالا، تمام توابع و جداول مربوط به CDC را پاک می کند(در این مثال، جدول dbo_Test_CT حذف خواهد شد).
غیر فعال کردن CDC روی بانک اطلاعاتی:
اجرای روال زیر CDC را روی بانک پاک می کند:
Exec sys.sp_cdc_disable_db
اجرای روال بالا تمام جداول سیستمی و ... مربوط به عملیات CDC را پاک می کند، در صورتی که نیاز به اطلاعات جدول خاصی دارید، قبل از اجرای این روال از اطلاعات بانک یک پشتیبان بگیرید.

[Galawij]

فایل PDF مطالب ارائه شده تا اینجا
فایل Script تمام مراحل ایجاد و حذف CDC
نرم افزار TomCdcInstaller برای ایجاد و اجرای CDC به صورت ویزارد

[mojtaheri]

ممنون از آموزشتون.من میخواستم این قابلیت رو رو ی فقط روی دستور delete , update بزارم چطوری میتونم؟اصلا میشه؟

[mahdy.asia]

متشکرم کامل و خوب بود اگر خواسته باشیم فیلدهای IDSP,HostName,username,... را داشته باشیم امکان پذیر می باشد؟

[fakhravari]

با سلام
حالا چطور این دیتابیس را با دیتابیس دوم سینک کنیم؟:متفکر: