Mencadangkan SQL Server menggunakan snapshot instan

Jika file database SQL Server Anda tersebar di beberapa disk, Anda dapat mencadangkan semua disk secara bersamaan, sehingga menjaga konsistensi aplikasi dan data. Anda dapat mencapai pencadangan tepat waktu ini dengan grup konsistensi Compute Engine dari snapshot instan, yang memungkinkan Anda mencadangkan data di seluruh grup disk.

Tutorial ini menjelaskan cara mencadangkan database SQL Server dengan snapshot Compute Engine dan fitur snapshot Transact-SQL (T-SQL) yang tersedia di SQL Server 2022 dan yang lebih baru. Solusi ini mendukung deployment Windows dan Linux serta meminimalkan dampak performa pada workload aktif Anda.

Cara kerjanya

Alur kerja terdiri dari langkah-langkah utama berikut, yang dikelola oleh skrip yang berjalan di instance komputasi:

1. Membekukan database: skrip mengirim perintah T-SQL ke SQL Server untuk menangguhkan semua operasi tulis untuk database target. Hal ini memastikan file database berada dalam status yang konsisten untuk pencadangan.
2. Mengambil snapshot instan: saat database dibekukan, buat grup snapshot instan dari disk tempat file data dan log database berada. Ini adalah Google Cloud yang setara dengan menggunakan mekanisme snapshot tingkat layanan atau hardware.
3. Merekam dan mencairkan: skrip mengirim perintah T-SQL lain ke SQL Server untuk merekam metadata cadangan. Perintah ini membuat file cadangan kecil yang mengarah ke grup konsistensi snapshot instan dan dicatat dalam histori pencadangan msdb. Setelah selesai, SQL Server akan otomatis mencairkan database dan melanjutkan operasi normal.

Seluruh proses ini biasanya diselesaikan dalam waktu kurang dari satu detik, sehingga meminimalkan durasi pembekuan penulisan di database Anda. Selama pembekuan diterapkan, data dapat dibaca, tetapi tidak dapat ditulis. Anda dapat membatalkan status dibekukan secara manual dengan menyetel SUSPEND_FOR_SNAPSHOT_BACKUP=OFF untuk database.

Tujuan

Dalam tutorial ini, Anda akan mempelajari cara menyelesaikan tugas-tugas berikut:

• Buat instance SQL Server dengan dua disk data.
• Buat database baru dengan file data dan log di disk terpisah.
• Buat grup konsistensi dari semua disk VM yang menjalankan SQL Server.
• Buat snapshot instan dari grup disk.
• Buat disk baru dari snapshot instan.

Biaya

Dalam dokumen ini, Anda akan menggunakan komponen Google Cloudyang dapat ditagih berikut:

Untuk membuat perkiraan biaya berdasarkan proyeksi penggunaan Anda, gunakan kalkulator harga.

Pengguna Google Cloud baru mungkin memenuhi syarat untuk mendapatkan uji coba gratis.

Setelah menyelesaikan tugas yang dijelaskan dalam dokumen ini, Anda dapat menghindari penagihan berkelanjutan dengan menghapus resource yang Anda buat. Untuk mengetahui informasi selengkapnya, baca bagian Pembersihan.

Sebelum memulai

1. Untuk tutorial ini, Anda memerlukan project Google Cloud . Anda dapat membuat project baru atau memilih project yang ada:

   1. Di konsol Google Cloud , pada halaman pemilih project, pilih atau buat project Google Cloud .

      Peran yang diperlukan untuk memilih atau membuat project

      • Pilih project: Memilih project tidak memerlukan peran IAM tertentu—Anda dapat memilih project mana pun yang telah diberi peran.
      • Membuat project: Untuk membuat project, Anda memerlukan peran Pembuat Project (roles/resourcemanager.projectCreator), yang berisi izin resourcemanager.projects.create. Pelajari cara memberikan peran.
      Catatan: Jika tidak berencana untuk menyimpan resource yang Anda buat dalam prosedur ini, buatlah project, bukan memilih project yang ada. Setelah menyelesaikan langkah ini, Anda dapat menghapus project, yang menghapus semua resource yang terkait dengan project ini.

      Buka pemilih project

   2. Verifikasi bahwa penagihan diaktifkan untuk project Google Cloud Anda.

   3. Di konsol Google Cloud , aktifkan Cloud Shell.

      Aktifkan Cloud Shell

2. Pastikan Microsoft SQL Server 2022 atau yang lebih baru telah diinstal dan berjalan.

Izin yang diperlukan

Selain akses baca standar, pastikan administrator SQL Server memberi Anda izin ALTER DATABASE untuk database target.

Untuk mendapatkan izin yang Anda perlukan untuk membuat instance dan membuat snapshot, minta administrator untuk memberi Anda peran IAM berikut pada project:

• Mengelola instance: compute.instanceAdmin.v1
• Membuat snapshot: compute.storageAdmin

Untuk mengetahui informasi selengkapnya tentang pemberian peran, lihat Mengelola akses ke project, folder, dan organisasi.

