Poster

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

شاید برای شما هم اتفاق افتاده باشه که مدیرانتون ازتون میخوان که میخوام بدونم فلان تنظیم رو چه کسی تغییر داده، یا اینکه میخوایم یک لاگ از تغییرات داده‌هامون توی فلان جدول داشته باشم که اگر نیاز شد در آینده بتونیم مقدار مربوط به فیلد یا رکورد خاصی رو برگردونیم. برای انجام این کارها راه های مختلفی وجود داره، توی این سری مقالات قصد داریم به طورکلی به مقوله Data Auditing بپردازیم، با ما همراه باشید.

 

 

یکی از مشکلاتی که در اغلب بانک های اطلاعاتی برنامه نویسان و یا مدیران بانک اطلاعاتی با آن مواجه هستند مساله پیگیری تغییرات دیتا در جداول مختلف می‌باشد، به عنوان مثال می‌خواهیم بدانیم که یک جدول مشخص چه تغییراتی را داشته(از نظر ساختاری و داده های موجود در جدول).

برای پیاده سازی چنین ساختاری در SQL Server میتوانیم از ابزارهای داخلی SQL Server نظیر Triggerها استفاده نماییم که مزایا و معایب آن عبارتند از:

مزایا:

  • راحتی پیاده سازی Triggerها
  • پشتیبانی در تمامی نسخه های SQL Server
  • قابلیت انعطاف بالا
  • امکان لاگ کردن تمامی تغییرات صورت گرفته

معایب:

  • اعمال بار پردازشی به سیستم
  • با تغییر ساختار جداول Triggerها نیاز به تغییر دارند

دومین مکانیزم داخلی که میتوان از آن برای دستیابی به این هدف کمک گرفت، استفاده از Stored Procedureها می‌باشد. در این حالت تمام روند تولید لاگ تغییرات در قالب یک Stored Procedure انجام می‌شود و همزمان با اعمال تغییرات داده های تغییر کرده و نوع عملیات انجام شده برروی آنها در جداول مربوط به Auditing نیز پر می‌شود، اما مزایا و معایب این روش:

مزایا:

  • راحتی استفاده از Stored Procedureها
  • انعطاف در نحوه پیاده سازی و منطق برخورد با تغییرات مختلف
  • امکان پیاده‌سازی در تمامی نسخه های SQL Server

معایب:

  • امکان لاگ کردن تغییراتی که خارج از Stored Procedure رخ می‌دهند را نداریم
  • نیاز به نگداری و همچنین همانند Triggerها با ایجاد تغییر در ساختار جداول SPهای ما نیز نیاز به تغییر خواهند داشت
  • در موارد امکان روبرو شدن با کندی در روند اجرای عملیات دیتابیس وجود دارد

ماکروسافت تا قبل از SQL Server 2008 برای پیاده سازی این لاگ ها(Data Auditing) ابزارهای مختلفی مانند C2 Audit و SQL Trace را ارائه داد که اگرچه این ابزارها حتی کسی که این داده ها را تغییر داده بود نیز مشخص می‌کردند، اما امکان نمایش داده های تغییر کرده را به کاربر نمی‌دهند.

Auditing در SQL Server 2008:

ماکروسافت وقتی که دید، بحث Auditing تبدیل به یکی از نیازهای پایه‌ای یک بانک اطلاعاتی شده است، تصمیم گرفت در SQL Server 2008 تکنیک‌های جدیدی را درون SQL Server برای این منظور تعبیه کند، تکنیک های مورد نظر عبارتند از:

  1. Change Tracking یا CT
  2. Change Data Capture یا CDC
  3. SQL Audit

در این سری مقالات سعی بر آن داریم که تمامی این موارد را به صورت کامل پوشش دهیم و در اولین مقاله از این سری مقالات به بررسی Change Tracking خواهیم پرداخت.

Change Tracking:

