content top
SQL Server ומכונת הזמן (חלק ראשון)

SQL Server ומכונת הזמן (חלק ראשון)

שלום חברים

בפוסט הזה אני ארחיב את השימוש במנגנון הCDC.

מי שמעוניין בהסבר מפורט על CDC יכול לעיין בפוסט של מאיר דודאי בנושא (שימוש ב-CDC בסביבות DWH) או בהרצאה של שי/שגיא בנושא Auditing.

נניח שהיינו רוצים לדעת מה היו הנתונים בטבלאות מסויימות בכל זמן נתון בעבר- לא משנה אם מאז שונו הנתונים ואיך, וזאת בלי שנידע מראש איזו נקודה בזמן היינו רוצים לבחון.

CDC נועד בעיקרו לשמש ככלי Auditing אבל ניתן בקלות להשתמש כדי לצפות בכל נקודה אחורה בזמן.

לפני שאמשיך כמה מגבלות:

  1. ב-CDC נקראים הנתונים מקובץ הלוג (Transaction log) ולא ע"י שימוש בטריגר, כך שלפעמים צפייה בנתונים של לפני שניה עלולה להיות בלתי אפשרית (למי מכם שאוהב להריץ סקריפטים ברצף ולא יבין למה נתוני ה-CDC שמוחזרים שגויים).
  2. יש מספר מגבלות על טבלאות מאופשרות CDC אחת מהן היא שלא ניתן לבצע עליהן פעולת Truncate (מישהו יכול לנחש למה? רמז, קשור לעובדה שהנתונים נקראים מהלוג).
  3. במהלך העדכונים על הטבלאות ה-SQL Server Agent חייב להיות פעיל (שהרי מישהו חייב לקרא מהלוג ברקע באמצעות JOB) כל השבתה שלו תפגום באיכות הנתונים.
  4. חייב להיות לטבלה מזהה חד ערכי (אפשר שלא אבל לא נקבל מזה הרבה תועלת).

לצורך ההדגמה אבנה מסד נתונים חדש הקרוי CDC_DEMO (כן, כן העתקתי קטעים מהסקריפטים של מאיר, מי שרוצה מוזמן לרדת עלי בתגובות).

הסקריפט להלן יוצר את המסד ומאפשר ביצוע CDC עליו:

USE MASTER

IF  EXISTS (SELECT name FROM sys.databases WHERE name = 'CDC_DEMO')

    BEGIN

    ALTER DATABASE [CDC_DEMO] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

    DROP DATABASE CDC_DEMO

    END

GO


CREATE DATABASE CDC_DEMO

Go


USE CDC_DEMO

GO


EXEC sys.sp_cdc_enable_db

GO

בהמשך ניצור טבלה ונאפשר עליה CDC (אם היא קיימת, למשל בהרצה השניה, אני מבטל את הCDC עליה ומוחק אותה):

 

IF EXISTS(SELECT 1 FROM sys.tables    WHERE name='POTCheck')

BEGIN

    EXECUTE sys.sp_cdc_disable_table

        @source_schema = N'DBO',

        @source_name = N'POTCheck',

        @capture_instance = N'all';

    DROP TABLE POTCheck

END


GO

CREATE TABLE POTCHECK

            (

            ID INT IDENTITY,

            CreationTime DATETIME DEFAULT GETDATE(),

            Value VARCHAR(100)

            )


ALTER TABLE POTCheck ADD CONSTRAINT CIX_POTCheck_ID PRIMARY KEY CLUSTERED (ID)

GO


EXEC sys.sp_cdc_enable_table

    @source_schema =  'DBO',

    @source_name =  'POTCheck' ,

    @role_name = NULL  ,

    @capture_instance = 'POTCheckCapture',

    @supports_net_changes = 1

GO

 

כעת נכניס לטבלה מידע התחלתי:

 

insert into POTCheck(Value)

  values ('A'),('B'),('C')


GO


בסקריפט הבא אני מכניס/משנה נתונים בטבלה ודוגם שני זמנים אליהם ארצה לחזור.

אני עושה שימוש גם בעמודה עם השם המלבב __$operation כדי לקבל את הפעולה שנעשתה:

  1. מחיקה
  2. הוספה
  3. ערך לפני עדכון (למעשה ערך שנמחק)
  4. ערך אחרי עדכון (למעשה ערך שנוסף)
  5. ערך עדכון ממקור לא ברור (אופציה שאני מתעלם ממנה מאחר ואין לי דרך בטוחה לדעת איזו רשומה שונתה).

