SQL Server: הצגת טכניקות שונות למעקב אחרי שינויים בטבלאות

מפתחים ו-DBA-ים רבים נדרשים בשלב זה או אחר להתמודד עם הדרישה הבאה: יש אפליקציה כלשהי שצריכה לקבל איזשהו feed על נתונים חדשים או נתונים שהתעדכנו עבור איזשהו צורך (אפליקציה שממתינה למידע חדש, תהליך ETL וכו’ ורוצות לקבל “דלתאות” של מה שקרה), כאשר המקור לנתונים הללו זאת איזושהי אפליקציה אחרת – שהיא בעצמה מעדכנת אותם ב- SQL Server.
בד”כ בשלב הזה הרבה פעמים הולכים לפי ההיגיון הבא – אם המידע בסופו של דבר נמצא ומאוכסן ב- SQL Server, למה שלא נשתמש בו גם בתור דרך “להפיץ” את המידע לאפליקציות ולתהליכים אחרים? המידע נכנס ל-DB, ואפליקציות אחרות שמעוניינות במידע פשוט ישלפו אותו ויעבדו עליו.

ההיגיון מאחורי התפיסה הזאת ברור – “האמת” נמצאת ב- DB ומתעדכנת תמיד מול ה- DB. אם נוודא שאפליקציות אחרות יוזנו בעדכונים, נוכל לוודא שהם תמיד יהיו מודעים לאותה “האמת” שב-DB שלנו. לכאורה, בצורה כזאת, ה-DB גם הופך לממשק מול העולם החיצוני: אנחנו לא מגדירים ממשקים בין אפליקציות, אלא משתמשים ב- DB כמתווך באמצע.

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

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

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

Crawling

הקונספט של השיטה הראשונה הוא לבצע polling קבוע על הטבלאות, ולשאול למעשה “מה השתנה מאז הפעם האחרונה שעשיתי את ה- polling”. בד”כ השיטה שזה מתבצע תהיה לעקוב אחרי איזשהו ערך של עמודה, לקבל את הערכים שגדולים ממנו, ואז לשמור את הערך העדכני ביותר. למשל, אם נרצה לעקוב אחרי ID רץ מספרי – נביא את כל השורות שה-ID שלהם גדול מה-ID האחרון שראינו ונשמור את ה- ID הגבוהה ביותר בתור ה- state של התהליך שלנו (כדי שפעם הבאה נביא רק את השורות שגדולות ממנו).

אחרי איזה עמודות עוקבים?

אז פה עקרונית יש כמה ווריאציות אפשריות. אם מעניין אותנו לדעת רק על שורות חדשות בטבלה, נעקוב בד”כ על אחד מהערכים האלה:

•  ID מספרי רץ: בד”כ זה יהיה הערך של ה- primary key, שהוא למשל איזשהו IDENTITY column. במקרה כזה, נרצה לשמור לעצמנו כל פעם “בצד” את הערך של ה- ID האחרון שראינו, ולהביא את ה- ID הבא.
  השיטה הזאת כמובן מתאימה לנו כשמה שמעניין אותנו זה רשומות חדשות. אנחנו לא נדע ככה כשרשומה מתעדכנת (כי ה- ID שלה, כמובן, נשאר זהה).
• עמודת “DateTimeCreated” של תאריך ושעה של יצירת הרשומה: אם אנחנו שומרים (ובד”כ כדאי לשמור) את תאריך ושעת היצירה של הרשומה, אנחנו יכולים לעשות את ה- crawling לפי זה.  במקרה הזה, מה שנשמור בצד כל פעם זה את התאריך והשעה שבהם התחלנו את השאילתה שתפקידה להביא את המידע העדכני. חשוב כמובן לוודא שאנחנו מסונכרנים מבחינת השעון ומבחינת ה- timezone (למשל, אם הערך של עמודת התאריך והשעה נקבע באפליקציה שמכניסה לפי UTC, לא נרצה לשמור את התאריך והשעה לפי שעון ישראל). צריך גם לוודא שיש מענה למקרה הקצה (שראיתי בעבר שיכול להתפספס) שהאפליקציה קובעת את הזמן ברמה האפליקטיבית לפי הזמן של ההכנסה באותו הרגע, מה שגורם לזה שבזמן הכנסת השורה בפועל היא עם ערך ישן יותר מהזמן האמיתי בעולם באותו רגע, מה שעלול לגרום לרשומה להתפספס.

איך נבחר בין האפשרויות האלה? אם אחת מהעמודות הללו היא ה- key column של ה- clustered index של הטבלה – נבחר בה. הסיבה, היא כמובן שאנחנו מקבלים ביצועים הרבה יותר טובים לסריקה הזאת שהיא לפי טווח, וחוסכים לעצמנו את ה- key lookup של הבאת הערכים העצמם. במקרה שאנחנו עובדים לפי ה- clustered index, הבאת שורות חדשות (או ההבנה שאין שורות חדשות) תהיה פעולה מיידית וזניחה מבחינת השפעת הביצועים שלה.

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

• עמודת “DateTimeUpdated” שכוללת תאריך ושעה של עדכון הרשומה: רלוונטי כמובן אם מתחזקים עמודה כזאת עבור כל עדכון. לא הייתי מגדיר טריגר כדי להחזיק את הערך בעמודה הזאת אם היא לא קיימת (עבור הצורך המסויים הזה).
• עמודת rowversion: אחד ה- data-types ב- SQL Server הוא rowversion (או בשמו הנרדף – timestamp), שמצמיד למעשה מספר גרסא לכל שורה. מספר הגרסא מתעדכן אוטומטית על כל שינוי ומניפולציה מול הטבלה, כך שאם שינינו שורה, הערך בעמודת ה- rowversion בהכרח גדל.  ניתן לשמור את הערך של ה- rowversion בצד, בעמודת varbinary( 8 ) ולמעשה לעקוב אחרי ערכים שגדולים מה- rowversion האחרות שראינו (ולעדכן עם הערך הגבוהה ביותר שראינו).

ואיך נבחר מבין האפשרויות האלה? פה זה קצת יותר טריקי. כל אינדקס שמתבסס על העמודות הללו, במידה שיש שינויים רבים, יקבל רמת פרגמנטציה הולכת וגדלה. בטבלה עם הרבה שינויים, יכולות להיות לזה השפעות לא חיוביות בכלל. מכיוון שהעמודות הללו כנראה לא יהיו עמודות clustered index (כי הן מועמדות לא טובות לזה), אז זה אומר שלעלות העבודה שלנו עם האינדקס תתווסף גם תמיד העלות של key-lookup, שזה כמובן מגדיל את העלות של הפעולה.

