גיבוי של SQL Server באמצעות תמונות מצב מיידיות

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

במדריך הזה מוסבר איך לגבות מסדי נתונים של SQL Server באמצעות תמונות מצב של Compute Engine ותכונת תמונות המצב של Transact-SQL ‏ (T-SQL) שזמינה ב-SQL Server 2022 ואילך. הפתרון הזה תומך בפריסות של Windows ו-Linux, וממזער את ההשפעה על הביצועים של עומסי העבודה הפעילים.

איך זה עובד

תהליך העבודה מורכב מהשלבים העיקריים הבאים, שמנוהלים על ידי סקריפט שפועל במופע של Compute:

  1. הקפאת מסד הנתונים: הסקריפט שולח פקודת T-SQL ל-SQL Server כדי להשהות את כל פעולות הכתיבה למסדי הנתונים של היעד. כך אפשר לוודא שקובצי מסד הנתונים נמצאים במצב עקבי לצורך הגיבוי.
  2. יצירת קובצי snapshot מיידיים: בזמן שמסד הנתונים מוקפא, יוצרים קבוצה של קובצי snapshot מיידיים של הדיסקים שבהם נמצאים קובצי הנתונים וקובצי היומן של מסד הנתונים. זהו Google Cloudהפתרון המקביל לשימוש במנגנון של תמונת מצב ברמת החומרה או השירות.
  3. תיעוד והפשרה: הסקריפט שולח פקודת T-SQL נוספת ל-SQL Server כדי לתעד את מטא-נתוני הגיבוי. הפקודה הזו יוצרת קובץ גיבוי קטן שמפנה לקבוצת העקביות של תמונות המצב המיידיות, והיא מתועדת בהיסטוריית הגיבויים של msdb. בסיום, SQL Server מפשיר אוטומטית את מסד הנתונים וממשיך בפעולות הרגילות.

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

מטרות

במדריך הזה מוסבר איך לבצע את הפעולות הבאות:

  • יוצרים מכונה של SQL Server עם שני דיסקים של נתונים.
  • ליצור מסד נתונים חדש עם קובצי הנתונים וקובצי היומן בדיסקים נפרדים.
  • יוצרים קבוצת אפליקציות עקביות של כל הדיסקים של ה-VM שמריץ את SQL Server.
  • ליצור תמונות מצב מיידיות של קבוצת הדיסקים.
  • יצירת דיסקים חדשים מקובצי ה-snapshot המיידיים.

עלויות

במסמך הזה משתמשים ברכיבים הבאים של Google Cloud, והשימוש בהם כרוך בתשלום:

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

משתמשים חדשים של Google Cloud ? יכול להיות שאתם זכאים לתקופת ניסיון בחינם.

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

לפני שמתחילים

  1. במדריך הזה צריך פרויקט ב- Google Cloud . אפשר ליצור פרויקט חדש או לבחור פרויקט קיים:

    1. בדף לבחירת הפרויקט במסוף Google Cloud , בוחרים פרויקט ב- Google Cloud או יוצרים אותו.

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

      • בחירת פרויקט: כדי לבחור פרויקט לא נדרש תפקיד IAM ספציפי – אתם יכולים לבחור כל פרויקט שקיבלתם בו תפקיד.
      • יצירת פרויקט: כדי ליצור פרויקט, צריך את התפקיד "יצירת פרויקטים" (roles/resourcemanager.projectCreator), שכולל את ההרשאה resourcemanager.projects.create. איך מקצים תפקידים

      כניסה לדף לבחירת הפרויקט

    2. מוודאים שהחיוב מופעל בפרויקט Google Cloud .

    3. מפעילים את Cloud Shell במסוף Google Cloud .

      הפעלת Cloud Shell

  2. מוודאים ש-Microsoft SQL Server 2022 ואילך מותקן ופועל.

ההרשאות הנדרשות

בנוסף לגישת קריאה רגילה, ודאו שהאדמין של SQL Server נותן לכם את ההרשאה ALTER DATABASE למסד הנתונים הרלוונטי.

כדי לקבל את ההרשאות שנדרשות ליצירת מכונות וליצירת קובצי snapshot, צריך לבקש מהאדמין להקצות לכם את תפקידי ה-IAM הבאים בפרויקט:

  • ניהול מופע: compute.instanceAdmin.v1
  • יצירת תמונות מצב: compute.storageAdmin

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

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

יצירת מכונה של Compute Engine SQL

