Automatisering av gjenoppretting av SQL-databaser fra Azure Recovery Services til SQL Server i en VM

I en tidligere rolle deltok jeg i en storstilt migrering fra on-premises til Azure. Å migrere serverne var den enkle delen (takk, Azure Migrate); det som er mer krevende, er å gjøre legacy-teknologier og -prosesser skyvennlige. Ett eksempel er en SQL Server der hovedformålet er å fungere som testmiljø for endringer i produksjon før de settes i produksjon.

Den opprinnelige prosessen

• Full backups tas på et fast tidspunkt hver dag på produksjonsserverne, lagres lokalt på backupdisken og gjøres tilgjengelige via SMB share
• Testmiljøet følger tre ulike tidsplaner, alle styrt av SQL Server Agent
  • Daily
  • Weekly
  • Monthly
• Hver tidsplan har bestemte databaser som skal kopieres eller gjenopprettes
• Produksjonsserverne og testserveren er i to ulike geografiske datasentre og er avhengige av at MPLS-forbindelsen er stabil
• Den daglige prosessen kan for eksempel ta opptil åtte timer fra start til slutt, på grunn av ventetid og fordi all koden er single-threaded

Ny prosess

Selv om denne prosessen kunne vært justert noe for å tilpasses migreringen, benyttet jeg anledningen til å fornye den. I prosjektet ble Azure Recovery Services brukt til å sikkerhetskopiere databasene, og det ga meg en idé: Hvorfor bruke tid på å kopiere BAK-filer mellom servere med single-threaded T-SQL når vi kan bruke Azure Recovery Services og multi-threaded PowerShell til å oppnå det samme på en mer effektiv og sikker måte, med Managed Identities i stedet for brukernavn/passord-autentisering?

I dette innlegget fokuserer jeg primært på PowerShell-skriptingen. For automatisering bør du bruke noe som SQL Server Agent Proxy Accounts for PowerShell.

Krav

• Du trenger et aktivt Azure Subscription
• (Valgfritt – Hvis du bruker private endpoints for Recovery Services Vault, må du implementere en sentralisert DNS-løsning, som Azure DNS Private Resolver, og sikre at alle involverte virtual networks kan løse DNS korrekt for disse endpointene.)
• Virtual Network(s), helst flere for hvert miljø, som skal hoste de nødvendige ressursene
  • Som beste praksis bør disse deles opp i dedikerte subnets, enten du bruker ett eller flere VNets.
  • Hvert VNet bør bruke Azure DNS Private Resolver til DNS-oppslag for å sikre riktig oppløsning.
  • Trafikk mellom VNet(s) bør tillates gjennom riktig bruk av peerings, virtual network appliances eller route tables.
  • Kilde-MSSQL-instanser (MSSQL Environment A)
  • (Valgfritt) Azure Recovery Services Vault Private Endpoints
    • Bør helst deles i dedikerte subnets for AzureBackup- og AzureSiteRecovery-endepunkter.
  • Mål-MSSQL-instans (MSSQL Test Server)
• Azure Recovery Services Vault
  • Backuper fra MSSQL Environment A må være konfigurert og fungere.
• MSSQL Test Server
  • DBATools PowerShell må være installert.
  • SQL Authentication-bruker med tillatelse til å gjenopprette databaser og kjøre SQL Agent Jobs
  • Windows-bruker som skal kjøre gjenopprettingsprosessen
    • Må kjøre setup.ps1 som denne brukeren for å opprette PSCredentialObject, slik at bare denne brukeren kan kjøre skriptet.
    • SQL Credential lagret i Database Engine for denne brukeren (Windows Auth Credentials)
    • Valgfritt: SQL Server Agent Proxy Account for PowerShell som bruker denne credentialen
  • Valgfritt: SQL Server Agent jobs som kjører trigger.ps1, som vist i eksempelet på GitHub, med nødvendige input parameters
• User Managed Identity
  • Backup Contributor-tillatelser på Recovery Services Vault
  • VM Contributor-tillatelser på MSSQL Test Server
  • Denne må være tilknyttet MSSQL Test Server som en identity.

Skripting av den nye prosessen

1. Installere EngineManagementSystem-databasen