מעקב אחרי שינויים באמצעות שילוב של Temporal Tables

החל מ- SQL Server 2016, התווסף פיצ’ר חדש של temporal tables. אם הטבלה עליה אתם רוצים לעשות את ה- crawling היא temporal table, ניתן לעשות שילוב בין השיטות: להשתמש ב- ID מספרי או עמודת DateTimeCreated על הטבלה הראשית כדרך לעקוב אחרי שורות חדשות, ולעקוב אחרי שורות שהשתנו באמצעות crawling על ה- history table (אחרי שמוסיפים אינדקס על התאריך סיום תוקף של הרשומה). הרשומות שנראה ב- history table הן הגרסאות הישנות (כלומר, אלה שהוחלפו כתוצאה משינוי) , אבל נוכל באמצעות ה- primary key לדעת באיזה שורה היה השינוי ולשלוף את המידע החדש מהטבלה המלאה.
חשוב להדגיש – לא הייתי יוצר temporal table עבור הצורך הזה, אלא מדובר בהצעה של איך ניתן לעשות בו שימוש במידה שהוא קיים.

ביצועים

השיטה של “crawling” על הטבלה, מתבססת למעשה על polling מתמיד מול SQL Server. כלומר, כדי לדעת שהיה איזשהו שינוי, אנחנו צריכים להריץ את השאילתה שלנו שעוקבת ומקבלת שורות חדשות (עדכניות יותר מאלה שראתה, או עם ID גדול יותר וכו’ – בהתאם למה שהזכרתי קודם).
שיטות מבוססות Polling הן, ככלל, בעייתיות. עלות הביצועים שלהן נוטה להיות גבוהה יותר בד”כ ממנגנונים שהותאמו מראש ותוכננו מראש לעבוד בשיטות מבוססות push, ולמעשה יש לנו פה איזשהו תהליך שמורץ כל הזמן ו-“דוגם” כל הזמן דברים, כאשר מרבית העבודה שהוא עושה היא מיותרת ומהווה למעשה סוג של “busy wait” על מידע מהטבלאות.
עם זאת, מדובר בשיטה שקלה מאד למימוש, ולכן לא פעם נעדיף אותה. בנוסף, השליפות שאנחנו מריצים בצורה האת אמורות להיות מאד פשוטות וקלות, כאלה שלא באמת יעמיסו באיזושהי צורה על ה- DB.

ובכל זאת, הנה כמה כללי אצבע שיעזרו לכם למזער את ה- impact השלילי של ה- polling הזה מבחינת ביצועים:

• בחרו את תדירות ההרצה בצורה מושכלת: אם האפליקציה שמקבלת את המידע צריכה עדכון פעם במס’ שעות, אל תעשו את ה- polling כל שנייה. נסו להתאים את תדירות ה- polling לתדירות שאתם באמת צריכים.
• תוודאו שהשאילתה שאתם מריצים קלה להרצה: ה- cost של השאילתה שאתם מריצים צריך להיות מינימלי, כשהחשיבות של זה כמובן הולכת וגדלה ככל שאתם מריצים בתדירות גבוהה. אם אתם ממש מריצים את השאילתה הזאת ב- busy loop ודוגמים את הטבלאות ב- busy wait, אתם תעדיפו שהשאילתה הזאת תהיה מאד קלה.
• הימנעו משאילתות מורכבות: המשך ישיר לנקודה הקודמת, ורלוונטי במיוחד אם השאילתה מורצת בתדירות גבוהה – אתם רוצים שהיא תהיה כמה שיותר פשוטה. אם היא שאילתה מורכבת (כלומר שאילתה שעושה יותר מ- SELECT TOP X …. FROM TABLE WHERE ID > NUMBER) אז אתם חושפים את עצמכם ליותר בעיות בהווה, ובעיות פוטנציאליות בעתיד. גם אם התהליך שלכם אמור לקבל למעשה תוצאה של שאילתה מורכבת, שכוללת JOIN-ים, grouping, window functions ומה לא – השאילתה שאתם מריצים בתדירות גבוהה היא לא המקום לזה.
  תזהו מה הטבלה שכניסת מידע אליה מהווה טריגר לתהליך שלכם ואומר שיש טעם להריץ את השאילה הגדולה, תעשו את ה- polling על שליפה פשוטה של עדכונים לטבלה הזאת, ורק במידה שיש ערכים חדשים – רק אז תבצעו את השאילתה המאסיבית.
  גם אם אתם רואים שהשאילתה המאסיבית עובדת מהר כשאתם מריצים אותה עכשיו, אל תסמכו על זה. יכול להיות ש-execution plan קצת שונה כתוצאה מסטטיסטיקות שונות, יתחיל לגרום לזה שתנסו להריץ בלופ אינסופי שאילתה כבדה, שזמן הריצה שלה ארוך, ולכן גם לא ייתן לכם את המענה המיידי שאתם רוצים לעדכונים (בגלל הזמן שיעבור בין איטרציות הרצה שונות).
  בנוסף, נסו להימנע ממצב שיכול לקרות בשאילתות מורכבות שמצפות למידע שמגיע למס’ טבלאות, שבמסגרתו בגלל שלא מגיע מידע לאחת מהטבלאות, אתם לא מעדכנים את הערכים שאחריהם אתם עוקבים, מה שגורם לכך שכמות החומר שאתם כל פעם מנסים לשלוף ולבדוק גדלה.
