خانه SQL Server Change Data Capture یا CDC چیست؟ SQL Server افزایش سرعت SQL Server نوشته شده توسط: علیرضا ارومند تاریخ انتشار: ۰۲ خرداد ۱۳۹۴ آخرین بروزرسانی: ۲۶ آبان ۱۴۰۲ زمان مطالعه: 8 دقیقه ۳.۳ (۷) Change Data Capture یا CDC چیست؟ یکی از قابلیت های ویژه SQL Server Enterprise Edition (که البته درنسخههای Developer و editions هم به خوبی کار میکند ) است که روی جدولی در دیتابیس ما فعال میشود و تغییرات ناشی از دستورات DML (Insert,Update,Delete) آن را نگهداری میکند و ما میتوانیم از این اطلاعات استفاده کنیم. وقتی که ما CDC را روی جدولی فعال میکنیم، SQL Server جدولی مشابه آن به همرای یکسری Metadata ایجاد میکند وتغییرات را در آن ذخیره مینماید. در کنار آن تعدادی Table-valued function در اختیار ما قرار میگیرند که به ما امکان استفاده از داده های ذخیره شده را میدهند. CDC چگونه کار میکند؟ هنگامی که CDC روی یک جدول فعال میشود، SQL Server از مکانیزم نامتقارنی (Async) استفاده میکند که به کمک آن تغییرات رخ داده در جداول را از فایل log میخواند و درجداولی که به منظور نگهداری تغییرات ایجاد کرده ذخیره میکند. در فایلهای log هر رکوردی کهذخیره میشود یک شناسه یکتا با نام Log Sequence Number دارد که به اختصار LSN نامیده میشود.بسیاری از Metadataهایی که برای ما نگهداری میشود به خاطر قرابت نزدیکی که اینتکنولوژی با Log دارد از همین اطلاعات Log استخراج میشوند. برای فعال سازی CDC روی یک جدول ابتدا به کمک دستور sys.sp_cdc_enable_db باید آنرا در پایگاهداده مورد نظر فعال کنیم. با اجرای این دستور سیستم CDC بر روی پایگاهداده ما فعال میشود و زیرساختهای مورد نیاز آن مانند meta-data table ها، CDC Schema ، CDC Database User و … روی پایگاه داده ما ایجاد میشوند. در صورتی که نیاز داشته باشید میتوانید در sys.databases و به کمک ستون is_cdc_enabled بررسی کنید که آیا CDC برای دیتابیسشما فعال است یا خیر. حالا شما میتوانید ازدستور sys.sp_cdc_enable_table برای فعال کردن CDC روی جدول خود استفاده کنید. با بررسی ستون is_tracked_by_cdc در sys.tablesمیتوانید از فعال بودن یا نبودن CDC روی جدول خود مطلع شوید. با فعال شدن این امکان، با هر تغییری در داده های جدول موردنظر شما، تغییرات آن در جدول CDC متناظر از Log استخراج شده و ذخیره می گردد. به صورت پیش فرض تمامی ستونهای جدول نگهداری میشوند اما در صورت نیاز مثلا به دلایل امنیتی یا پرفورمنسی شما میتوانید تنها اطلاعات بعضی از ستونهای جدول را نگهداری کنید. این کار را به کمک پارامتر @captured_column_list هنگام ایجاد CDC روی جدول انجام میدهیم. همچنین به صورت پیش فرض این اطلاعات روی FileGroup اصلی ما نگهداری میشوند که باز هم در صورت نیاز میتوانید با استفاده از پارامتر @filegroup_name یک FileGroup دیگر را جهت نگهداری اطلاعات معرفی کنید. فعال بودن SQL Server Agent برای فعال کردن این امکان اجباری نیست. اما فعال بودن آن برای کارکرد صحیح CDC لازم است. به همین منظور در صورتی که هنگام فعال کردن این امکان روی جدول اگر این SQL Server Agent استارت نباشد، با اینکه کار فعال سازی درست انجام میشود اما به شما پیامی مبنی بر عدم کارکرد صحیح سیستم داده میشود. آیا جدول CDC بسیار بزرگ میشود؟ با توجه به اینکه هر تغییراتی که در جداول ما اتفاق می افتددر جدول CDC نگهداری میشود، احتمالا باید این سوال برای شما پیش آمده باشد کهآیا این جدول بسیار بزرگ نمیشود؟! پاسخ این است خیر. یک روال سیستمی وجود دارد که به صورت اتوماتیک هر ۳ روزیکبار داده ها قدیمی را پاک میکند. البته میتوانید این اندازه را به هر اندازه ایکه نیاز دارید تغییر دهید و یا میتوانید به کمک روال ذخیره شده sys.sp_cdc_cleanup_change_table این کار را بهصورت دستی انجام دهید. در صورت تغییر در جدولی که تغییرات آن را نگهداری میکنیم چه اتفاقی میافتد؟ با تغییر در ساختار جدول روال CDC به کار خودادامه میدهد، با این تفاوت که در صورتی که ستونی اضافه شود، دیگر تغییرات آننگهداری نمی شود، و اگر ستونی هم حذف شود مقدار NULL برای آننگهداری می شود. یعنی CDC توجهی به تغییرات اتفاق افتاده ندارد و کار خود را به همان شکلسابق ادامه می دهد. در صورت نیاز شما میتوانید یک روال دیگر برای نگهداری تغییراتستون های جدید ایجاد کنید. اما به خاطر داشته باشید که تنها ۲ نمونه از نگهداریسابقه را میتوانید برای یک جدولفعال کنیم. خلاصه در این مطلب، شرح کلی از CDC و نحوه فعالسازی آن را بر روی یک جدول داشتیم. در قسمت بعد با جزئیات این قابلیت جدید و نحوهاستفاده از آن بیشتر آشنا می شویم. چه رتبه ای میدهید؟ میانگین ۳.۳ / ۵. از مجموع ۷ اولین نفر باش دانلود مقاله Change Data Capture یا CDC چیست؟ فرمت PDF 2 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده مقالات 24 مقاله توسط این نویسنده محصولات 43 دوره توسط این نویسنده علیرضا ارومند علیرضا ارومند به عنوان Product Manager شرکت داتین (وابسته به فناپ) در حوزه پروژههای بانکی فعال است.او همچنین مدرس و Technical Manager پروژههای نیک آموز می باشد از دیگر تخصص های او میتوان به: تولید فریمورک برنامه نویسی فوق العاده حرفهای با مدیریت بیش از 1 میلیون تراکنش در ثانیه، همکاری با تیم توسعه شرکت ارتباط فردا (بانک آینده)، مشاور فنی شرکت توسعه رفاه پردیس (بانک رفاه)، مدیر فنی خبرگزاری نسیم، سخنران تنها همایش مورد تایید مایکروسافت در خاورمیانه در حوزه ASP.NET Core، مدیر فنی خبرگزاری بین المللی پیامکوتاه نسیم (برنده جشنواره وب ایران)، مدرس دوره های Dot Net ، ASP.NET در نیک آموز، همکاری با تیم توسعه شرکت ارتباط فردا معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ امیر حیدری ۱۹ / ۰۱ / ۰۲ - ۱۰:۳۴ با سلام در دستور exec sys.sp_cdc_enable_table پارامتر های زیر اجباری است. @source_schema=’dbo’, @source_name = ‘cdc_test’, @role_name=null پاسخ به دیدگاه m ۰۹ / ۰۶ / ۹۴ - ۰۷:۲۷ سلام خوب بود فقط سعی کنید برای افرادی که از Express هم استفاده می کنند راه کار های جدید ارائه بدید من در حال حاضر از تریگر ها استفاده می کنم اگه لطف کنید راهکارهای جدیدتر در این زمینه ارائه بدید ممنون میشم تمام برنامه ها که نیاز به دیتابیس های بزرگ ندارند میشه از express استفاده کرد در ضمن دوره هایی مثل linked server را هم اگه لطف کنید ادامه بدید خیلی خوب میشه پاسخ به دیدگاه مسعود طاهری ۰۹ / ۰۶ / ۹۴ - ۱۰:۰۴ یکی از راه های ساده جمع آوری لاگ تغییرات استفاده از تریگر است . یا اینکه در خود Application از این کار را انجام دهید. (لایه DAL) البته لازم می دانم اشاره کنم که این موضوع برای خود دردسرهایی نیز به همراه دارد پاسخ به دیدگاه جواد ۲۱ / ۰۴ / ۹۴ - ۰۳:۲۳ درست شد خیلی خیلی ممنون سایت بسیار خوبی دارید خسته نباشید پاسخ به دیدگاه جواد ۲۱ / ۰۴ / ۹۴ - ۰۲:۱۴ خیلی ممنون بابت راهنماییتوناین کارو کردم ولی نه اطلاعات برگشت نه دیگه تغییرات را ثبت میکنه موقع Backup گرفتن هم باید از Keep_CDC استفاده کنم؟ پاسخ به دیدگاه مسعود طاهری ۲۱ / ۰۴ / ۹۴ - ۰۳:۰۱ ۱- آیا CDC الان روی دیتابیس اصلی فعال است ؟ ۲- آیا به ازای تغییرات شما Capture گیری انجام می شود؟ توجه داشته باشید که هنگام Restore باید سرویس Agent هم فعال باشه تا CDC بتونه روی اون تنظیم بشه. در ضمنا پس از Restore برای پروسه Capture گیری شما یک Job اضافه خواهد شد. چک کنید ببینید بعد از Restore اجرا شده و یا خیر ؟ در صورتیکه اجرا نشده باشد دستی آن را اجرا کنید موفق باشید پاسخ به دیدگاه جواد ۲۱ / ۰۴ / ۹۴ - ۱۲:۱۹ سلام خسته نباشید خیلی ممنون بابت مطلبی که بهش پرداخته ایدمن از CDC استفاده کردم و Backup از دیتابیس گرفتم وقتی که دیتابیس رو Restore کردم تمام اطلاعات جداول CDC حذف شده اند و دیگر تغییرات را ذخیره نمی کند با تشکر پاسخ به دیدگاه مسعود طاهری ۲۱ / ۰۴ / ۹۴ - ۱۲:۰۸ سلام برای اینکار از Option ی به نام Keep_CDC باید استفاده کنید به مثال زیر توجه کنید RESTORE DATABASE YourDB FROM DISK = N’C:\Dump\YourBackupFile.bak’ WITH FILE = 1, KEEP_CDC موفق باشید پاسخ به دیدگاه مجتبی شهریور ۲۱ / ۰۴ / ۹۴ - ۱۰:۱۹ سلامضمن تشکر از مقاله خوبتون سئوالی برام پیش اومد و اون اینکه می خواستم بدونم اتفاقاتی که برای نگهداری در جدول رخ می دهد (بق بند آخر مقاله) منظور جول اصلی بانک اطاعاتی می باشد؟؟؟؟ پاسخ به دیدگاه فرشید علی اکبری ۲۲ / ۰۳ / ۹۴ - ۰۱:۴۱ سلام مهندس طاهری عزیز میخواستم بدونم در بانکهای اطلاعاتی بزرگ که بالای ۱۵۰ جدول اطلاعاتی دارند (منظورم اینه که کل مجموعه نرم افزارها شامل سیستم های مختلف از یک دیتابیس تغذیه میشوند ولی با استفاده از گروه بندی های مختلف و شماها و سطح دسترسی های مختلف جداسازی شده اند) چنانچه در چنین بانک اطلاعاتی CDC را (برای اطلاعات پایه گرفته تا کلیه فرمهای عملیاتی) راه اندازی کنیم آیا مشکلی برای Performance سرور پیش نمی آید؟ البته در شرایطی که job های مربوطه را روی ۶۰ روز برای حذف سوابق تنظیم کنیم و تعداد کاربران شبکه حداقل ۱۰ نفر باشند آیا برای سرور شبکه هزینه بر است و یا اینکار فقط برای فاکتورها- رسیده- اسناد- کدینگ وامثال این موارد پیاده سازی شود؟ آیا بالاخره در نسخه ۲۰۱۶ ما امکان استفاده از cdc برای جداول In-Memory OLTP را خواهیم داشت؟ از اینکه سئوالم طولانی شد عذرخواهی میکنم. با سپاس فراوان پاسخ به دیدگاه فرشید علی اکبری ۲۲ / ۰۳ / ۹۴ - ۰۱:۴۱ سلام مهندس طاهری عزیز میخواستم بدونم در بانکهای اطلاعاتی بزرگ که بالای ۱۵۰ جدول اطلاعاتی دارند (منظورم اینه که کل مجموعه نرم افزارها شامل سیستم های مختلف از یک دیتابیس تغذیه میشوند ولی با استفاده از گروه بندی های مختلف و شماها و سطح دسترسی های مختلف جداسازی شده اند) چنانچه در چنین بانک اطلاعاتی CDC را (برای اطلاعات پایه گرفته تا کلیه فرمهای عملیاتی) راه اندازی کنیم آیا مشکلی برای Performance سرور پیش نمی آید؟ البته در شرایطی که job های مربوطه را روی ۶۰ روز برای حذف سوابق تنظیم کنیم و تعداد کاربران شبکه حداقل ۱۰ نفر باشند آیا برای سرور شبکه هزینه بر است و یا اینکار فقط برای فاکتورها- رسیده- اسناد- کدینگ وامثال این موارد پیاده سازی شود؟ آیا بالاخره در نسخه ۲۰۱۶ ما امکان استفاده از cdc برای جداول In-Memory OLTP را خواهیم داشت؟ از اینکه سئوالم طولانی شد عذرخواهی میکنم. با سپاس فراوان پاسخ به دیدگاه مرجان آب بر ۱۹ / ۰۳ / ۹۴ - ۰۸:۵۹ با سلام و تشکر فراوانمن چند ماه پیش روی این قضیه کار کردم تا بتوانم از cdc برای ذخیره تغییرات انجام شده توسط هر کاربر در برنامه ام استفاده کنم اما تا جایی که یادم هست مشکل عمده این بود مشخص نمیشد کدام کاربر این تغییرات را اعمال کرده است .با وجود ساختن یوزر جداگانه در اس کیو ال برای هر کاربر و لاگین کردن با این یوزر به اس کیو ال اما sql server aqent باز کاربر فعلی را همان یوزر ادمین ذخیره میکرد. و متاسفانه به نتیجه ای نرسیدم ممنون میشم اگه کسی راهنمایی نماید. پاسخ به دیدگاه مسعود طاهری ۲۱ / ۰۳ / ۹۴ - ۰۵:۱۲ سلام این موضوع در تاپیک بالا بررسی شده و چند راه حل ارائه شده است در ضمن یادتان باشد که کاربران موجود در App عموما کاربران Businessی می باشند و نه کاربران دیتابیس بنابراین برای اینکه بفهمید چه کاربری اینکار را انجام داده است مجبور هستید از روش های اشاره شده در تاپیک بالا استفاده کنید (پاسخ سوال مربوط به آقای محمد حسین عبدالهی) بررسی شود پاسخ به دیدگاه محمدحسین عبدالهی ۱۲ / ۰۳ / ۹۴ - ۰۳:۱۹ تشکر می کنمسوال : برای اینکه مشخص بشه چه کاربری تغییرات رو در جدول داده به چه صورت باید عمل کنیم؟ آیا باید به ازای تک تک کاربران یوزری در اس کیو ال سرور به صورت کامل ایجاد کنیم ؟ یا اینکه می تونیم با یک یوزر و پسورد که در اس کیو ال ایجاد شده برای هر کاربری به صورت مجزا مشخص کنیم چه فردی تغییرات CRUD را انجام داده است ؟ پاسخ به دیدگاه مسعود طاهری ۱۵ / ۰۳ / ۹۴ - ۰۹:۱۶ آیا باید به ازای تک تک کاربران یوزری در اس کیو ال سرور به صورت کامل ایجاد کنیم ؟ یکی از راه ها همین روشی است که شما اشاره کردید به این نوع فیلدها اصطلاحا Audit Field می گوییم. که عموما به صورت D-Normal در جداول اعمال می گردد. مانند (کاربری که رکورد را درج کرده * آخرین کاربری که رکورد را ویرایش کرده * تاریخ درج رکورد* آخرین تاریخ ویرایش رکورد * آی پی کاربر درج کننده رکورد * آی پی کاربر درج کننده رکورد و…) یا اینکه می تونیم با یک یوزر و پسورد که در اس کیو ال ایجاد شده برای هر کاربری به صورت مجزا مشخص کنیم چه فردی تغییرات CRUD را انجام داده است ؟ این روش را هم می توانید استفاده کنید در این حالت لازم از Database Audit Specification استفاده کنید پاسخ به دیدگاه 1 2