Anda mungkin juga bisa mendapatkan izin yang diperlukan melalui peran khusus atau peran bawaan lainnya.

Membuat instance SQL Compute Engine

Buat instance SQL Server.

1. Di konsol Google Cloud , buka Cloud Shell dengan mengklik tombol Aktifkan Cloud Shell .

   Buka Google Cloud konsol

2. Buat instance SQL Server. Tempelkan perintah berikut:

  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

Ganti kode berikut:

• Region: Region tempat instance baru Anda akan di-deploy.
• VM_NAME: Nama instance SQL Server baru Anda.

Catatan: Variabel hanya dipertahankan untuk sesi terminal saat ini dan perlu direset saat sesi terputus.

Membuat grup konsistensi disk

1. Buat grup konsistensi.

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

2. Tambahkan disk VM ke grup konsistensi.

   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
   

Membuat database baru

1. Buat nama pengguna dan sandi untuk instance VM.
2. Hubungkan ke VM menggunakan Desktop Jarak Jauh dan login menggunakan nama pengguna dan sandi yang dibuat di langkah sebelumnya.
3. Klik kanan tombol Start (atau tekan Win+X), lalu klik Terminal (Admin).
4. Konfirmasi prompt elevasi dengan mengklik Yes.

5. Jalankan skrip PowerShell berikut di jendela Terminal yang terbuka. Skrip ini menginisialisasi disk data, memformat disk dengan ukuran blok 64 KB, dan menetapkan huruf drive.

   $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. Buka SQL Server Management Studio (SSMS). Jalankan sebagai Administrator.

7. Pada dialog Connect to server, pastikan nama server disetel ke localhost, lalu pilih Connect.

8. Di menu file, pilih File > New > Query dengan koneksi saat ini.

9. Salin kode berikut ke jendela kueri yang baru dibuka.

   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
   );
   

Men-deploy skrip snapshot

Skrip ini mengotomatiskan seluruh proses yang diperlukan untuk mengambil snapshot yang konsisten dengan aplikasi dari disk yang terpasang ke VM yang menjalankan SQL Server.

1. Dengan menggunakan sesi Desktop Jarak Jauh yang sama, buka Notepad.
2. Salin konten skrip berikut dan tempel ke jendela Notepad yang terbuka.

3. Simpan file sebagai take-snapshot.ps1 di 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. Klik kanan tombol Start (atau tekan Win+X), lalu klik Terminal (Admin).

5. Konfirmasi prompt elevasi dengan mengklik Yes.

6. Jalankan skrip dengan menjalankan perintah berikut.

       C:\scripts\take-snapshot.ps1
   

Memverifikasi bahwa snapshot instan telah dibuat

Jalankan perintah berikut di Cloud Shell

1. Pastikan skrip membuat snapshot.

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

2. Mencantumkan grup snapshot instan.

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

3. Melihat detail grup snapshot instan.

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

4. Salin nilai selfLink untuk digunakan di bagian berikutnya.

Memulihkan snapshot instan ke disk baru

Jalankan perintah berikut di jendela konsol Google Cloud .

1. Buat disk dari grup konsistensi snapshot instan.

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

2. Konfirmasi bahwa snapshot Anda telah dipulihkan dengan mencantumkan disk di zona.

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

3. Buat VM baru dari disk yang baru dibuat.

   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
   

Ganti kode berikut:

• Region: Region tempat instance baru Anda akan di-deploy.
• VM_NAME: Nama instance SQL baru Anda.
• BOOT_DISK_NAME: Nama boot disk yang dibuat pada langkah sebelumnya.
• DATA_DISK_1_NAME: Nama disk data pertama yang dibuat pada langkah sebelumnya.
• DATA_DISK_2_NAME: Nama disk data kedua yang dibuat pada langkah sebelumnya.

Sekarang Anda dapat melampirkan disk ke VM baru atau yang sudah ada.

Pembersihan

Agar akun Google Cloud Anda tidak dikenai biaya untuk resource yang digunakan dalam tutorial ini, hapus project.

Menghapus project

Perhatian: Penghapusan project memiliki efek berikut:
• Semua hal dalam project akan dihapus. Jika Anda menggunakan project yang ada untuk mengerjakan tugas di dokumen ini, saat Anda menghapusnya, pekerjaan lain dalam project tersebut juga akan dihapus.
• Project ID kustom akan hilang. Saat membuat project ini, Anda mungkin juga membuat project ID kustom yang masih ingin digunakan pada masa mendatang. Agar tidak kehilangan URL yang menggunakan project ID tersebut, seperti URL appspot.com, hapus resource yang dipilih di dalam project, bukan menghapus seluruh project.
1. Di Konsol Google Cloud , buka halaman Manage resources.

   Buka Kelola resource

2. Pada daftar project, pilih project yang ingin Anda hapus, lalu klik Delete.
3. Pada dialog, ketik project ID, lalu klik Shut down untuk menghapus project.

Langkah berikutnya

• Pelajari arsitektur referensi, diagram, dan praktik terbaik tentang Google Cloud. Lihat Cloud Architecture Center kami.