יצירת מכונה של SQL Server עם רמת ביצועים גבוהה

יצירת מכונה וירטואלית ב-Compute Engine עם דיסקים

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

שיקולים לגבי Persistent Disk

כדי לבחור את סוג הדיסקים לאחסון מתמיד למכונת ה-VM, כדאי לעיין בשיקולים הבאים:

• דיסק SSD מקומי מספק מיקום עם ביצועים גבוהים ל-tempdb ולקובץ ההחלפה של Windows.

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

• דיסק מתמיד שמבוסס על SSD מספק אחסון עם ביצועים גבוהים לקבצים של מסד הנתונים.

  הביצועים של Persistent Disk מבוססים על חישוב שמשתמש במספר המעבדים ובגודל הדיסק. עם 32 ליבות וירטואליות ודיסק של 1TB, הביצועים מגיעים לשיא של 40,000 פעולות קריאה לשנייה (ops) ו-30,000 פעולות כתיבה לשנייה. התפוקה הכוללת המתמשכת לקריאה ולכתיבה היא 800MB לשנייה ו-400MB לשנייה בהתאמה. המדדים האלה מייצגים את הסכום של כל נפחי ה-Persistent Disk שמצורפים למכונה הווירטואלית, כולל הכונן C:\. כדי להבטיח ביצועים עקביים, צריך ליצור דיסק SSD מקומי ולהעביר את כל פעולות הקלט/פלט שנדרשות לקובץ ההחלפה, tempdb, נתוני ההעברה וגיבויים.

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

יצירת מכונה וירטואלית ב-Compute Engine עם דיסקים

כדי ליצור מכונה וירטואלית עם SQL Server 2022 Standard שהותקן מראש ב-Windows Server 2022, פועלים לפי השלבים הבאים:

1. נכנסים לדף Create an instance במסוף Cloud de Confiance .

   כניסה לדף Create an instance

2. בשדה Name (שם), מזינים ms-sql-server.

3. בקטע Machine configuration בוחרים באפשרות General Purpose, ואז מבצעים את הפעולות הבאות:

   1. ברשימה סדרה, לוחצים על N2.
   2. ברשימה Machine type (סוג המכונה), לוחצים על n2-highmem-16 (16vCPU, 128 GB memory) (‎16 vCPU, זיכרון של ‎128 GB).

4. בקטע Boot disk (דיסק אתחול), לוחצים על Change (שינוי) ומבצעים את הפעולות הבאות:

   1. בכרטיסייה תמונות ציבוריות, לוחצים על הרשימה מערכת הפעלה ואז בוחרים באפשרות SQL Server on Windows Server.
   2. ברשימה Version, לוחצים על SQL Server 2022 Standard on Windows Server 2022 Datacenter.
   3. ברשימה סוג דיסק אתחול, לוחצים על דיסק מתמיד סטנדרטי.
   4. בשדה Size (GB) (גודל (GB)), מגדירים את גודל דיסק האתחול ל-50 GB.
   5. כדי לשמור את ההגדרות של דיסק האתחול, לוחצים על Select.

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

   1. מרחיבים את הקטע Disks.

   2. כדי ליצור דיסקים מקומיים, לוחצים על הוספת SSD מקומי ומבצעים את הפעולות הבאות:

      1. ברשימה ממשק, בוחרים את הפרוטוקול שעומד בדרישות הביצועים של המערכת.
      2. ברשימה Disk capacity, בוחרים את קיבולת הדיסק שתתאים לגודל הצפוי של קובצי tempdb.
      3. כדי לסיים את יצירת הדיסק, לוחצים על Save.

   3. כדי ליצור דיסקים נוספים, לוחצים על Add New Disk (הוספת דיסק חדש).

      1. משאירים את השדה שם ללא שינוי.
      2. ברשימה Disk source type, בוחרים באפשרות Blank disk.
      3. ברשימה Disk type בוחרים באפשרות דיסק מתמיד שמבוסס על SSD.
      4. בשדה Size (גודל), מזינים את גודל הדיסק שיכול להכיל את גודל מסד הנתונים.
      5. כדי לסיים את יצירת הדיסק השני, לוחצים על Save (שמירה).