Som med en bil er det viktig å kunne diagnostisere problemer med prosesser som feiler i SQL Server. Noen ganger er ikke de innebygde verktøyene tilstrekkelige når du utvikler tilpassede løsninger som denne. For å støtte dette har jeg laget databaseskjemaet EngineManagementSystem. Den første versjonen lagrer bare logger fra PowerShell-prosessekjøringene nedenfor og ivaretar dataryddighet ved å fjerne gamle poster fra OperationLog-tabellen. SQL Server Database Project bruker som standard kompatibilitetsnivå 160/SQL Server 2022, men du kan endre dette ved behov. Du finner det på GitHub.

2. Opprette oppsettskriptet

Før vi begynner å skrive kode, bør vi planlegge hvordan prosessen skal fungere. Koden kan gjøres så dynamisk som vi ønsker. For å lagre felles variabler bruker eksempelkoden min et PowerShell-oppsettskript som lagrer dem i et JSON-dokument. Det betyr at vi ikke trenger å endre selve koden dersom innstillinger endres senere. Her er hva vi kan ønske å lagre i JSON-dokumentet:

• Navnet på Recovery Services Vault
• Resource group der Recovery Services Vault ligger
• Azure Subscription ID
  • I eksemplet mitt ligger kilde-SQL-VM-en, MSSQL Test Server og Recovery Services Vault i samme subscription.
• Entra ID Client ID for User Managed Identity
• Katalogen på Target MSSQL Test Server der backupfilene skal lastes ned til og gjenopprettes fra
• Navnet på EngineManagementSystem-databasen
• FQDN for kilde-SQL-VM-en
• Datamaskinnavnet på kilde-SQL-VM-en
• Datamaskinnavnet på mål-MSSQL Test Server
• SQL-instansnavnet på mål-MSSQL Test Server

Du kan opprette dette JSON-konfigurasjonsdokumentet ved å kjøre Setup.ps1 på MSSQL Test Server. Du finner det på GitHub. I tillegg inneholder skriptet kommandoer som oppretter et PSCredential-objekt, som lagres lokalt på MSSQL Test Server for autentisering mot SQL-instansen på Target MSSQL Test Server. Det er avgjørende at du kjører oppsettskriptet innlogget som brukeren som skal kjøre databasegjenopprettingsskriptet, fordi bare brukeren som oppretter og lagrer PSCredential-objekter, kan lese dem. Se Krav under MSSQL Test Server.

Eksempel på output:

{
    "azureRecoveryServicesVaultName": "myRecoveryServicesVault",
    "azureRecoveryServicesVaultResourceGroupName": "myResourceGroup",
    "azureSubscriptionId": "00000000-0000-0000-0000-000000000000",
    "azureUserManagedIdentityId": "00000000-0000-0000-0000-000000000000",
    "databaseRestoreDirectory": "'F:\\MSSQL16.MSSQLSERVER\\MSSQL\\Restore",
    "dbmsManagementDatabase": "EngineManagementSystem",
    "sourceInstanceName": "MSSQLDB51.myactivedirectorydomain.com",
    "sourceInstanceCode": "MSSQLDB51",
    "targetInstanceName": "TESTMSSQLDB01",
    "targetSQLInstanceNameLocalIdentity": "TESTMSSQLDB01\MSSQLSERVER"
}

3. Opprette gjenopprettingsskriptet

Her kan vi gjøre skriptet dynamisk nok til å støtte flere scenarioer. Du kan for eksempel ønske å støtte et array med forskjellige databaser i ett scenario, men gjenopprette en enkelt database ad hoc i et annet.

Skriptet får følgende form. Du finner hele koden på GitHub, eller beskrevet nedenfor.

• Input parameters
  • $configFilePath – String – Den fullstendige banen til filen vi opprettet i steg 2
  • $databaseScope – Array – Et array med databasene vi ønsker å gjenopprette. Det kan inneholde flere elementer eller én database ved behov. Det er viktig å bruke et array her, slik at vi kan bruke foreach-løkker til å iterere gjennom gjenbrukbar kode. En verdi kan for eksempel sendes inn via trigger.ps1.
  • $logHistoryToKeepInDays – Int – Antall dager med logger som skal beholdes i EngineManagementSystem-databasen. I eksempelkoden min brukes 45 dager (-45). En verdi kan for eksempel sendes inn via trigger.ps1.
  • $sqlServiceCredential – PSCredential object – Et credential-objekt med brukernavn/passord for SQLAuthentication-brukeren som brukes som grensesnitt mellom skriptet og SQL Server database engine.
  • $triggerType – String – En enkel strengverdi som forbedrer skriptloggingen og hjelper med å identifisere bestemte kjøringer, for eksempel ad hoc, daglig, ukentlig eller månedlig
