ردیابی تغییرات

در این مبحث همراه هم خواهیم دید که چگونه می توان تغییرات در data موجود در data base را ردگیری کرد، اگر بخواهیم دقیق تربگوییم قصد داریم تغییرات رکوردهای هر جدول را در صورت نیاز ردگیری کنیم. اما مسئله بحث امنیت اطلاعات نیست بلکه هدف ما از این پروسه این است که در صورت بروز تغییر، قرار است اقداماتی توسط برنامه نویس صورت گیرد. با این مطلب از آموزشگاه مهندسی کندو همراه باشید.

Change Tracking

به منظور درک هدفمان از پیاده سازی این قابلیت از SQL مثالی مطرح میکنیم، بطوریکه فرض کنید یک جدول جاری داریم که اطلاعات فروش هر روز در آن قرار دارد و در انتهای هر روز این اطلاعات مثلا در جدول بایگانی قرار می گیرد. در حالی که هنوز نسخه ای اصلی در جدول جاری هست، حال در روز بعد بازهم اطلاعات فروش درون جدول جاری قرار گرفته و انتهای روز به جدول بایگانی انتقال پیدا خواهد کرد. اما در این میان ممکن است رکوردی در جدول جاری مربوط به روز قبل تغییراتی داشته باشد.

پس باید به نحوه ای رکورد معادل آن در جدول بایگانی با توجه به این تغییر به روز شود،پس باید ما در نقش admin این تغییر را به برنامه نویس اعلام کرده تا او اقدام به اعمال تغییر در جدول روزانه کرده و یا هر سیاست دیگری را پیاده سازی کند.با توجه به مثال فوق می توان گفت change tracking قابلیت جدیدی در SQL 2008 است که بوسیله آن می توان هر گونه تغییرات رکوردهای هر جدول را به منظور اعمال سیاستی خاص ردگیری کرد.

پیش از مطالعه این مباحث، اگر علاقه مند به یادگیری مفاهیم SQL Server دارید، با شرکت در دوره های آموزش sql server کندو می توانید تجربه خود را در این زمینه افزایش دهید.

• پیاده سازی معمولی: قبل از بوجود آمدن این قابلیت در SQL برای پیاده سازی پروسه ردگیری تغییرات معمولا از یک فیلد به عنوان flag برای هر رکورد در نظر گرفته می شد، و حتی در حالت پیش رفته تر بجای استفاده از یک ستون اضافه از یک جدول اضافه تر به منظور تهیه log استفاده می شد که طراحی و پیاده سازی آن بر عهده برنامه نویس بود.
• پیاده سازی بوسیله امکان جدید در SQL: هدف این است که هرگونه تغییر در ستون های هر رکورد،را اعلام کنیم و وظیفه این پروسه را به SQL واگذار کرده و برنامه نویس درگیر پیاده سازی چنین پروسه ای نباشد.

نحوه کار: در ادامه می توان کار را به دو قسمت تقسیم کرد:1.وظایف admin 2.وظایف برنامه نویس

1. وظیفه admin در واقع فعال سازی این پروسه است که بدین مظور ابتدا از data base مورد نظر که قرار است پروسه فوق روی آن اعمال شود Properties گرفته و سپس خواهیم داشت:

فعال سازی Change tracking

و یا با استفاده از query خواهیم داشت:

USE [master]

GO

ALTER DATABASE [Jobmgmt] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 5 DAYS)

GO

حال که این پروسه را برای data base فوق فعال کردیم، نوبت به فعال سازی آن برای جدول مورد نظرمان است، پس برای این کار از جدول مورد نظر properties گرفته و خواهیم داشت:

فعال سازی برای یک table

و یا بصورت query خواهیم داشت:

USE [Jobmgmt]

GO

ALTER TABLE [dbo].[Test8] ENABLE CHANGE_TRACKING –WITH(TRACK_COLUMNS_UPDATED = ON)

GO

2. حال که این پروسه برای جدول مورد نظر ما فعال شد،نوبت به برنامه نویس است که از آن استفاده کند،که خارج از بحث ما است اما در یک توضیح اجمالی می توان گفت به منظور استفاده از پروسه change tracking توسط برنامه نویس، یکسری توابع سیستمی وجود دارد که با استفاده از آن میتوان این تغییرات را متوجه شد.برای دسترسی به این system function ها توصیه می شود به books online مراجعه شود،اما به عنوان مثال یکی از این توابع را در زیر بررسی خواهیم کرد.

نکته: همانطور که می دانیم، معرف هر رکورد در جدول مفهومی معادل primary key است، پس به منظور شناسایی تغییرات هر رکورد ابتدا باید ابزاری جهت شناسایی آن رکورد داشته باشیم،پس می توان نتیجه گرفت،پروسه change tracking در مورد جداولی قابل پیاده سازی است که حتما یک کلید اصلی داشته باشند.

