Automatisering av gjenoppretting av SQL-databasar frå Azure Recovery Services til SQL Server i ein VM

I ei tidlegare rolle deltok eg i ei storstilt migrering frå on-premises til Azure. Å migrere serverane var den enkle delen (takk, Azure Migrate); det som er meir krevjande, er å gjere legacy-teknologiar og -prosessar skyvennlege. Eit døme er ein SQL Server der hovudføremålet er å fungere som testmiljø for endringar i produksjon før dei blir sette i produksjon.

Den opphavlege prosessen

• Full backups blir tekne på eit fast tidspunkt kvar dag på produksjonsserverane, lagra lokalt på backupdisken og gjorde tilgjengelege via SMB share
• Testmiljøet følgjer tre ulike tidsplanar, alle styrte av SQL Server Agent
  • Daily
  • Weekly
  • Monthly
• Kvar tidsplan har bestemte databasar som skal kopierast eller gjenopprettast
• Produksjonsserverane og testserveren er i to ulike geografiske datasenter og er avhengige av at MPLS-sambandet er stabilt
• Den daglege prosessen kan til dømes ta opp til åtte timar frå start til slutt, på grunn av ventetid og fordi all koden er single-threaded

Ny prosess

Sjølv om denne prosessen kunne ha blitt justert litt for å passe til migreringa, nytta eg høvet til å fornye han. I prosjektet blei Azure Recovery Services brukt til å sikkerheitskopiere databasane, og det gav meg ein idé: Kvifor bruke tid på å kopiere BAK-filer mellom serverar med single-threaded T-SQL når vi kan bruke Azure Recovery Services og multi-threaded PowerShell til å oppnå det same på ein meir effektiv og trygg måte, med Managed Identities i staden for brukarnamn/passord-autentisering?

I dette innlegget fokuserer eg først og fremst på PowerShell-skriptinga. For automatisering bør du bruke noko som SQL Server Agent Proxy Accounts for PowerShell.

Krav

• Du treng eit aktivt Azure Subscription
• (Valfritt – Dersom du bruker private endpoints for Recovery Services Vault, må du implementere ei sentralisert DNS-løysing, som Azure DNS Private Resolver, og sikre at alle involverte virtual networks kan løyse DNS korrekt for desse endpointa.)
• Virtual Network(s), helst fleire for kvart miljø, som skal hoste dei nødvendige ressursane
  • Som beste praksis bør desse delast i dedikerte subnets, anten du bruker eitt eller fleire VNets.
  • Kvart VNet bør bruke Azure DNS Private Resolver til DNS-oppslag for å sikre riktig oppløysing.
  • Trafikk mellom VNet(s) bør tillatast med rett bruk av peerings, virtual network appliances eller route tables.
  • Kjelde-MSSQL-instansar (MSSQL Environment A)
  • (Valfritt) Azure Recovery Services Vault Private Endpoints
    • Bør helst delast i dedikerte subnets for AzureBackup- og AzureSiteRecovery-endepunkta.
  • Mål-MSSQL-instans (MSSQL Test Server)
• Azure Recovery Services Vault
  • Backupar frå MSSQL Environment A må vere konfigurerte og fungere.
• MSSQL Test Server
  • DBATools PowerShell må vere installert.
  • SQL Authentication-brukar med løyve til å gjenopprette databasar og køyre SQL Agent Jobs
  • Windows-brukar som skal køyre gjenopprettingsprosessen
    • Må køyre setup.ps1 som denne brukaren for å opprette PSCredentialObject, slik at berre denne brukaren kan køyre skriptet.
    • SQL Credential lagra i Database Engine for denne brukaren (Windows Auth Credentials)
    • Valfritt: SQL Server Agent Proxy Account for PowerShell som bruker denne credentialen
  • Valfritt: SQL Server Agent jobs som køyrer trigger.ps1, som vist i eksempelet på GitHub, med nødvendige input parameters
• User Managed Identity
  • Backup Contributor-løyve på Recovery Services Vault
  • VM Contributor-løyve på MSSQL Test Server
  • Denne må vere knytt til MSSQL Test Server som ein identity.

Skripting av den nye prosessen

1. Installere EngineManagementSystem-databasen

Som med ein bil er det viktig å kunne diagnostisere problem med prosessar som feilar i SQL Server. Nokre gonger er ikkje dei innebygde verktøya tilstrekkelege når du utviklar tilpassa løysingar som denne. For å støtte dette har eg laga databaseskjemaet EngineManagementSystem. Den første versjonen lagrar berre loggar frå PowerShell-prosesseksekveringane nedanfor og tek vare på dataryddigheit ved å fjerne gamle postar frå OperationLog-tabellen. SQL Server Database Project bruker som standard kompatibilitetsnivå 160/SQL Server 2022, men du kan endre dette ved behov. Du finn det på GitHub.

2. Opprette oppsettskriptet

Før vi byrjar å skrive kode, bør vi planleggje korleis prosessen skal fungere. Koden kan gjerast så dynamisk som vi ønskjer. For å lagre felles variablar bruker eksempelkoden min eit PowerShell-oppsettskript som lagrar dei i eit JSON-dokument. Det tyder at vi ikkje treng å endre sjølve koden dersom innstillingar endrar seg seinare. Her er kva vi kan ønskje å lagre i JSON-dokumentet:

• Namnet på Recovery Services Vault
• Resource group der Recovery Services Vault ligg
• Azure Subscription ID
  • I dømet mitt ligg kjelde-SQL-VM-en, MSSQL Test Server og Recovery Services Vault i same subscription.