6. כדי ליצור את המכונה הווירטואלית (VM), לוחצים על האפשרות Create.

הגדרת Windows

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

התחברות למכונה

1. נכנסים לדף VM instances במסוף Cloud de Confiance .

   כניסה לדף VM instances

2. בעמודה שם, לוחצים על שם המופע, ms-sql-server.

3. בראש דף הפרטים של המופע, לוחצים על הלחצן הגדרת סיסמה ל-Windows.

4. מציינים שם משתמש.

5. לוחצים על הגדרה כדי ליצור סיסמה חדשה למופע הזה של Windows.

6. חשוב לרשום לפניכם את שם המשתמש ואת הסיסמה כדי שתוכלו להתחבר למופע.

7. מתחברים למופע באמצעות RDP.

הגדרת נפחי דיסק

יוצרים ומעצבים את אמצעי האחסון:

1. בתפריט Start (התחל), מחפשים את האפשרות Computer Management (ניהול המחשב) ופותחים אותה.
2. בקטע אחסון, בוחרים באפשרות ניהול דיסקים.
3. כשמופיעה בקשה לאתחל את הדיסקים, מאשרים את הבחירות שמוגדרות כברירת מחדל ולוחצים על אישור.

4. יוצרים מחיצה לדיסקים של SSD מקומי:

   כדי לאתר דיסק SSD מקומי, לוחצים לחיצה ימנית על דיסק ובוחרים באפשרות מאפיינים. השם של מאפייני הדיסק המקומי מסוג SSD יהיה Google EphemeralDisk לממשק SCSI או nvme_card לממשק NVMe. גם דיסקים של SSD מקומי וגם דיסקים של SSD מתמשך מסומנים עם Unallocated מחיצות.

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

      1. מתחת לרשימת כונני הדיסק, לוחצים לחיצה ימנית על הדיסק המקומי SSD בנפח 374.98GB ובוחרים באפשרות New Simple Volume (יצירת נפח פשוט חדש).
      2. במסך הפתיחה, לוחצים על הבא כדי להפעיל את האשף של עוצמת הקול של הדיסק.
      3. בשלב Specify Volume Size (ציון גודל הנפח), משאירים את גודל הנפח בערך ברירת המחדל ולוחצים על Next (הבא) כדי להמשיך.
      4. בשלב הקצאת אות כונן או נתיב, בוחרים באפשרות P:‎ לאות הכונן ולוחצים על הבא כדי להמשיך.

      5. בשלב Format Volume (עיצוב עוצמת הקול), משנים את Allocation unit size (גודל יחידת ההקצאה) ל-8192 ומזינים pagefile ב-Volume label (תווית עוצמת הקול). כדי להמשיך, לוחצים על הבא.

         

      6. לוחצים על סיום כדי להשלים את האשף של נפח הדיסק.

   2. אם המכונה הווירטואלית מכילה כמה כונני SSD מקומיים, פועלים לפי השלבים הבאים:

      1. מתחת לרשימת כונני הדיסק, לוחצים לחיצה ימנית על הדיסק הראשון מסוג SSD מקומי בנפח 374.98GB ובוחרים באפשרות New Striped Volume (כרך חדש עם פסי נתונים).
      2. במסך הפתיחה, לוחצים על הבא כדי להפעיל את האשף של עוצמת הקול של הדיסק.

      3. בשלב Select Disks, מוסיפים לקטע Selected את כל הדיסקים הזמינים בגודל 383,982MB. כדי להמשיך, לוחצים על הבא.

         

      4. בשלב הקצאת אות כונן או נתיב, בוחרים באפשרות P:‎ לאות הכונן ולוחצים על הבא כדי להמשיך.

      5. בשלב Format Volume (עיצוב עוצמת הקול), משנים את Allocation unit size (גודל יחידת ההקצאה) ל-8192 ומזינים pagefile ב-Volume label (תווית עוצמת הקול). כדי להמשיך, לוחצים על הבא.

         

      6. לוחצים על סיום כדי להשלים את האשף של נפח הדיסק.