همچنین تابعی دیگری وجود دارد که بر اساس primary key که معرف هر رکورد در هر جدول میباشد،ورژن تغییرات -را نمایش می دهد که برای مثال بالا کاربرد این تابع بصورت زیر خواهد بود:

مثال عملی:

USE [Jobmgmt]

GO

ALTER TABLE [dbo].[Test8] ENABLE CHANGE_TRACKING –WITH(TRACK_COLUMNS_UPDATED = ON)

GO

——————————————

SELECT * FROM test8

INSERT INTO test8 VALUES(4,’rr’,’Eslami’)

UPDATE test8 SET id=7 WHERE id =3

DELETE test8 WHERE id = 4

SELECT * FROM CHANGETABLE(CHANGES test8 ,0) AS l1 –0 version starrt

SELECT l1.id [before],l2.id [after]

FROM CHANGETABLE(CHANGES test8 ,0) AS l1 INNER JOIN CHANGETABLE(CHANGES test8 ,0) AS l2

ON l1.SYS_CHANGE_VERSION = l2.SYS_CHANGE_CREATION_VERSION AND l1.id <> l2.ID

–Taghirat

SELECT * FROM CHANGETABLE(VERSION test8 ,([id]),(2)) AS l1 

• Change Data Capture یا CDC

پیرو هدف ما جهت ردیابی تغییرات رکورد، کمبودی در روش change Tracking احساس می شود، که تغییرات رکورد را فقط در سطح Primary key نگهداری می کرد و گرچه قابلیت ردیابی column Base را نیز دارد، نهایت بتوان تغییر را در سطح ستون بررسی کرد، اما خوب بود یک نمونه از مقدار رکورد را قبل از تغییر داشته باشیم که به راحتی بتوان با مقایسه مقدار فعلی و مقادیر قبلی، احینا به مقدار قبلی بازگردیم.

از طرفی بحث Performance نیز برای ما مهم هست،چرا که Change Tracking تغییرات را از Data File اصطلاحا کشف و دخیره می کند. پس در این روش ما به Log مراجعه خواهیم کرد و بر اساس تراکنش اقدام به ذخیره سازی تراکنش ها، خواهیم کرد و مقدار یک رکورد را قبل از هر اپریشنی از log و بر اساس LSN ذخیره خواهیم کرد. با این مقدمه در ادامه به جزئیات روش خواهیم پرداخت.

یکی از قابلیت های ویژه SQL Server Enterprise Edition که البته درنسخه های Developer editions هم به خوبی کار میکند بطوریکه روی یک جدول در دیتابیس ما فعال میشود و تغییرات ناشی از دستورات DML (Insert,Update,Delete) را نگهداری میکند و ما میتوانیم از این اطلاعات استفاده کنیم. وقتی که ما cdc را روی جدول یفعال میکنیم،در واقع این وظیفه SQL Server است که جدولی مشابه آن به همرای یکسری Metadata ایجاد کرده وتغییرات را در آن ذخیره نماید. در کنار آن تعدادی Table-valued function در اختیار ما قرار میگیرند که به ما امکان استفاده از داده های ذخیره شده را می دهند.

هنگامی که CDC روی یک جدول فعال میشود، SQL Server از مکانیزم نامتقارنی (Async) استفاده میکندکه به کمک آن تغییرات رخ داده در جداول را از فایل log میخواند و درجداولی که به منظور نگهداری تغییرات ایجاد کرده ذخیره میکند. در فایلهای log هر رکوردی کهذخیره میشود یک شناسه یکتا با نام Log Sequence Number دارد که به اختصار LSN نامیده میشود.بسیاری از Metadataهایی که برای ما نگهداری میشود به خاطر قرابت نزدیکی که اینتکنولوژی با Log دارد از همین اطلاعات Log استخراج می شوند.

عبارت Async در پارگراف قبلی کمی بحث بر انگیز است، چراکه این معنی در ذهن خطور می کند که اطلاعات در زمان تغییر مستقیما capture نمی شود، بلکه با یک اختلاف زمانی و با یک روش خاص اعمال خواهد شد.و این یک نکته مهم است و نحوه کار بدین ترتییب است که یک job ساخته میشود، که وظیفه این job خواندن اطلاعات از Log و بر اساس LSN و نوشتن آن در جدول به عنوان History تغییرات خواهد بود.

مشکلی دیگری که CDC دارد، درصورت تغییرات یک جدول است،یعنی اگر یک ستون به جدول اضافه گرددCDC جدید باید طراحی شود، دیتا به CDC جدید انتقال یابد و قبلی حذف گردد، که کاری کاملا manually است.

