Changing INT to BigInt
אחד מלקוחותיי עדכון אותי על רצונו לבצע שינוי בבסיס הנתונים. השינוי הינו המרת שדה (int to bigint) במספר טבלאות, כאשר מיד בסיום פעולה זו יהיה עליו לבצע את העדכון גם באפליקציה של לקוחותיו.
מכיוון שיצא לי לטפל בעבר בנושא זה, האפשרויות הפתוחות בפניי לא היו חדשות.
לאחר שעברתי על הנתונים שסיפק הלקוח, עניין אותי שני דברים חשובים כדי שאוכל לדעת כיצד ברצוני לפעול:
תחילה, עניין אותי לדעת מהו הערך המקסימאלי בשדה ה-Identity המדובר וכמה קרוב הוא (באחוזים) לערך המקסימאלי של data type מסוג Integer.
לשם כך הרצתי את הקובץ הבא (Monitor Max Value - Identity Column.sql).
שנית, רציתי לדעת לכמה זמן ניתן להשבית את הסביבה ובאיזה שעות.
התשובה שקיבלתי הייתה ברורה מאליה.. מינימום downtime וניתן לבצע רק בשעות הערב המאוחרות, כאשר הלקוח מזכיר לי שגם עליו לבצע באפליקציה מספר רב של פעולות עבור עדכון השינוי.
ממצאי התוצאה בסעיף 1 (ערך מקסימלי) נתנו אוויר לנשימה, ערך ה-Identity המקסימאלי שהתקבל עמד על 17% בלבד. אפשר לחייך J
אשמח לשתף אתכם בדרך הפעולה שנקטתי לטובת מזעור זמן ההשבתה – הפעם כפעולה מונעת ולא כפעולה מתקנת.
המשימה:
צד SQL:
ביצוע המרת שדה מ-int ל-bigint עבור עמודות מסוימות ב-14 טבלאות.
קיים יחס גומלין בין הטבלאות.
צד אפליקטיבי:
בסיום פעולת המרת השדה, יבוצע מיד העדכון באפליקציה של הלקוח אשר בתחום אחריותו.
ממצאים:
מערכת הפעלה: Windows 2008 R2 Standard (16GB, 4 processors).
גרסת SQL: SQL Server 2008 R2 Standard.
נפח DB: 146GB.
טבלאות:
14 טבלאות.
כמות רשומות בטבלאות:
עבור 12 טבלאות, כמות הרשומות נעה בין 100,000 ל- 3 מיליון רשומות.
טבלה אחת עם 35 מיליון רשומות.
טבלה נוספת עם 410 מיליון רשומות !
אינדקסים:
21 אינדקסים בכל הטבלאות יחד.
מפתחות זרים (FK):
כל טבלה כללה בין 1-3 FK.
מטרה:
ביצוע הפעולה תוך מינימום זמן השבתה של שרת ה-SQL.
כמו כן, לבנות את התהליך בסביבת טסט ולאחר מכן לעלות את הפתרון לסביבת הייצור כאשר מבצע את הפעילות מאחורי הקלעים ומבלי שאני מפריע לעבודה השוטפת.
ביצוע העתקה יומית של הדלתאות שנוצרו בין הטבלאות המקוריות לחדשות כך שביום החלפת הטבלאות אצטרך להעתיק מינימום רשומות כדי להשלים את הדלתא שנוצרה.
דרכי הביצוע:
תחילה הייתי צריך להכיר את הסביבה של הלקוח ואת היחסים בין הטבלאות השונות.
לשם כך נעזרתי בדיאגרמה שהלקוח בנה מבעוד מועד.
בנוסף, השתמשתי במספר שאילתות כדי לאסוף נתונים הכרחיים לבניית התהליך
Max Identity Columns Value קבלת סטאטוס ערך מקסימאלי בטבלאות הנ"ל כדי לדעת אם אני קרוב ל-100% או שיש לי זמן לתכנן תוכנית כמו שצריך וללא לחץ.
Search References מציאת קשרים בין הטבלאות.
Get Table Size קבלת מידע אודות נפח טבלה וכמות רשומות.
Search Indexes מטרת הייתה לספור את כמות האינדקסים שקיימים בטבלאות.
לאחר איסוף המידע, יצרתי קובץ אקסל שמשמש כסוג של צ'ק ליסט וגם יכיל תיעוד של הזמנים שנדרש לי לביצוע הפעולה Convert to Bigint - Template.xlsx).
כעת מגיע השלב הטכני (מוזכר גם בקובץ האקסל):
יצירת טבלאות עם שינוי העמודה ל-bigint.
הערה – שמרתי שה-Identity יתחיל מ-1,1 כמו בטבלה המקורית. לא היה לי צורך לאתחל אותו לערך המקסימאלי.
כתיבת קוד יעיל (בלולאה) עבור העתקת המידע (Copy in Chunk - Table_1.sql).
יצירת אינדקסים.
הכנת קוד להסרת FK ושאר האילוצים מהטבלאות המקוריות (עדיין לא מריץ רק מייצר את הקוד).
כתיבת קוד דינאמי להחלפת שמות הטבלאות:
קוד - Multiple Tables אשר בסיום מדפיס למסך את הקוד.
ככלי עזר נדרשתי ליצור טבלת עזר עם שמות כל הטבלאות(AllTables).
הכנת קוד להחזרת FK ושאר האילוצים (רק מייצר קוד).
בנוסף, כדי לנטר את התהליך יצרתי שני Views אשר מבצעים השוואה בין מספר הרשומות בטבלה מקורית לטבלה החדשה וכן בין הערך המקסימאלי בטבלה מקורית לטבלה חדשה. התוצאה מוצגת גם ב-אחוזים.
הקוד בשני קבצים - CompareTables_RowCounts.sql , CompareTables_MinMaxValues.sql.
להלן תוצאות קטעי הקוד בזמן אמת:
השוואת רשומות בין טבלה מקורית לחדשה:
השוואת ערך מקסימאלי של עמודת Identity בין הטבלאות:
לסיום, יצרתי ג'וב אשר רץ פעם אחת ביום ומשלים את הפער שנוצר בין טבלת המקור לטבלה החדשה.
בכל ג'וב יצרתי Step אשר כלל את הקוד של העתקת הטבלה הרלוונטית (ניתן להדביק את הקוד כולו בחלונית הפקודה או ליצור פרוצדורה ולקרוא לה באמצעות חלון הפקודה).
לסיכום:
ביום המעבר הפעולות המינימאליות שנותר לי לבצע:
הרצת ג'וב להעתקת דלתאות של נתוני טבלאות.
הסרת FK מטבלאות מקוריות.
החלפת שמות הטבלאות.
הוספת FK עם שמות הטבלאות המקוריות.
עצירת ג'וב.
סה"כ השבתה בצד ה-SQL 20 דקות בלבד!
שיהיה בהצלחה ! J