5. חוזרים על השלבים הקודמים כדי ליצור New Simple Volume (כרך פשוט חדש) לדיסק ה-SSD, עם שלושת השינויים הבאים:

   • בוחרים באפשרות D:‎ לאות הכונן.

   • מגדירים את Allocation unit size ל-64k.

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

   • מזינים sqldata בשדה תווית עוצמת הקול.

העברת קובץ ההחלפה של Windows

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

1. בתפריט התחלה, מחפשים את האפשרות הצגת הגדרות מערכת מתקדמות ופותחים את תיבת הדו-שיח.
2. לוחצים על הכרטיסייה מתקדם, ובקטע ביצועים לוחצים על הגדרות.
3. בקטע זיכרון וירטואלי, לוחצים על הלחצן שינוי.
4. מבטלים את סימון התיבה ניהול אוטומטי של גודל קובץ ההחלפה לכל הכוננים. המערכת אמורה להגדיר את קובץ ההחלפה בכונן C:\, ואתם צריכים להעביר אותו.
5. לוחצים על C:‎ ואז על לחצן הבחירה No paging file (אין קובץ החלפה).
6. לוחצים על הלחצן הגדרה.
7. כדי ליצור את קובץ ההחלפה החדש, לוחצים על הכונן P: ואז על לחצן הבחירה גודל בניהול המערכת.
8. לוחצים על הלחצן הגדרה.

9. לוחצים על אישור שלוש פעמים כדי לצאת ממאפייני המערכת המתקדמים.

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

הגדרת פרופיל צריכת החשמל

מגדירים את פרופיל צריכת החשמל ל-High-Performance במקום ל-Balanced.

1. בתפריט Start (התחלה), מחפשים את האפשרות Choose a Power Plan (בחירת תוכנית צריכת חשמל) ופותחים את אפשרויות צריכת החשמל.
2. לוחצים על כפתור הבחירה ביצועים גבוהים.
3. יוצאים מתיבת הדו-שיח.

הגדרת שרת SQL

משתמשים ב-SQL Server Management Studio כדי לבצע את רוב משימות הניהול. קובצי האימג' שהוגדרו מראש ל-SQL Server מגיעים עם Management Studio שכבר מותקן. מפעילים את Management Studio ואז לוחצים על Connect כדי להתחבר למסד הנתונים שמוגדר כברירת מחדל.

העברת הנתונים וקבצי היומן

הקובץ האימג' של SQL Server שמוגדר מראש כולל את כל מה שמותקן בכונן C:\, כולל מסדי הנתונים של המערכת. כדי לייעל את ההגדרה, צריך להעביר את הקבצים האלה לכונן D:\ החדש שיצרתם. חשוב גם לזכור ליצור את כל מסדי הנתונים החדשים ב-Drive של D:\. מכיוון שאתם משתמשים ב-SSD, אין צורך לאחסן את קובצי הנתונים ואת קובצי היומן במחיצות נפרדות בדיסק.

יש שתי דרכים להעביר את ההתקנה לדיסק המשני: באמצעות קובץ ההתקנה או העברת הקבצים באופן ידני.

שימוש בכלי להתקנה

כדי להשתמש בקובץ ההתקנה, מריצים את c:\setup.exe ובוחרים נתיב התקנה חדש בדיסק המשני.

העברה ידנית של הקבצים

מעבירים את מסדי הנתונים של המערכת ומגדירים את SQL Server כך שישמור את הנתונים ואת קובצי היומן באותו נפח אחסון:

1. יוצרים תיקייה חדשה בשם D:\SQLData.
2. פותחים חלון פקודות.

3. מזינים את הפקודה הבאה כדי להעניק גישה מלאה ל-NT Service\MSSQLSERVER:

   icacls D:\SQLData /Grant "NT Service\MSSQLServer:(OI)(CI)F"
   

4. כדי להעביר את מסדי הנתונים של המערכת ולשנות את מיקומי הקבצים שמוגדרים כברירת מחדל למסדי נתונים חדשים, אפשר להשתמש ב-Management Studio ובמדריכים הבאים.