change Tracking می‌تواند به شما بگوید که چه سطرهایی از جداول شما تغییر کرده و همچنین مقدار فعلی آنها چیست و همچنین توضیحی در مورد چگونگی تغییر و داده های تغییر کرده به شما نمی‌دهد، برای مثال اگر مقدار یک فیلد به ترتیب از ۱ به ۴ سپس به ۶ و پس از آن به ۱۲ تغییر کند، به شما تنها مقدار ۱۲ را نشان می‌دهد و شما از گذشته تغییرات اطلاعی نخواهید داشت. اجازه دهید این روش را با یک مثال عملی نشان بررسی نماییم:

USE master
GO	
IF EXISTS(SELECT * FROM sys.databases WHERE name=N'TestCT')
   DROP DATABASE TestCT;
GO

CREATE DATABASE TestCT
GO	
ALTER DATABASE TestCT SET ALLOW_SNAPSHOT_ISOLATION ON	
GO	
----Snapshot Isolation is Needed for Change Tracking
USE TestCT
GO	

CREATE TABLE dbo.SomeTable
(
   ID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_SomeTable PRIMARY KEY,
   SomeColumn VARCHAR(255) NOT NULL
)
GO

در این قطعه کد یک دیتابیس تست به همراه یک جدول ایجاد کردیم و تنها عملیات خاصی که در این بخش باید به آن توجه کرد آن است که برای استفاده از Change Tracking باید در دیتابیس مورد نظر ALLOW_SNAPSHOT_ISOLATION را ON کنیم.

اکنون توسط دستورات قطعه کد زیر Change Tracking را در سطح دیتابیس فعال می‌کنیم و این کار به هیچ وجه بدین معنی نیست که با این کار تمامی بخش های آن دیتابیس Track خواهد شد، بلکه باید برای هر بخشی که نیاز به Change Tracking داریم به صورت جداگانه آن را فعال نماییم.

ALTER DATABASE TestCT
SET CHANGE_TRACKING=ON
(
   CHANGE_RETENTION=2 DAYS, 
   AUTO_CLEANUP = ON
)
GO
  • گزینه CHANGE_RETENTION به منظور مشخص کردن تعداد روزهایی که باید تغییرات نگهداری شود مورد استفاده قرار می‌گیرد
  • AUTO_CLEANUP مشخص می‌کند که پس از مدت زمان تعیین شده در پارامتر CHANGE_RETENTION داده های قدیمی(در این مثال با عمر بیش از دو روز ) را پاک کند یا خیر؟

حالا جهت تست صحت عملکرد این مکانیزم، تعدادی داده درون SomeTable درج می‌کنیم:

INSERT INTO dbo.SomeTable
        ( SomeColumn )
VALUES  ('Ali'),('Ahmad')
GO

برای بررسی اینکه در حال حاضر تغییرات انجام شده Track شده یا نه، کوئری زیر را اجرا می‌کنیم

SELECT * FROM dbo.SomeTable AS T CROSS APPLY CHANGETABLE(VERSION dbo.SomeTable,(ID),(T.ID)) AS CT

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

خطای عدم فعال بودن ChangeTracking برروی جدول

که همانطور که مشاهده می‌نمایید به فعال نبودن Change Tracking برروی جدول مورد نظر اشاره دارد.

ALTER TABLE	dbo.SomeTable 
   ENABLE CHANGE_TRACKING;
GO

از این لحظه تغییرات جدول SomeTable در حال track شدن می‌باشد و اگر کد قبلی را اجرا نماییم، اینبار بدون خطا اجرا می‌شود.

SELECT * FROM dbo.SomeTable AS T CROSS APPLY CHANGETABLE(VERSION dbo.SomeTable,(ID),(T.ID)) AS CT

ChangeTrack02

با اجرای دستورات فوق در خروجی دو فیلد اضافه نیز وجود دارد که عبارتند از :

  • SYS_CHANGE_CONTEXT
  • SYS_CHANGE_VERSION