יוצרים מכונה של SQL Server.

  1. במסוף Google Cloud , לוחצים על הכפתור Activate Cloud Shell מפעילים את Cloud Shell. כדי לפתוח את Cloud Shell.

    כניסה ל Google Cloud מסוף

  2. יוצרים מכונה של SQL Server. מדביקים את הפקודה הבאה:

  REGION=REGION
  ZONE=$REGION-a
  VM_NAME=VM_NAME
  gcloud compute instances create $VM_NAME \
    --boot-disk-auto-delete \
    --boot-disk-size 100 \
    --boot-disk-type hyperdisk-balanced \
    --image-family sql-std-2022-win-2025 \
    --image-project windows-sql-cloud \
    --machine-type c4-highmem-4 \
    --zone $ZONE \
    --network-interface subnet=default \
    --tags sql-server-instant-snapshot \
    --scopes=cloud-platform,service-control,service-management,monitoring-write,logging-write,storage-rw \
    --create-disk=device-name=$VM_NAME-data-disk1,mode=rw,name=$VM_NAME-data-disk1,size=100,type=hyperdisk-balanced \
    --create-disk=device-name=$VM_NAME-data-disk2,mode=rw,name=$VM_NAME-data-disk2,size=100,type=hyperdisk-balanced

מחליפים את מה שכתוב בשדות הבאים:

  • אזור: האזור שבו המופע החדש ייפרס.
  • ‫VM_NAME: השם של מכונת SQL Server החדשה.

יצירת קבוצת עקביות של דיסקים

  1. יוצרים קבוצת אפליקציות עקביות.

    gcloud compute resource-policies create disk-consistency-group $VM_NAME-snap-grp \
    --region=$REGION

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

    gcloud compute disks add-resource-policies $VM_NAME \
    --zone=$ZONE \
    --resource-policies=$VM_NAME-snap-grp

gcloud compute disks add-resource-policies $VM_NAME-data-disk1 \
    --zone=$ZONE \
    --resource-policies=$VM_NAME-snap-grp

gcloud compute disks add-resource-policies $VM_NAME-data-disk2 \
    --zone=$ZONE \
    --resource-policies=$VM_NAME-snap-grp

יצירת מסד נתונים חדש

  1. יוצרים שם משתמש וסיסמה למכונה הווירטואלית.
  2. מתחברים למכונה הווירטואלית באמצעות Remote Desktop ונכנסים באמצעות שם המשתמש והסיסמה שנוצרו בשלב הקודם.
  3. לוחצים לחיצה ימנית על הלחצן Start (או מקישים על Win+X) ואז לוחצים על Terminal (Admin).
  4. לוחצים על כן כדי לאשר את ההנחיה להעלאת הרשאות.

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

    $availableDisks = Get-PhysicalDisk -CanPool $True
$diskLetters = [char[]] (68..72) | Where-Object { !(Get-PSDrive $_ -ErrorAction SilentlyContinue) }
$diskCount = 0
foreach ($disk in $availableDisks) {
    $diskLetter = $diskLetters[$diskCount]
    $diskLabel = "$diskLetter-DataDisk"
    Initialize-Disk -Number $disk.DeviceId -PartitionStyle MBR -PassThru

    New-Partition -DiskNumber $disk.DeviceId -UseMaximumSize `
    -DriveLetter $diskLetter | Format-Volume -FileSystem NTFS `
    -NewFileSystemLabel $diskLabel -AllocationUnitSize 65536 -Confirm:$false

    New-Item -ItemType Directory -Path "$($diskLetter):\MSSQL"
    $diskCount = $diskCount +1
}

  6. פותחים את SQL Server Management Studio ‏ (SSMS). מריצים אותו כאדמין.

  7. בתיבת הדו-שיח Connect to server, מוודאים ששם השרת הוא localhost ולוחצים על Connect.

  8. בתפריט הקובץ, בוחרים באפשרות קובץ > חדש > שאילתה עם החיבור הנוכחי.

  9. מעתיקים את הקוד הבא לחלון השאילתה שנפתח.

    USE Master;
GO
CREATE DATABASE SnapBackupDB
ON PRIMARY
(
    NAME = 'SnapBackupDB_Data',
    FILENAME = 'D:\MSSQL\SnapBackupDB.mdf',
    SIZE = 500MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 64MB
)
LOG ON
(
    NAME = 'SnapBackupDB_Log',
    FILENAME = 'E:\MSSQL\SnapBackupDB_log.ldf',
    SIZE = 250MB,
    MAXSIZE = 2GB,
    FILEGROWTH = 64MB
);

פריסת סקריפט של תמונת מצב

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

  1. באותו סשן של Remote Desktop, פותחים את פנקס הרשימות.
  2. מעתיקים את התוכן של הסקריפט הבא ומדביקים אותו בחלון של פנקס הרשימות.

  3. שומרים את הקובץ בשם take-snapshot.ps1 בתיקייה c:\scripts

    # Import Modules