• נסו לשפר את ביצועי ה- polling באמצעות ביצוע שלו ברמת השאילתה: דמיינו תרחיש די סטנדרטי, במסגרתו יש לכם אפליקציה, שהיא זאת שעושה את ה- crawling מול ה- MSSQL. מימוש נאיבי ומאד בעייתי של זה, הוא להחזיק thread למשל שעושה while(true) ובפנים ניגש, מנסה לקבל נתונים חדשים ולטפל בהם. במידה שאין נתונים, הוא נכנס לעוד איטרציה של הלולאה. וכך הלאה לעד. 
  מימוש כזה למעשה גורם לכם לחוות את הבעיות ב- polling בכל המקומות. גם בשרת SQL Server עצמו, אבל גם באפליקציה שצורכת את המידע, שעושה busy-wait על מידע מהטבלאות.
  דרך למזער את זה, היא להעביר חלק מה- polling להתבצע ברמת ה- MSSQL, למשל באמצעות שאילתה כזאת:
  

  DECLARE @lastId int = 6000

  DECLARE @attempts int=0

  WHILE NOT EXISTS (SELECT NULL FROM collectedData WHERE ID > @lastId) AND @attempts < 20

  BEGIN

      WAITFOR DELAY '00:00:00.500'

      SET @attempts=@attempts+1

  END

  SELECT  *

  FROM collectedData

  WHERE ID > @lastId

  החלק המעניין של מה שאנחנו עושים פה, זה שבמקרה שאין שורות (שבלא מעט אפליקציות, זה יהיה המצב רוב המקרים, אבל נרצה להגיב בכל זאת מהר ברגע שיש שורות), אנחנו פשוט משתמשים ב- WAITFOR כדי “לישון” ברמת ה- MSSQL במשך חצי שנייה, ואז לנסות שוב לבדוק אם יש איזשהם ערכים. אם יש, אז נחזיר אותם. בכל אופן, אחרי 10 שניות במקרה הזה, נסיים את הריצה (וניתן לאפליקציה הזדמנות להפעיל את זה מחדש).
  החסרון של השיטה הזאת, הוא שאנחנו מחזיקים ליותר זמן connection-ים מול ה- DB, ושבמקום ישר “להביא” את המידע, אנחנו קודם בודקים האם יש מידע להביא.  החלק הראשון הוא לא ממש חסרון, כי במילא היינו מהר מאד משתמשים ב- connection הזה מחדש לטובת הרצת אותה שליפה (שוב, polling…). החלק השני נכון – במידה שיש מידע, אנחנו מריצים פעולה נוספת (של ה- EXISTS) לפני שאנחנו פונים ללהביא אותו.  עם זאת, זה לא ממש משנה, כי אנחנו מניחים שברוב הריצות שלנו לא יהיה מידע חדש. ובכל אופן, שליפה שעובדת ישר מול האינדקס (וגם לא דורשת key lookup במקרה של nonclustered-index) תהיה לנו מאד זולה בלי קשר.

  מה היתרון פה, שבגללו אני כן מציע את זה?

  • העלות של האפליקציה “לחדש” את הריצה של השאילתה היא גבוהה יותר, ומערבת round-trip רשתי, איפוס של ה- session (שמתבצע תמיד לפני מיחזור של connection שנשמר כחלק מה- connection pool) וכו’. פה אנחנו מקטינים את העלות הזאת, כי אנחנו מעבירים למעשה חלק מה- busy wait להיות בצד של ה- MSSQL.
  • אנחנו מקלים על פיתוח נכון יותר בצד של הקליינט. הרי “לגנן” את התהליך הזה ולחדש אותו, זה לא משהו שמעניין את הקליינט (במובן שלא על זה נרצה להשקיע CPU של האפליקציה). אם הקליינט כתוב נכון, ועושה שימוש ביכולות של IO אסינכרוני (למשל, עושה שימוש ב- async/await במידה שהוא ממומש ב- C# בעת העבודה מול ה- SQL Server), הוא בהחלט יכול לנצל את הזמן CPU לעשות דברים מעניינים יותר מאשר לקרוא ולהריץ את התהליך של ה- polling מחדש.
• תעבדו עם Read Committed Snapshot Isolation: אחרת תיצרו לכם התנגשות בין הכתיבות לבין הקריאות האינטנסיביות.
• תעבדו ב- batch-ים: תוודאו שאתם לא שולפים כל מה שעונה על התנאי, אלא עובדים ב- batch-ים. זה יאפשר לכם לשלוט על עלות השליפה והזמן שלה, ולהפוך את זה ליותר דטרמיניסטי.

טריגרים

יכולת נוספת, שאני מתאר לעצמי שכולם מכירים זה טריגרים. במשפט אחד, עבור מי שלא מכיר, מדובר ביכולת שלנו לכתוב קוד שיורץ בתגובה לאירועים שונים שקורים ב- DB. בין אם DDL Triggers שקורים בתגובה לפעולות שונות ברמת הסכימה של ה-DB ופעולות על אובייקטים שונים בשרת, ובין אם DML Triggers שקורים בתגובה למניפולציה על נתונים (הוספה, עדכון, מחיקה).
ה-DML Triggers מתחלקים למשפחת ה- INSTEAD OF שמאפשרות לנו להחליף את ההתנהגות של פעולות (למשל, של פעולת INSERT) בקוד משלנו ו- AFTER triggers שקורים לאחר שפעולות אלה מתבצעות לפי הלוגיקה הרגילה.

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

• הטריגר הוא למעשה חלק אינטגרלי מכל טרנזקציה שמתבצעת. כלומר, במקרה שהטריגר נכשל – הטרנזקציה עצמה נכשלת ועושה rollback.
• הטריגר הופך לחלק אינטגרלי מכל פעולה, ויכולות להיות לו השפעות דרמטיות מבחינת ביצועים. אם אתם מבצעים בטריגר שרץ אחרי כל INSERT/UPDATE/DELETE פעולה, היא תתבצע אחרי כל INSERT/UPDATE/DELETE שלכם, ויכולה להיות לה השפעה לרעה על הביצועים. כל טעות בהקשר הזה יכולה להפוך למאד יקרה.
• קשה יותר לדבג ולהבין בעיות ביצועים שצצות בעתיד כתוצאה מקיום טריגר.
• טריגר לא בהכרח רץ: יש כל מיני מצבים (למשל שימוש ב- BULK INSERT) שבהם ריצת טריגר לא מובטחת (ולמעשה, ב- default הוא לא ירוץ).

אז עכשיו אחרי שאנחנו יודעים שאנחנו יכולים לעשות שימוש במנגנון הזה של הטריגרים, השאלה היא מה נעשה. יש לנו כמה אפשרויות:

• לעשות את הפעולה אנחנו רוצים לעשות כבר כחלק מהטריגר – כלומר אם יש איזשהו תהליך שאנחנו רוצים לעשות כתגובה לשינויים, אנחנו יכולים לכאורה לעשות אותו בטריגר. זה רעיון די גרוע, כי כמעט תמיד הפעולות הללו לא יהיו זולות. המשמעות של זה יכולה להיות האה דרמטית של פעולות אלמנטריות שאנחנו עושים מול ה- DB, ואפילו תוספת סיכון לפעולות הללו – יכול להיות שהם לא יצליחו.
  אם התהליך הנוסף שרוצים לעשות הוא חלק מובנה ומובהק משלב העדכון, אז כבר היינו עושים את זה כחלק ממנו (עם ה-SP שעושה את העדכון, או במימוש של ה- API שאנחנו חושפים החוצה שעושה את פעולת העדכון הזה). אחרת, הוא כנראה לא חלק אינהרנטי מהתהליך, ובטח שלא צריך להכשיל אותו על תקלה זמנית (למשל) במערכת שאמורהה לקבל את המידע.
• להכניס את המידע על זה שהיה שינוי לטבלה אחרת: אנחנו יכולים לעשות טבלה שמעתדכת את השינויים שקרו, ובטריגר פשוט לכתוב אליה רשומה שמכילה למשל את ה- primary key שרלוונטי לשינוי. יש תרחישים שבהם דבר כזה יכול להיות הגיוני (ואפשר לגרום לו להיות זול יותר, למשל עם memory optimized tables), אבל בד”כ זה יהיה תרחיש די יקר. כי בסופו של דבר אנחנו נשלם את המחיר של הטבלה הנוספת, רק כדי להגיע לפיתרון של crawling על הטבלה שמתעדת את השינויים. ואם כבר crawling – למה אנחנו צריכים את הטריגר באמצע?
• להשתמש ב- SQL CLR כדי לכתוב ל- message broker חיצוני (למשל, RabbitMQ) מידע על השינוי לאיזשהו exchange/queue שהאפליקציה(/ות) האחרת שמצפה למידע מאזינה לו. זה פיתרון אפשרי,  אבל כאמור מדובר בתוספת overhead מאד משמעותית לתהליך העבודה מול ה- DB, שלא בטוח שזה המקום עבורה. כלומר, פיתרון שמבוסס על message broker הוא מבורך והרבה פעמים הוא הפיתרון הטוב ביותר, אבל בד”כ יחסי הכוחות צריכים להיות שונים: לא שה-DB מעדכן את ה- message broker לאחר שהעדכון מתבצע אליו, אלא שה- DB  מוזן בעדכון בעצמו כתוצאה מהודעה שנשלחה ב- message broker (ואז למשל האפליקציה שמבצעת את טעינת הנתונים קיבלה אותה, ועדכנה את הנתונים ב- DB).
• על סמך הפתרונות הקודמים שהצענו והחסרונות שעלו מהם, אפשר להבין שהפיתרון האידיאלי מבחינתנו יהיה איזשהו שילוב שיאפשר לנו לענות על הדרישות הבאות: אנחנו רוצים לעדכן מישהו שהיה שינוי בטריגר, רוצים שאותו אחד יידע להעביר את המידע החדש ב- push לאלה שממתינים לו (אחרת אנחנו מגיעים שוב לפיתרון של ה- crawling) ומעדיפים שזה יקרה בתוך תחומי ה- SQL Server שלנו (כדי להקטין את הסיכוי לכשלונות חיצוניים, ואת ה- overhead שאנחנו מוסיפים מבחינת ביצועים).
  למעשה, זה מוביל אותנו למנגנון ה– SQL Server Service Broker – מנגנון תורים מובנה ב- SQL Server, שבאופן כללי אין לי דברים הרבה יותר מדי טובים להגיד עליו. הוא over-engineered ביחס למה שהוא מספק, הוא לא מאד נוח, יש לו מס’ חסרונות ובאופן כללי, לא הייתי ממליץ עליו כ- message broker. אבל, בתרחיש המסויים הזה שעליו אנחנו מדברים עכשיו, הוא בהחלט יכול להתאים.
  אני לא רוצה להיכנס פה למדריך על איך משתמשים בו, ובתיעוד הרשמי יש מספיק מידע. אבל אני כן רוצה להתייחס לפרוייקט open-source נחמד בשם SqlTableDependency  שעוטף ביחד את הטריגרים, את השימוש ב- service broker ומאפשר לקבל על זה נוטיפיקציה בצורה נוחה מקוד C# (בתצורה של event-ים), בלי להתייחס להקמה של הסיפור מסביב. מי שמפתח ב- C# ורוצה לממש בצורה מהירה פיתרון מהתצורה הזאת, ממליץ לו בחום לבחון את הפרוייקט הזה.

הביצועים שנקבל כמובן יושפעו מאד מהפיתרון שנבחר, ומצורת המימוש שלו. 
אם נבחר בפיתרון שמבוסס על לתחזק טבלה משלנו בצד שבנפרד יתבצע מולה crawling ע”י התהליך שורצה לקבל את הדלתאות, נשלם את עלות ההכנסה של המידע לטבלה ההיא (שמושפע גם הוא משאלות כמו אינדקסים, האם memory optimized, האם delayed durability וכל הדברים הרגילים שמשפיעים על כתיבה לטבלה). 
אם נבחר לעבוד מול message broker חיצוני כלשהו (כמו RabbitMQ, למשל) נשלם את ה- overhead של ה- SQL CLR. גם הביצועים מול ה- message broker תלויים בד”כ בכל מיני שאלות (האם ההודעה נשלחת בתור הודעה ‘רגילה’ שקיימת רק ב- RAM או בתור הודעה durable שגם נכתבת לדיסק, למשל).
אם נבחר לעבוד מול ה- SQL Server Service Broker, ה- overhead שלנו יהיה ביצועי הכתיבה מולו שמושפעים ממספר גורמים שניתן לקרוא עליהם עוד כאן.

בכל אופן, בגלל ההשפעה הרוחבית של פיתרון מבוסס טריגרים על כל פעולות ה- CRUD (Create, Update, Delete) מול הטבלה הרלוונטית, אני נוטה שלא להמליץ על פיתרון הטריגרים כפיתרון גורף. למעשה, טריגרים בכלל הם דבר שצריך לשקול מתי להתשמש בו בתבונה, ובמקרה הזה אני פחות ממליץ על טריגרים.
לגרום לטריגרים לעבוד כפיתרון “push” דורש שימוש ב- Service Broker שמבין ה- brokers השונים שראיתי הוא נוטה להיות מצד אחד מורכב, ומצד שני לא באמת תחרות טובה לפתרונות מתחרים (פרט כמובן ליתרון המובהק שלו שהוא מובנה ב- SQL Server). עם זאת,  אם בוחרים לממש פיתרון טריגרים שלא מבוסס כעבודה כ- push, אז אין לו יתרון ממשי על פני crawling (כי זה אומר שעדיין יתבצע crawling על משהו, למשל על הטבלה שאליה נכניס את המידע לטריגרים).
כמובן, שאם אתם משתמש באיזשהו 3rd party application שעוטף עבורכם יפה את השימוש בטריגרים עם Service Broker או פיתרון מקביל, ומייצר מזה פיתרון שבו אתם רק משתמשים כ- black-box, אז צריך לשקול כל מקרה לגופו.

Change Tracking & Change Data Capture

שני המנגנונים הבאים שאני רוצה לדבר עליהם הם Change Tracking (או בקיצור CT) ו- Change Data Capture (או בקיצור CDC). שניהם מנגנונים שהמטרה שלהם היא לאפשר להריץ שאילתה שנותנת לקבל תשובה לשאלה “מה השתנה ב-DB מאז הפעם האחרונה שבדקתי” באמצעות מנגנון מובנה.

ההבדל בינהם ש- Change Tracking, המנגנון הזול בינהם (מבחינת ביצועים), יודע לספר באיזה שורות היה שינוי (ואם רוצים אז גם באיזה עמודות), כאשר הוא מתעד את עצם השינוי (אבל לא את השינוי עצמו, אלא רק את עמודות ה- primary key של השורה הרלוונטית). את השורה עצמה נקבל מהטבלה, כאשר נסתכל על המידע שיש שם עכשיו – כלומר השורה העדכנית ביותר (אם למשל היה באמצע 10 שינויים, נראה רק את המצב הנוכחי, לא נדע את כל הוריאציות שהוא עבר בדרך).
לעומתו, Change Data Capture מאפשר לנו לדעת כל וריאציה שעברו הנתונים  בדרך. מטבע הדברים, ה- overhead שלו מבחינת ביצועים ושמירת המידע גדול משמעותית.

מה שמשותף הוא קונספט הפעולה – בשניהם נבצע למעשה polling על גרסאות, כדי לקבל את המידע והשינויים.

Change Tracking

כדי לאפשר Change Tracking נצטרך קודם כל לאפשר על ה- DB (בשם Sample בדוגמא שלנו) ולאחר מכן על הטבלה הספיציפית שבה אנחנו רוצים לעשות שימוש (Test1 במקרה שלנו):

ALTER DATABASE [Sample] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

GO

ALTER TABLE Test1 ENABLE CHANGE_TRACKING

לאחר מכן, תהליך ה- polling כדי לקבל את המידע שהשתנה בטבלאות פשוט, ומסתמך על זה שאנחנו מאכסנים ערך bigint שמספר את “מספר הגרסא” האחרון שראינו של הטבלה (המספר גרסא ב- Change Tracking הוא גלובאלי עבור ה- DB, אבל אנחנו מתשאלים אותו כל פעם על השינויים שהתבצעו בטבלה ספיציפית. ולכן, אנחנו צריכים לשמור את מספר הגרסא ברוזולוציה של טבלה שטיפלנו בה):

• שולפים מהטבלת state שלנו את מספר הגרסא האחרון ב- tracking שלנו עבור הטבלה. נסמן את המס’ הזה כ- X.
• לוקחים מספר גרסא נוכחי (באמצעות הפונקציה CHANGE_TRACKING_CURRENT_VERSION). נסמן את המס’ הזה כ-Y.
• מבקשים לקבל את השינויים שהתבצעו בטבלה שאחריה אנחנו עוקבים מאז X
• עושים איתם מה שאנחנו רוצים
• מעדכנים את הערך בטבלת ה- state שלנו (או מוסיפים, בהתאם לאיך שאנחנו מתחזקים אותה) כך שבפעם הבאה נבקש כבר מגרסא Y.

איך זה נראה בפועל? משהו בסגנון הזה (Test1, כאמור, זאת הטבלה שאחריה אנחנו עוקבים ו- VersionTracking טבלה שמשמשת אותנו לנהל את מספרי הגרסאות האחרונים שראינו):

BEGIN TRANSACTION

 DECLARE @currentVersion bigint = (SELECT TOP 1 LastVersion FROM VersionTracking WHERE TableName='Test1')

 DECLARE @newVersion bigint = CHANGE_TRACKING_CURRENT_VERSION()

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, @currentVersion) CT ON Test1.EmployeeID = CT.EmployeeID

