האח הגדול עינו פקוחה (6) – מי שינה את הנתונים בטבלה? Change Data Capture

16/07/2013

אין תגובות

לפני כשלוש שנים פרסמתי את הפוסט האח הגדול עינו פקוחה (5) – מי שינה את הנתונים בטבלה? בו הצעתי דרך לבניית טבלת מעקב אחר שינויים בטבלה וטריגר שיעדכן אותה, וכך נוכל לדעת מי שינה את נתוני הטבלה, מתי, ומה.
בפוסט ההוא המצאתי את הגלגל מחדש מכיוון שקיים כבר כלי יעודי לשם כך בשם Change Data Capture, ובקיצור – CDC (זה לא אמור להיות Changed Data Capture? לכידת מידע שהשתנה..), שעושה זאת בערך: לא בדיוק, לא הכל, אבל מה שיש יכול לחסוך טירחה מאיתנו ומאמץ מהמחשב מכיוון שמדובר באופציה שאינה עושה שימוש בטריגרים אלא "מאזינה" ללוג. מנגד- לא הכל היא עושה, ומי שרוצה פתרון תפור לפי מידה – יצטרך לתפור אותו בעצמו (כאמור- הפוסט הקודם).

מה SQL Server נותן לנו בהנחה שמדובר בגרסת Developer או Enterprise מ-2008 ואילך?
קודם כל ניצור טבלת נסיונות:

Use MyDB;

If Object_ID('Tmp','U') Is Not Null Drop Table Tmp;

Create Table Tmp(ID Int Identity Primary Key,

                 Txt Varchar(10));

Go

כעת נאפשר שימוש ב-CDC בדטבייס (חד פעמית),

ונגדיר שהוא יופעל על הטבלה שיצרנו (לגבי טבלאות נוספות בדטבייס – יש להפעיל רק את הפרוצדורה הזו):

Exec SP_CDC_Enable_DB;

Exec sys.SP_CDC_Enable_Table @source_schema=N'dbo',

                             @source_name=N'Tmp',

                             @role_name=Null;

Go

הפעולות האלו יצרו מאחורי הקלעים שלל אובייקטים:

* סכימה חדשה בשם cdc עבור כל האובייקטים.

* טבלאות מערכת כלליות וטבלה מעקב ספציפית לטבלה tmp הנ"ל.

* פרוצדורות ופונקציות.

* שני ג'ובים חדשים.

נתחיל להציק למערכת – נכניס שלוש שורות חדשות, נעדכן אחת ונמחוק אחרת:

Insert

Into   Tmp(Txt)

Values ('a'),('bb'),('ccc');

Go

 

Update Tmp

Set    Txt='bbbb'

Where  ID=2;

 

Delete

From   Tmp

Where  ID=3;

 

Select * From Tmp;

clip_image002

איזה מידע ניתן לשלוף מטבלאות המערכת?

Select  *,

        sys.fn_cdc_is_bit_set(1,__$update_mask) [ID],

        sys.fn_cdc_is_bit_set(2,__$update_mask) [Txt]

From    cdc.dbo_Tmp_CT CT

Inner Join cdc.lsn_time_mapping M

        On CT.__$start_lsn=M.start_lsn;

clip_image004

טבלת dbo_Tmp_CT היא טבלת המעקב אחר Tmp,

__$Start_Lsn מסייעת לפענח אותה,

והפונקציה Fn_CDC_Is_Bit_Set עוזרת לגלות אילו עמודות השתנו.

פעולת ה-Insert: 3 השורות הראשונות (למשל – לפי Tran_Login_Time או Start_Lsn שהם זהים עבור שלושתן), שתי העמודות התעדכנו (1 בשתי העמודות הימניות), והערכים שנכנסו מופיעים בעמודות ID, Txt שבאמצע.

פעולת ה-Update: שורות 4-5 שמראות את הערך הקודם (__$Operation=3) והערך החדש (__$Operation=4). רק עמודת Txt השתנתה (צד ימין של השליפה).

פעולת ה-Delete: שורה 6.

מה קורה בפעולות DML בהן הטבלה משתנית? זה אחד החסרונות של ה-CDC: ניתן להוסיף עמודה לטבלה, אך המערכת לא תעקוב אחריה. אפשר כמובן לבטל את המעקב אחר הטבלה באמצעות sys.SP_CDC_Disable_Table וליצור אותו מחדש, אך זה יגרום ליצירה מחדש של טבלת המעקב ולאובדן כל המידע שבה. פתרון אפשרי לכך יהיה ליצור טבלה נפרדת שאליה יועבר כל המידע מטבלת המעקב, ולפנות להבא לשתיהן באמצעות Union All.

בכל מקרה- אחר שינויי הסכימה המערכת דווקא עוקבת, למשל – הוספת עמודה:

Alter Table Tmp Add Taarih DateTime Default GetDate();

נמתין כמה שניות כי מדובר כנראה בפעולה אסינכרונית, ונשלוף באחת משתי הדרכים:

Select  *

From    cdc.ddl_history DH

Inner Join cdc.change_tables CT

        On DH.object_id=CT.object_id;

 

Exec    sys.sp_cdc_get_ddl_history 'dbo_Tmp';

clip_image006

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

אחר אילו טבלאות עוקבים? לא הייתי רץ ויוצר טבלאות מעקב אחר כל הטבלאות שבדטבייס.. עד כאן! כלים ופתרונות שכאלו נועדו לעקוב אחר מידע רגיש, טבלאות עם הגדרות שונות וכו' שיאפשרו לשחזר מה השתנה ומתי.

את רשימת כל פונקציות המערכת הקשורות ל-CDC ניתן למצוא כאן.

את רשימת כל פרוצדורות המערכת הקשורות ל-CDC ניתן למצוא כאן.

לסיום- כדאי למחוק את הטבלה ולשנות את ההגדרה של הדטבייס – כך שכל האובייקטים שנוצרו יעלמו:

Drop Table Tmp;

Exec SP_CDC_Enable_DB;

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

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