שימו לב, אין בCDC מידע על הזמן בו נעשתה פעולה כלשהי אלא רק מידע על הLSN שלה, אבל, מאחר ויש את הפונקציה fn_cdc_map_time_to_lsn אין בעיה להמיר זמן ל LSN (הפונקציה ההפוכה היא sys.fn_cdc_map_lsn_to_time).

בסוף אני עושה שתי שאילתות שמשתמשות בזמני הדגימה הרצויים:

use CDC_DEMO


declare @TimeA datetime,

        @TimeA_lsn binary(10),

        @TimeB datetime,

        @TimeB_lsn binary(10)



Set @TimeA = getdate()


insert into POTCheck(Value)

values ('You'),('and'),('Me')


update POTCheck

set value = NewValue

from POTCheck

inner join

    (values(1,'1'),(2,'2'),(3,'3')) v(code,NewValue) on code=ID



waitfor delay '00:00:05' -- The Deay needed for the CDC to be updated

delete POTCheck where id>2

set @TimeB=getdate()



waitfor delay '00:00:10' -- The Deay needed for the CDC to be updated

delete POTCheck


waitfor delay '00:00:10' -- The Deay needed for the CDC to be updated


SELECT  * FROM cdc.POTCheckCapture_CT (nolock)


set @TimeA_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than',@TimeA);

set @TimeB_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than',@TimeB);


declare @Current_lsn varbinary(10)


set @Current_lsn = @TimeA_lsn


select @TimeA,@TimeA_lsn,@TimeB,@TimeB_lsn,@Current_lsn


select * from POTCheck (nolock)

union

select id,creationTime,value

from cdc.POTCheckCapture_CT (nolock)

where __$operation in (1,3)

and __$start_lsn>=@Current_lsn

except

select id,creationTime,value

from cdc.POTCheckCapture_CT (nolock)

where __$operation in (2,4)

and __$start_lsn>=@Current_lsn




set @Current_lsn = @TimeB_lsn


select * from POTCheck (nolock)

union

select id,creationTime,value

from cdc.POTCheckCapture_CT (nolock)

where __$operation in (1,3)

and __$start_lsn>=@Current_lsn

except

select id,creationTime,value

from cdc.POTCheckCapture_CT (nolock)

where __$operation in (2,4)

and __$start_lsn>=@Current_lsn

 

והתוצאה (אחרי המתנה מורטת עצבים) היא:

clip_image002

השאילתא הראשונה מביאה לי את הערכים בטבלת הAuditing שלי cdc.POTCheckCapture_CT (איך ידעתי את שם הטבלה? נראה בהמשך).

השאילתא השניה מחזירה לי את זמני הדגימה וערכי הLSN הרלוונטים + משתנה שאני משתמש בו כדי לאחסן את ערך הLSN שאני דוגם בכל שאילתא.

השלישית והרביעית מחזירות לי את הנתונים שהיו בטבלה בנקודות הזמן שאני מעוניין לבדוק.

ואיך ידעתי מה הטבלה בה נשמרו נתוני הAuditing?

5 השאילתות הבאות יכולות לתת לנו את כל המידע שנרצה לדעת (ונעיז לשאול):

select * from cdc.change_tables

select * from cdc.lsn_time_mapping

select * from cdc.captured_columns

select * from cdc.ddl_history

select * from cdc.index_columns

image

ומהן הטבלאות?

  1. cdc.change_tables מכילה מידע על טבלאות שנעשה עליהן CDC, ביצוע השאילתא select OBJECT_NAME(645577338),OBJECT_NAME(597577167) יחזיר לנו את שמות האובייקטים שמעניינים אותנו הטבלה שלנו – POTCHECK וטבלת ה Auditing – POTCheckCapture_CT.
  2. cdc.lsn_time_mapping מחזירה לנו מיפוי זמן התחלה וסיום מול LSN.
  3. cdc.captured_columns מחזירה את העמודות עליהן נעשה CDC (ניתן לבצע CDC רק על חלק מהעמודות).
  4. cdc.ddl_history מכילה תיעוד של פעולות DDL.
  5. cdc.index_columns העמודות המזהות באופן חד ערכי את השורות בטבלה.

תהנו


תגובה אחת על “SQL Server ומכונת הזמן (חלק ראשון)”

  1. מאת שביט:

    מאוד שימושי!
    כל הכבוד יוסי, תמשיך לפרסם מאמרים מעניינים!

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

6 + eight =