מה הבעייה עם nvarchar(max) או varchar(max)

כולנו (או לפחות, כל מי שיצר טבלה ב- DB) מכיר את הרגע שבו הוא מגיע לקבוע data type של עמודה טקסטואלית, בוחר varchar/nvarchar וצריך לקבוע את הגודל המקסימלי. האם לשים 10? 20? 100? הרי אף אחד לא יודע מה יילד יום. וכשלא יודעים מה יקרה – יש כאלה שפשוט בוחרים ללכת על המקסימום. וככה נולדות להן הרבה עמודות nvarchar(max) / varchar(max), כי הרי למה להגביל היום ולחטוף את השגיאה של string or binary data would be truncated מתישהו בעתיד?
דרך נוספת שבה עמודות כאלה נולדות – היא משימוש ב- Entity Framework Code First ושאר ה-ORM-ים. אם מגדירים string property במודל, ומייצרים ממנו באמצעות EF Code First טבלה, אז Entity Framework שואף לשמר את ההתנהגות של System.String, שאינו מוגבל באורכו, ולכן מגדיר את העמודה בתור nvarchar(max) (ניתן כמובן להשתמש ב- StringLengthAttribute כדי להגדיר אורך שונה, אבל זאת פעולה נוספת, שהרבה מפתחים לא עושים).
לכאורה, באמת לא ברור מה המשמעות של ההגבלה. הרי כשאני כותב קוד ב- C#, אף אחד לא מבקש ממני להגיד מה גודל ה-string המקסימלי שאני מחזיק בזמן ההצהרה. למה שאני אדרש לכזה דבר בהגדרת הטבלה? ואם אני כבר נדרש, מה רע בלשים את ה- max?
אז ללבחור max יש כמה חסרונות, והמטרה של הפוסט הזה היא להתמקד בעיקריים שבהם.

אתם מקשים על MSSQL לייצר Execution Plan טוב

שאילתות ב- SQL מגדירות איך אנחנו רוצים שהתוצאה תיראה, ולא כיצד צריך לבצע את הפעולות כדי להגיע לתוצאה. מי שמחליט בסופו של דבר מבין שלל הדרכים להריץ את השליפה, באיזה דרך לבחור, הוא המנוע DB עצמו.
כדי להגיע להחלטה הזאת, הוא נעזר בשלל כלים – אחד הבסיסיים בהם הוא הסכימה של הטבלה, והמידע שקיים כחלק מהסכימה. למשל, אם מוגדרת על עמודה מסויימת unique constraint, זה מידע ש- SQL Server יכול להשתמש בו כדי לייצר execution plan. מידע נוסף שהוא משתמש בו, הוא הערכה של גודל הנתונים שיהיו מעורבים בביצוע. יש הבדל בין הדרכים האפקטיביות לבצע, למשל, JOIN של טבלה קטנה עם טבלה קטנה, טבלה קטנה עם גדולה או שתי טבלאות גדולות.
כדי להעריך את כמות הנתונים, בנוסף לסטטיסטיקות שונות, ה- query optimizer (הרכיב שבוחר את ה- execution plan) מסתמך גם על הגדרת הטבלה עצמה, כדי להעריך מה גודל שורה שמוחזר מכל אופרטור ב- execution plan. במקרה של ערכים שהגודל שלהם הוא קבוע (כמו int, bigint, nchar וכו’) – הוא יודע להגיד את הגודל המדוייק. במקרה של עמודות עם אורך משתנה, הוא נדרש לנחש, כאשר הבסיס לניחוש הוא הגודל המקסימלי המוצהר של העמודה.

לשם הדוגמא, לקחתי את טבלת Posts של StackOverflow, עם הנתונים מ- 2016 בלבד, (ניתן להוריד מכאן)  – והשוויתי בין שתי סכימות של הטבלה: בראשונה העמודה Title מוגדרת בתור nvarchar(250) ובשנייה בתור nvarchar(max).

הרצתי שתי שליפות:

SET STATISTICS IO ON

SET STATISTICS TIME ON

 

SELECT COUNT(DISTINCT Title)

FROM PostsMax a

 

GO

 

SELECT COUNT(DISTINCT Title)

FROM Posts a

 

השליפה הראשונה לקחה קצת יותר מ-29 שניות (90 שניות של CPU Time, ה-execution plan כלל parallelism), בעוד שהשליפה השנייה לקחה כ-5 שניות (18 שניות של CPU Time). למה הפער הכ”כ מהותי הזה?  כי ה- execution plan היה שונה. הסיבה לשוני היא  ה- estimation לגבי גודל השורה. הפער אפילו ברור יותר כאשר עושים SELECT פשוט מהטבלאות:

12

כלומר – אם אתם מגדירים סתם עמודות nvarchar(max) ו- varchar(max), או עמודות עם גדלים שמנותקים מהגודל האמיתי של הנתונים, אתם מקשרים על ה- query optimizer להביא execution plan טוב כי אתם מכריחים אותו להסתמך על נתונים שגויים. אם יודעים מה הגודל הצפוי – רצוי פשוט להגדיר אותו.

Compression

בפוסט הקודם נגעתי קצת ב- compression מזווית של דחיסת נתונים גדולים. בפרט, ציינתי ששימוש ב- row compression או page compression לא עובד באותה הצורה עבור עמודות שמוגדרות max (למשל, עמודות nvarchar(max) לא עוברות unicode compression). הנ”ל יכול לגרום לכם לאכסן סתם יותר מידע בדיסק, ולבזבז סתם יותר זיכרון ב- buffer pool – ולחינם (אם הגודל של המידע לא באמת מצדיק את זה).

והערה כללית לגבי שמירת ערכים גדולים ב- DB

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

  • הן נוטות יותר לפרגמנטציה (למשל בגללעדכונים שבהם משתנה גודל הטקסט, או בגלל ששורות חדשות ממוקמות ב- page נפרד כדי שיהיה מקום לכל השורה בתוך ה- page)
  • ה- data access pattern בשורות כאלה הוא פחות יעיל – כאשר הגודל של השורה עובר את ה- 8KB (הגודל של page בודד), MSSQL יפצל את השורה, ויעביר חלק מהמידע ל- overflow pages, כאשר בשורה עצמה יישמר pointer ל- overflow page. הקפיצות הללו יוצרות IO pattern לא יעיל, שרצוי להימנע ממנו
  • full table scans יהיו יותר איטיים – בגלל שהטבלה יותר גדולה, שליפות שדורשות full table scan (למשל, לצרכי reporting, או שליפות ad-hoc), יצטרכו לעבור על יותר data, גם אם ה- data המעניין הוא יחסית קטן. כמובן שיש לזה פתרונות שונים (בין אם לפצל את העמודות הגדולות לטבלאות נפרדות, אינדקסים על הערכים של העמודות הקטנות יותר, שימוש ב- columnstore indexes ועוד)
  • הערכים הגדולים, שנטענים לזיכרון ל- buffer pool, מגיעים על חשבון data שאולי חשוב לנו יותר שיישמר ב- cache, וגורמים לזה שמידע יפונה יותר מהר לדיסק – ולכן יידרש יותר IO.

במקרה כזה, כדאי לשקול פתרונות אחרים – שמירה של המידע בדיסק עם הפנייה מה- DB, שימוש ב- FILESTREAM tables, שמירה של המידע דחוס ב- DB ועוד.

SQL Server: שימוש בפונקציות COMPRESS ו- DECOMPRESS לטובת דחיסת נתונים

מזה מספר שנים, SQL Server מציע באופן מובנה יכולת של Data Compression. מדור ביכולת לדחיסת המידע בטבלאות ואינדקסים, בין אם “רגילים” (row store) או clustered / nonclusted columnstore indexes.
כאשר מדובר על דחיסה של אינדקסים “רגילים” – אנחנו יכולים לבחור בין דחיסה ברמת השורה – שהיא בעיקרה רלוונטית ל- fixed length data types, שהשמירה שלהם נעשית יותר יעילה במקום,  או ב- page level compression שמבצע בפועל row level compression של כלל השורות באותו ה- page, ומעל זה מוסיף רובד של prefix compression (שמקטין את הנפח במידה שיש ערכים מייצגים בעמודות, או לחלופין תחיליות משותפות) ו- dictionary compression (שמחליף ערכים חוזרים בהפנייה למילון שנשמר כחלק מה- metadata, ומקטין את הנפח במידה שיש חזרתיות גבוהה של ערכים ברמת ה- page).
בין אם משתמשים ב- row level compression או ב- page level compression, מופעלת גם היכולת של unicode compression שמקטינה את הנפח במידה שהעמודה מוגדרת בתור nvarchar או nchar, אולם בפועל עיקר הטקסט שבו נעשה שימוש לא באמת מתפרס על ה-2 בתים שמוקצים ב- UCS-2 לכל תו (כלומר, לאחר הדחיסה מתקבלת התנהגות שמזכירה יותר את UTF-16 במובן שלא נדרשים בפועל תמיד 2 בתים לכל תו, אלא ניתן להסתפק בתו בודד). זה יכול במקרים רבים לחסוך עד 50% (כי עבור הרבה מאד שפות, לא צריך להתפרס על 2 בתים כדי לייצג את התו).

אולם, יש מס’ חסרונות ל- page/row level compression, בעיקר כשבאמת רוצים לדחוס כמות גדולה של data. החיסרון הראשון הוא אופי הדחיסה – האלגוריתם של מיקרוסופט נבחר (ככה”נ, אני מין הסתם לא יודע את זה בוודאות) מתוך מחשבה על איזון בין חיסכון במקום לבין צריכת CPU בזמן ה- compression וה- decompression. מדובר באיזון חשוב, מאחר שחלק מהיתרונות של ה- compression הוא שהמידע נשמר דחוס גם ב- buffer pool, מה שנותן חיסכון בזיכרון – אבל שכפול של עלות ה-decompression בכל שימוש במידע. אולם, בהשוואה לאלגוריתמי דחיסה אחרים (DEFLATE), הוא מכיל פחות שלבים שיכולים לשפר את האפקטיביות של הדחיסה.
חיסרון נוסף, שנובע ככה”נ מאותו השיקול שהזכרתי קודם – מיקרוסופט בכלל לא מתיימרים לדחוס כמות גדולה של מידע- הדחיסה מתבצעת רק ל- in-row data, כלומר למידע שנשמר ב-page של השורה עצמה. מידע שנשמר ב- row-overflow page לא נדחס כלל. אגב, ה- unicode compession (ודחיסה באופן כללי) לא מופעל בכלל עבור עמודות שמוגדרות בתור varchar(max), nvarchar(max).

במרבית המקרים, ה- trade-off של מיקרוסופט בין העלות CPU של ה-compression/decompression ב- row/page level compression בהחלט נשמע הגיוני. אולם, יש מקרים שבהם אנחנו נעדיף דחיסה טובה משמעותית. מקרה כזה לדוגמא, הוא טבלאות שמכילות מידע שנשמר “אם יהיה צורך”. מדובר למשל בטבלאות, שמכילות עמודות nvarchar(max) עם איזשהו JSON גדול, שאמנם כל המידע המעניין ממנו כבר מחולץ ונשמר בטבלאות אחרות בצורה רלציונית סטנדרטית, אולם שומרים את ה- JSON קלט המלא למקרה שיהיה צורך לתשאל בדיוק איזשהו שדה שאולי לא חולץ, או לעשות פעולה אחרת על המידע שלא תוכננה מראש. אלה עמודות שאול אפשר היה לשמור אותן אפילו כקבצים על הדיסק, ולשמור רק את המיקום ב-DB, אבל מכיוון שיש את היכולת שליפות על ה- JSON-ים באופן מובנה בצורה נוחה ב- SQL Server, מעדיפים בכל זאת לשמור את המידע בטבלה – ולחסוך את הסקריפט שיאסוף את המידע מהדיסק במידת הצורך.
כלומר, מדובר במידע שאנחנו כנראה לא מעוניינים לתשאל (למעט אולי במקרים נדירים), ולכן לא אכפת לנו ממש מה-CPU – אבל כן רוצים לשמור. בדיוק עבור מקרים כאלה, יש לנו (החל מ-SQL Server 2016) את הפונקציות COMPRESS ו- DECOMPRESS.

הפונקציה COMPRESS מקבלת data ומחזירה אותו אחרי דחיסת GZIP, ו-DECOMPRESS מקבלת varbinary שמכיל את ה- GZIP, ומחזירה את ה- plain data שבתוכו.

Benchmark

לטובת ה- Benchmark, לקחתי את ה-DB לדוגמא שמבוסס על ה-DB של Stackoverflow וכולל את כל השאלות שנשאלו, והתשובות שנענו, בשנת 2016. כדי לייצג את הטבלה שלנו, זאת עם ה- JSON הגדול, אני אייצר טבלה שכוללת עבור כל tag שקיים באתר עמודה עם ה- Id של ה- tag ו-JSON שכולל את כל השאלות שנשאלו באותו ה- tag, ולכל שאלה עוד JSON פנימי שכולל את כל התשובות שנענו לאותה השאלה. כלומר, בסופו של דבר נקבל JSON-ים בגודל מכובד למדי.

ה-SQL כדי לייצר את הטבלה ולמלא אותה בנתונים נראה כך:

SET STATISTICS TIME ON

SET STATISTICS IO ON

 

CREATE TABLE [dbo].[TagsExtendedInfoUncompressed](

    [TagId] [int] NOT NULL,

    [TagName]  nvarchar(100) NOT NULL,

    [ExtendedInfoJson] nvarchar(max) NULL,

 CONSTRAINT [PK_TagsExtendedInfoUncompressed] PRIMARY KEY CLUSTERED (TagId)

) 

;with data as (

    SELECT

        TagId = t.Id,

        TagName = t.TagName,

        QuestionsJson =    (

                            SELECT 

                                    q.*,

                                    RepliesJson =    (

                                                        SELECT *

                                                        FROM Posts a WITH(FORCESEEK)

                                                        WHERE a.ParentId = q.Id

                                                        FOR JSON AUTO

                                                    )

                            FROM Posts q WITH(FORCESEEK)

                            JOIN PostsToTags ON PostsToTags.PostId = q.Id AND PostsToTags.TagId = t.Id

                            WHERE q.ParentId IS NULL

                            FOR JSON AUTO

                        ) 

    FROM Tags t

)

 

INSERT INTO [TagsExtendedInfoUncompressed](TagId, TagName, ExtendedInfoJson)

SELECT TagId, TagName,QuestionsJson

FROM data

זמן הריצה שלו היה 308 שניות, ה- CPU time היה 285 שניות  וגודל הטבלה שנוצרה בסוף הוא 8978MB (והיא כוללת 47,392 שורות – מקרה שבו ה- JSON-ים באמת מאד גדולים).

הרצה נוספת של אותו ה- SQL רק עם הוספה של DATA_COMPRESSION = PAGE להגדרת ה- primary key בטבלה, הסתיימה לאחר 281שניות (ה- CPU time היה 270 שניות) וגודל הטבלה 8766MB– שזה כמעט זהה לחלוטין לגודל הטבלה המקורית. למה זה המצב? כי הטבלה שלנו מכילה BLOB-ים גדולים, שה- page compression של SQL Server בכלל לא מיועד לעבוד עליהם, ולא נוגע בהם כלל. כלומר, ה-page compression של SQL Server לחלוטין לא אפקטיבי במקרה הזה.
למעשה, עבור כל JSON שהוא גדול מספיק כדי שיוגדר nvarchar(max), varchar(max) הדחיסה של SQL Server תהיה מאד לא אפקטיבית. לכן, אם יש לכם JSON-ים שאתם שומרים ואתם יודעים שהגודל יהיה פחות מ-8000 תווים עבור טקסט ללא יוניקוד, או 4000 תווים לטקסט עם יוניקוד – אל תגדירו את העמודות כ- max, כי יהיו לזה השפעות משמעותיות על הדחיסה שתקבלו.

