גיבוי של 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. יצירת תמונות מצב מיידיות: בזמן שמסד הנתונים קפוא, יוצרים קבוצה של תמונות מצב מיידיות של הדיסקים שבהם נמצאים נתוני מסד הנתונים וקבצי היומן. זהו Google Cloud הפתרון המקביל לשימוש במנגנון צילום מצב (snapshot) ברמת החומרה או השירות.
  3. תיעוד והפשרה: הסקריפט שולח פקודת T-SQL נוספת ל-SQL Server כדי לתעד את מטא-נתוני הגיבוי. הפקודה הזו יוצרת קובץ גיבוי קטן שמצביע על קבוצת העקביות של תמונות המצב המיידיות, והיא מתועדת בהיסטוריית הגיבויים של msdb. עם סיום התהליך, SQL Server מפשיר אוטומטית את מסד הנתונים וממשיך בפעולות הרגילות.

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

מטרות

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

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

עלויות

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

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

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

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

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

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

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

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

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

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

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

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

      הפעלת Cloud Shell

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

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

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

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

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

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

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

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

יוצרים מכונה של 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. בתיבת הדו-שיח 'התחברות לשרת', מוודאים ששם השרת מוגדר ל-localhost, ובוחרים באפשרות התחברות.

  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. מציגים רשימה של קבוצות של תמונות מצב מיידיות.

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

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

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

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

שחזור תמונות המצב המיידיות לדיסקים חדשים

מריצים את הפקודות הבאות בחלון המסוף. 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. במסוף Google Cloud , נכנסים לדף Manage resources.

    כניסה לדף Manage resources

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

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

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