UPDATE VersionTracking SET LastVersion = @newVersion WHERE TableName='Test1'

COMMIT

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

ניתן לשים לב שלמשל במקרה של מחיקת שורה, יש לנו רק את הערך של ה- primary key, שנשמר ע”י מנגנון ה- change tracking. אין לנו את הערך של ה- Name וה- Position, מכיוון שהם לא נשמרים כחלק מהמנגנון והשורה כבר נמחקה. לגבי השורה שעודכנה, חשוב לשים לב שגם אם היא עודכנה הרבה פעמים בין לבין – אנחנו נראה רק את הגרסא האחרונה.

מאחורי הקלעים, Change Tracking יודע גם לנקות באופן אוטומטי את ההיסטוריה שהוא שומר (לפי ה- retentioin שהגדרנו), והוא גם מציע לנו כלים לדעת האם “איבדנו” גרסאות באמצע (למשל, אם הרבה זמן לא לקחנו מידע על שינויים, והמספר גרסא שלנו הוא ישן יותר ממספר הגרסא הישנה יותר שיש – מה שאומר שאיבדנו דברים).

נשים לב שבסופו של דבר, מדובר באיזושהי שיטה שמבוססת על polling, כמו בשיטת ה- Crawling שדיברנו עליה קודם. כלומר, החסרונות של polling חלים גם פה (וגם הטיפים שדיברתי על איך אפשר כן לשפר את הביצועים כאשר עושים polling אגרסיבי, ובכלל זה גם ההמלצה החמה שתקפה גם פה לעבוד עם Read Committed Snapshot Isolation).

