Change Tracking در SQLSERVER موضوع قابل توجهی است. بسیاری از برنامه‌نویسان و مدیران بانک‌های اطلاعاتی همواره به دنبال راهی برای رصد کردن اطلاعات تغییر یافته در بانک اطلاعاتی (حذف، اضافه یا ویرایش) سیستم‌ها بوده‌اند. به دلیل این که بانک اطلاعاتی مد نظر ما SQLSERVER است. از این رو راهکارهای پیشنهادی در این مقاله مبتنی بر تکنولوژی‌های موجود در این RDBMS است که در این جا قصد داریم به صورت خلاصه آن‌ها را بررسی کنیم.
- استفاده از افزایش دو ستون Insert Date, Update Date
- استفاده از ستون Row Version
- استفاده از Temporal Tables
- استفاده از Triggers
- استفاده از تکنولوژی Change Tracking
- استفاده از تکنولوژی Change Data Capture
- استفاده از Service Broker (Query Notification)
استفاده از افزایش دو ستون (Insert Date, Update Date) در جدول:
در این روش یک ستون به عنوان تاریخ ایجاد رکورد در جدول اضافه و به محض ثبت رکورد در جدول این ستون پر می‌شود. با این ستون می‌توان تشخیص داد کدام رکوردها به جدول اضافه شده‌اند. همچنین با اضافه کردن ستون Update Date می‌توان به محض تغییر رکورد، تاریخ تغییر را در آن ثبت کرد. بدین ترتیب تاریخ ایجاد و تاریخ آخرین تغییر هر رکورد در این ستون‌ها به صورت مجزا مشخص می‌شود. از این رو می‌توان با هر بار مراجعه به جدول، رکوردهایی که جدیدا ایجاد شده و یا تغییر کرده است را مشخص کرد. تعدادی از معایب افزودن این دو ستون به جدول این است که حجم جدول اضافه شده و امکان تشخیص اطلاعات حذف شده از آن وجود نخواهد داشت. همچنین این روش نیاز به کدنویسی دارد و در صورت تغییر اطلاعات در جداول به صورت دستی ممکن است ستون UpdateDate بروزسانی نشود.
استفاده از ستون Row Version
اما یک روش ساده‌تر برای این منظور وجود دارد. این روش افزودن یک ستون با نوع Timestamp است که این ستون یک مقدار Binary دارد. با افزودن رکورد جدید و یا تغییر یک رکورد، مقدار آن به صورت افزایشی اضافه می‌شود. (MaxValue+1) در داخل این ستون قرار می‌گیرد (مربوط به رکورد تغییر کرده). بدین ترتیب با هر بار سرکشی به جدول، مقادیر بزرگ‌تر از آخرین مراجعه را می‌توان به عنوان تغییرات جدول دریافت کرد.
به طور مثال اگر در آخرین مراجعه به این جدول مقدار Max این ستون ۱۰ باشد در مراجعه بعدی مقادیر بزرگ‌تر از ۱۰ به عنوان تغییرات دریافت می‌شود. ضعف این روش این است که در صورت حذف اطلاعات از جدول امکان رصد کردن آن و اینکه کدام رکورد حذف شده است، وجود ندارد. یکی از مزایای این روش این است که در صورت تغییر اطلاعات رکورد به صورت دستی، بازهم مقدار Row Version تغییر می‌کند.
استفاده از TemporalTable :
این قابلیت در نسخه ۲۰۱۶ ایجاد شده است. با استفاده از TemporalTables می‌توان تاریخچه تغییرات را در جدول دیگری که ساختار، شبیه به ساختار جدول اصلی دارد (History Table)، دریافت کرد. به عبارت دیگر آخرین اطلاعات در جدول اصلی و تاریخچه تغییرات در History Tale قرار می‌گیرد. از بزرگ‌ترین محاسن این روش می‌توان به استفاده از فانکشن‌های آماده و نگهداری تاریخچه تمامی رکوردها (در History Table) اشاره کرد. اما نقطه ضعف آن حذف نکردن اطلاعات تاریخچه پس از یک دوره مشخص است که منجر به افزایش بانک اطلاعاتی می‌شود. همچنین در این روش امکان مشخص‌کردن ستون‌های جدول برای لاگ گرفتن وجود ندارد و از مقادیر تمامی ستون‌ها لاگ گرفته می‌شود.
استفاده از Triggers :
در این روش می‌توان با پیاده‌سازی تریگرها به محض تغییر اطلاعات، یک نسخه از آن را در جای دیگری ( به طور مثال جداول دیگر) ذخیره و در هر بار مراجعه این تغییرات را دریافت کرد.
تریگرها علاوه بر سربار اضافه که ممکن است منجر به کند کردن عملیات شود، امکان Debug کردن را هم برای برنامه‌نویسان سخت‌تر می‌کنند.همچنین DML تریگرها در عملیات Bulk Insert نیز اجرا نخواهند شد.
استفاده از تکنولوژی Change Tracking
تکنولوژی CT یک روش سبک و ساده است که به ما اعلام می‌کند کدام رکورد از اطلاعات جداول تغییر کرده است. روش کار آن به این شکل است که تنها مقدار فیلد Primary-Key مربوط به رکورد تغییر کرده و در جدول دیگری (ChangeTable) که مخفی هم هست، ذخیره می‌شود. نباید فراموش کرد که تنها وضعیت آخرین عملیات انجام شده (مثلا Insert, Update, delete) بر روی رکوردها در این جدول نگهداری می‌شود. همچنین در زمان راه‌اندازی این قابلیت در بانک اطلاعاتی این امکان وجود دارد که مدت زمان نگهداری تاریخچه تغییرات را مشخص کرد. (Retention) که پس از آن بازه زمانی به صورت اتوماتیک اطلاعات حذف می‌شود. تنها ضعف این روش نگهداری نکردن تاریخچه تمامی اطلاعات است.
نحوه فعالسازی
یا اجرای دستورات زیر :
————————————————————- فعالسازی ——————————————–
ALTER DATABASE [Sample]
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
GO
ALTER TABLE Test1
ENABLE CHANGE_TRACKING
GO
———————————————دریافت اطلاعات تغییر کرده در جدول————————————-
SELECT CT.EmployeeID, Test1.Name, Test1.Position, CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM Test1
RIGHT OUTER JOIN CHANGETABLE(CHANGES dbo.Test1, 0) CT ON Test1.EmployeeID = CT.EmployeeID
GO
استفاده از تکنولوژی Change Data Capture
در برخی مواقع لازم است علاوه بر اطلاع از تغییر رکورد، مقادیر آن را هم داشته باشیم. CDC یک قابلیت رصدگیری دقیق از وضعیت رکوردهای قبل و بعد از تغییر را در خود نگه می‌دارد. حتی پس از حذف اطلاعات در جدول اصلی اطلاعات کاملی از رکورد حذف شده در جداول لاگ (تاریخچه لاگ‌ها) وجود دارد. به عبارت دیگر تمامی عملیات ایجاد، حذف و تغییر اطلاعات در CDC لاگ گرفته می‌شود.
همچنین در این جا امکان تعیین بازه زمانی برای حفظ تاریخچه اطلاعات وجود دارد و حتی می‌توان تاریخچه تغییرات را به صورت دستی حذف کرد.
در CDC می‌توان علاوه بر جداول، فیلد مورد نظر را هم برای لاگ‌گیری مشخص کرد.
نحوه عملکرد CDC به این شکل است که یک Agent به صورت دوره‌ای (که زمان آن قابل تنظیم است) اطلاعات مربوطه را از لاگ فایل خوانده و در جداول تاریخچه لاگ ذخیره می کند.
————————————————————- فعالسازی ——————————————–
EXEC sys.sp_cdc_enable_db
GO
exec sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'customer' ,
@role_name = NULL,
@supports_net_changes = 0
GO
SELECT name, type, type_desc, is_tracked_by_cdc from sys.tables
SELECT * FROM CDC.DBO_CUSTOMER_CTبا اجرای کوئری بالا می‌توان از جدول لاگ، تغییرات اطلاعات را واکشی کرد. در این جدول یک ستون با نام __$operation وجود دارد که ۴ مقدار زیر را دارد:
۱=Delete
۲=Insert
۳=Before Update
۴=After Update
استفاده از Service Broker در Query Notification:
این روش که یک Method polling-based است به برنامه‌نویس اجازه می‌دهد در زمانی که تغییری در جدولی اتفاق افتاد از دیتابیس یک کوئری بگیرد. این روش با استفاده از کلاس‌های زبان برنامه‌نویسی و serviceBroker (برای تحویل پیام ها به Application) در SQLSERVER انجام می‌شود. یکی از مزایای این روش این است که میزان رفت و برگشت‌ها به سمت دیتابیس کم می‌شود. همچنین شایان ذکر است این روش به تنهایی بر روی Performance تاثیرگذار نخواهد بود ولی در صورتی که تعداد query notification ها افزایش پیدا کند، Performance در عملیات CRUD باید بررسی شود.
جمع بندی :
در این مقاله سعی کردیم درباره Change Tracking در SQLSERVER توضیحاتی ارائه کنیم.به طور کلی درباره رصد کردن تغییرات در اطلاعات روش‌های زیادی وجود دارد که هر کدام از آن‌ها مزایا و معایب خاص خود را دارند. از این رو نمی‌توان از یک روش به عنوان روش بهتر و برتر یاد کرد. بنابراین باید با بررسی دقیق شرایط و نیاز مورد نظرمان، هر کدام از قابلیت‌های ذکر شده را ارزیابی و بعد از آن برای گرفتن تصمیم مناسب اقدام کرد.
نویسنده: مهدی ربیعی