• Pakke ut konfigurasjons-JSON-filen til $configurationSettings. Dette parser innholdet i konfigurasjonsfilen angitt ved $configFilePath.
• Angi globale variabler som brukes flere ganger i skriptet
  • Pakk ut alle verdiene fra $configurationSettings til egne variabler.
  • $jobId, en New-Guid som hjelper oss å identifisere hver kjøring av skriptet
  • $jobType, hardkodet som PowerShell Script, i tilfelle logger fra andre prosesser skrives til OperationLog-tabellen
  • $operationType, hardkodet som Database Restore, i tilfelle logger fra andre prosesser skrives til OperationLog-tabellen
• Opprett en funksjon (AddOperationLog) slik at hver database som behandles, kan bruke denne gjenbrukbare koden.
  • Den kalles i foreach-løkken nedenfor for å logge både vellykkede og mislykkede resultater med try/catch.
  • Kjører meldingen AddOperationLog i EngineManagementSystem-databasen for å legge til loggmeldingen.
• Opprett en foreach-løkke for hvert element angitt i array-parameteren $databaseScope.
  • Opprett en script block som starter en PowerShell-jobb i bakgrunnen for hver database i $databaseScope-arrayet (multi-threading).
  • Angi variabler for script block-en. Script blocks er i praksis isolerte kodeblokker, derav behovet for $using: hvis du må bruke globale variabler som allerede er definert.
    • Vi gjenbruker følgende forhåndsdefinerte variabler:
      • $azureUserManagedIdentityId
      • $azureSubscriptionId
      • $azureRecoveryServicesVaultName
      • $azureRecoveryServicesVaultResourceGroupName
      • $database (references the current item in the foreach loop)
      • $databaseRestoreDirectory
      • $sourceInstance
      • $sourceInstanceCode
      • $sqlServiceCredential
      • $targetInstance
      • $targetSQLInstanceNameLocalIdentity
    • $azureContext = (Connect-AzAccount -Identity -AccountId $azureUserManagedIdentityId).context
    • $azureContext = Set-AzContext -Subscription $azureSubscriptionId -DefaultProfile $azureContext
    • $azureRecoveryServicesVault = (Get-AzRecoveryServicesVault -ResourceGroupName $azureRecoveryServicesVaultResourceGroupName -Name $azureRecoveryServicesVaultName).ID
    • $correlationId = New-Guid, som identifiserer hver bakgrunnsjobb som kjører under hver $jobId
  • Hver kodeblokk plasseres i en try-catch-blokk for å hjelpe med feilsøking.
  • Kall AddOperationLog-funksjonen for å starte sporingen.
  • Importer DBATools PowerShell-modulen.
  • Angi Az Recovery Services Restore Target Backup Container.
  • Hent backup item fra Azure Recovery Services Target Container.
  • Hent en liste med recovery points for databasen der recovery point type er Full.
  • Filtrer recovery point-historikken for å hente det nyeste recovery point-et.
  • Opprett recovery point-filter.
  • Opprett recovery configuration for databasegjenopprettingsinstansen.
  • Last ned backupfilen fra Azure Recovery Services.
  • Hent navnet på .BAK-filen som skal gjenopprettes.
  • Beregn full filbane for backupfilen.
  • Gjenopprett databasen.
  • Fjern backupfiler.
• Vent til alle PowerShell-bakgrunnsjobber er fullført, og fjern dem fra sesjonen.
• Fjern alle historiske logger eldre enn verdien angitt for $logHistoryToKeepInDays.
• Fullfør sporingsloggen for denne kjøringen av skriptet ($jobId).

Konklusjon

Kort oppsummert har vi nå en dynamisk og gjenbrukbar prosess for å hente SQL-databasebackuper som er lagret i Azure Recovery Services, og deretter gjenopprette dem på en annen SQL-instans for videre behandling. Jeg håper du har hatt nytte av dette, og ser frem til å dele flere tips etter hvert som jeg finner dem.