נשים לב, שמאחורי הקלעים, הצורה שהמנגנון הזה ממומש קצת מזכירה טריגר שפשוט מתעד לאיזושהי טבלת גרסאות את ה- primary key של מה שהשתנה. כמובן, שיש להניח שמכיוון שזה מבוצע ברמת ה- MSSQL עצמו זה יעיל יותר מטריגר (ועדיף אם זה מה שאתם מתכננים לעשות במילא). צורת המימוש הזאת נתמכת גם מה- execution plan של פעולת ה- INSERT (למשל) מול טבלה שמופעל עליה CHANGE TRACKING:

ניתן לראות שאנחנו מבצעים פה הוספה לאינדקס של איזושהי טבלה (שלא אנחנו יצרנו ולא אנחנו מתחזקים) ש-“תומכת” למעשה את המנגנון הזה של ה- change tracking מאחורה. כלומר, ה- overhead שזה מוסיף לנו לפעולות ה- CRUD קיים (כמובן), אבל שולי (ביחס לטכניקות אחרות, וגם ביחס לטריגר).

עם זאת, כאמור, מדובר בסופו של דבר בפיתרון מבוסס polling – עם היתרונות שבכך (ויש יתרונות לפעמים בקלות מימוש וכו’) ובעיקר עם החסרונות שבכך.

Change Data Capture