• Entra ID Client ID for User Managed Identity
• Katalogen på Target MSSQL Test Server der backupfilene skal lastast ned til og gjenopprettast frå
• Namnet på EngineManagementSystem-databasen
• FQDN for kjelde-SQL-VM-en
• Datamaskinnamnet på kjelde-SQL-VM-en
• Datamaskinnamnet på mål-MSSQL Test Server
• SQL-instansnamnet på mål-MSSQL Test Server

Du kan opprette dette JSON-konfigurasjonsdokumentet ved å køyre Setup.ps1 på MSSQL Test Server. Du finn det på GitHub. I tillegg inneheld skriptet kommandoar som opprettar eit PSCredential-objekt, som blir lagra lokalt på MSSQL Test Server for autentisering mot SQL-instansen på Target MSSQL Test Server. Det er avgjerande at du køyrer oppsettskriptet innlogga som brukaren som skal køyre databasegjenopprettingsskriptet, fordi berre brukaren som opprettar og lagrar PSCredential-objekt, kan lese dei. Sjå 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 gjere skriptet dynamisk nok til å støtte fleire scenario. Du kan til dømes ønskje å støtte eit array med ulike databasar i eitt scenario, men gjenopprette ein enkelt database ad hoc i eit anna.

Skriptet får denne forma. Du finn heile koden på GitHub, eller skildra nedanfor.

• Input parameters
  • $configFilePath – String – Den fullstendige stien til fila vi oppretta i steg 2
  • $databaseScope – Array – Eit array med databasane vi ønskjer å gjenopprette. Det kan innehalde fleire element eller éin database ved behov. Det er viktig å bruke eit array her, slik at vi kan bruke foreach-løkker til å iterere gjennom attbrukbar kode. Ein verdi kan til dømes sendast inn via trigger.ps1.
  • $logHistoryToKeepInDays – Int – Talet på dagar med loggar som skal takast vare på i EngineManagementSystem-databasen. I eksempelkoden min bruker eg 45 dagar (-45). Ein verdi kan til dømes sendast inn via trigger.ps1.
  • $sqlServiceCredential – PSCredential object – Eit credential-objekt med brukarnamn/passord for SQLAuthentication-brukaren som blir brukt som grensesnitt mellom skriptet og SQL Server database engine.
  • $triggerType – String – Ein enkel strengverdi som betrar skriptlogginga og hjelper med å identifisere bestemte køyringar, til dømes ad hoc, dagleg, vekentleg eller månadleg
• Pakk ut konfigurasjons-JSON-fila til $configurationSettings. Dette parsar innhaldet i konfigurasjonsfila som er angitt ved $configFilePath.
• Set globale variablar som blir brukte fleire gonger i skriptet
  • Pakk ut alle verdiane frå $configurationSettings til eigne variablar.
  • $jobId, ein New-Guid som hjelper oss å identifisere kvar køyring av skriptet
  • $jobType, hardkoda som PowerShell Script, i tilfelle loggar frå andre prosessar blir skrivne til OperationLog-tabellen
  • $operationType, hardkoda som Database Restore, i tilfelle loggar frå andre prosessar blir skrivne til OperationLog-tabellen
• Opprett ein funksjon (AddOperationLog) slik at kvar database som blir behandla, kan bruke denne attbrukbare koden.
  • Han blir kalla i foreach-løkka nedanfor for å logge både vellukka og mislukka resultat med try/catch.
  • Køyrer meldinga AddOperationLog i EngineManagementSystem-databasen for å leggje til loggmeldinga.
• Opprett ei foreach-løkke for kvart element som er angitt i array-parameteren $databaseScope.
  • Opprett ein script block som startar ein PowerShell-jobb i bakgrunnen for kvar database i $databaseScope-arrayet (multi-threading).
  • Set variablar for script block-en. Script blocks er i praksis isolerte kodeblokker, derav behovet for $using: viss du må bruke globale variablar som allereie er definerte.
    • Vi bruker desse førehandsdefinerte variablane på nytt:
      • $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 kvar bakgrunnsjobb som køyrer under kvar $jobId
  • Kvar kodeblokk blir plassert i ei try-catch-blokk for å hjelpe med feilsøking.
  • Kall AddOperationLog-funksjonen for å starte sporinga.
  • Importer DBATools PowerShell-modulen.
  • Set Az Recovery Services Restore Target Backup Container.
  • Hent backup item frå Azure Recovery Services Target Container.
  • Hent ei liste med recovery points for databasen der recovery point type er Full.
  • Filtrer recovery point-historia for å hente det nyaste recovery point-et.
  • Opprett recovery point-filter.
  • Opprett recovery configuration for databasegjenopprettingsinstansen.
  • Last ned backupfila frå Azure Recovery Services.
  • Hent namnet på .BAK-fila som skal gjenopprettast.
  • Rekn ut full filsti for backupfila.
  • Gjenopprett databasen.
  • Fjern backupfiler.
• Vent til alle PowerShell-bakgrunnsjobbar er fullførte, og fjern dei frå sesjonen.
• Fjern alle historiske loggar eldre enn verdien angitt for $logHistoryToKeepInDays.
• Fullfør sporingsloggen for denne køyringa av skriptet ($jobId).

Konklusjon

Kort oppsummert har vi no ein dynamisk og attbrukbar prosess for å hente SQL-databasebackupar som er lagra i Azure Recovery Services, og deretter gjenopprette dei på ein annan SQL-instans for vidare behandling. Eg håpar du har hatt nytte av dette, og ser fram til å dele fleire tips etter kvart som eg finn dei.