5. אם אתם מתכננים להשתמש בתכונות של Report Server, צריך להעביר גם את הקבצים ReportServer ו-ReportServerTempDB.

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

ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'D:\SQLData\model.mdf' )
ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'D:\SQLData\modellog.ldf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\SQLData\MSDBData.mdf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBlog , FILENAME = 'D:\SQLData\MSDBLog.ldf' )

אחרי שמריצים את הפקודות האלה:

1. משתמשים בתוסף services.msc כדי לעצור את שירות מסד הנתונים של SQL Server.
2. משתמשים בסייר הקבצים של Windows כדי להעביר את הקבצים הפיזיים מכונן C:\ שבו נמצא מסד הנתונים master לספרייה D:\SQLData.
3. מפעילים את שירות מסד הנתונים של SQL Server.

הגדרת הרשאות מערכת

אחרי שמזיזים את מסדי הנתונים של המערכת, צריך לשנות כמה הגדרות נוספות, ומתחילים עם ההרשאות של חשבון המשתמש ב-Windows שנוצר כדי להריץ את התהליך של SQL Server, שנקרא NT Service\MSSQLSERVER.

מתן הרשאה ל-Lock Pages in Memory

הרשאת מדיניות הקבוצה Lock Pages in Memory מונעת מ-Windows להעביר דפים בזיכרון הפיזי לזיכרון וירטואלי. כדי לשמור על הזיכרון הפיזי פנוי ומאורגן, Windows מנסה להעביר דפים ישנים שמשתנים לעיתים רחוקות לקובץ ההחלפה של הזיכרון הווירטואלי בדיסק.

‫SQL Server מאחסן בזיכרון מידע חשוב, כמו מבני טבלאות, תוכניות ביצוע ושאילתות שנשמרו במטמון. חלק מהמידע הזה משתנה לעיתים רחוקות, ולכן הוא הופך ליעד לקובץ ההחלפה. אם המידע הזה מועבר לקובץ ההחלפה, הביצועים של SQL Server עלולים להיפגע. הענקת הרשאה למדיניות הקבוצה Lock Pages in Memory לחשבון השירות של SQL Server מונעת את ההחלפה הזו.

איך לעשות את זה?

1. לוחצים על התחל ואז מחפשים את האפשרות עריכת מדיניות קבוצתית כדי לפתוח את המסוף.
2. מרחיבים את האפשרויות Local Computer Policy > Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment.
3. מחפשים את האפשרות Lock pages in memory (נעילת דפים בזיכרון) ולוחצים עליה פעמיים.
4. לוחצים על הוספת משתמש או קבוצה.
5. מחפשים את NT Service\MSSQLSERVER.
6. אם מופיעים כמה שמות, לוחצים לחיצה כפולה על השם MSSQLSERVER.
7. לוחצים פעמיים על אישור.
8. משאירים את מסוף Group Policy Editor פתוח.

מתן הרשאה ל-Perform volume maintenance tasks

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

יש API נפרד להקצאת שטח דיסק לאפליקציה, שלעתים קרובות נקרא אתחול מיידי של קובץ. לצערנו, ההגדרה הזו פועלת רק עבור קובצי נתונים, אבל בהמשך נסביר על גידול בקובצי יומן. כדי להשתמש באתחול מיידי של קבצים, לחשבון השירות שמריץ את תהליך SQL Server צריכה להיות הרשאה נוספת של מדיניות קבוצתית שנקראת Perform volume maintenance tasks.

1. בעורך מדיניות הקבוצה, מחפשים את האפשרות 'ביצוע משימות תחזוקה של עוצמת הקול'.
2. מוסיפים את החשבון NT Service\MSSQLSERVER כמו שעשיתם בקטע הקודם.
3. מפעילים מחדש את תהליך SQL Server כדי להפעיל את שתי ההגדרות.

מתבצעת ההגדרה של tempdb