اما در نهایت مکانیسمی جهت مدیریت History نگهداری شده وجود خواهد داشت که به DBMS بگوییم تا چه زمانی سوابق تغییرات را نگهداری کند.البته می توان با استفاده از طراحی File Group مجزا برای نگهداری سوابق، در جهت ارتقا کارایی و مدیریت دیتا اقدامات مفید داشته باشیم.

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

گام اول: فعال سازی در سطح یک Database

exec sys.sp_cdc_enable_db

گام دوم: فعال سازی در سطح یک جدول

CREATE TABLE Test8

(

id INT PRIMARY KEY,

nameP nvarchar(100),

Family NVARCHAR(100)

)

EXEC sys.sp_cdc_enable_table 

@source_schema = N’dbo’, 

@source_name = N’Test8′, 

@role_name = NULL ,

–@filegroup_name = N’Primary’, 

@supports_net_changes = 0 

GO 

گام سوم: تنظیمات مربوط به مدت زمان نگهداری سوابق

EXEC sys.sp_cdc_cleanup_change_table @capture_instance = N’Test5′, — sysname

@low_water_mark = NULL, — binary(10)

@threshold = 3 — bigint

گام چهارم: اعمال تغییرات و مشاهده سوابق:

INSERT INTO Test5(id,namep,Family)

VALUES(1,’amir’,’alahyari’)

SELECT * FROM [Jobmgmt].[cdc].[dbo_Test8_CT]

UPDATE test8 SET nameP = ‘ali’ where id = 1

SELECT * FROM [Jobmgmt].[cdc].[dbo_Test5_CT]

DELETE FROM test5 WHERE id = 1

SELECT * FROM [Jobmgmt].[cdc].[dbo_Test5_CT]

نتیجه کار تا این جا بصورت زیر خواهد بود

و در جدول History داریم:

گام پنجم: احیانا حذف این قابلیت

EXEC sys.sp_cdc_disable_table @source_schema = N’dbo’, 

@source_name = N’Test8′, 

@capture_instance = N’dbo_Test8′ — sysname

• نکته مهم در این پیاده سازی با مشاهده جدول سوابق جلب توجه می کند، که آنچه به عنوان تغییر ثبت شده مقدار قبلی رکورد ها به همراه LSN آن است و حال باید دنبال روشی برای تبدیل آن به زمان سیستم داشته باشیم؛ که با توجه به binary بودن مقادیر LSN قطعا باید جدولی پیدا کنیم سیستمی که زمان هر LSN در آن ضبط شده باشد و با join با این خروجی به زمان مورد نظر دسترسی پیدا کنیم.البته در 2016 بدین منظور توابع سیستمی در نظر گرفته شده است و خواهیم داشت:

SELECT *,sys.fn_cdc_map_lsn_to_time([__$sTART_lsn]) AS SaveDate FROM [Jobmgmt].[cdc].[dbo_Test5_CT]

• Temporal Table (Table Versioning)

روشی جدید جهت نگهداری سابقه تغییرات رکورد های یکی جدول است که از SQL 2016 به DBMS ماکروسافت اضافه شده است که اینطور که ادعا دارد با کمترین افت Performance اقدام به نگهداری مقدار قبلی اطلاعات خواهد کرد.این قابلیت معروف به Versioned Temporal Table است.

روش کار در این ساختار بدین ترتیب است که برای هر جدول باید دو ستون اضافه بر ستون های خودش،از نوع Versioned Temporal داشته باشد که از نوع date Time بوده که برای ذخیره زمان تولد سطر [TimeStart] و زمان پایان عمر [TimeEnd] آن سطربوده که توسط ما ساخته و توسط سیستم بصورت خودکار مقدار دهی می شوند.

سپس جدول دیگری بصورت خودکار با همان ساختار توسط DBMS ساخته میشود که قرار است تغییرات را در خود نگهداری کند.جدول اصلی تنها آخرین نسخه سطرهای جدول را در خود نگهداری میکند و جدول دوم که بصورت خودکار ایجاد شده به جدول اصلی متصل است و در آن تغییرات سطرها بصورت خودکار ذخیره میشود. این جدول، جدول تاریخچه نام دارد.

پس زمامی که ما یک جدول می سازیم بصورت موازی جدول دیگری بر اساس الگو SYSTEM_VERSIONING تحت عنوان تاریخچه یا History ساخته خواهد شد.همچنین می توان به جدولی که قبلا ایجاد شده نیز این قابلیت اضافه گردد.

دقت شود دیتا داخل جدول History تحت هیچ عنوان قابل تغییر نیست و توسط DBMS مدیریت شده و درصورت سعی بر اینکار با خطا مواجه خواهیم شد.

اگوریتم که DBMS از آن استفاده می کند بدین ترتیب است که:

• زمانی یک رکورد در جدول اصلی اضافه میشود، فیلد TimeStart بصورت خودکار با تاریخ سیستم پر میشود و فیلد TimeEnd مقدار حداکثر(9999-12-31 23:59:59.99) به خود می گیرد.
• زمانی که که رکورد حذف میشود، مقدار حذف شده در جدول History نگهداری می شود اما ستون TimeStart از جدول اصلی آورده شده و ستون TimeEnd زمان حذف شدن رکورد را مقدار می گیرد.
• زمانی که یک رکورد ویرایش می شود، مقدار قبل ویرایش با تاریخ درج یا آخرین ویرایش انجام شده در جدول اصلی به جدول History در ستون TimeStart انتقال پیدا می کند که زمان فعلی به عنوان زمان حذف رکورد ویرایش شده در ستون TimeEnd قرار خواهد گرفت. سپس در جدول اصلی، رکورد جدیدی با تاریخ فعلی در ستون TimeStart و TimeEnd برابر مقدارحداکثر ثبت خواهد شد.

با توجه به این توضیحات نحوه کار بصورت زیر خواهد بود:

گام اول: ساخت جدول

CREATE TABLE test600

(

id INT PRIMARY KEY,

nameP nvarchar(100),

Family NVARCHAR(100),

[TimeStart] datetime2 (2) GENERATED ALWAYS AS ROW START ,

[TimeEnd] datetime2 (2) GENERATED ALWAYS AS ROW END ,

PERIOD FOR SYSTEM_TIME (TimeStart, TimeEnd) ) WITH (

SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.test600_History, History_retention_period = 10 DAYS)– ,HISTORY_RETENTION_PERIOD = 6 MONTHS)

)

گام دوم: ویرایش جدول موجود

ALTER TABLE dbo.test600 ADD 

 [TimeStart] DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL DEFAULT GETDATE(),

 [TimeEnd] DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL DEFAULT ‘9999.12.31 23:59:59.99’, PERIOD FOR SYSTEM_TIME ([TimeStart], [TimeEnd]);

ALTER TABLE dbo.test600 SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.Test600_History ) )

ALTER TABLE dbo.Test600 SET ( SYSTEM_VERSIONING = ON ( History_retention_period = 10 DAYS ) )

گام سوم: احیانا حذف

ALTER TABLE dbo.test60 SET (SYSTEM_VERSIONING = OFF); 

ALTER TABLE dbo.test60 

DROP PERIOD FOR SYSTEM_TIME; 

DROP TABLE test60

گام چهارم: بررسی نتیجه

گام پنجم: Insert To Table

INSERT INTO test600(id,namep,Family)

VALUES((1,’Bari2′,’Bro2′) ,(2,’Bari2′,’Bro2′))

SELECT * FROM dbo.test600

SELECT * FROM dbo.test600_History

گام ششم: Update Data

UPDATE test600 SET nameP = ‘333333’ where id = 2 

SELECT * FROM dbo.test600

SELECT * FROM dbo.test600_History

گام هفتم: Delete Data

DELETE FROM test600 WHERE id = 1

SELECT * FROM dbo.test600

SELECT * FROM dbo.test600_History

گام هشتم: تبدیل ساعت های به ساعت سیستم با استفاده از فانکشن زیر

CREATE FUNCTION [dbo].[udfGetLocalDateTime](@StartDate datetime)

RETURNS datetime

AS

BEGIN

RETURN (dateadd(hour,(cast(datepart(hour,sysdatetime()) as int) –

cast(datepart(hour,sysutcdatetime()) as int)),@startdate))

END

SELECT *,[dbo].[udfGetLocalDateTime]([TimeStart]) AS RealTime FROM dbo.test600_History

گام kil: چک کردن تغییرات یک رکورد

DECLARE @id INT = 2

IF EXISTS(SELECT TOP 1 1 FROM test600 WHERE id = @id)

begin

PRINT ‘Record is Exists History:’

SELECT id,namep,family,[dbo].[udfGetLocalDateTime](TimeStart) StartTime,NULL AS TimeEnd FROM dbo.test600 WHERE id =@id UNION

SELECT id,namep,family,[dbo].[udfGetLocalDateTime](TimeStart) StartTime,[dbo].[udfGetLocalDateTime](TimeEnd) EndTime FROM dbo.test600_History WHERE id = @id ORDER BY StartTime DESC

END

ELSE

BEGIN

PRINT ‘Record is Not Exists History:’

SELECT id,namep,family,[dbo].[udfGetLocalDateTime](TimeStart) StartTime,[dbo].[udfGetLocalDateTime](TimeEnd) EndTime FROM dbo.test600_History WHERE id =@id ORDER BY StartTime desc

end 

تفاوت این 3 روش