Import-Module -Name SQLPS

# Set variables
$formattedTimestamp = Get-Date -Format 'yyyyMMdd-HHmmss'
$backupLocation = 'D:\MSSQL\Backup'
$dbName = 'SnapBackupDB'
$bkmFile = Join-Path -Path $backupLocation  -ChildPath ("${dbName}_${formattedTimestamp}.bkm")
$sqlServer = 'localhost'

# SQL Commands
$suspendDbCmd = "ALTER DATABASE [$dbName] SET SUSPEND_FOR_SNAPSHOT_BACKUP=ON WITH NO_WAIT;"
$backupMetadataCmd = "BACKUP DATABASE [$dbName] TO DISK='$bkmFile' WITH METADATA_ONLY, FORMAT;"
$unsuspendDbCmd = "ALTER DATABASE [$dbName] SET SUSPEND_FOR_SNAPSHOT_BACKUP=OFF;"

# --- Helper Function for SQL Execution ---
function Invoke-MySqlCommand {
    param(
        [Parameter(Mandatory=$true)]
        [string]$CommandText,
        [Parameter(Mandatory=$true)]
        [System.Data.SqlClient.SqlConnection]$SqlConnection
    )
    Write-Host "Executing SQL Command: $($CommandText)"
    try {
        $Command = New-Object System.Data.SqlClient.SqlCommand($CommandText, $SqlConnection)
        $Result = $Command.ExecuteNonQuery()
        return $true
    }
    catch {
        Write-Host "Error executing SQL Command: $($CommandText)`n$($_.Exception.Message)" -ForegroundColor Red
        return $false
    }
}

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "server='$sqlServer';database='$dbName';Integrated Security=True;"
$databaseSuspended = $false

if (-not(Test-Path $backupLocation)) {
    New-Item -Path $backupLocation -ItemType directory -Force
}

try {
    $instanceName = (Invoke-RestMethod -Headers @{"Metadata-Flavor"="Google"} -Uri "http://metadata.google.internal/computeMetadata/v1/instance/name")
    $vmConfigString = (gcloud compute instances list --filter="name=('$instanceName')" --format=json --quiet)
    if ($LASTEXITCODE -ne 0) {
        Write-Host "Error querying gcloud for VM instances (exit code: $LASTEXITCODE). Exiting." -ForegroundColor Red
        exit 1
    }
    $vmConfig = $vmConfigString | ConvertFrom-Json

    # Open SQL Server connection
    $sqlConn.Open()

    # Suspend Database
    Write-Host "Suspending database '$dbName' for snapshot..."
    if (-not (Invoke-MySqlCommand -CommandText $suspendDbCmd -SqlConnection $sqlConn)) {
        Write-Host "Failed to suspend database. Exiting." -ForegroundColor Red
        exit 1
    }
    $databaseSuspended = $true

    # Take a disk snapshot
    try {
        $consistencyGroupListStr = (gcloud compute resource-policies list --filter='name:*-snap-grp' --format=json --quiet)
        if ($LASTEXITCODE -ne 0) { throw "gcloud resource-policies list failed (exit code: $LASTEXITCODE)." }
        $consistencyGroupList = $consistencyGroupListStr | ConvertFrom-Json

        if (@($consistencyGroupList).Count -eq 0) { throw "No consistency group found matching '*-snap-grp'." }
        if (@($consistencyGroupList).Count -gt 1) { Write-Warning "More than one consistency group found, using the first one." }

        $consistencyGroupSelfLink = $consistencyGroupList[0].selfLink
        $zoneName = Split-Path $vmConfig[0].zone -Leaf
        $snapshotName = "$($vmConfig.Name)-${formattedTimestamp}"

        $snapshotCmd = "gcloud compute instant-snapshot-groups create $snapshotName --source-consistency-group=$consistencyGroupSelfLink --zone=$zoneName --quiet"
        Invoke-Expression $snapshotCmd
        if ($LASTEXITCODE -ne 0) { throw "gcloud compute instant-snapshot-groups create failed (exit code: $LASTEXITCODE)." }
        Write-Host "Instant snapshot group created successfully."
    }
    catch {
        Write-Host "Error during snapshot creation: $($_.Exception.Message)" -ForegroundColor Red
        exit 1 # Exit after handling in finally
    }

    # Backup database metadata
    if (-not (Invoke-MySqlCommand -CommandText $backupMetadataCmd -SqlConnection $sqlConn)) {
        Write-Host "Failed to backup database metadata." -ForegroundColor Red
        exit 1
    }

    # Unsuspend Database
    Write-Host "Unsuspending database '$dbName'..."
    if (-not (Invoke-MySqlCommand -CommandText $unsuspendDbCmd -SqlConnection $sqlConn)) {
        Write-Host "Failed to unsuspend database after successful snapshot and metadata backup. Manual intervention may be required." -ForegroundColor Red
        exit 1
    }
    $databaseSuspended = $false # Successfully unsuspended

}
catch {
    Write-Host "An unhandled error occurred: $($_.Exception.Message)" -ForegroundColor Red
    exit 1
}
finally {
    if ($databaseSuspended -and ($sqlConn.State -eq [System.Data.ConnectionState]::Open)) {
        if (-not (Invoke-MySqlCommand -CommandText $unsuspendDbCmd -SqlConnection $sqlConn)) {
            Write-Host "Failed to unsuspend database in finally block. Manual intervention may be required." -ForegroundColor Red
        }
    }

    # Ensure SQL connection is closed
    if ($sqlConn.State -eq [System.Data.ConnectionState]::Open) {
        $sqlConn.Close()
    }
}

  4. לוחצים לחיצה ימנית על Start (או מקישים על Win+X) ואז לוחצים על Terminal (Admin).

  5. לוחצים על כן כדי לאשר את ההנחיה להעלאת הרשאות.

  6. מריצים את הסקריפט באמצעות הפקודה הבאה.

        C:\scripts\take-snapshot.ps1