בעבר, השיטה המומלצת הייתה ליצור קובץ tempdb אחד לכל CPU כדי לבצע אופטימיזציה של השימוש ב-CPU של SQL Server. עם זאת, מכיוון שמספר ליבות המעבד גדל עם הזמן, יישום ההנחיה הזו עלול לגרום לירידה בביצועים. נקודת התחלה טובה היא שימוש ב-4 קובצי tempdb. כשבודקים את הביצועים של המערכת, יכול להיות שבמקרים נדירים יהיה צורך להגדיל בהדרגה את מספר הקבצים מסוג tempdb עד למקסימום של 8.

אפשר להריץ סקריפט של Transact-SQL ‏ (T-SQL) בתוך SQL Server Management Studio כדי להעביר את הקבצים tempdb לתיקייה בכונן p:.

1. יוצרים את הספרייה p:\tempdb.

2. נותנים גישת אבטחה מלאה לחשבון המשתמש NT Service\MSSQLSERVER:

   icacls p:\tempdb /Grant "NT Service\MSSQLServer:(OI)(CI)F"
   

3. מריצים את הסקריפט הבא ב-SQL Server Management Studio כדי להעביר את קובץ הנתונים tempdb ואת קובץ היומן:

   USE master
   GO
   ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf')
   GO
   ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf')
   GO
   

4. מפעילים מחדש את SQL Server.

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

   ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf', SIZE=8GB)
   ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf' , SIZE = 2GB)
   ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev1', FILENAME = 'p:\tempdb\tempdev1.ndf' , SIZE = 8GB, FILEGROWTH = 0);
   ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev2', FILENAME = 'p:\tempdb\tempdev2.ndf' , SIZE = 8GB, FILEGROWTH = 0);
   ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev3', FILENAME = 'p:\tempdb\tempdev3.ndf' , SIZE = 8GB, FILEGROWTH = 0);
   GO
   

   אם אתם משתמשים ב-SQL Server 2016, יש עוד 3 קבצים tempdb שצריך להסיר אחרי שמבצעים את השלבים הקודמים:

   ALTER DATABASE [tempdb] REMOVE FILE temp2;
   ALTER DATABASE [tempdb] REMOVE FILE temp3;
   ALTER DATABASE [tempdb] REMOVE FILE temp4;
   

6. מפעילים מחדש את SQL Server.

7. מוחקים את הקבצים model, MSDB, master ו-tempdb מהמיקום המקורי בכונן C:\.

העברת בהצלחה את הקבצים tempdb למחיצת הדיסק של ה-SSD המקומי. יש סיכונים מסוימים במעבר הזה, כמו שצוין קודם, אבל אם הקבצים האלה אבדו מסיבה כלשהי,SQL Server בונה מחדש את קובצי tempdb. העברה של tempdb מאפשרת לכם ליהנות מביצועים משופרים של ה-SSD המקומי, ומפחיתה את מספר פעולות הקלט/פלט בשנייה (IOPS) שנעשה בהן שימוש בנפחי ה-Persistent Disk.

הגדרה max degree of parallelism

ההגדרה המומלצת של ברירת המחדל ל-max degree of parallelism היא להתאים אותה למספר המעבדים בשרת. עם זאת, יש נקודה שבה ביצוע שאילתה ב-16 או ב-32 נתחים מקבילים ומיזוג התוצאות איטי הרבה יותר מביצוע שלה בתהליך יחיד. אם אתם משתמשים במופע עם 16 או 32 ליבות, אתם יכולים להגדיר את הערך max degree of parallelism ל-8 באמצעות T-SQL:

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max degree of parallelism', 8
GO
RECONFIGURE WITH OVERRIDE
GO

הגדרה max server memory

ההגדרה הזו מוגדרת כברירת מחדל למספר גבוה מאוד, אבל כדאי להגדיר אותה למספר המגה-בייט של זיכרון ה-RAM הפיזי הזמין, פחות כמה גיגה-בייט למערכת ההפעלה ולתקורה. בדוגמה הבאה של T-SQL, המגבלה max server memory משתנה ל-100GB. משנים אותו כדי להתאים את הערך למופע שלכם. מידע נוסף זמין במאמר אפשרויות להגדרת השרת של זיכרון השרת.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
exec sp_configure 'max server memory', 100000
GO
RECONFIGURE WITH OVERRIDE
GO

לקראת סיום

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