همانطور که مشاهده می‌کنید این دو فیل اضافه هر دو مقدار NULL گرفته‌اند که به دلیل آن است که Change Tracking را پس از درج این رکورد ها فعال کرده ایم.برای مشخص شدن این موضوع یک رکورد دیگر در جدول درج می‌کنیم و نتیجه را مشاهده می‌کنیم.

INSERT INTO dbo.SomeTable
        ( SomeColumn )
VALUES  ( 'Hamid')
GO
UPDATE dbo.SomeTable SET SomeColumn='Behrouz' WHERE SomeColumn='Hamid'

SELECT * FROM dbo.SomeTable AS T CROSS APPLY CHANGETABLE(VERSION dbo.SomeTable,(ID),(T.ID)) AS CT
GO

ChangeTracking03

همانطور که مشاهده می‌کنید سطر جدیدی به نتایج ما اضافه شد که در آن فیلد SYS_CHANGE_VERSION برابر ۲ می‌باشد که این بدین معنی است که این سطر مربوط به دومین تغییر پس از فعال سازی Change Tracking برروی این جدول می‌باشد. تنها مشکل این متد آن است که ما متوجه نمی‌شویم که چه مقداری به Behrouz تغییر کرده است و در هر لحظه تنها آخرین مقدار را نمایش می‌دهد.

INSERT INTO dbo.SomeTable ( SomeColumn ) (SELECT (SELECT TOP 1 name FROM sys.objects ORDER BY NEWID()))
GO

در کد فوق یک سطر دیگر به جدول اضافه کردیم تا تغییرات را در جداول Change Tracking مشاهده کنیم. از اینجا به بعد می‌خواهیم تنها رکوردهایی که تغییر کرده‌اند را مشاهده کنیم، برای این منظور کد زیر را اجرا می کنیم:

DECLARE @version BIGINT=2;
SELECT T.*,CT.* FROM CHANGETABLE(CHANGES dbo.SomeTable,@Version) AS CT INNER JOIN dbo.SomeTable AS T ON CT.ID=T.ID;
GO

ChangeTracking04

با اجرای دستورات فوق با خروجی متفاوتی روبرو خواهیم شد، در فیلد SYS_CHANGE_OPERATION نوع تغییر انجام شده مشخص شده است(Insert,Update,Delete).

تا اینجا تمام حالتهایی که از Change Tracking مورد بررسی قرارگرفت در مورد تغییرات موجود در داده های یک سطر از جدول بود، همانطور که در ابتدای مقاله عنوان شد یکی از مزایای روش‌های Auditing نسبت به استفاده از Trigger و SP آن است که این روش ها امکان Track کردن تغییرات در ساختار جداول را نیز دارند، به همین منظور میخواهیم به بررسی این مورد در مورد جدولی جدید بپردازیم:

IF OBJECT_ID('NewTable') >0
   DROP TABLE NewTable;
GO	

CREATE TABLE NewTable
(
   ID INT IDENTITY	CONSTRAINT PK_NewTable_ID PRIMARY KEY,
   BigColumn VARCHAR(255) NOT NULL,
   AnotherBigColumn VARCHAR(255) NOT NULL
)
GO	

--Enable ChangeTracking on Newly Created Table
ALTER TABLE dbo.NewTable ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED=ON)
GO

 

همانطور که در کد بالا مشاهده می‌کنید، بزرگترین تفاوت نسبت به حالت قبلی WITH (TRACK_COLUMNS_UPDATED=ON) می‌باشد، که از این طریق به SQL Server می‌گوییم که تغییرات موجود در ساختار آن جدول را نیز Track کند. حال یک رکورد درون این جدول درج می‌کنیم:

INSERT INTO dbo.NewTable (BigColumn, AnotherBigColumn )VALUES  ('Some data','Some More Data')

و Version فعلی تغییرات را از طریق دستور زیر بدست می‌آوریم:

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CHANGE_TRACKING_CURRENT_VERSION