אולם, במקרים כמו המקרה שבחרתי ל- test case הנ”ל – יש לנו באמת JSON-ים שעוברים את הגודל הזה (אפילו באופן משמעותי). כאמור, החל מ- SQL Server 2016 אנחנו יכולים לבחור לבצע GZIP Compression ל- data באמצעות הפונקציה COMPRESS, וכך להקטין את הגודל של ה- data.

נעשה עכשיו את אותה הבדיקה עם הפונקציה COMPRESS, ונריץ את ה- SQL הבא (שימו לב שהעמודה בטבלה מוגדרת כ- varbinary(max) במקרה הזה, וכמובן שאנחנו לא מפעילים גם page compression – אין היגיון בלנסות לדחוס מידע שהוא כבר דחוס):

SET STATISTICS TIME ON

SET STATISTICS IO ON

 

CREATE TABLE [dbo].[TagsExtendedInfoGzipCompressed](

    [TagId] [int] NOT NULL,

    [TagName]  nvarchar(100) NOT NULL,

    [ExtendedInfoJson] varbinary(max) NULL,

 CONSTRAINT [PK_TagsExtendedInfoGzipCompressed] PRIMARY KEY CLUSTERED (TagId) WITH(DATA_COMPRESSION=PAGE)

) 

;with data as (

    SELECT

        TagId = t.Id,

        TagName = t.TagName,

        QuestionsJson =    (

                            SELECT 

                                    q.*,

                                    RepliesJson =    (

                                                        SELECT *

                                                        FROM Posts a WITH(FORCESEEK)

                                                        WHERE a.ParentId = q.Id

                                                        FOR JSON AUTO

                                                    )

                            FROM Posts q WITH(FORCESEEK)

                            JOIN PostsToTags ON PostsToTags.PostId = q.Id AND PostsToTags.TagId = t.Id

                            WHERE q.ParentId IS NULL

                            FOR JSON AUTO

                        ) 

    FROM Tags t

)

 

INSERT INTO [TagsExtendedInfoGzipCompressed](TagId, TagName, ExtendedInfoJson)

SELECT TagId, TagName,COMPRESS(QuestionsJson)

FROM data

זמן הריצה במקרה הזה היה ארוך משמעותית בהשוואה להכנסת המידע ללא דחיסה, או עם page compression (שלא מייצר overhead במקרה הזה, מאחר שהוא לא נוגע ב-LOB, ולכן גם לא דוחס לנו כמעט בכלל).  הריצה לקחה 608 שניות, כמעט פי 2 מהריצה עם page compression. אולם, הדחיסה הייתה גם משמעותית יותר טובה – נפח הטבלה שהתקבלה הוא 908MB, שזה חיסכון של כ-90% בנפח המידע.

מבחינת השליפות, אם נריץ את השליפה הבאה:

SET STATISTICS IO ON

SET STATISTICS TIME ON 

SELECT JSON_VALUE(ExtendedInfoJson, '$[0].Id')

FROM [SO-2016].[dbo].[TagsExtendedInfoPageCompressed]

נגלה שעבודה כלשהי מול הטבלה עם ה- page compression עולה לנו (במקרה הזה) 5.3 שניות (מתוכן 1.5 זמן CPU).

אם, לעומת זאת, נעשה אותו הדבר מול הטבלה שדחוסה ב-GZIP, באמצעות השליפה הבאה:

SET STATISTICS IO ON

SET STATISTICS TIME ON 

SELECT JSON_VALUE(CAST(DECOMPRESS(ExtendedInfoJson) as nvarchar(max)), '$[0].Id')

FROM [SO-2016].[dbo].[TagsExtendedInfoGzipCompressed]

אז נצטרך להמתין משמעותית יותר זמן – 86 שניות – פי 16 יותר זמן.

Offloading של ה- Compression וה- Decompression

ראינו שהפונקציות COMPRESS ו- DECOMPRESS אינן זולות (כי דחיסת GZIP אינה זולה). אולם, העובדה שאנחנו עושים שימוש בשיטת דחיסה סטנדרטית, מאפשר לנו לעשות offload של הדחיסה (שהיא פעולה שיכולה להיות CPU intensive) מהשרת MSSQL שלנו, ל- client שמכניס או שולף. זאת בניגוד ל-page compression, שחייב להתבצע בצד של ה- MSSQL. ביצוע offload ל- client יכול להוריד עומס מה- MSSQL, לבזר את התהליך (אם יש מס’ תהליכים במס’ שרתים שמבצעים את ההכנסה), ולהקטין את התעבורה של המידע שעוברת על הקו (כי הוא כבר יעבור דחוס).

את הדחיסה ניתן לבצע מקוד .NET באמצעות שימוש ב- GZipStream. הדבר היחיד שחשוב לזכור הוא שבמידה שרוצים לדחוס טקסט יוניקודי, כדי לקבל את ה- byte array עליו נבצע את ה- compression יש להשתמש ב- Encoding.Unicode.GetBytes. שימוש ב- UTF-8, למשל, לא יאפשר cast של ה- data ל- nvarchar(max).
ביצוע offload של הדחיסה לאפליקציות שמבצעות את טעינת החומר, יכול להקל את ה- performance penalty שמתלווה לשימוש ב- GZIP Compression, ולמנוע מה- MSSQL להפוך ל- bottleneck באיזור הזה.

סיכום

ראינו שיש מקרים שבהם row/page compression הם פחות אפקטיביים – בין אם כי מדובר ב- LOB-ים, ובין אם כי נדרשת דחיסה טובה יותר מהדחיסה שהם מספקים. במקרים האלה, כאשר ה- data הוא “ארכיוני” באופיו (כזה שאין שליפות קבועות שמתבססות עליו), ניתן לעשות שימוש ב- GZIP Compression ב- SQL Server באמצעות הפונקציות COMPRESS/DECOMPRESS.

DbFunctions.TruncateTime ובעיית הביצועים המוחבאת

 

לאחרונה נתקלתי בבעיית ביצועים בעת עבודה עם Entity Framework שנגרמה כתוצאה משימוש במתודה DbFunctions.TruncateTime.image_thumb7

לטובת הדוגמא, בואו נסתכל על טבלה בשם Posts במבנה כמו בתמונה מצד שמאל.
הטבלה מכילהה מס’ עמודות, ובין היתר עמודת CreationDate מסוג datetime (או datetime2, פחות רלוונטי למקרה שלנו) שכוללת התאריך והזמן של יצירת רשומה.
נניח גם שהעמודה הזאת היא ה- clustered index של הטבלה (מדובר ב- clustered index הגיוני).

המטרה שלנו –  להביא את כל השורות שנוצרו בתאריך מסויים. למשל, נרצה להביא את כל השורות שנוצרו ב- 25.06.2016 (בלי חשיבות לזמן שבה הן נוצרו).
יש כמה דרכים אפשריות לעשות את זה. אפשר לעשות שאילתה על הטווח (בין ה-25 ל-26), או שיטה אחרת, אפשרית גם כן (למרות שגם יקרה קצת יותר בביצועים),  היא לעשות CAST לעמודה ל- date (ואז מאבדים למעשה את החלק של הזמן) ולהשוות לתאריך. למשל, השאילתה הבאה:

 

SELECT Id, Title, CreationDate

FROM Posts

WHERE CAST(CreationDate as date) = '2016-06-25'

אם אנחנו עושים שימוש בשליפה הזאת,  נקבל את ה- execution plan הבא:

image_thumb8

השליפה גם מסתיימת די מהר (150ms), דורשת מעט CPU (סהכ 31ms של CPU Time) ודורשת מעט מאד IO (סה”כ  190 logical reads), שזה כמובן מאד הגיוני – בסוף אנחנו ניגשים לטווח מאד ברור ב- clustered index שלנו.  כמובן, שהיינו יכולים לעשות את זה גם יותר מפורשות ולתת טווח זמנים (ואז היינו חוסכים לחלוטין את ה- nested loop), כמו למשל עם השאילתה הזאת:

SELECT Id, Title, CreationDate

FROM Posts

WHERE CreationDate >= '2016-06-25' AND CreationDate < '2016-06-26'

שאז ה- execution plan שלנו מורכב למעשה רק מאופרטור ה- Index Seek (לא נדרש לבצע כלל את ה- cast).

עד פה – שום דבר מעניין. אנחנו יכולים לשלוף תאריך ספיציפי, בלי מאמץ. אבל – מה קורה אם נכתוב את הקוד הבא באמצעות Entity Framework? אז כמובן, שאנחנו יכולים לכתוב שליפה שמקבילה לשליפה השנייה שהראיתי, ולתת טווח. שליפה כזאת תרוץ כמו שאנחנו מצפים.
אולם, מי שיחפש בגוגל איך עושים את זה, ימצא גם דוגמאות שמתבססות על שימוש ב- DbFunctions.TruncateTime – שעושה בדיוק את זה. מוציא את אלמנט הזמן, ומשאיר רק עם התאריך.  במקרה כזה, אפשר לכתוב את הקוד הבא:

using (var data = new Entities())

{

    DateTime dt = new DateTime(2016, 6, 26);

    var posts = data.Posts.Where(k => DbFunctions.TruncateTime(k.CreationDate) == dt)

       .Select(k => new { k.Id, k.Title, k.CreationDate }).ToArray();

}

לפני שאני אראה לכם את השאילתה שנוצרת כתוצאה מהקוד הזה, אני אספר לכם שהיא לוקחת הרבה יותר זמן מהשאילתת SQL שהראיתי קודם (שנייה וחצי לעומת 150ms), דורשת הרבה יותר CPU Time (סה”כ 7860ms) והרבה יותר IO (40,615 logical reads).
אז איך נראית השאילתה שגורמת לזה?

exec sp_executesql N'SELECT 

    [Extent1].[Id] AS [Id], 

    [Extent1].[Title] AS [Title], 

    [Extent1].[CreationDate] AS [CreationDate]

    FROM (SELECT 

    [Posts].[Id] AS [Id], 

    [Posts].[AcceptedAnswerId] AS [AcceptedAnswerId], 

    [Posts].[AnswerCount] AS [AnswerCount], 

    [Posts].[ClosedDate] AS [ClosedDate], 

    [Posts].[CommentCount] AS [CommentCount], 

    [Posts].[CommunityOwnedDate] AS [CommunityOwnedDate], 

    [Posts].[CreationDate] AS [CreationDate], 

    [Posts].[FavoriteCount] AS [FavoriteCount], 

    [Posts].[LastActivityDate] AS [LastActivityDate], 

    [Posts].[LastEditDate] AS [LastEditDate], 

    [Posts].[LastEditorDisplayName] AS [LastEditorDisplayName], 

    [Posts].[LastEditorUserId] AS [LastEditorUserId], 

    [Posts].[OwnerUserId] AS [OwnerUserId], 

    [Posts].[ParentId] AS [ParentId], 

    [Posts].[PostTypeId] AS [PostTypeId], 

    [Posts].[Score] AS [Score], 

    [Posts].[Title] AS [Title], 

    [Posts].[ViewCount] AS [ViewCount]

    FROM [dbo].[Posts] AS [Posts]) AS [Extent1]

    WHERE (convert (datetime2, convert(varchar(255), [Extent1].[CreationDate], 102) ,  102)) = @p__linq__0',N'@p__linq__0 datetime2(7)',@p__linq__0='2016-06-25 00:00:00'

מה שמעניין פה זאת השורה האחרונה, של ה- WHERE. כדי להיפטר מאלמנט הזמן, ה- datetime מומר ל- string (מסוג varchar(255)) בפורמט שלא כולל את השעה (102) ולאחר מכן מומר בחזרה ל-datetime2 – ועל בסיס זה מתבצעת ההשוואה לתאריך שסיפקנו.

ההמרה הזאת, ל-string ואז בחזרה ל-datetime, למעשה דורשת מ- SQL Server לעבור על כל השורות בטבלה, לתרגם אותם ל-string ואז ל- datetime ולעשות את ההשוואה.  ה- CAST ל- date בדוגמא הראשונה הוא גם קריאה לפונקציה, אולם הוא מאפשר ל- SQL Server לעשות שימוש עדיין בידע המוקדם שיש לו (הפרמטר) על החלק בעץ של ה- clustered index שיהיה רלוונטי.
ה-SQL שנוצר כתוצאה מ- entity framework, לעומתו, דורש המרה של כל הערכים ל- string (ואז בחזרה ל- datetime) וביצוע ההשוואה על כולם – ולא מייצר execution plan שסורק רק את החלק הרלוונטי בעץ.  זה למעשה מה שאנחנו רואים ב- execution plan.
דבר כזה דורש מ- SQL Server לעשות יותר עבודה בביצוע השאילתה (גם מבחינת CPU וגם מבחינת IO): הוא נדרש לעבור על כל השורות  (לא יכול למעשה להשתמש במבניות של ה- clustered index). הוא גם לא יכול להשתמש בסטטיסטיקות וכו’ בצורה אופטימלית. בנוסף, למשל, גם אם הטבלה הייתה מפורטשת, שינוי כזה היה גורר מעבר על כל ה- partitions ולא מאפשר partition elimination  – או בקיצור, מימוש לא מוצלח כלל.

כלומר, מימוש לא מוצלח של המתודה TruncateTime ב- Entity Framework גורר את הבעייה הזאת. כמובן שניתן לעקוף אותה בקלות – מספיק שב-C# היינו משנים את הקוד לקוד שעושה את ההשוואה על בסיס גדול מ- וקטן מ-  במקום ע”י שימוש ב- TruncateTime, כדי שנקבל שאילתה ללא בעיית הביצועים הזאת.

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

בהצלחה!

טעינת נתונים מ- SQL Server ל- Elasticsearch

תוצאת תמונה עבור ‪elasticsearch‬‏

Elasticsearch, למי שלא מכיר, הוא מנוע אינדוקס פופולרי מאד המבוסס על מנוע האינדוקס של Lucene. מדובר למעשה ב- Document DB עם דגש חזק מאד על חיפוש טקסטואלי במידע (למרות שככל שעובר הזמן מתווספים לו פיצ’רים של document db גנרי, בכלל זה גם אגרגציות וכו’).
בפוסט הזה אני אסביר איך  אפשר לדאוג לזרימת נתונים שוטפת מ- SQL Server ל- Elasticsearch.

למה שנרצה דבר כזה?

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

אבל, גם אם אתם לא משתמשים בהווה ב- Elasticsearch, כדאי להיות מודעים לפחות לאפשרויות שהוא נותן, בדגש על אינדוקס טקסטואלי מעולה. כיום, הפיתרון כאשר נדרש אינדוקס טקסטואלי ב- SQL Server הוא Full Text Search (בקיצור, FTS). אין ספק ש- FTS הוא פיתרון קל להטמעה ושהוא משולב טוב מאד עם התשתית של SQL Server (אותו storage, כלול בגיבויים שלכם,  נתמך ב-AlwysOn וכו’). אבל, FTS יכול לא להספיק.  למשל –כאשר מדובר בכמויות מידע גדולות, ואתם צריכים יכולות scale-out לפיתרון האינדוקס,. או לחלופין, אם אתם צריכים שליטה טובה יותר בצורה שבה המידע מתאנדקס ונשמר מהשליטה המוגבלת יחסית שמתאפשרת עם FTS.

המטרה ודרישות הקדם

המטרה שלנו בפוסט הזה, היא בהינתן DB מבוסס SQL Server, שמידע נכנס (ו/או מתעדכן) בטבלאות שבו, להזרים את המידע הזה ל- Elasticsearch שלנו.
השיטה שבה נעשה את זה מבוססת על הרצת שאילתות מחזורית מול ה- DB, שמביאה כל פעם את המידע שהתווסף/השתנה.  לשיטה הזאת של crawling יש יתרונות וחסרונות, שכתבתי עליהם לא מעט בפוסט שעסק בטכניקות לבצע מעקב אחרי מידע שהשתנה ב- SQL Server. על בסיס התשתית שאני אראה אפשר להשתמש גם בשיטות אחרות לחילוץ מידע, כמו אלה שמוסברות בפוסט הנ”ל אבל בדוגמאות אני אתמקד בשיטה הפשוטה ביותר של ביצוע crawling על ה- data (ניתן היה להשתמש גם ב- CDC ובדברים נוספים על בסיס אותה התשתית).