Change Tracking הוא נחמד מאד, אבל לפעמים לא מספיק לנו לדעת שהיה שינוי כלשהו (ולקבל את ה- primary key ובאמצעותו להסתכל על המידע המעודכן), אלא נרצה לדעת על כל שינוי שהיה ומה התוכן שלו. לצורך העניין, נרצה תיעוד מלא של כל הפעולות שהתבצעו. שורות שהתווספו, נמחקו (ואז נרצה לדעת גם מה היה התוכן שלהם לפני שנמחקו), התעדכנו (ואם הם התעדכנו כמה פעמים בין הפעם הקודמת שהסתכלנו על השינויים לבין עכשיו – נרצה לדעת את כל השינויים).

במקרה שזאת הדרישה, ניתן לעשות שימוש ב- Change Data Capture.

איך Change Data Capture עובד מאחורי הקלעים?

מאחורי הקלעים, המטרה של מנגנון ה- change data capture היא לשמור change table עבור כל instance של הפעלת Change Data Capture (או בקיצור, מעכשיו, CDC) על טבלה.  מה זה אומר? שמרגע שמפעילים CDC על טבלה, יש תהליך שדואג לעקוב אחרי כל השינויים שנעשים בטבלה ולתעד אותם (כולל כל הערכים, לא רק ה- primary key) בטבלה בצד.

מכיוון שהמטרה לדעת מצד אחד על כל שינוי שמתבצע, אבל מצד שני להפוך את התהליך ליותר א-סינכרוני, כך שישפיע פחות על הביצועים של העבודה מול הטבלאות באופן שוטף – הצורה שבה זה מתבצע היא ע”י תהליך שקורא את ה- transaction log, כל פעם קורא קבוצת טרנזקציות שתועדו ב- transaction log, מחלץ ממנה את המידע, ומה שהשפיע על טבלה שמופעל עליה CDC – מתועד ב- change table המתאים.
התהליך שקורא את ה- transaction log ומוציא ממנו מידע על הטרנזקציות, כמו גם תהליך שעושה rentention ל- data שנשמר, מופעלים כ- jobs ע”י SQL Server Agent.

הקונספט הזה יישמע מוכר למי שעובד או עבד עם Transactional Replication, ובצדק. מדובר למעשה באותו מנגנון פנימי של SQL Server של “פירסור” ה- transaction log וחילוץ מידע על שינוים ממנו, שגם ה-Transactional Replication Log Reader וגם CDC עושים בו שימוש (אם שניהם מופעלים על טבלה, אז למעשה ה- log reader לוקח אחריות על שני התהליכים).

איך מפעילים CDC על טבלה?

דבר ראשון, צריך לאפשר CDC ברמת ה- DB:

USE test4

EXEC sys.sp_cdc_enable_db 

אח”כ נפעיל על הטבלאות שמעניינות אותנו, במקרה הזה אני מפעיל על טבלה בשם TestCDC:

EXEC sys.sp_cdc_enable_table  

    @source_schema = N'dbo'  

  , @source_name = N'TestCDC'   

  , @capture_instance = N'CDCINST1'   

  , @supports_net_changes = 1  

  , @role_name = NULL

הפרמטרים שמעניינים פה (מעבר לאלה שהשמות שלהם מסבירים את עצמם) אלה capture_instance שמאפשר לנו למעשה לתת שם ייחודי ל-instance המסויים הזה שאוסף מידע על שינויים ב- source table (יכולים להיות עד שני instance-ים לטבלה) ו- support_net_changes קובע האם אנחנו רוצים ש- CDC יבצע עבורנו אגרגציה של שינויים לשורה עם אותו ה- primary key, כאשר אנחנו מבקשים טווח של שינויים (כלומר, אם כשנבקש טווח מסויים של שינויים שכולל 10 שינויים לאותה השורה נקבל את כל ה- 10 שינויים, או רק את העדכני בטווח המסויים הזה של ה- LSN [לא בהכרח הערך של השורה העדכנית ביותר]).
מי שמעוניין בהסבר מקיף יותר על הפרמטרים, יכול לקרוא את התיעוד המלא.

אחרי שנריץ, יווצרו לנו 2 job-ים ב- sql server agent, של ה- capture ושל ה- cleanup עם פרמטרים דיפולטיים (ניתן כמובן לשנות), כמו גם פונקציות שיכולות לשמש לתשאול.

איך מתשאלים את השינויים?

אז אחרי שהפעלנו את ה- Change Data Capture על טבלה (TestCDC במקרה שלנו, ושם ה- instance הוא CDCINST1), נרצה לתשאל את השינויים.
כאמור, גם ב- CDC הקונספט של התשאול מבוסס על polling, כאשר הפרמטר שאחריו אנחנו “עוקבים” הוא ה- LSN, ה- log sequence number (המזהה של רשומות ב- transaction log). אנחנו יכולים לבקש למעשה את המידע על שינויים ולבקש את כל השינויים שנעשו בין LSN אחד ל-LSN אחר.  בד”כ, נרצה להחזיק טבלה בצד שתעזור לנו לעקוב אחרי ה- LSN-ים שתשאלנו בין ריצות שונות של התהליך שלנו שלוקח את הדלתאות. למשל, נחזיק טבלה בשם CDCTracking במבנה הזה:

ובשביל לתשאל את דלתאות השינויים (אחרי שאיתחלנו ערך ראשוני של LSN בטבלה) נריץ את השאילתה הבאה:

DECLARE @instanceName varchar(50)='TestCDC_1'

DECLARE @lastLSN binary(10) = (SELECT LastLSN FROM CDCTracking WHERE InstanceName=@instanceName)

DECLARE @toLSN binary(10) = sys.fn_cdc_get_max_lsn()

SELECT * 

FROM cdc.fn_cdc_get_all_changes_TestCDC_1(@lastLSN, @toLSN, N'all');  

UPDATE CDCTracking SET LastLSN=@toLSN WHERE InstanceName=@instanceName

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

תשובה לדוגמא מהשאילתה תראה כך:

כאשר שימו לב שניתן לדעות מה הפעולה שנעשה (הוספה-2, עדכון [מוצגים הערכים של השורות החדשות]-4, מחיקה-1), ולראות את הערכים השונים בכל שלב. אפשר גם לראות מה העמודות שהושפעו (את ה- mask ניתן לפענח באמצעות fn_cdc_has_column_changed).