حال می‌خواهیم به بررسی تغییرات انجام شده در ساختار یک جدول بپردازیم، برای مثال بررسی کنیم که فیلد BigColumn تغییر کرده است یا خیر؟ برای این کار از یک فانکشن به نام CHANGE_TRACKING_IS_COLUMN_IN_MASK استفاده می‌کنیم که دو ورودی column_id و changes_columns دریافت می‌کند و در خروجی یک مقدارbit برمی‌گرداند که مشخص می‌کند تغییر داشته یا خیر؟ برای ورودی‌های این function از مقادیر زیر استفاده می‌کنیم :

DECLARE @column_id INT =COLUMNPROPERTY(OBJECT_ID('dbo.NewTable'),'FName','ColumnId');

و برای ورودی changes_columns از کد زیر استفاده می‌کنیم:

DECLARE @changesColumn VARBINARY(4100)
SELECT @changesColumn=ct.SYS_CHANGE_COLUMNS FROM CHANGETABLE(CHANGES dbo.NewTable,NULL) AS ct

که در نهایت به قطعه کد زیر می‌رسیم:

DECLARE @column_id INT =COLUMNPROPERTY(OBJECT_ID('dbo.NewTable'),'FName','ColumnId');
DECLARE @Version BIGINT =CHANGE_TRACKING_CURRENT_VERSION()-1;
DECLARE @changesColumn VARBINARY(4100)
SELECT @changesColumn=ct.SYS_CHANGE_COLUMNS FROM CHANGETABLE(CHANGES dbo.NewTable,NULL) AS ct

SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK(@column_id,@changesColumn);

اکنون با ایجاد تغییری در ساختار فیلد BigColumn کد فوق را تست می‌کنیم:

ALTER TABLE dbo.NewTable 
ALTER COLUMN BigColumn CHAR(1000)
GO 

DECLARE @column_id INT =COLUMNPROPERTY(OBJECT_ID('dbo.NewTable'),'BigColumn','ColumnId');
DECLARE @Version BIGINT =CHANGE_TRACKING_CURRENT_VERSION()-1;
DECLARE @changesColumn VARBINARY(4100)
SELECT @changesColumn=ct.SYS_CHANGE_COLUMNS FROM CHANGETABLE(CHANGES dbo.NewTable,NULL) AS ct

SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK(@column_id,@changesColumn) AS IS_BIGCOLUMN_CHAGED;

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

ChangeTracking05

جمع بندی:

  • ChangeTracking از SQL Server 2008 اضافه شد
  • این قابلیت را دارد که حتی درون Transactionها نیز ChangeTable را مقدار دهی کند
  • می‌توان اینگونه گفت که بیشتر برای بررسی تغییرات انجام گرفته در ساختار فیلدها مورد استفاده قرار می‌گیرد
  • یکی از معایب استفاده از ChangeTracking ایجاد IO اضافه بابت درج تغییرات در Change Table می‌باشد.
  • CT هیچ گونه تاریخچه ای از مقادیر تغییر کرده ذخیره نمی‌کند و تنها مقدار نهایی را به شما نمایش می‌دهد
  • به هنگام فعال سازی CT برروی یک جدول فیلدی به اندازه ۸ بایت به صورت مخفی به آن جدول اضافه می‌گردد
  • CT هیچ گونه اطلاعاتی در مورد اینکه چه کسی داده مورد نظر را تغییر داده به شما نخواهد داد

خب دوستان این بود بخش اول از سری مقالات کنترل تغییرات دیتا در SQL Server که به Change Tracking پرداختیم در مقاله بعدی Change Data Capture یا CDC رو بررسی خواهیم کرد که اگر از این مقاله خوشتون اومد پیشنهاد می کنم اون رو هم مرور کنید.

منابع:

اسکریپت:

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

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

Categories: SQL Server

پاسخ دهید

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