הכלי שבו נשתמש כדי להריץ את השאילתות באופן מחזורי ולהכניס אותם ל- Elasticsearch נקרא Logstash.  מדובר למעשה בכלי ETL פשוט, מבית היוצר של המפתחים של Elasticsearch, שמאפשר להגדיר מספר inputs – מקורות שמהם מגיע ה- data, את הטרנספורמציות שהמידע יעבור ולאן המידע יוכנס בסוף (במקרה שלנו, Elasticsearch).

נקודת המוצא של הפוסט הזה היא שכבר יש לכם cluster של Elasticsearch שאליו יגיע המידע ו- instance של Logstash שאליו נוסיף את הקונפיגורציה שלנו. רצוי גם שיהיה Kibana (ממשק ויזואלי לתחקור המידע). ההתקנה לא מורכבת, ואפשר למצוא הוראות התקנה לכל הרכיבים פה (מבין הדברים נשתמש בפועל רק ב- Elasticsearch ו- Logstash)

הורדת JDBC Driver ל- SQL Server

Logstash זאת אפליקציה מבוססת Java, כאשר ה- plugin ל- Logstash שבו נשתמש צריך את ה- JDBC Driver המתאים ל- SQL Server. לצורך כך, נוריד את ה-package שכולל את ה- JDBC Driver,  ונחלץ את ה- JAR שאנחנו צריכים מה- tar.gz. הוא נמצא במיקום enu\jre8\sqljdbc42.jar. נעתיק אותו ונשמור אותו במקום ידוע בשרת שלנו שמריץ את ה- Logstash.

דוגמא 1: הרצת שאילתה מחזורית שמביאה רק רשומות חדשות

לעיתים, יש לנו טבלאות שבהן אין עדכונים ומחיקות, אלא רק מתווספים ערכים חדשים. למשל, נדמיין טבלה שמכילה מידע על היסטוריית ההדפסות בארגון.  ה- clustered index של הטבלה הוא על ה- ID המספרי הרץ (ולפיו נעשה את ה- crawling בשאילתה). כך נראית הטבלה:

CREATE TABLE [dbo].[PrintJobsHistory](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [SentDateTime] [datetime2](7) NOT NULL,

    [DocumentName] [nvarchar](150) NOT NULL,

    [Username] [nvarchar](150) NOT NULL,

    [NumPages] [int] NOT NULL,

    [NumCopies] [int] NOT NULL,

    [PrinterName] [varchar](150) NOT NULL,

    [ClientComputerName] [nvarchar](150) NOT NULL,

    [ClientIP] [varchar](50) NOT NULL,

 CONSTRAINT [PK_PrintJobsHistory] PRIMARY KEY CLUSTERED 

(

    [ID] ASCמה 

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

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

(כהערת אגב, אפשר היה לעשות פה גם משהו יותר מתוחכם, כמו למשל להגדיר ש- ClientIP יישמר כ- data type של כתובת IP ב- Elasticsearch עצמו. אולם, זה היה דורש לשנות את ה- mappings ב- Elasticsearch ואני מעדיף לא להיכנס לזה כחלק מהפוסט הנ”ל)

בואו נראה איך נראית קונפיגורציית ה- Logstash שמאפשרת את מה שאנחנו רוצים:

input {

    jdbc {

        jdbc_driver_library => "/tmp/jdbc/sqljdbc42.jar"

        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"

        jdbc_connection_string => "jdbc:sqlserver://192.168.50.1:1433;databasename=Sample"

        jdbc_user => "testuser"

        jdbc_password => "testpass"

        schedule => "*/1 * * * *"

        statement => "

SELECT ID

      ,[@timestamp] = CONVERT(datetime, SentDateTime, 21)

      ,[SentDateTime]

      ,[DocumentName]

      ,[Username]

      ,[NumPages]

      ,[NumCopies]

      ,[PrinterName]

      ,[ClientComputerName]

      ,[ClientIP]

FROM [Sample].[dbo].[PrintJobsHistory]

WHERE ID > :sql_last_value

        "

        tracking_column => "ID"

        use_column_value => "true"

        last_run_metadata_path => "/tmp/logstash_printer_last_id.state"

        lowercase_column_names => false

        type => "printHistory"

    }

    

}

 

output {

    if [type] == "printHistory" {

        elasticsearch {

            hosts => ["127.0.0.1"]

            index => "printjobs-%{+YYYY.MM.dd}"

        }

        stdout { codec => rubydebug }

    }

    

}

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

יש לנו שני חלקים מרכזיים פה: ה- input, שכולל את כל ה- data sources שלנו וה- output שכולל את המקומות שבהם אנחנו רוצים לשמור את המידע. למשל, בדוגמא הזאת אין לנו filter (שהוא חלק אפשרי נוסף) שמגדיר טרנספורמציות ותנאים על המידע.

ה- input

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

  • jdbc_driver_library: הנתיב לקובץ JAR של ה- JDBC Driver ל- SQL Server שהורדנו ושמרנו מקודם
  • jdbc_driver_class: השם של המימוש ב- JAR ל- JDBC Driver. ספיציפית, תתייחסו לערך הזה כקבוע במקרה שאתם עובדים עם SQL Server.
  • jdbc_connection_string: ה- connection string ל- DB, בסינטקס של JDBC. במקרה שלנו – התחברות לשרת בכתובת ה- IP של 192.168.50.1, ל- DB בשם Sample
  • jdbc_user: שם המשתמש שאיתו מתבצעת ההתחברות (SQL Authentication)
  • jdbc_password: הסיסמא
  • schedule: התזמון של הרצת השאילתה, ב- syntax של cron. בדוגמא שלנו, הרצה כל דקה.
  • statement: השליפת SQL עצמה. אם אתם מעדיפים, אפשר להפריד גם לקובץ נפרד ולתת את הנתיב שלו ב- statement_filepath. שימו לב שבשליפה עצמה, בתנאי, אנחנו מבצעים שליפה ומתנים על זה שה- ID גדול מ- sql_last_value. מה זה בדיוק sql_last_value? הערך האחרון שראינו בשליפה של  העמודה ID. למה דווקא העמודה ID? כי זה שם העמודה שהעברנו בהגדרה של tracking_column.
  • tracking_column: שם העמודה שלפיה נעשה את הבאת הדלתאות בטבלה. כלומר, שם של עמודה שהערך של התא שלה בשורה האחרונה שחוזרת נשמר, ומועבר אלינו בתור פרמטר של sql_last_value לשליפה הבאה. שימו לב ששם העמודה צריך להיות באותיות קטנות (בלי קשר לצורה שהוא מופיע ב- SELECT)
  • last_run_metadata_path: הנתיב שבו יישמר ה- state (הערך האחרון של ה- tracking_coolumn
  • lowercase_column_names: האם להפוך את כל שמות העמודות ל- lower case. ה- default הוא כן לעשות את זה.
  • type: ה- type של ה- data שאנחנו מאנדקסים. השם הזה ישמש אותנו בהמשך הקונפיגורציה כדי לפלטר ולהגיד שחלקים מסויימים חלים רק על דברים מה- type המסוים שאנחנו רוצים, ובנוסף – זה שם ה- type ב- Elasticsearch שיכול לשמש אותנו אח”כ לעדכון Mappings (ה- data types של השדות באינדקס, למשל)

לפני שנעבור ל- output, כמה הערות שכדאי לשים לב אליהן:

  • ניתן לעקוב אחרי ערך בודד. כלומר, אתם לא יכולים לחלץ באמצעות ה- jdbc plugin ערכים של מספר עמודות מהשאילתה ולעקוב אחריהם
  • אם השליפה שלכם קצת יותר מורכבת, וכוללת למשל הכנסה לטבלאות זמניות או פעולות שמייצרות למעשה הודעה על x rows affected, אבל שלא מצורף לה result sets, אתם עלולים להיתקל ב- exception שאומר statement did not return a resultset, על אף שכן חוזר בסופו של דבר result-set. כדי לפתור את זה, שימו SET NOCOUNT ON בראש ה- query שלכם, במידה שאתם מתעסקים עם יותר מ-statement יחיד של SELECT

ה- output

הגדרנו שימוש בשני output plugins. נתחיל דווקא מהשני, שהוא הכי פשוט – stdout. לצרכי debugging, נוח לראות את המידע נזרק גם ל- stdout.

הפלאגין output העיקרי שבו אנחנו עושים שימוש, הוא ה- elasticsearch. המטרה שלו, כמה לא מפתיע, זה לאפשר לאנדקס את המידע לתוך Elasticsearch.  אנחנו מעבירים לו שני פרמטרים:

  • hosts: רשימה של hosts שיכולים לשמש לטובת האינדוקס. במקרה שלי, אני עובד על VM שמריץ לי הכל, אז פשוט שמתי 127.0.0.1.
  • index: השם של ה- index שלתוכו יישמר המידע. במקרה שלנו, printjobs שלאחריו יש מקף, ואז את התאריך. כלומר, יהיה לנו אינדקס לכל יום שבו הגיע מידע. זה נוח במובן של ניהול retention – קל ככה למחוק מידע היסטורי. כמובן, אפשר לשמור את הכל באינדקס אחד (ואז היינו כותבים רק printjobs, בלי ההמשך), או לעשות את החלוקה לפי ערך אחר. חשוב לשים לב ששם האינדקס חייב להיות מורכב רק מאותיות קטנות. אם הוא יכיל אותיות גדולות, למשל, לא תקבלו שגיאה – אבל מידע פשוט לא יתאנדקס לכם.

דוגמא 2: הוספת של מקור מידע נוסף, עם רשומות שמתעדכנות

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

הטבלה שנסתכל עליה הפעם נראית ככה:

CREATE TABLE [dbo].[Users](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [Username] [varchar](100) NOT NULL,

    [FirstName] [nvarchar](50) NOT NULL,

    [LastName] [nvarchar](50) NOT NULL,

    [Email] [nvarchar](150) NOT NULL,

    [AboutMe] [nvarchar](4000) NOT NULL,

    [CreationTime] [datetime2](7) NOT NULL,

    [LastUpdated] [datetime2](7) NOT NULL,

 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 

(

    [ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

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

אז ככה נראית הקונפיגורציה עכשיו:

input {

    jdbc {

        jdbc_driver_library => "/tmp/jdbc/sqljdbc42.jar"

        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"

        jdbc_connection_string => "jdbc:sqlserver://192.168.50.1:1433;databasename=Sample"

        jdbc_user => "testuser"

        jdbc_password => "testpass"

        schedule => "*/1 * * * *"

        statement_filepath => "/tmp/printjobs.sql"

        tracking_column => "ID"

        use_column_value => "true"

        last_run_metadata_path => "/tmp/logstash_printer_last_id.state"

        lowercase_column_names => false

        type => "printHistory"

    }

    jdbc {

        jdbc_driver_library => "/tmp/jdbc/sqljdbc42.jar"

        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"

        jdbc_connection_string => "jdbc:sqlserver://192.168.50.1:1433;databasename=Sample"

        jdbc_user => "testuser"

        jdbc_password => "testpass"

        schedule => "* * * * *"

        tracking_column_type => "timestamp"

        statement => "

    SELECT    ID,

        Username,

        FirstName,

        LastName,

        Email,

        AboutMe,

        CreationTime,

        LastUpdated

    FROM Users

    WHERE  LastUpdated > :sql_last_value

        "

        tracking_column => "LastUpdated"

        use_column_value => "true"

        last_run_metadata_path => "/tmp/logstash_users_last_timestamp.state"

        lowercase_column_names => false

        type => "users"

    }

}

 

output {

    if [type] == "printHistory" {

        elasticsearch {

            hosts => ["127.0.0.1"]

            index => "printjobs-%{+YYYY.MM.dd}"

        }

    }

    if [type] == "users" {

        elasticsearch {

            hosts => ["127.0.0.1"]

            document_id => "%{ID}"

            index => "users"

        }

    }

    stdout { codec => rubydebug }

}

אני רוצה להסב את תשומת לבכם למס’ שינויים ביחס להגדרה של ה- input הקודם:

  • למען הבהירות של הקונפיגורציה, אתם רואים שאפשר להעביר את חלק מה-SQL statements להיות בקובץ נפרד (העברתי למשל את ה- jdbc הראשון, שכבר ראינו בדוגמא הקודמת)
  • בדוגמא הזאת, אנחנו עוקבים אחרי עמודה שהיא datetime2. לכן, הגדרתי tracking_column_type להיות timestamp (ה- default זה numeric).  זה חשוב במיוחד עבור הערך הראשון שמועבר (כשעדיין אין קובץ state). אם זה numeric, אז זה 0. אחרת, זה ה-01/01/1970.
  • ה- type של הדוגמא השנייה עכשיו הוא users. זה מאפשר לנו להבדיל גם בהתנייה על ה- type בחלק של ה- output, וגם ב-Elasticsearch, לטובת הגדרת mapping
  • ב- output אנחנו רוצים התנהגות שונה עבור דברים שהם רשומות של הדפסה, כמו שהיה לנו קודם, ועבור משתמשים (כמו של ה- input החדש שהוספנו עכשיו). את זה אנחנו משיגים באמצעות הוספת ה- if בתוך הקונפיגורציה. כאמור, היה אפשר להפריד את זה ל- piplines שונים עם קונפיגורציות שונות, אבל זאת דוגמא לאיך עושים את זה באותה הקונפיגורציה (כי גם זה משהו ששימושי להכיר).
  • ב- output, עבור דברים שהם מה- type של users, אני מציין document_id. כלומר, במקום לתת לו לייצר document_id חדש, אני מגדיר במפורש מה אמור להיות ה- document_id שלי. בהגדרה הזאת, אני משתמש בערך של אחד השדות שחזרו.
    הפעולה שמתבצעת היא פעולת UPSERT. אם לא קיים document עם אותו ה- id, אז נוצר אחד חדש. אם קיים, אז אנחנו מעדכנים.
  • ב- output, ה- index מוגדר ללא timestamp, אלא כשם קבוע. למשל, במקרה הזה, פחות משמעותי לעשות חלוקה לאינדקסים לפי תאריך היצירה (כי יחסית יש פחות משתמשים מכל תאריך, אנחנו כנראה לא נעשה חיפושי range בד”כ, ובתרחיש הסטנדרטי לא ממש מקובל לעשות rentention ולמחוק יוזרים ישנים…).
  • ה-index ב- output חייב להיות שונה מה- index קודם שהגדרנו, ששימש את ה- type הקודם. לפני גרסת Elasticsearch 6, היה ניתן להכניס מספר types שונים לאותו ה- index. החל מ- Elasticsearch 6, זה כבר לא אפשרי. לכן, אנחנו לא יכולים לערבב מספר types באותו האינדקס (ויש גם נימוקים טובים למה לא לעשות את זה בלינק).
  • ומה עם מחיקות..? אז עקרונית, אין דרך straight forward לגרום למחיקת ה- documents מ-Elasticsearch כתוצאה מהשינויים בטבלאות. ההמלצה, במקרה הזה, היא להיצמד ל- soft deletes, שהם יופיעו כשינויים ולכן כן יסתנכרנו ל- Elasticsearch.

סיכום

כפי שראינו, ניתן יחסית בקלות לדאוג להזרמת נתונים מ- SQL Server ל- Elasticsearch, תוך שימוש ב- jdbc input plugin של Logstash. זה יכול להיות מאד שימושי אם יש לכם כבר סביבת Elasticsearch שאתם צריכים להזרים אליה נתונים מה- MSSQL שלכם, ויכול להיות שימושי גם אם אתם בוחרים להשתמש ב- Elasticsearch כתחליף ל- Full Text Search.

בהצלחה!

QDS_LOADDB-מה זה אומר ומה אפשר לעשות

אחד הפיצ’רים שהושקו ב- SQL Server 2016 היה ה- Query Store. אם עוד לא יצא לכם להכיר אותו, ממליץ לקרוא עליו קצת פה. בגדול, מדובר בכלי שמאפשר להוציא insights טובים יותר בסוגיות שמעסיקות DBA-ים. בין היתר, זיהוי של שאילתות מעמיסות, זיהוי רגרסיות בריצת שאילתות וטיפול נוח בזה ועוד.
החל מ- SQL Server 2017, המנגנון של ה- Query Store גם משמש את פיצ’ר ה- Automatic Tuning של שאילתות, שמאפשר להגדיר את ה- flag של “FORCE_LAST_GOOD_PLAN” שעוזר (פוטנציאלית) למנוע רגרסיות שנובעות מבחירת execution plan פחות טוב מ-execution plan שנעשה בו שימוש בעבר.

אבל, לפעמים אנחנו מגלים את העלויות הנסתרות של דברים טובים. הרבה פעמים זה גם קורה בזמן לא מאד נוח….
תופעה שנתקלתי בה – בזמן ביצוע Failover ל- AlwaysOn Availability Group שכולל מס’ דטאבייסים, לאחר שמרבית ה- DB-ים סיימו לעבור recovery בשרת שאליו בוצע ה- failover, לא ניתן היה לעבוד כלל עם אחד מה- DB-ים שהיו ב-AG. כשאני אומר שלא ניתן לעבוד, הכוונה שלא היה ניתן להריץ אף שאילתה – כולן היו blocked, וה- Wait Type היה משותף לכולם – QDS_LOADDB.
גם לאחר המתנה לא קצרה לא היה נראה שהתהליך מתקרב לסיום. השרת, אגב, כל הזמן הזה לא התאמץ ולא הזיע בכלל. פחות או יותר אפס ניצול של ה- CPU, אפס IOPS, אפס throughput מול הדיסק.  שזה, דרך אגב, אחד הדברים שיותר מטרידים אותי כשאני רואה תהליך “תקוע”.

מפה לשם, כדאי לדעת שהליך ה- initialization של ה- Query Data Store מתבצע סינכרונית עם עליית ה-DB (זאת ההתנהגות הדיפולטית).  כלומר, אם מאיזושהי סיבה משהו בתהליך הזה “נתקע” (ואין לי לצערי משהו חכם להגיד על הסיבה האמיתית שגרמה לתקיעה הזאת מאחורה) – כוווולם ימתינו. אז מה אפשר לעשות?

  • אם אתם נמצאים כבר בסיטואציה הזאת, ורוצים “לשחרר” את התקיעה – כיבוי ה- Query Store (עם ALTER DATABASE [Sample] SET QUERY_STORE = OFF) משחרר את התקיעה הזאת. אחרי זה אתם יכולים להפעיל אותו מחדש ולראות מה קורה – האם זה יעבוד as is (אולי) ואז גם יהיה לכם את כל ה- data שנאגר עד עכשיו. אם לא, אתם יכולים לעשות purge ואז להפעיל מחדש את ה- Query Store.
  • אם אתם נתקלתם בה בעבר, ורוצים למנוע ממנה מלהתרחש בעתיד (או כיביתם ועכשיו אתם רוצים להפעיל מחדש בלי להיתקע שוב, גם אם התהליך ייקח זמן רב) אז הפעילו את trace flag 7752 (גלובלי). הוא גורם להפעלה א-סינכרונית של ה- Query Data Store, בצורה כזאת שלא הכל ייתקע עד שהוד רוממותו יואיל להיטען.
    על אף שנשמע שזאת ההתנהגות ההגיונית, מאיזושהי סיבה היא איננה ה- default, ואף כשהשאלה הזאת עלתה בסשן AMA ב- reddit, ההמלצה שניתנה שם היא לא להפעיל את ה-TF הזה באופן גורף.

בכל אופן, אחת המסקנות שאני לקחתי מהנושא הזה היא שה- Query Data Store מגמגם כשנאגרים בו נפחי מידע גדולים מאד. אז קחו את זה לתשומת לבכם, גם מבחינת הגדרות ה- query data store (ה- retention וכו’) וגם מבחינת ההחלטה של מה לעשות במידה שהוא גדול ונתקלים בבעיית ה- QDS_LOADDB (הבחירה בין לבצע purge לבין להעביר לטעינה אסינכרונית עם ה- TF הנ”ל).

בהצלחה.

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 @[email protected]+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

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

    image_thumb1

    ניתן לשים לב שלמשל במקרה של מחיקת שורה, יש לנו רק את הערך של ה- 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:

    image_thumb3

    ניתן לראות שאנחנו מבצעים פה הוספה לאינדקס של איזושהי טבלה (שלא אנחנו יצרנו ולא אנחנו מתחזקים) ש-“תומכת” למעשה את המנגנון הזה של ה- 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 במבנה הזה:

    image_thumb

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

    DECLARE @instanceName varchar(50)='TestCDC_1'

     

    DECLARE @lastLSN binary(10) = (SELECT LastLSN FROM CDCTracking WHERE [email protected])

    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 [email protected] WHERE [email protected]

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

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

    image_thumb2

    כאשר שימו לב שניתן לדעות מה הפעולה שנעשה (הוספה-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):

    image_thumb5

    בצד של ה- 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
    • התאמת הדיזיין של האפליקציה

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

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

    קורס SQL בסיסי–חינם ביוטיוב

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

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

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

    חלוקה כללית של ההרצאות בקורס:

    1. מבוא ל-DB-ים רלציוניים ו-SQL
    2. התקנת SQL Server ו-SQL Server Management Studio ושחזור ה-DB לדוגמא על המחשב האישי
    3. כתיבת שאילתות SELECT בסיסיות
    4. הרחבה על nested queries, שימוש ב- common table expressions
    5. JOIN על סוגיו
    6. שימוש ב- GROUP BY והכרות עם OUTER APPLY / CROSS APPLY
    7. הרחבה נוספת על APPLY, עבודה עם אופרטורים שונים: ALL, ANY, UNION, UNION ALL, INTERSECT, EXCEPT
    8. Window Functions
    9. יצירת טבלאות ועריכתן (CREATE/ALTER TABLE) ופעולות INSERT, UPDATE, DELETE ו-MERGE
    10. תנאים, לולאות, CURSOR-ים ו-Dynamic SQL

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

    צפייה נעימה!

    SQL Server: גיבויים, שחזורים–ומה עושים כשה-DB נעשה גדול

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

    Back to Basics – גיבויים ושחזורים

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

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

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

    • כמה מידע אנחנו מאבדים בעת השחזור (ידוע בשם RPO)– כלומר אם קרתה תקלה בשעה 10:00 בבוקר ואנחנו מתחילים לבצע שחזור, איזה מרווח של נתונים נאבד. שעה? שעתיים? עשר? מין הסתם, אנחנו רוצים למזער את הזמן הזה כמה שיותר ולחשוב מראש על מה אנחנו עושים עם המידע שאובד.
    • כמה זמן לוקח השחזור (ידוע בשם RTO)– נניח שקרתה תקלה והתחלנו להשתחזר. כמה זמן ייקח עד שהכל יעבוד שוב? שעה? עשר? יממה? גם פה, נרצה למזער את הזמן הזה כמה שיותר
    • כמה זמן לוקח הגיבוי – אמנם זה פרמטר שבד”כ הוא פחות חשוב (משמעותית) מהפרמטרים האחרים, אבל הוא עלול להיעשות חשוב יותר ככל שה-DB גדול יותר ועמוס יותר. דמיינו לעצמכם DB גדול ועמוס (שמתבצעות אליו גם הרבה פעולות, כולל כתיבות ושינויים, על ביס קבוע). בזמן הגיבוי יש איזושהי השלכה מסויימת על ביצועי המערכת (בעיקר משאבי IO שנדרשים, וקצת CPU – בעיקר בשביל ה- compression) ובנוסף בזמן הגיבוי לא מתנקים ה- transaction logs. ההצטברות שלהם עלולה להיות משמעותית אם זה מגיע לנפחים גדולים.

    כשאנחנו עובדים עם SQL Server יש לנו כל מיני אמצעים שאנחנו יכולים להשתמש בהם כשעושים גיבוי. אם אתם לא מכירים איך עושים גיבויים ב- SQL Server אזי מומלץ לכם (בחום!) לקרוא את המאמר של פול רנדל בנושא וכמובן גם תמיד כדאי לקרוא את מה שיש להגיד לתיעוד הרשמי להגיד בנושא.

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

    כשמדברים על דטאבייס קטן (נניח, גודל כולל של מתחת ל- 500GB) – בד”כ אין בעייה, ואלה שיש – פתירות באמצעות טוויקים שגרתיים יחסית.
    ה- Best Practice הסטנדרטי של “תעשה full backup פעם ביום, differential backup פעם במס’ שעות [נגיד 6-7] ו- transaction log backup כל 15-30 דק’” עובד מעולה. השחזור יתבצע ע”י restore של ה- full backup, ולאחריו ה- diffrenetial backup העדכני ביותר ואח”כ לנגן את ה- transaction logs כל הדרך עד הסוף. 
    כשעובדים ב-VM, אז גם קל מאד לשלב גם גיבוי system מלא, application consistent, שמתבצע תוך שילוב היכולות של snapshots ל-VM, עם snapshot של ה- storage (כאשר לכל ה- vendors העיקריים יש אינטגרציה מעולה שמאפשרת לבצע את זה בצורה קלה ויעילה) ועם VSS שרץ על המכונה לשמירה על הקונסיסטנטיות. זה מאפשר גם שחזור מלא של המכונה על כל הנתונים שבה (על כל הדטאבייסים שרצים בה, ה- state המדוייק שבו היו דברים מותקנים כך שזה נותן מענה גם לתקלות שדרוג וכו’).

    בקיצור, כשעובדים עם DB-ים קטנים יחסית, אפשר לאכול את העוגה ולהשאיר אותה שלמה ולהנות למעשה מיתרונות בכל הסעיפים שהזכרתי קודם שבוחנים למעשה יעילות של תוכנית גיבוי ושחזור.
    לעיתים, כמובן, נדרשים כל מיני טוויקים ושיפורים כדי לייעל את מהירות הגיבוי והשחזור. בין היתר אפשר למנות שיפורים כמו כתיבת הגיבויים ל- storage “טוב” יותר (אם קיים) או ל-storage שונה מהנוכחי (כדי לפצל את ה-IO), שימוש ב- compression (אם לא משתמשים, וסתם חבל…) ,שימוש במספר קבצי backups במקביל (מוסיף backup threads, מאפשר לפצל IO), הגדלת כל מיני באפרים וכו’. באמצעות שיפורים כאלה אפשר להגיע למהירויות גיבוי ושחזור סבירות לחלוטין שבד”כ יענו על הדרישה.

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

    הפתרונות האפשריים

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

    • שיטת גיבוי “סטנדרטית”
    • פיצול דטאבייסים
    • פיצול ל- Filegroup-ים וגיבוי Filegroup-ים ספיציפיים
    • גיבויים מבוסס application-consistent snapshot בסביבות וירטואליות ומערכי אכסון תומכים
    • גיבוי crash-consistent מבוסס snapshot ב- storage

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

    המטרה בתיאור הפתרונות הללו היא להציג לכם אוסף של שיטות אפשריות לגיבוי דטאבייסים גדולים, היתרונות והחסרונות שלהן. חשוב לי להדגיש מראש שאין אלמנט של “שיטה טובה” ו-“שיטה לא טובה”. שיטה היא טובה אם היא מתאימה לצרכים שלכם כמה שיותר – שיטה שמתאימה ל-DB אחד לא בהכרח תתאים ל-DB אחר.

    השיטה “הסטנדרטית”

    במשפט אחד: גיבוי מלא בתדירות מסויימת, גיבוי דיפרנציאלי בתדירות גבוהה קצת יותר וגיבוי transaction logs בתדירות גבוהה מאד. ה- comfort zone של כל DBA. יאפשר לכם להשתחזר אפילו עד לדקה האחרונה (כתלות בתדירות הגיבויי transaction logs), אבל זה יכול להיות עסק די ארוך ב-DB גדול.

    בואו נסתכל על השיטה הסטנדרטית: לקחת Full Backup פעם בפרק זמן מסויים (יום, יומיים, שבוע… נדבר על ההשלכות עוד מעט), לקחת Differential Backup בתדירות קטנה יותר ולקחת transaction log backups בתדירות גבוהה מאד (15-30 דק’).
    כאשר, אני מזכיר – אנחנו מדברים על DB גדול מצד אחד ודינמי מצד שני (כלומר, יש מולו לא מעט פעילות של כתיבה). בואו נניח שהגודל שאנחנו מדברים עליו הוא סדר גודל של מס’ טרות (5-10TB).

    היתרונות ברורים. זה ה- best practice. אנחנו עובדים בדיוק כפי שרצוי, לא לוקחים הנחות מקלות. אנחנו גם מקבלים זמן שחזור שמאפשר לנו לחזור ל-15-30 דקות עד לרגע שבו קרתה התקלה (ואפילו 2-3 דקות, כי אפשר להוריד את זמן גיבויי ה- transaction log כמה שרוצים. אבל מה החסרונות?

    שחזור

    שחזור של DB מורכב למעשה מהרבה מאד IO. ברגע שנחליט לשחזר נצטרך לשנע את קבצי הגיבוי (ה- full, ה- differential וכל ה- transaction logs מאז אותו גיבוי דיפרנציאלי) מהמקום שבו הם שמורים למכונה שלנו (פעולת שמורכבת מ-IO רשתי אל המכונה שלנו ו-IO דיסק מהמקום שבו הם מאוכסנים). חוץ מזה, נידרש גם לבצע מלא IO של כתיבות: לכתוב את כל ה- data וה- transaction log לדיסק. לפעמים אפילו יידרש עוד יותר IO, כי נצטרך לאכסן את הקבצים אולי במקום “זמני” במכונה שלנו (לפעמים זה משפר), או לפתוח איזשהו archive שבו נמצא המידע.
    וכל ה- IO הזה לוקח זמן. אם ניקח DB במשקל של 10 טרה, ונניח שאנחנו אפילו מזיניחים את ה- IO הרשתי – השחזור הראשוני של ה- full backup ידרוש כתיבה של 10 טרה לדיסק. אפילו בקצב כתיבה מכובד של 1GB/sec זה עומד לקחת קרוב ל-3 שעות. בקצב כתיבה  נמוך יותר – אתם יכולים כבר לעשות את החשבון לבד.
    אחרי ה- full backup אנחנו עוד צריכים להוסיף את העלות של הגיבוי הדיפרנציאלי, ואז את עלות ניגון ה-transaction logs בחזרה. בקיצור, זה לוקח לא מעט זמן (וגם הדברים שהזנחתי בחישוב יכולים להפוך למשמעותיים).

    גיבוי

    גיבוי של DB דורש קריאה של כל הנתונים שלו וכתיבה (של פחות נתונים, בזכות הדחיסה) למקום שאליו אנחנו מגבים. מכיוון שבד”כ גם אם ה- storage שעליו אנחנו מריצים את השרת production שלנו הוא חזק ומטורף, ה- storage שאליו אנחנו מגבים נוטה להיות משיקולי מחיר פחות טוב. אם יש לנו מזל זה פיתרון שמשלב tiering דינמי כלשהו, אבל גם אז אנחנו צפויים לקבל אפקט של כתיבה למערך storage משמעותית פחות טוב (בד”כ). כלומר ה- full backup עומד לקחת הרבה זמן.
    בזמן הזה יש ירידה מסויימת בביצועים של ה-DB (בשל ה- overhead של הגיבוי) אבל בד”כ מה שהכי חשוב זה שה- transaction logs לא מתנקים בזמן הזה (אין בעייה לעשות גיבויי transaction logs במקביל, אבל ה- transaction log לא יתנקה) – מה שיכול להפוך בשלב כלשהו למגבלה משמעותית (שהולכת וגדלה ככל שזמן הגיבוי מתארך…).

    הפיתרון הסטנדרטי שאפשר לחשוב עליו יכול להיות על בסיס fine-tuning לפיתרון הזה. כלומר, במקום לקחת full backup פעם ביום ניקח פעם ב-3 ימים או פעם בשבוע ואז נפחית את ה- impact של זמן הגיבוי. כדי לא להזדקק לנגן 3-7 ימים של transaction logs, ניקח גם differential backups בתדירות של 6-7 שעות ונאפשר לצמצם את זמן הניגון של הטרנזקציות.
    אבל, צריך לזכור ש- differential backups הם לא אינקרמנטליים, אלא הם כוללים למעשה את כל האיזורים ב- data files שנעשה בהם שינוי מאז הגיבוי המלא האחרון. כלומר, הם גדלים ככל שעושים יותר שינויים עד שבשלב מסויים הם יהפכו ללא אפקטיביים (כי כבר יהיה עדיף לקחת full backup).
    * החל מ- SQL Server 2017 יש עמודה של modified_extent_page_count  ב- sys.dm_db_file_space_usage שיכול לאפשר לנו לדעת אם משתלם לבצע גיבוי differential או לא.

     

    כלומר, בשיטה הסטנדרטית אנחנו מרוויחים באופן מובהק את היתרון של היכולת שלנו להשתחזר עד לרוזולוציה שנרצה (אפשר אפילו דקה-שתיים) ואנחנו מרוויחים גם את היתרון שאנחנו יכולים לנגן את הגיבויי transaction log ולהשתחזר ל- point-in-time (ב- Full recovery model). זאת גם שיטה די סטנדרטית, שמאפשרת לנו לא מתבססת על שום דבר שהוא “מחוץ” ל- SQL Server.

    כהערת אגב, אני אציין שאם אתם עובדים עם איזושהי תוכנת גיבוי/שחזור חיצונית (NetBackup למשל, או אחת המתחרות שלה) שבאה כחלק ממערך הגיבוי שבו הארגון משתמש, מאד מומלץ כשעושים בדיקות והערכות זמנים גם לבדוק מה ה- overhead של התוכנה ביחס לביצוע הפעולות הללו רק מתוך SQL Server עצמו.
    לעיתים גיבוי שלא דרך תוכנת הגיבוי יהיה מהיר יותר,  בין אם בגלל ההתנהגות של תוכנת הגיבוי (למשל, המימוש של ה-VDI שהיא מפעילה כדי לאפשר ל-SQL SERVER “לכתוב” את הגיבויים לתוך ה- stream שלה שאותו היא מעבירה למקומות אחרים), או הביצועים של ה- storage שמוקצה לגיבויים בארגון (storage ייעודי, שרתי המדיה של תוכנת הגיבוי וכו’).
    לעיתים גיבוי ל- storage כלשהו שיש בארגון והוא בכל זאת זול (NAS כלשהו) יהיה טוב יותר מה-storage שמוקצה באופן רגיל לגיבויים (שרתי המדיה של תוכנות הגיבוי השונות, או מערכי אכסון הגיבויים השונים שיש בשוק) – בצורה כזאת ששימוש בתוכנת הגיבוי ופיתרון הגיבוי “הסטנדרטי” בארגון לא יהיה טוב, אבל שימוש בפתרונות הגיבוי המובנים מול storage-ים אחרים שיש בארגון יעשה את העבודה, ואז ניתן לפתור את הסוגייה עם קצת סקריפטים שמחליפים למעשה את תוכנת הגיבוי (וזה ישתלם, על אף שזה חורג מה-“תקן” שיש בארגונים מסויימים, בהיבטים אחרים).

    פיצול לדטאבייסים שונים

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

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

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

    בפעמים אחרות, יכול להיות שאין דברים שאפשר “לחתוך” ולהעביר בטבעיות ל-DB אחר, אבל אפשר לסמן קבוצה מסויימת של דברים שהיא מספיק בלתי תלוייה בדברים אחרים כדי להעביר אותם כיחידה ל-DB אחר, ובכך להוריד את הנפח שבשימוש ב-DB המקורי (שימו לב, מה שמעניין אותנו פה זה בעיקר הנפח שבשימוש, ולא הנפח שאתם מקצים ל-DB – אם משתמשים ב- sparse files בשחזור, אז כל השטח “הריק” לא עולה לנו בזמן שחזור, וגם בגיבוי הוא לא מגדיל את נפח קובץ הגיבוי). אפשר לעשות שימוש ב- views שעושים cross-database query כדי לשמר את השמות המקוריים ב-DB, רק שעכשיו אלה יהיו views שיפנו לאובייקט שנמצא ב-DB אחר (עדיין אפשר יהיה לעשות אליהם INSERT-ים, BULK INSERT-ים וכו’, כי זה סה”כ SELECT * FROM OtherDB.schema.Table).

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

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

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

    פיצול ל- Filegroups וגיבוי ושחזור Filegroup-ים מתאימים בלבד

    במשפט אחד:  פיצול מידע (ברמת הטבלאות או ה- partitions) ל- FILEGROUP-ים שונים (מתאים במיוחד כשאופי הנתונים מאפשר FILEGROUP-ים שהם READ ONLY) ובמידת הצורך שחזור ה- FILEGROUP שבו נשמר המידע “הפעיל” בלבד. אפשר לעשות את כל זה גם בזמן שה-DB הוא אונליין. פיצ’ר מעולה, אבל זמין רק למשתמשי SQL Server 2016 ומעלה, בגרסת האנטרפרייז בלבד.

    [החלק הזה מתבסס על ההנחה שעובדים עם SQL Server 2016 ומעלה בגרסאת Enterprise. חלק מהפרוצדורות שמופיעות פה לא אפשריות ב-SKU שונה]

    ידוע לכולם ש- SQL Server מאפשר לנו לייצר מספר Data Files שהמידע מתפצל בינהם. ה- Data Files הללו מחולקים ל- Filegroups, כאשר ב- default קיים FILEGROUP יחיד. ניתן לפצל טבלאות ל- Filegroups שונים, כך שטבלה אחת תישמר ב- Filegroup א’ והשנייה ב- Filegroup ב’. יותר מכך – ניתן לעשות את הפיצול גם ברמת האינדקס (אינדקסים שונים של אותה טבלה ב- filegroups שונים) ואף ברמת ה- partition (כלומר, לחלק partitions שונים של אותה טבלה או אינדקס ל- filegroups שונים).

    Filegroup יכול להיות בשני מצבים: פעיל ו- Read Only. כאשר Filegroup הוא Read Only, לא ניתן לשנות את המידע ששמור בו בשום צורה.

    בתרחיש שבו למשל יש לנו DB גדול שמתווסף אליו כל הזמן מידע, שנשמר בצורה מפורטשת לפי חודשים. אנחנו יכולים להעביר את החודשים שהסתיימו כבר ל- FILEGROUP של מידע לקריאה בלבד. את הגיבויים אנחנו יכולים לעשות בין ה- FILEGROUP-ים באופן בלתי תלוי (כלומר, לגבות את אלה שהם read only פעם אחת, ולגבות באופן תדיר את ה- read-write) ולשחזר אותם גם באופן בלתי תלוי, וכך למעשה להפחית את זמן הגיבוי (כי מגבים רק מידע “חם”) ואת זמן השחזור (כי בד”כ לא נדרשים לשחזר הכל, רק את מה שצריך [מה שנדפק]).

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

    דוגמא בסיסית

    לשם הדוגמא, נייצר DB בשם test3 שכולל שני קבצים: test3, test3_sec כאשר test3 הוא ב- Filegroup שנקרא PRIMARY (הדיפולטי) ו- test3_sec ב-FILEGROUP בשם SECONDARY. לאחר מכן, נמלא מידע בטבלאות A ו-B שנמצאות ב- PRIMARY ו-SECONDARY בהתאמה ונפוך את SECONDARY ל- Read-Only.

    CREATE DATABASE [test3]

     CONTAINMENT = NONE

     ON  PRIMARY 

    ( NAME = N'test3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test3.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

     LOG ON 

    ( NAME = N'test3_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test3_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

    GO

    ALTER DATABASE [test3] SET COMPATIBILITY_LEVEL = 130

    IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [test3] MODIFY FILEGROUP [PRIMARY] DEFAULT

    GO

    ALTER DATABASE [test3] ADD FILEGROUP [SECONDARY]

    GO

    ALTER DATABASE [test3] ADD FILE ( NAME = N'test_sec', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test_sec.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [SECONDARY]

    GO

    USE [test3]

    CREATE TABLE [dbo].[A](

        [ID] [int] NOT NULL

    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[B](

        [ID] [int] NOT NULL

    ) ON [SECONDARY]

    GO

    USE [test3]

    INSERT INTO A(ID) VALUES (1), (2), (3), (4), (5), (6)

    INSERT INTO B(ID) VALUES (1), (2), (3), (4), (5), (6)

    GO

    USE [test3]

    GO

    declare @readonly bit

    SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'SECONDARY'

    if(@readonly=0)

        ALTER DATABASE [test3] MODIFY FILEGROUP [SECONDARY] READONLY

    GO

    USE [master]

    GO

    declare @readonly bit

    SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'SECONDARY'

    if(@readonly=0)

        ALTER DATABASE [test3] MODIFY FILEGROUP [SECONDARY] READONLY

    GO

    עכשיו, אחרי שיצרנו את המידע בשביל הבדיקה, ניקח גיבוי של PRIMARY בלבד:

    BACKUP DATABASE [test3] FILEGROUP = N'PRIMARY' TO  DISK = N'c:\tmp\bak\primary.bak' WITH NOFORMAT, NOINIT,  NAME = N'test3-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

    ועכשיו נרוקן את טבלת A שעל ה-primary:

    truncate table [test3].[dbo].[A]

    לאחר שרוקנו (אוי, שיט!) נראה שאפשר לשחזר אותה רק מהגיבוי של ה- FILEGROUP של ה- primary שעשינו קודם. קודם כל נצטרך לקחת גיבוי של ה- transaction log ולהגדיר שהוא יתבצע עם NORECOVERY.  זה יכניס את כל ה-FILEGROUPS ל-state של RECOVERY. בנקודה הזאת, נשחזר את ה-FILEGROUP שלנו מהגיבוי:

    USE [master]

    BACKUP LOG [test3] TO  DISK = N'c:\tmp\bak\l1.trn' WITH NORECOVERY

    GO

    USE [master]

    RESTORE DATABASE [test3] FILEGROUP = N'PRIMARY' FROM  DISK = N'C:\tmp\bak\primary.bak' WITH RECOVERY, REPLACE

    GO

    וזהו – שחזרנו את הטבלה A, בלי שהיינו צריכים לגבות בכלל (!) ובטח שלא לשחזר את ה- FILEGROUP שבו נמצאת B.

    כמובן שיש עוד הרבה תרחישים שאפשר לעשות (עם יותר מ- filegroup אחד, לשחזר קובץ בודד,  שילובים שונים בין Read-Only ל- Read-Write file groups, דברים שאפשר לעשות רק כשה- recovery model הוא FULL ודברים שאפשר לעשות גם ב- Simple. למידע נוסף, אני ממליץ בחום לקרוא את התיעוד בנושא, במיוחד אם יש לכם מחשבות ליישם את זה אצלכם:

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

    מה הקאצ’?

    • הנתונים צריכים להיות במבנה מתאים כדי שזה יהיה רלוונטי, והטבלאות צריכות להיות מפורטשות לפי המבנה הזה (אם יש DB עצום שכל הנתונים בו מתעדכנים כל הזמן, כל הטבלאות קשורות זה לזה, ואין משמעות לשחזור “חלקי” [כי זה יפגום בנכונות המידע] – אז זה לא רלוונטי)
    • רלוונטי ונוח בעיקר ב- Full Recovery Model (על אף שאפשר לעשות בזה שימוש גם ב- Simple Recovery Model – זה דורש Partial Backup שכולל אולי filegroups שאנחנו גם לא צריכים, מה שהופך אותו לכבד יותר)
    • דורש ניהול מתאים של ה- backup-ים, כאשר הניהול הוא קצת יותר מורכב מניהול של BACKUP-ים בתרחיש הרגיל. במיוחד כשב-DB יש מעבר של טבלאות ו- partitions בין FILEGROUP-ים, ואז כשרוצים להשתחזר לגרסא שהיא אולי לא הכי עדכנית, צריכים לוודא שמחזיקים ביד את הגיבויים המתאימים, ויודעים מה בדיוק רוצים לשחזר – איזה גיבוי מלא של ה-FILEGROUP ואיזה transaction logs אחריו.
    • בתצורה שהראיתי (עם יכולות אונליין וכו’) – רלוונטי ל- SQL Server  עדכני בגרסת אנטרפרייז

    Application-Consistent Snapshot שמתבסס על יכולות snapshot של מערכת האכסון (וסביבת וירטואליזציה)

    במשפט אחד: להתבסס על יכולות ה- snapshot של מערך האכסון, שמאפשרות לשמור את ה- state בדיסקים ברגע נתון ללא ביצוע פעולות IO (בד”כ metadata only), תוך שילוב עם יכולות snapshot של הסביבה הוירטואלית ורכיב שמותקן על השרת עצמו – שמאפשר לוודא שה- snapshot נלקח בצורה שהיא application consistent. נשמע כמו הפיתרון האידיאלי, אבל ב-DB-ים עמוסים הוא כנראה פשוט לא יעבוד.

    Snapshot-ים וחברים

    אם אתם עובדים בסביבת אנטרפרייז, סביר מאד להניח שה-DB הגדול שבאחריותכם לא רץ על דיסקים מקומיים. בסבירות גבוהה אתם רצים על שרת פיזי שמחובר לאיזשהו מערך אכסון מבוסס SAN, או לחלופין, רצים על מכונה וירטואלית שהדיסקים שלה מאוכסנים על מערך אכסון כלשהו (SAN או NFS).
    מרבית פתרונות האכסון (אם לא כולם) תומכים באופן מובנה ב- snapshot-ים ברמת מערך האכסון. 
    ברמה הפשטנית ביותר, זה אומר שאם אנחנו לוקחים snapshot של שטח מסויים, התוכנה של מערך האכסון זוכרת שה- snap שהשם שלו הוא XYZ קיים. בד”כ היא לא צריכה לעשות שום פעולת IO משמעותית בעת לקיחת ה- snapshot, חוץ מלרשום את עצם קיומו. בכל שינוי (כתיבה מכל סוג) שמתבצעת אח”כ על אותם בלוקים ששייכים לאותו ה- snapshot, מתבצעת ההפרדה – הבלוקים המקוריים נשארים as is, השינוי מתבצע על בלוקים אחרים, ו- metadata שמשייך כל בלוק ל-snapshots הרלוונטיים מתוחזק.

    ככלל, הנקודות הבאות מאפיינות snapshot:

    • הלקיחה שלו זולה מאד במשאבים ובזמן
    • השחזור ל- snapshot זול מאד במשאבים ובזמן
    • הגודל של snapshot הוא למעשה גודל השינויים שבוצעו מאז שהוא נלקח (כלומר בזמן הלקיחה הגודל שלו הוא בערך 0, וככל שהוא ישן יותר, אז נצברו יותר שינויים מאז, ואז יותר מידע נשמר למעשה רק כדי להחזיק את ה- snapshot- כך שהגודל שלו משמעותי יותר).

    מעבר ל-snapshots שבהם תומך מערך האכסון, לא מעט שרתי DB בעולם מתבססים ורצים מעל תשתית וירטואלית.
    בד”כ במצב כזה יש כמה שחקנים עיקריים: המכונה הוירטואלית (במקרה שלנו, כזאת שמריצה Windows Server 2012 R2 ומעלה ככה”נ, אם עובדים עם גרסאות עדכניות של SQL Server), תשתית הוירטואליזציה (HyperV/ESXi) ומערך האכסון (EMC/NetApp/Infinidat וכו’).
    אמרנו כבר שהתשתית אכסון כנראה תומכת ב- snapshots, אבל גם תשתית הוירטואליזציה (vSphere / HyperV) תומכת גם היא ב- snapshots שיכולים לעבוד ללא תלות במערך האכסון. עם זאת, לכל היצרנים בתחום האכסון יש אינטראקציה טובה בד”כ עם יצרניות הוירטואליזציה (כי אלה דברים שהרבה פעמים הולכים ביחד) באמצעות API-ים שמאפשרים לתשתית הוירטואליזציה להיות מודעת למערך האכסון, ולהיפך.
    הפתרונות הללו בד”כ מאפשרים לשלב את העולמות – לקחת snapshot באמצעות תשתית הוירטואליזציה, שעושה שימוש גם ביכולות ה- snapshot של ה- storage כדי לייעל את התהליך, ולהקטין את ה- overhead המתמשך (כי ברמת ה-storage המימוש של הסנאפשוטים טוב יותר מהמימוש של תשתית הוירטואליזציה, ובד”כ משתלב עם יכולות אחרות שהפיתרון storage מציע).

    עד עכשיו כל מה שאמרנו למעשה היה מנותק לחלוטין מהמכונה הוירטואלית עצמה, וה- SQL Server שרץ עליה, ולכאורה הם לא מודעים לתהליך שקורה. עם זאת, כדי לאפשר את היתרון של גיבוי קונסיסטנטי (כי בזמן לקיחת ה- snapshot יש נתונים בזיכרון, דברים שמתבצעים בזיכרון, ולהסתכל רק על ה- state של הדיסק עלול להיות בעייתי עבור אפליקציות מסויימות), ל- Windows קיימת תשתית שנקראת Volume Shadow Copy Service, שמאפשרת לו להיות מודע לכך שנלקח snapshot כלשהו ולרכיבים שונים שמותקנים במכונה להשתתף בתהליך כדי לספק snapshot קונסיסטנטי ברמת האפליקציה.

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

    • יכולות ה- snapshot של מערך האכסון
    • יכולת ה- snapshot-ים של הסביבה הוירטואלית
    • התממשקות של הוירטואליזציה לאכסון באמצעות API משותף
      למרות שתאורטית אין הכרח שמערך ה-storage ומערך הוירטואליזציה יהיו מודעים זה לזה, בפועל, קיימים ממשקים שמאפשרים להם כן להיות מודעים זה לזה ולחשוף API-ים משותפים, מה שמאפשר שיפור ביצועים והוספה של לא מעט יכולות חשובות. הנקודה שמעניינת אותנו בהקשר הזה, היא יכולות ה- snapshot של מערך האכסון שנחשפות החוצה ומשתלבות עם יכולות ה- snapshot של סביבת הוירטואליזציה.
    • התממשקות של הסביבה הוירטואלית, ותהליך לקיחת ה- snapshot שלה עם VSS (שזה ה- Volume Shadow Copy Service שרץ ב- Windows ותפקידו לאפשר את התהליך של “יצירת snapshot” על מכונת windows
      • SQL Writer Service – הוא הרכיב שחושף את יכולות הגיבוי של SQL Server בממשק שתואם ל-VSS ומאפשר למעשה ליצרני תוכנות הגיבוי השונות להביא גיבוי שהוא SQL Server-aware. ה- service הזה למעשה חושף ליצרני תוכנות הגיבוי שעובדים עם VSS את היכולת לעשות גיבוי מלא ודיפרנציאלי (אך לא גיבוי transaction log) דרך ה-API וצורת העבודה של VSS (אם מישהו רוצה לקרוא יותר לעומק איך יצרני תוכנות הגיבוי משתמשים ב- VSS וב- SQL Writer Service, ממליץ לקרוא את המאמר המעמיק יותר הזה).
    • שימוש ב-VDI (Virtual Device Interface) שזה למעשה “כוננים וירטואליים” ש- SQL Server רואה וכותב אליהם, כאשר המשמעות של מה זה אומר למעשה כתיבה אליהם מסופקת באמצעות רכיב צד שלישי (agent כלשהו) שמותקן במכונה
    שימוש לטובת גיבוי DB

    השילוב של כל הנקודות שכתבתי קודם הוליד שורה של פתרונות של מספר חברות, שהקונספט שלהן פשוט: הן מאפשרות לקחת snapshot מלא של המכונה, שהוא application-awere (כלומר, התהליך של הלקיחה לא “מנותק” מה- SQL Server שרץ על המכונה, אלא נעשה תוך כדי שהוא מודע לתהליך ולמעשה מאפשר יצירת גיבוי סדור) ולאחר מכן שחזור שלו. אני רוצה לשמור על ההסבר כללי, ולא להיכנס למוצרים ספיציפיים, אבל אני אזכיר בכל זאת כמה דוגמאות: מוצרים של Veam, ה- applience של vSphere Data Protection, ה- SMVI של NetApp ו- SMSQL וכו’.

    למשל, בסביבת וירטואליזציה מבוססת NetApp, אז ניתן להשתמש ב- SMVI (SnapManager for Virtual Infrastructure ) כדי לקחת snap מלא של המכונה או להשתמש ב- SMSQL (Snap Manager for Microsoft SQL Server) כדי לקחת גיבויים של DB-ים ספיציפיים שמתבססים למעשה על לקיחת snapshot ברמת ה- NetApp.

    למשפחת הפתרונות הזאת יש על הנייר מספר יתרונות משמעותיים בהיבט לקיחת הגיבוי ופיצ’רים נלווים:

    • זמן לקיחת הגיבוי קצר משמעותית בד”כ, בנוסף, גם הגודל של הגיבוי שנשמר בפועל הוא קטן יותר ומתבסס רק על גודל השינויים שהתבצעו ב-DB.
    • הרבה פעמים משולבים לא מעט יכולות חמודות שמתבססות על פיצ’רים שונים של מערך האכסון – למשל היכולת של באופן שקוף לחבר את ה-DB-ים לאחר הגיבוי לשרת אחר לטובת הרצת DBCC CHECKDB (לא באמצעות שחזור שלהם שם, אלא חיבור למעשה לבלוקים ממש ששמורים בדיסק), יכולות DR וכו’
    • בסביבת וירטואליזציה הרבה פעמים הדברים הללו משתלבים באופן טבעי עם הגיבוי הכללי של הסביבה הוירטואלית
    • מרוויחים RPO (כמות המידע שמאבדים) טוב, כי אפשר לשלב את זה עם גיבויי transaction log סדירים.

    בהקשרי השחזור, הדבר כבר תלוי בפיתרון הספיציפי.  בד”כ ה- snapshot  מתבצע ביחידות של volume/LUN – בעוד שאנחנו רוצים בד”כ לשחזר דטאבייס ספיציפי. הרבה פעמים זה ממומש באמצעות “mount and copy”, כלומר טעינה של ה- volume בגרסא כפי שהיא שמורה של ה- snapshot “בצד” ואז ביצוע הרבה data movements והעתקות ממנו. וזה כבר יכול להיות גם מאד יקר כשמדובר בכמויות נתונים גדולות. אולי יותר מהיר משחזור גיבוי שמגיע ממקום אחר (יתרון הלוקאליות, cache, tiering , ים אופטימיזציות שיצרני ה- storage עושים וכו’) – אבל עדיין מתייקר עם הזמן.

    אבל זה השחזור הוא לא הבעייה האמיתית שיכולה להפיל את הפיתרון הזה.
    הבעייה המהותית ביותר שאפשר להיתקל בה, ומשפיעה על ההיתכנות של הפיתרון, בהקשר הזה נוגעת דווקא לביצוע התהליך על שרתי high-end עמוסים מאד.  אם התרחיש הוא רק תרחיש של DB גדול, שלא מתבצעת מולו הרבה פעילות אונליינית (טעינת מידע, תשאול  מידע, עדכונים בדיסק וכו’) אז פיתרון כזה יכול להתאים. אולם, תהליך לקיחת snapshot על מכונת high-end שעושה גם הרבה פעולות ועובדים מולה הרבה משתמשים – עלול פשוט לא לעבוד.
    תופעות כמו תהליך לקיחת snapshot ארוך (שפשוט לא מסתיים), איטיות קשה ובלתי נסבלת בשימוש במכונה תוך כדי עד כדי תקיעה מוחלטת שלה בזמן לקיחת ה-snapshot (שנובעת מכך שכדי לאפשר את לקיחת ה- snapshot,  מתבצעת האטה מכוונת ואף הקפאה מלאה של גישה ל-RAM ופעולות CPU) עלולות בהחלט לקרות, וכמובן להשפיע על המשתמשים לטובת תהליך שבסוף לא ממש מצליח. מה שהופך את כל מה שכתבנו קודם ללא רלוונטי ופוסל את הפיתרון הזה לחלוטין במצבים מסויימים.

    מילה לגבי SQL Snapshots

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

    Crash-Consistent Snapshot ברמת מערך האכסון (למכונה וירטואלית ולשרתים פיזיים)

    במשפט אחד: לגבות בדקה ולהשתחזר בדקה. זה במחיר של איבוד פרק זמן ארוך יותר של מידע בזמן השחזור ותהליך קצת יותר טריקי

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

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

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

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

    התשובה פשוטה – במקום לקחת את ה- snapshot ברמת הסביבת הוירטואליזציה, תוך כדי המעורבות של מערכת ההפעלה וה-VSS – ניקח רק snapshot של מערך האכסון.  כאמור, זאת פעולת metadata בלבד ובד”כ מסתיימת תוך שניות. בצורה דומה, השחזור שלנו יורכב גם הוא משחזור ה- snapshot של מערך האכסון, in-place (כלומר, להחזיר את המצב למצב שהיה ב- snapshot, ולא להרים את המידע מה- snapshot בצד ולהעתיק רק את מה שאנחנו צריכים).

    איך זה עובד – מבט מלמעלה
    • נגדיר תהליך מחזורי ברמת מערך האכסון שלוקח פעם במס’ שעות snapshot של כל ה-volumes/LUN’s הרלוונטיים
    • נגדיר retention ל- snapshot-ים. חשוב לזכור שגודל ה- snapshot מתחיל מ-0 וגדל בהתמדה ככל שעובד הזמן ויותר שינויים נעשו למידע, וצריך לקחת את זה בחשבון
    • בעת שחזור – נעשה restore ל-snapshot. נשים לב שאנחנו לא מחזירים פה DB, אלא מחזירים פה את כל מה שכלול ב- snapshot למצבו הקודם.  לאחר מכן, נריץ תהליכים שהמטרה שלהם זה לשחזר (ממקורות אחרים) את המידע שנוצר מאז ה- snapshot הקודם.
    מה מגבים?

    חשוב להבין שה-storage שלנו לא יודע מה זה DB. הוא לא מכיר Data Files, הוא לא מכיר Transaction Logs.
    הוא מכיר יחידה הרבה יותר כללית מזה. מה בדיוק היחידה הזאת ומה היא כוללת, זה בהתאם לתצורה שבה אנחנו עובדים:

    • זה יכול להיות  LUN שבו כתוב data מסוגים שונים שהוא לא יודע מה הוא ומה בדיוק הוא מכיל. ה- data הזה יכול להיות ה- transaction logs שלנו, קבצים שלנו, הגדרות שלנו אם אנחנו מדברים על תרחיש שבו יש לנו שרת פיזי שמדובר ל-SAN ומשתמש ב-LUN מסויים.
    • זה יכול להיות LUN שבו מאוכסנים vmdk-ים/vhd-ים של מכונות וירטואליות שמרוצות ממנו
    • זה יכול להיות volume שחשוף ב- NFS עם כל מיני קבצים, שבהם יש vmdk-ים של המכונה הוירטואלית שלנו (בתרחיש של וירטואליזציה מעל NFS)

    בכל אופן, כשאנחנו לוקחים snapshot אנחנו לוקחים snapshot של כל ה- data שנמצא על אותו Volume/LUN. וכשאנחנו “נשתחזר” אנחנו למעשה נעשה restore ל-snapshot הזה ונדרוס את כל ה- data, בגרסא ישנה יותר שלו (של ה- snap הקודם).

    כלומר, אם אנחנו רוצים להשיג הפרדה, נצטרך לעשות אותה על בסיס היחידות הללו. לכל הפחות, נרצה להבטיח שאם אנחנו משחזרים, אנחנו נמצאים ומוכלים רק ב- scope של המכונה שלנו. למשל, בסביבת וירטואליזציה מבוססת NFS עם volume גדול שחשוף בתור datastore ושבו נמצאים vmdk-ים של הרבה מכונות, אם נעשה restore ל-snapshot נחזיר את כל ה- vmdk-ים הללו למצב הקודם שלהם – שזה ממש לא מה שאנחנו רוצים. אנחנו גם לא רוצים לעשות data movement בשעת השחזור, ולכן הדרך שלנו תהיה להפריד מראש את המכונה שלנו ל- volume נפרד.

    אם נרצה להשיג רמה גבוהה יותר של ההפרדה, נוכל אולי להפריד גם דטאבייסים שונים ל-volumes שונים (ל-VMDK-ים שונים), או ל-LUN-ים שונים ולייצר policy של לקיחת snapshots שמופרד בהתאם לחלוקה שאנחנו מכירים של דטאבייסים ל- LUN-ים.

    חשוב לזכור שבמידה שה- data files וה- transaction log מפוצלים על פני volumes שונים או LUN-ים שונים, חשוב לוודא שה-snap שלהם נלקח ב- storage ביחד בצורה אטומית,  אחרת ה- DBעלול להפוך ל- corrupted בזמן השחזור.

    באיזה תדירות מגבים ולכמה זמן שומרים אחורה?

    במרבית מפתרונות ה- storage נהיה מושפעים מהמגבלות הבאות:

    • מגבלת האכסון והגודל של ה- snapshots(שהוא לא אכסון זול שמיועד לגיבויים, אלא בד”כ אכסון high-end יקר יותר, שהוא חלק ממערך האכסון שמשמש אותנו ל-data עצמו) כאשר הגודל של ה- snapshot הוותיק ביותר ייקבע מכמה רחוק אחורה נשמור, וכמה שינויים אנחנו עושים
    • מגבלה על מספר ה- snapshot-ים שקיימת בחלק ממערכי האכסון

    שיטת הגיבוי הזאת מותאמת יותר להתאוששות מהירה מאד מאסונות שמגלים מהר (טעויות אנוש, למשל, כמו truncate table לטבלה הלא נכונה…) ופחות לשמירה היסטורית ארוכה של מידע. לכן, בד”כ הכיוון שאליו אני ממליץ ללכת הוא לקחת snapshot בתדירות כמה שיותר גבוהה שאפשר, ולשמור אחורה לפרק זמן לא ארוך מדי. למשל, בתור מספרים לדוגמא – snapshot כל 4 שעות (אם יודעים שאפשר להשלים באמצעים אחרים את המידע שהיה ב-4 שעות הללו) ושמירה אחורה של שבוע.
    צריך לזכור גם שאחרי שעושים את ה- restore, אין לנו אפשרות לנגן transaction logs קדימה – ולכן התדירות של לקיחת ה- snapshots היא אחד הגורמים העיקריים שקובע כמה מידע נאבד בעת שחזור (גם לגרסא הכי אחרונה).

    שימו לב שהגיבויים במקרה הזה הם snapshots, שנשמרים בתוך אותו מערך אכסון. כלומר אם ה- storage עצמו נפל – הגיבויים הללו לא יצילו אתכם. עם זאת, מרבית ה- storage-ים כיום תומכים בתצורות שונות של יציבות – כולל הרחקה לאתרי DR וסנכרון מולם, פיצול למספר “ראשים” שעובדים במקביל וכו’, כך שזאת לא תקלה כזאת שכיחה. אם אתם נדרשים לגיבוי off-site, כמובן שצריך לקחת את זה בחשבון כשקובעים את תוכנית הגיבוי.

    איך הולך שחזור?

    השחזור שלנו יילך בד”כ בצורה הבאה:

    1. ניכנס ל-downtime (נוריד אפליקציות)
    2. נתכונן לשחזור: נעצור AlwaysOn, נוריד את השרת DB (אם הוא למשל ב-VMDK שאנחנו עומדים להחזיר אחורה) או לחלופין נעשה detach ל-DB-ים הרלוונטיים
    3. נעשה restore ל-snapshot של ה- volume הרלוונטי, בד”כ ל-snapshot האחרון
    4. נעלה את השרת, או נעשה attach לקבצים
    5. נעשה בדיקות sanity, נעלה מערכות ונחזיר דברים שצריך להחזיר (AlwaysOn ידרוש הוספה מחדש של ה-DB)

    אחד היתרונות שיכולים להיות לנו הוא שאנחנו יכולים לעשות בד”כ שימוש בפיצ’רים חמודים שונים שמציע המערך אכסון, למשל לעשות mount של ה- snapshot הספיציפי “בצד” ולבדוק שבאמת עובד לנו ואנחנו באמת מצליחים לעבוד מולו (למשל בתרחיש וירטואליזציה, לעשות mount של ה- snapshot, לחשוף אותו בתור datastore ולהוסיף ממנו את ה-VM ל- inventory, להפעיל ולראות מה קורה. או לחלופין, בתרחיש של SAN לשרת פיזי, לעשות mount לכוננים שמכילים את הקבצים תחת אות כונן אחרת ולראות שמצליחים לעשות להם attach).

    האם מובטח שזה יעבוד?

    התהליך הזה הוא לא תהליך שגרתי ולכן יכולה לעלות השאלה – למה שזה יעבוד?

    התשובה היא “באותה צורה שזה היה עובד אם הייתה הפסקת חשמל וה-UPS-ים לא היו עובדים. לא אידיאלי, אבל המוצרים הללו [רק SQL Server בתרחיש של שרת פיזי, ESXi ו- SQL Server בתרחיש של שרת וירטואלי] נבדקים היטב כדי לתמוך גם בתרחישים הללו, כי אלה דברים שיכולים לקרות.

    ברמת ה- SQL Server, אם ה-snapshot כולל גם את ה- transaction log וגם את ה- data files שמייצגים את אותה נקודה אטומית בזמן שבה הוא נלקח, בזכות ה- Write ahead logging שזה אחד הדברים הבסיסיים בכל DB שצריך להבטיח את עקרונות ה-ACID, הכל אמור לעבוד. ה-DB יעלה, ויעשה recovery כמו שקורה בכל עלייה מאפס. הוא יעשה Analysys, ינגן קדימה (REDO) את כל המידע ויעשה UNDO לטרנזקציות שלא הושלמו.

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

    מה החסרונות ואיך מתמודדים איתם?
    • שחזור בגרנולריות גבוהה ממה שרוצים: כאמור, כשמשחזרים אנחנו לא משחזרים DB, אלא קבוצה של דברים שהיו באותו ה- volume. אנחנו כן יכולים לנסות לפצל את זה, אבל יש עוד כל מיני שיקולים שיכולים למנוע מאיתנו (מסיבות טובות) להגיע לרמת הגרנולריות שאנחנו רוצים (שחזור של DB) וצריך להיות מודעים לזה.
      עם זאת, בד”כ כשיש מספר דטאבייסים על אותו שרת DB, אחד מהם הוא בד”כ הגדול והמשמעותי שבגינו אתם קוראים את הפוסט הזה ומחפשים פתרונות יצירתיים – והשאר קטנים יותר. לא פעם החיסכון זמן בשחזור של ה-DB הגדול מצדיק את ה-extra עבודה שיש אח”כ – לשחזר את ה-DB-ים האחרים שהשתחזרו ללא סיבה ביחד איתו מפתרונות גיבוי אחרים שמגבים אותם (סטנדרטיים יותר).
      כמובן, שחשוב לוודא שבסביבה וירטואלית נשמרת לפחות גרנולריות של VM – שלא “נשחזר” גם VM-ים שבכלל לא קשורים אלנו ל-state קודם.
    • איבוד מידע: בניגוד לשיטות קודמות שאנחנו יכולים לשחזר ממש עד לנקודה העדכנית ביותר שלקחנו בה גיבוי transaction log, פה אין לנו את האפשרות הזאת. צריך להיות מודעים לזה, כאשר השיטה הזאת מתאימה מין הסתם באחד מהתרחישים הבאים: או שלא אכפת לאבד מידע מטווח מסויים, או שיודעים “לנגן” את המידע ממקור אחר. למשל, מערכת שמתבססת על טעינת נתונים מקבצים – ואז אפשר להריץ טעינה חוזרת של אותם הקבצים ל-DB הגדול (כאשר ההנחה היא שה-DB התפעולי שמשמש כדי לעקוב אחרי מה נטען ומה טופל מגובה בנפרד באמצעי שמאפשר להגיע באמת לרמה העדכנית ביותר).
    • פיתרון לא סטנדרטי: הפיתרון הזה הוא לא סטנדרטי. הוא לא חלק מתוכנת גיבויי ולא פעם זה דברים שאנשי ה- storage וה-system יידרשו להיות מעורבים בו בזמן התקלה (כי נדרשת עבודה מול ה- storage עצמו,  ולפעמים גם טריקים ברמת ה- system). זה אומר שבד”כ זה פיתרון שייעשה בו שימוש לדברים שבאמת הפתרונות הסטנדרטיים פשוט לא מספקים עבורם מענה טוב.
      בכל אופן, כמו כל דבר לא סטנדרטי, חשוב לוודא ששומרים על כשירות. שיודעים שה-snapshots אכן קורים, ושיודעים במידת הצורך שלב אחרי שלב איך משתחזרים (והכי טוב – אם הכל מובנה גם בסקריפטים מוכנים מראש, כי תקלה זה לא הזמן האידיאלי לאילתורים).
    • ה-snap נשמר למעשה בתוך המכונת storage עצמה ולא במדיה חיצונית (אלא אם כן מערך ה-storage הוא streched על פני אתר מרוחק, או עושה שימוש בפתרונות שונים כדי לשנע את ה- snap-ים החוצה).
    • לא ניתן (או לכל הפחות, משמעותית יותר יקר) לשמור גיבויים לטווח ארוך

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

     

    סיכום

    לשם הנוחות, הנה סיכום קצר של השיטות שראינו בטבלת השוואה:

    image_thumb3

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

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

    • תוודאו שיש לכם תוכנית גיבוי ושחזור
    • תוודאו שכל האנשים שעלולים להידרש לבצע את השחזור יודעים איך לעשות את זה, בהתאם לתוכנית שבחרתם
    • הקפידו לבדוק באופן תקופתי את התוכנית שלכם, ובפרט את הפרמטרים הבאים:
      • האם היא עובדת? האם אתם מצליחים לגבות ולהשתחזר? אולי זה נשמע טריוויאלי, אבל הקפידו לעשות את זה. העולם מלא בסיפורים מפורסמים יותר ופחות של אנשים שגילו ברגע האמת שאין להם גיבוי. אם אתם זקוקים לסיפור כזה למוטיבציה, תלמדו את הלקח של GitLab שגילו ברגע האמת שהגיבויים שלהם לא באמת עבדו.
      • האם היא עונה על הדרישות מבחינת זמני ריצה, וכמות איבוד החומר (אין טעם בתוכנית מפוארת אם לוקח לכם 20 שעות להשתחזר, כשמצופה מכם להשתחזר תוך 4, או בתוכנית שאתם מאבדים במסגרתה מידע שנכנס בדקות האחרונות, אם הוא קריטי לארגון)?
      • האם התוכנית שלכם עונה לדרישות הכלליות של הארגון (שמירה במקום חיצוני, מענה לתקלות כאלה ואחרות)?
    • תוודאו שאתם יודעים מה לעשות אחרי שחזור (טיפול בשאריות מידע שלא השתחזרו, הוספה מחדש ל-AG של ה- AlwaysOn שלכם)?

     

     

    הרבה הצלחה, ושלא נדע מצרות!

    עובדים עם SQL Server? אתם צריכים לעקוב אחרי ה- CU שמתפרסמים

    אם אתם עובדים עם SQL Server בסביבת production, אתם צריכים לעקוב אחרי רשימת ה- CU שמתפרסמים. אם אתם DBA-ים, אז כנראה שזה לא מחדש עבורכם (אני מקווה…). אם, לעומת זאת, אתם מפתחים שיוצא להם לעבוד עם אפליקציה מבוססת DB, ואתם אלה “שחיים” את האפליקציה, פותרים בעיות שצצות בה (גם ברמת התחזוקה), ומתמודדים עם נושאים לכל רוחב הגזרה (כולל דטאבייסים) – אז אולי אתם לא יודעים שקיים כזה דבר, וחבל.

    ב- SQL Server (כאשר אני מדבר על גרסת ה- on-prem, ולא על SQL Azure)– מיקרוסופט מוציאה גרסא עיקרית חדשה פעם במס’ שנים. הגרסא החדשה כוללת בד”כ פיצ’רים נוספים, יכולות חדשות, שיפורים ביכולות קיימות – וכו’. לכולנו ברור שבשביל להישאר רלוונטיים, מפתח צריך להתעניין ולהכיר מה יש בגרסאות החדשות של מוצרים שאיתם הוא עובד, רצוי הרבה לפני שהן יוצאות – עוד כשהן בשלבי preview / RC וכו’.

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

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

    אם אתם לא מכירים, אתם צריכים  להוסיף את בלוג העדכונים של SQL Server כבר היום לקורא ה-RSS שלכם,  כדי שתתעדכנו כל פעם שיוצאת גרסא חדשה ו- CU חדש. ההמלצה (גם שלי, גם של מיקרוסופט)היא להתקין את העדכונים הללו שיוצאים. הם פותרים באגים, לא פעם גם באגים עיקריים (כאלה שיש סיכוי טוב שנתקלתם בהם). לכל הפחות, צריך לעבור כשיוצא עדכון כזה על רשימת ה-FIX-ים הכלולים בו (כאן תוכלו למצוא למשל את ה- KB של CU1, שהוא האחרון שיצא עד היוםל-MSSQL 2016 SP1) ולראות אם אחד מהדברים שמופיעים שם “מצלצל” לכם. גם אם נראה לכם ששום דבר לא רלוונטי אליכם – עדיין כדאי להתקין. יכול להיות שחלק מהבאגים (בעיקר באגים של פגיעה בביצועים) משפיעים עליכם, למרות שאתם לא מודעים לזה.

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

    כדאי לדעת שבגרסאות מעודכנות – השאילתה SELECT @@VERSION מחזירה לכם string שמתאר גם את ה-CU שמותקן (בניגוד לעבר שה-CU לא היה מופיע בבירור ב- string של הגרסא).

    נקודה נוספת שכדאי להכיר, היא ששינויים ב- query optimizer לא מופעלים כברירת מחדל על דטאבייסים קיימים, כדי להפחית את הסיכוי ששאילתות קיימות ייפגעו ויווצר עבורם execution plan פחות טוב מהנוכחי. עם זאת, עבור אפליקציות “חיות” (כלומר, אפליקציות שיש משאבי פיתוח שמופנים אליהם, שיש מי שמסתכל עליהם וכו’) כדאי לאפשר הפעלה של Query Optimizer Fixes- כאשר כמובן שרצוי אחרי שדרוגים לתת תשומת לב מיוחדת ולראות שלא נפגעו לכם שאילתות. כדי לאפשר לעדכונים ל- query optimizer לפעול (גם ב- primary וגם ב- secondary שקיימים), השתמשו בפקודות הבאות:

    USE [SO]

    GO

    ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;

    GO

    ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = OFF;

    GO

    בהצלחה!

    SQL Server למפתחים: Unique Index ו- IGNORE_DUP_KEY

    אחד הפיצ’רים הבסיסיים לסכימה של מסדי נתונים, היא היכולת להגדיר unique constraint.
    מאחורי הקלעים, Unique Constraints מתבססים על Unique Indexes שנוצרים כדי לשרת את ה- constraint, כלומר – כדי לאפשר ל- SQL Server לבדוק בזמן ביצוע פעולות הוספה ועדכון שה- constraint לא מופר.

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

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

    לכאורה אנחנו נמצאים פה עם בעיית “UPSERT” מנוונת: אנחנו לא רוצים להכניס-או-לעדכן,  אנחנו רוצים להכניס-או-לוותר [אם יש כפילות].

    לפני שאני אמשיך בהצגת Unique Indexes, אני רוצה להתייחס רגע לפיתרון הנאיבי של “נבדוק אם לא קיים ואז נכניס”. כלומר, נעשה תנאי של IF NOT EXISTS וע”ס הערכים שלו נפעל.
    פיתרון הזה פחות טוב, ולא רק בגלל שהוא פחות אלגנטי ונוח. הוא גם לא מאפשר לנו לעבוד בצורה נוחה עם BULK INSERT-ים (כי אז אנחנו לא יכולים לשלב את הבדיקת IF NOT EXISTS), הוא מקשה עלינו מבחינת טרנזקציות שרצות במקביל (צריך לבחור isolation level כזה שיאפשר לנו לוודא שבין הבדיקה של ה- IF NOT EXISTS להכנסה לא הוכנס הערך מטרנזקציה אחרת) – ובנוסף, הוא גם לא חוסך לנו את האינדקס (כי אם יש לנו הרבה שורות בטבלה, הבדיקת IF NOT EXISTS עצמה תהיה יקרה). ואם כבר אינדקס, אז עדיף שזה יהיה תוך שימוש בפיצ’ר שמובנה במנגנון ומאפשר לנו להנות מיתרונות נוספים : –)

    Unique Indexes והתנהגות במקרה של כפילויות

    בגדול, unique index, זה אינדקס רגיל שמסומן ל- SQL Server שכל קומבינציה של ה- key columns שלו (לשם פשטות, אני אקרא לזה “הערך” שלו – למרות שכמובן זה יכול להיות שילוב של ערכים) הוא יוניקי. הוא למעשה “הרוח החיה” מאחורי כל unique constraint (כי הוא זה שמאפשר גישה מהירה כדי לבדוק שה- constraint נשמר).
    Unique Indexes יכולים לשמש אותנו לאכיפת constraint, ובנוסף הם מספקים מידע חשוב לטובת ייצור Execution Plan. כאשר מובטחת ייחודיות של ערכים מדובר למעשה באמירה חזקה מאד על המידע שמאפשרת לבצע פעולות מסויימות בצורה יעילה יותר מה שמאפשר לייצר execution plan טוב יותר.

    כאשר אנחנו יוצרים unique index, ההתנהגות הדיפולטית במקרה שאנחנו מכניסים ערך שקיים כבר היא פשוטה: לזרוק שגיאה (וכמובן שהשורה לא מתווספת). אם השגיאה הזאת קרתה כחלק מטרנזקציה (למשל, כאשר אנחנו עושים רצף של פעולות בטרנזקציה אחת, או למשל עושים שימוש ב- Bulk Insert ומכניסים מס’ שורות בבת אחת) – יתבצע rollback של הטרנזקציה, כלומר המצב יחזור לקדמותו ואף אחת מהפעולות שהתבצעה כחלק מהטרנזקציה לא תקרה. ההיגיון מאחורי זה ברור – אם רצף הפעולות הזה הביא למצב שבו ניסינו להכניס שורה עם ערך כפול, למרות שהוא אמור להיות ייחודי, אז משהו לא נכון קרה.

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

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

     

    יצירת Unique Index עם IGNORE_DUP_KEY

    .איך עושים את זה? משתמשים ב- flag שנקרא IGNORE_DUP_KEY ומגדירים אותו ל- ON בעת יצירת ה- index.

    CREATE UNIQUE NONCLUSTERED INDEX [IX_FatherSonInfo_Unique_SonID_FatherID] ON [dbo].[FatherSonInfo]

    (

        [SonID] ASC,

        [FatherID] ASC

    )WITH (IGNORE_DUP_KEY = ON)

    מה אמרנו בפועל? ייצרנו אינדקס יוניקי חדש, ואמרנו שבמידה שמתבצעת פעולת הכנסה/עדכון לאינדקס הזה שתפגע בייחודיות – הוא ימנע את זה, מעצם הגדרתו כאינדקס יוניקי, אבל יעשה את זה עם אזהרה ולא עם שגיאה (לטובת הבהרה: כאשרמתבצעת פעולת הכנסה/עדכון על הטבלה אז מתבצעת פעולה על האינדקס – כי כל פעולה על הטבלה כרוכה בפעולה מתאימה על כל אינדקס שמוגדר בטבלה [למעט היוצא מין הכלל של Filtered Index, ועל זה בפוסט אחר]).

    כמובן, שכאשר מריצים את ה- statement הזה חייבים שבטבלה לא יהיו כפילויות, אחרת נקבל שגיאה כזאת:

    Msg 1505, Level 16, State 1, Line 5

    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.FatherSonInfo' and the index name 'IX_FatherSonInfo_Unique_FatherID_SonID'. The duplicate key value is (1, 1).

    The statement has been terminated.

    ——————

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

    with cte as (

        SELECT    ID,

                DateCreated,

                FatherID,

                SonID,

                RN = ROW_NUMBER()OVER(PARTITION BY FatherID, SonID ORDER BY ID)

        FROM FatherSonInfo

    )

     

    DELETE FROM cte

    WHERE RN > 1

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

    ——————

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

    שימוש באינדקס שמוגדר עם IGNORE_DUP_KEY=ON

    אז אחרי שיצרנו את האינדקס הזה, שכאמור הוא אינדקס יוניקי שמסומן ב IGNORE_DUP_KEY = ON, כך שההתנהגות במקרה של כפילויות היא אזהרה בלבד, בואו נראה באמת מה קורה.

    נתבונן רגע בשאילתה הבאה:

     

    INSERT INTO FatherSonInfo(DateCreated, FatherID, SonID)

    VALUES

    (GETDATE(), 1, 1),

    (GETDATE(), 1,1),

    (GETDATE(), 1,2)

    אנחנו מכניסים פה באותו statement (שקל וחומר שזה אומר שזה באותה טרנזקציה) מס’ ערכים לטבלת FatherSonInfo – כאשר הראשון והשני כפולים.  התוצאה שנקבל היא ההודעה הבאה:

    Duplicate key was ignored.

     

    (2 row(s) affected)

    כלומר, קיבלנו אזהרה – אבל השורות הוכנסו. בצורה הזאת, היינו יכולים להבטיח כבר בשלב ההכנסה שמצד אחד לא יהיו לנו שורות “מיותרות” בטבלה, מצד שני לא להעסיק את עצמנו עצמאית בשאילתה של IF NOT EXISTS ובכל זאת להבטיח ייחודיות של ערכים.

    לעומת זאת, אם זה היה מתבצע מול טבלה שהאינדקס היוניקי מוגדר בה בתור IGNORE_DUP_KEY = OFF (או פשוט בלי אזכור של IGNORE_DUP_KEY, כי ה- default הוא OFF) אז היינו מקבלים שגיאה שנראית ככה:

    Msg 2601, Level 14, State 1, Line 2

    Cannot insert duplicate key row in object 'dbo.FatherSonInfo' with unique index 'IX_FatherSonInfo_Unique_FatherID_SonID'. The duplicate key value is (1, 1).

    The statement has been terminated.

    ולא הייתה מוכנסת אף שורה לטבלה.

    אם הכפילות הייתה למשל בתאריך ובשעה, שהם לא חלק מה-key columns של ה- unique index שלנו – אז כמובן שבאף אחד מהמקרים זה לא היה מכשיל את הפעולה.

    חשוב לציין גם שבעוד שהדוגמאות שהראיתי היו כשה- IGNORE_DUP_KEY מוגדר על nonclustered index, הוא יכול להיות מוגדר גם על unique clustered index באותה הצורה (וגם על ה- primary key).

    מה עושים כאשר יש הרבה Key Columns?

    אם יש לכם הרבה Key Columns שלפיהם אתם רוצים לעשות את היוניקיזציה – אתם עלולים להיתקל באחת משתי בעיות: (1) שמכיוון שיש הרבה עמודות, שלוקחות פוטנציאלית הרבה נפח, האינדקס יהיה גדול מבחינת הנפח שלו (וגם פעולות שונות עליו יהיו יותר איטיות, כי יערבו יותר IO), או ש- (2) תעברו את מגבלת 900 הבתים של ה- Key Column ואז בכלל לא תוכלו להגדיר את האינדקס כמו שאתם רוצים.

    במקרה כזה, אפשר להוסיף עמודה, נקרא לה UniqueHash, שבה בזמן ההכנסה תחשבו MD5 hash (למשל) על ה- string שנוצר מ- concat של השדות שאתם רוצים שיהיו ה- key שלכם מופרדים באיזשהו seperator. למשל, נניח שהערכים שלי הם DataA, DataB, DataC,…DataZ אז נחשב hash על DataA~DataB~DataC ונשמור את ה- hash, כאשר את ה- unique index נגדיר על ה- hash בלבד.

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

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

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

     

    השפעה של IGNORE_DUP_KEY על עבודה מהקוד

    אני רוצה להתייחס לכמה אספקטים חשובים שנוגעים לעבודה מקוד מול טבלאות שיש עליהם unique index שמוגדר עליו IGNORE_DUP_KEY = ON.

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

    ADO.NET “קלאסי”

    אם אתם משתמשים באובייקטים הבסייים של ADO.NET לטובת עבודה, כלומר עובדים עם SqlCommand / SqlConnection ישירות, אז אם היה מוגדר unique index עם IGNORE_DUP_KEY = OFF (הדיפולט) והייתם מכניסים ערכים כפולים, היה עף SqlException.
    אם אתם מכניסים ערכים כפולים לאינדקס שמוגדר עם IGNORE_DUP_KEY = ON, תוכלו להירשם ל-event של SqlConnection שנקרא InfoMessage כדי לקבל את כל הטקסט של ה- messages, ובכלל זה את המידע על ה- duplicate key ignored.
    דרך יותר אלגנטית תהיה לשלוף את הערך של @@ROWCOUNT, כלומר מספר השורות שהושפעו ע”י השאילתות הכנסה שלכם. אם הוא קטן ממספר השורות שהכנסתם בטבלה שיש לה אינדקס שמוגדר עם IGNORE_DUP_KEY=ON, הגיוני שלשם “נעלמו” השורות החסרות.

    BULK INSERT

    אם אתם משתמשים ב- SqlBulkCopy לטובת ההכנסות לטבלה שמוגדרת עם IGNORE_DUP_KEY=ON – הכל יהיה שקוף לכם. פשוט תזרקו את המידע, וייכנס רק מה שצריך להיכנס.

    Entity Framework

    כאשר אתם מבצעים הכנסת שורות באמצעות Entity Framework(ועדכון, כמובן) אז כשאתם עושים SaveChanges, מאחורי הקלעים Entity Framework בודק שהפעולה של ההוספה/עדכון אכן התבצעה. אם היא לא, נזרק exception מ- SaveChanges ששמו DbUpdateException. כלומר, כשננסה להריץ את הקוד הבא:

    using (var data = new check1Entities())

    {

        data.FatherSonInfoes.Add(new FatherSonInfo() {FatherID = 1, SonID = 2, DateCreated = DateTime.Now});

        data.FatherSonInfoes.Add(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

        data.SaveChanges();

    }

    נקבל:

    image

    ה- Inner Exception פה הוא מסוג OptimisticConcurrencyException.

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

    חשוב לזכור שכשאנחנו עושים Save Changes, מאחורי הקלעים entity framework לוקח את רשימת השינויים שהוא מכיר (כברירת מחדל הוא עושה change tracking אוטומטי, אם מבטלים את זה [למשל כדי לשפר ביצועים של קריאה למתודה Add], חשוב לסמן את ה- Entity ב- state המתאים [Added/Modified/Deleted…]).
    בהסתמך על רשימת השינויים, Entity Framework מבין איזה שאילתות צריך להריץ וגם באיזה סדר. למשל, אם יש לנו אובייקט A ש-“מכיל” הרבה אובייקטים מסוג B (כלומר, ל-B שמור המפתח של A) – אז קודם כל נדרשת הכנסה של A, קבלת ה-ID (המפתח) ואז ההכנסה של כל האובייקטים מסוג B ששוייכו ל-A, תוך שימוש במפתח כדי לעשות את הקישור ברמת ה-DB.

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

    exec sp_executesql N'INSERT [dbo].[FatherSonInfo]([DateCreated], [FatherID], [SonID])

    VALUES (@0, @1, @2)

    SELECT [ID]

    FROM [dbo].[FatherSonInfo]

    WHERE @@ROWCOUNT > 0 AND [ID] = scope_identity()',N'@0 datetime2(7),@1 int,@2 int',@0='2016-10-26 21:37:55.0438727',@1=1,@2=2

     

    נשים לב שהוא עושה INSERT, ואז שולף את ה-ID שהוקצה לישות (כי זה ה- key שמתמלא ע”י ה-DB, והוא רוצה למלא את ה- entity בצד של ה- client), אבל עושה את זה רק בתנאי שה- @@ROWCOUNT (כמות השורות שהושפעו) גדול מ-0.

    המטרה של Entity Framework בבדיקות הללו ב-WHERE, היא ששיטת העבודה של Entity Framework עצמו מבוססת Optimistic Concurrecy (וכמובן, גם השאילתה יכולה לרוץ ב- Isolation Level מבוסס optimistic concurrency ברמת הדטאבייס). ולמעשה, באמצעות ה- WHERE הזה הוא בודק שההנחות שלו התקיימו בפועל. ששורה שהוא חשב שהתווספה אכן התווספה, או ששורה שהוא רוצה לעדכן באמת נראית טרם תחית העדכון כמו שהוא חושב שהיא נראית (כלומר, שהיא לא השתנתה במקביל “מתחת לרגליים”).

    במקרה שיש כישלון, ולמעשה לא מתאמת שום דבר על ה-WHERE כמו במקרה שלנו, הוא מניח שהאופטימיות נכשלה – ושזה מעיד על בעייה. ואז נזרק DbUpdateException שעוטף InnerException מסוג OptimisticConcurrencyException.

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

    אנחנו יכולים לכתוב קוד בסגנון הזה:

    using (var data = new check1Entities())

    {

        data.FatherSonInfoes.Add(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

        data.FatherSonInfoes.Add(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

        data.FatherSonInfoes.Add(new FatherSonInfo() { FatherID = 91, SonID = 8, DateCreated = DateTime.Now });

        try

        {

            data.SaveChanges();

        }

        catch (DbUpdateException ex)

        {

            var optimisticConcurrencyException = ex.InnerException as OptimisticConcurrencyException;

            if (optimisticConcurrencyException != null)

            {

                if (optimisticConcurrencyException.StateEntries.Any(k => k.State != EntityState.Added))

                {

                    throw;

                }

            }

        }

    }

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

    אפשר לעקוף את זה בכל מיני דרכים, למשל להפריד לחלקים ולהכניס כל פעם entity בודד ולעשות SaveChanges. או לחלופין, במקרה של exception, לעשות לולאה של retries ולהוריד כל פעם את ה- entity הבעייתי. אבל, כאמור, כל אלה הם מעקפים – כי אין באמת פיתרון אלגנטי (לפחות לא כזה שאני מכיר).

    הבעייה המהותית פה היא שגם אם הייתה דרך לבטל את המנגנון של הבדיקה של העדכונים – לא היה טוב להחיל את ההתנהגות הזאת באופן גורף, כי יש סיכוי שמתישהו (אפילו בלי שנשים לב) ה- SaveChanges שלנו יגלם בתוכו שינויים נוספים, מעבר ל- INSERT-ים האלה שאנחנו יודעים שגורמים ל- exception הנ”ל, שעבורם השגיאה כן תהיה רלוונטית.

    מעבר לזה, ה- exception הזה הוא לא הבעייה היחידה שלנו. כאשר אנחנו מיישמים את הטריק הזה ברמת ה-DB, אנחנו צריכים להיות מודעים לו ברמת הקוד כאשר אנחנו עובדים עם Entity Framework, כי חלק מה-“קסמים” שהוא עושה עבורנו עלולים להיפגע. למשל, אם הישות שאנחנו מוסיפים לטבלה מהסוג הזה מכילה relationship מול טבלאות אחרות שבה לידי ביטוי בתור object graph, גם אם “נבלע” את הדילוג על השורה הספיציפית, נשאלת השאלה מה אנחנו עושים עם כל האובייקטים שהוא מפנה אליהם ולהיפך? במקרה כזה, לא ניתן יהיה לייצג את ה- object graph של ה- entities בתור קשרים בין שורות שונות בטבלאות שלנו – כי למשל אחת מהשורות לא הוכנסה.

    למעשה, ברגע שאנחנו מגדירים IGNORE_DUP_KEY = ON, זה מכריח אותנו להתמודד עם מצב שבו אנחנו “מדלגים” על הכנסת שורה מסויימת. וזה אומר שאנחנו לא יכולים להשתמש בחלק מהפיצ’רים של Entity Framework אם entities שייכנסו לטבלה שבה קיים אינדקס כזה.

    מה שמביא אותנו להמלצה שלי – אם אתם מיישמים את הטריק הזה עם Entity Framework, תדאגו להבדיל בקוד שלכם את ההכנסה לטבלה המדוברת ביחס לטבלאות אחרות.

    עבור פריטים בודדים

    אם אתם בד”כ מכניסים פריטים בודדים בכל פעם, אתם יכולים להפריד את זה ליחידת קוד (מתודה/מחלקה/whatever) שאחראית על ההוספה, בצורה שיהיה ברור למי שמשתמש בה שלא מדובר בהוספה סטנדרטית של Entity Framework, כי מכיוון שאנחנו מראש מודעים למצב שיש סיכוי שההכנסה שלנו לא תתבצע ואנחנו רוצים שזה יקרה, זה מגביל אותנו בפיצ’רים מסויימים (כמו relationship שמתמפה ל- object graph) שלא נרצה לעשות בהם שימוש.

    במקרה כזה מה שנעשה זה נוודא שאת ההכנסה אנחנו עושים מ- instance נפרד של ה-context שלנו, ואז אנחנו יודעים שרק הפריט המסויים הזה כלול למעשה במשימה שמגולמת ב- SaveChanges, ולכן אם עף exception של OptimisticConcurrencyException, נוכל להתעלם ממנו בבטחה.

    static void Main(string[] args)

    {

        AddSingleItem(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

        AddSingleItem(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

        AddSingleItem(new FatherSonInfo() {FatherID = 91, SonID = 81, DateCreated = DateTime.Now});

    }

     

    static void AddSingleItem(FatherSonInfo item)

    {

        using (var data = new check1Entities())

        {

            data.FatherSonInfoes.Add(item);

            try

            {

                data.SaveChanges();

            }

            catch (DbUpdateException ex) when (ex.InnerException is OptimisticConcurrencyException)

            {

                //ignore.

            }

        }

    }

     

    אם אנחנו מכניסים מספר פריטים

    במקרה כזה, מומלץ שכבר נעשה שימוש בפיצ’ר ה- Bulk Insert ואז אפשר להשתמש ב- EntityFramework.BulkInsert לטובת ההכנסה, כמו בקוד הבא (אפשר לקרוא עוד על BULK INSERT פה):

    using (var data = new check1Entities())

    {

        List<FatherSonInfo> items = new List<FatherSonInfo>()

        {

            new FatherSonInfo() {FatherID = 1, SonID = 2, DateCreated = DateTime.Now},

            new FatherSonInfo() {FatherID = 1, SonID = 2, DateCreated = DateTime.Now},

            new FatherSonInfo() {FatherID = 91, SonID = 8, DateCreated = DateTime.Now}

        };

     

        data.BulkInsert(items);

    }

     

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

     

    בהצלחה!