אם רוצים, אפשר להסתכל ישירות גם על ה- change table שייקרא, במקרה שלנו (עבור instance בשם TestCDC_1 יהיה cdc.TestCDC_1_CT.

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

השפעות ביצועים

• ברגע שמפעילים את ה- change data capture על DB, אפילו אם ה- recovery model הוא SIMPLE, נתונים של טרנזקציות שהסתיימו לא יתנקו לפני שה-CDC ייגע בהם. אם מסתכלים על העמודה log_reuse_wait_desc ב- sys.databases כאשר יש עיכוב ב- log truncation, הסיבה שתופיע תהיה REPLICATION. במצבים של טעינות גדולות של מידע, בהחלט יכולה להיגרם התנפחות משמעותית מאד של ה- transaction log.
• ברגע שמפעילים CDC, אז גם פעולות שבאופן רגיל היו minimally logged (תחת SIMPLE ו- BULK LOGGED) יהיו fully logged, כלומר יתבצעו הרבה יותר כתיבות. הסיבה היא שב- minimally logged ה- data לא נכתב ל- transaction log, אלא בעיקר מידע על אילקוצים – מה שאומר שלא יהיה מספיק מידע כדי לייצר את טבלת השינויים.
  כלומר, במצבים של טעינת כמויות גדולות של מידע, ההתנפחות של ה- transaction log תהיה משמעותית יותר ממה שאתם מצפים (וכאמור, לא מתבצע log truncation לפני שהמידע תועד ב- change table).
• בסופו של דבר, כל טרנזקציה מתבטאת בכתיבת נתונים ל-change table, כלומר בפועל אתם מכפילים את הכתיבות. גם מבחינת נפח, גם מבחינת ביצועים.
• התהליך של ה- CDC הוא אסינכרוני ביחס לפעולת ה- DML שהוא מתעד, בין היתר כדי לא לפגוע בביצועים של השאילתות שמבצעות את פעולת עדכון המידע. אולם תזכרו, שאם אתם עושים פעולות כבדות בזמן t0 וה- CDC מתחיל לעבוד בזמן t1, אז יכול להיות שהוא ממשיך לעבוד בזמן t2 כשאתם ממשיכים ועושים פעולות כבדות חדשות – וככה למעשה כן יוצר הכבדה כללית על ה- DB שלכם.
• יכולים להיות פערי זמנים גדולים בין מתי שמתבצע השינוי, למתי שהוא ישתקף ב- CDC (כתלות בעומס על ה- DB וכמה פיגור יוצר ה-CDC מאחור).
  במצבים של DB-ים שעמוסים תמידית ומבצעים טעינות גדולות של מידע על בסיס קבוע – CDC כנראה לא יתאים מבחינת ביצועים כי הוא עלול להיכנס לפיגור כרוני, מה שיפגע בביצועים ועלול גם לפגוע בזמינות ה- DB (התפוצצות של transaction log, למשל).
• ההערות על polling שהופיעו קודם בפוסט, וההשפעות של זה על ביצועים, תקפות גם פה.
• ה-capture job מייצר נעילות, גם על ה- change table, גם לפעמים נעילות סכימה על ה- DB. יש לזה השפעות מאטות כשיש הרבה שינויים ב- DB.
• אם אתם עושים שימוש ב- CDC, מומלץ לקרוא את המאמר הזה כדי ללמוד קצת על נושא הביצועים ב-CDC מניסויים ו- benchmarks שהריצו מיקרוסופט.

הערות כלליות

• לטובת תאימות של מי שקורא את המידע מהפונקציות של ה-CDC, שינויי סכימה לא משתקפים כל עוד לא מייצרים את ה- capture instance מחדש (מוחקים ועושים חדש). ניתן לקרוא על זה עוד כאן.
• במסגרת התהליך שמתשאל על שינוים, רצוי לוודא גם שלא מאבדים מידע (כלומר, שה- LSN הכי ישן שאנחנו רוצים לקבל ממנו חומר, לא יותר ישן מה-LSN הכי ישן שעבורו קיים חומר – כלומר שבמסגרת ה- rentention איבדנו חומר, ולכן אנחנו עלולים להסתכל על תמונה שמבחינתנו היא חלקית). ניתן לעשות את זה באמצעות השוואה ל- min lsn.
• CDC דורש ש- SQL Server Agent יפעל (אם הוא לא פועל מאיזושהי סיבה, ה- job שקורא את ה- transaction log לא יורץ, וסתם ייערמו לכם דברים)
• מאד מומלץ לקרוא את התיעוד בעיון לפני שמפעילים CDC.

Query Notification

אם עד עכשיו ראינו (בעיקר) פתרונות מבוססי polling מסוגים שונים, אז עכשיו נכיר פיתרון שמבוסס הודעות push – ה- Query Notification. מדובר במנגנון שמשולב ב- SQL Server וב- ODBC Driver של SQL Server לפלטפורמות השונות, שמאפשר למעשה להריץ איזושהי שאילתת SELECT (בתנאי שהיא עונה על כמה קריטריונים) ולקבל result-set של התשובות באופן רגיל. אולם, מעבר לקבלת ה- result-set הראשוני, כל פעם שה- reseult-set הופך ל-“לא עדכני”, כלומר היה שונה אם היינו שולפים אותו ברגע זה, נקבל על זה עדכון (באמצעות תור מיוחד של ה- SQL Server Service Broker שעליו נאזין). בעקבות העדכון, נוכל להריץ את השאילתה שוב ולקבל למעשה את התוצאות מחדש “ולרענן” לנו את המידע.

כדי לאפשר Query Notification, נצטרך לאפשר קודם את SQL Server Service Broker ברמת ה- DB:

ALTER DATABASE Sample  SET ENABLE_BROKER

דוגמת קוד C# שיכולה להמחיש את הנושא:

var connectionString = "database=Sample;server=.;Integrated Security=SSPI";

SqlDependency.Start(connectionString);

using (var conn = new SqlConnection(connectionString))

{

   conn.Open();

   string query = "SELECT ID, FirstName, LastName FROM dbo.Students WHERE ID >5";

   var cmd = conn.CreateCommand();

   cmd.CommandText = query;

   cmd.CommandType = CommandType.Text;

   SqlDependency dep = new SqlDependency(cmd);

   dep.OnChange += (sender, eventArgs) =>

   {

       //we'll get into this handler when data is changed in any way

       Console.WriteLine("Data Changed!");

       //here we'll re-run the query to get the updated data

       //we'll need to re-subscribe for changes

   };

   using (var reader = cmd.ExecuteReader())

   {

       while (reader.Read())

       {

       }

   }

   //do something with the reader and the original data

   Console.Read();

}

אנחנו משתמשים פה באובייקט SqlDependency שמממש מאחורי הקלעים את הממשק שמתואר במסמך הזה (תחת SQL ServerNative Client ODBC Driver) וחושף לנו אותו עבור שימוש מ- ADO.NET.

מה שיקרה עכשיו, זה שלאחר ההרצה הראשונית של השאילתה, על כל שינוי שמשפיע על איך היה נראה ה- result-set שהיה חוזר אם היינו מריצים את השאילתה עכשיו, ה- event היה נקרא, ואז היינו יכולים להריץ את השאילתה מחדש ולטפל בדברים שהשתנו/התווספו.

יש לשים לב שלא כל שאילתה נתמכת. למעשה, יש רשימת מגבלות לא קצרה. אם תייצרו שאילתה שלא עונה על התנאים, אז תקבלו מיד את ה- event בחזרה על זה שהתקבל query notification (בלי קשר לשינוי שהתבצע או שלא).

מאחורי הקלעים, עבור טבלאות שנעשה בהם שימוש במנגנון של Query Notification, מתווסף בכל פעולת DML איזשהו חלק ל- Execution Plan שעושה למעשה את העדכון על כך שהתבצע שינוי (ניתן לראות פעולות ה- DELETE מהטבלאות query_notification). זה לא באמצעות טריגר, אלא מנגנון מובנה נפרד ב- SQL Server. הנה למשל טבלה שרצות מולה שתי שאילתות שונות עם Query Notification (אם זה אותה שאילתה ואותו משתמש אז כן נעשה איזשהו re-use):

בצד של ה- client, שממתין לעדכונים, הוא למעשה עושה RECEIVE מול ה- SQL Server Service Broker וממתין לעדכונים שמספרים לו שקרה שינוי (ללא מידע על השורות והמידע שהשתנה). כלומר, אם נפרוט למעשה את העלויות המעורבות במנגנון הזה:

1. כתיבת הנוטיפיקציה כחלק מכל שינוי לטבלאות המתאימות של ה- service broker
2. העברה שלהן ל- queue המתאים (מתבצע ע”י ה- service broker)
3. קבלה שלהם ע”י ה- client
4. ובשלב הזה ה- client רק יודע שהיה שינוי, בשביל לקבל את השינוי הוא צריך להריץ את השאילתה שוב – מה שעלול להיות גם כבד (בהתאם לשאילתה).

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

הערה: על אף שהדוגמא שנתתי היא ב-C#, כמובן שניתן לעשות שימוש בזה לא רק מקוד C#, אלא ניתן לעשות שימוש במנגנון הזה מכל אפליקציה שמתחברת באמצעות ה- ODBC Driver של SQL Server. לדוגמאות על איך מממשים (עצמאית, באמצעות ה- API של ה-ODBC DRIVER) את הקריאות הרלוונטיות ניתן לראות כאן.

התאמת ה- Design של האפליקציה העיקרית שמקבלת את המידע לדיווחים

כל האפשרויות שסקרתי עד עכשיו, עבדו מתוך אותה נקודת ההנחה – SQL Server הוא ה-“אמת המוחלטת” של הנתונים, הוא המקום שאליו הם מתנקזים בסוף, ולכן הוא זה שחייב לעדכן את כל מי שצריך לקבל מידע על שינויים בכך שהם היו שינויים.
זאת נקודת הנחה שקל לקחת אותה, במובן שהיא מבטיחה נכונות למעשה בתרחישים רבים מאד. אם מצאנו פיתרון שעונה לה, אז למעשה לא משנה איך נעשה את השינוי (אפליקציה קיימת, חדשה, שינויים ידניים, עבודות תחזוקה) – הוא ישתקף לאפליקציות חיצוניות.

נשים לב שראינו הרבה מאד דוגמאות. הרבה מאד דרכים לתקוף את הבעייה הזאת – אבל מכנה משותף אחד שהיה כמעט לכולם – הם רגישים לגודל. כלומר, הרבה מהם פתרונות מעולנים ומצויינים שקל לממש לא צריך לחשוב יותר מדי כשעובדים עם טבלאות בעלות היקף שינויים קטן, אבל עם כמויות השינויים גדולות – חלק מהדברים מתחילים לחרוק. הסיבה לכך פשוטה – אף אחד מהמנגנונים המובנים לא באמת מותאם לגודל, וכל המנגנונים שלא מובנים (כמו crawling וכו’) סובלים למעשה מבעייה אינהרנטית – הם לא מובנים, הם לא יודעים מה קורה (וכדי לא להעמיס, אנחנו לא משלבים משהו בתוך ה- SQL Server שיספר להם מה קורה).

במקרים כאלה, כדאי לשקול מחדש את הנחות המוצא. למשל, לחשוב אולי העדכונים הללו לא צריכים לצאת מ- SQL Server עצמו.

בחלק מהמקרים, שינוי של הדיזיין של האפליקציה שטוענת את המידע יכול לתת פיתרון טוב יותר לנושא “הפצת העדכונים” מכל אחד ואחד מהפתרונות שדיברנו עליהם פה.

למשל, ניתן לחשוב על דיזיין שבו האפליקציה שמקבלת את המידע שצריך להיטען, מפיצה אותו מעל איזושהי מערכת תורים (RabbitMQ, Kafka…) כאשר הטעינה ל-SQL Server עצמו, היא סה”כ עוד consumer של המידע הזה. consumer-ים נוספים, שמעוניינים לקבל את ה- feed של המידע, מקבלים אותו למעשה מהראביט, או מ- kafka, ולא משתמשים ב- SQL Server כערוץ להפצת המידע.
כמובן, הגישה הזאת מתאימה יותר למצבים של feed מתמשך של מידע, שבנוי בצורה כזאת שיש לו משמעות מלאה לאפליקציות האחרות שיקבלו אותו as-is (הן לא צריכות להסתכל בעצמן ב- DB כדי להביא מידע קשור, למשל, שיכול להיות שהוא עוד לא שם, כי ה- consumer שאחראי לטעינת המידע ל-DB עוד לא טען אותו). בגישה הזאת אנחנו למעשה אומרים שכל ה- consumer-ים מקבלים את המידע באופן בלתי תלוי, ושהמידע בינהם יהיה למעשה evantually consistent. ברגע נתון, ייתכן שחלק מהם יותר מעודכנים מהאחרים (מה שתמיד יקרה, אבל פה בפרט ייתכן שחלק מהם יותר מעודכנים מה- DB שאמור להכיל את “האמת”), אולם בסופו של דבר כולם שואפים להיות מעודכנים.

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

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

סיכום

סקרנו בפוסט הזה כמה שיטות שונות שהמטרה שלהן היא לאפשר לאפליקציות שונות להתעדכן במידע שנכנס או משתנה ב-DB שלנו. הסתכלנו על שיטות שונות:

• crawling על טבלאות
• עבודה עם טריגרים
• Change Tracking
• Change Data Capture
• Query Notification
• התאמת הדיזיין של האפליקציה

ראינו את היתרונות והחסרונות של כל שיטה, והבנו את העלויות שלהם.

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