אימות של יצירת תמונות המצב המיידיות

מריצים את הפקודות הבאות ב-Cloud Shell

  1. מוודאים שהסקריפט יצר את תמונות המצב.

        gcloud compute instant-snapshots list --filter="name:$VM_NAME*"

  2. מציגים את קבוצות קובצי ה-snapshot המיידיים.

        gcloud compute instant-snapshot-groups list --zones $ZONE

  3. לראות את הפרטים של קבוצת הצילום המיידי.

        gcloud compute instant-snapshot-groups describe  \
    INSTANT_SNAPSHOT_NAME --zone=$ZONE

  4. מעתיקים את הערך selfLink כדי להשתמש בו בקטע הבא.

שחזור קובצי ה-snapshot המיידיים לדיסקים חדשים

מריצים את הפקודות הבאות בחלון Google Cloud המסוף.

  1. יצירת דיסקים מקבוצת העקביות של קובצי snapshot מיידיים.

        gcloud compute disks bulk create --source-instant-snapshot-group \
    INSTANT_SNAPSHOT_NAME_URL \
    --source-instant-snapshot-group-region $REGION --zone=$ZONE

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

        gcloud compute disks list --zones=$ZONE --filter="name:$VM_NAME-*"

  3. יוצרים מכונה וירטואלית חדשה מהדיסקים החדשים.

    REGION=REGION
ZONE=$REGION-a
VM_NAME=VM_NAME
gcloud compute instances create $VM_NAME \
    --machine-type c4-highmem-4 \
    --zone $ZONE \
    --network-interface subnet=default \
    --tags sql-server-instant-snapshot \
    --disk=name=BOOT_DISK_NAME,boot=yes,auto-delete=no \
    --disk=name=DATA_DISK_1_NAME,mode=rw,auto-delete=no \
    --disk=name=DATA_DISK_2_NAME,mode=rw,auto-delete=no

מחליפים את מה שכתוב בשדות הבאים:

  • אזור: האזור שבו המופע החדש ייפרס.
  • ‫VM_NAME: השם של מכונת ה-SQL החדשה.
  • ‫BOOT_DISK_NAME: השם של דיסק האתחול שנוצר בשלב הקודם.
  • ‫DATA_DISK_1_NAME: השם של דיסק הנתונים הראשון שנוצר בשלב הקודם.
  • ‫DATA_DISK_2_NAME: השם של דיסק הנתונים השני שנוצר בשלב הקודם.

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

הסרת המשאבים

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

מחיקת הפרויקט

  1. נכנסים לדף Manage resources במסוף Google Cloud .

    כניסה לדף Manage resources

  2. ברשימת הפרויקטים, בוחרים את הפרויקט שרוצים למחוק ולוחצים על Delete.
  3. כדי למחוק את הפרויקט, כותבים את מזהה הפרויקט בתיבת הדו-שיח ולוחצים על Shut down.

המאמרים הבאים

  • כדאי להעמיק את הקריאה ולהכיר דוגמאות לארכיטקטורות, תרשימים ושיטות מומלצות בנושאי Google Cloud. כל אלה זמינים במרכז הארכיטקטורה של Cloud.