Automation of Restoring SQL Databases from Azure Recovery Services to SQL Server in VM

In a previous role, I participated in  a large-scale migration from on-premisies to Azure. Migrating the servers was the easy part (thanks Azure Migrate), what is more challenging is getting legacy technologies/processes to be cloud friendly. One particular example is a SQL server where its main purpose is to act as testbed for changes in production before committing them into production.

The original process

• Full backups are taken at a prescribed time every day on the production servers and saved locally to the backup drive and made available via SMB share
• There are three different schedules that the testbed works to, all controlled by SQL Server Agent
  • Daily
  • Weekly
  • Monthly
• Each of these schedules has particular databases that should be copied/restored
• The production servers and the testbed server are in two different geographic datacentres and rely on the MPLS link being stable
• The daily process for example, end to end can take up 8 hours because of latency and all the code is single-threaded

New process

So whilst this process could have been modified slightly to accommodate the migration, I took the opportunity to overhaul the process. In this project, Azure Recovery Services was being used to backup the databases, which gave me the idea; why waste time copying BAK files between servers using single-threaded T-SQL when we can make use of Azure Recovery Services and multi-threaded PowerShell to achieve the same task in a more efficient and secure way (using Managed Identites rather than username/password auth).

I will be focussing primarily on the PowerShell scripting in this post, to automate you should use something like SQL Server Agent Proxy Accounts for PowerShell.

Requirements

• You will need an active Azure Subscription
• (Optional – If you’re using private endpoints for your Recovery Services Vault, you will need to implement a centralised DNS solution such as Azure DNS Private Resolver and ensure that all virtual networks involved can resolve DNS to these endpoints correctly)
• Virtual Network(s) ideally multiple for each environment, that will host the required resources
  • As a best practice, these should be split up into dedicated subnets regardless of using a single VNet or multiple
  • Each VNet should use the Azure DNS Private Resolver to query DNS to ensure correct resolution
  • Traffic between VNet(s) should be allowed by appropriate use of peerings or virtual network applicances or route tables
  • Source MSSQL Instances (MSSQL Environment A)
  • (Optional) Azure Recovery Services Vault Private Endpoints
    • Ideally split into AzureBackup and AzureSiteRecovery endpoint dedicated subnets
  • Target MSSQL Instance (MSSQL Test Server)
• Azure Recovery Services Vault
  • Backups from MSSQL Environment A successfully configured and working
• MSSQL Test Server
  • DBATools PowerShell should be installed
  • SQL Authentication User with Permissions to Restore Databases and Execute SQL Agent Jobs
  • Windows User to execute the restore process
    • Will need to run the setup.ps1 script as this user to create the PSCredentialObject so only this user can run the script
    • SQL Credential stored in Database Engine for this user (Windows Auth Credentials)
    • Optional – SQL Server Agent Proxy Account for PowerShell created that uses the above credential
  • Optional – SQL Server Agent jobs to execute trigger.ps1 as shown as an example on my GitHub with required input params
• User Managed Identity
  • Backup Contributor permissions on Recovery Services Vault
  • VM Contributor permissions on MSSQL Test Server
  • This should be attached as an identity to MSSQL Test Server

Scripting the new process

1. Installing the EngineManagementSystem database

Much like a car, it is important that you are able to diagnose issues with processes that are having issues in SQL Server. Sometimes, the built-in tooling may not suffice when you’re developing custom solutions such as this one. To facilitate this, I have created the EngineManagementSystem database schema. The initial iteration of this purely stores logs from the below PowerShell process executions and is able to maintain data hygeine by removing stale records (OperationLog table). The SQL Server Database Project defaults to the 160/SQL Server 2022 compatibility level but you can change this if required. You can get from my GitHub.

2. Creating the setup script

Before we begin with writing the code we should plan how we want this process to work. We can make our code as dynamic as we want it to be. For storing common variables, in my example code I’ve using a PowerShell setup script to these in a JSON document. This means if we change any settings in future, we don’t have to change the code itself. So let’s list out what we might want to store in this JSON document:

• Recovery Services Vault name
• The resource group in which the Recovery Services Vault resides
• The Azure Subscription Id
  • In my example, the source SQL VM, MSSQL Test Server and Recovery Services Vault are in the same subscription
• The Entra ID Client Id of the User Managed Identity
• The directory on the Target MSSQL Test Server where we want our backup files to be downloaded to/restored from
• The name of our EngineManagementSystem database
• The FQDN of the source SQL VM
• The computer name of the source SQL VM
• The computer name of the target MSSQL Test Server
• The SQL instance name on the target MSSQL Test Server

You can produce this JSON configuration document by running ‘Setup.ps1’ on the MSSQL Test Server, you can get that from my GitHub. In addition to the above, this script also includes some commands to produce a PSCredential object that will be stored locally on the MSSQL Test Server to authenticate against the Target MSSQL Test Server SQL Instance. It is imperative that you execute the setup script logged-in as the user who will be executing the database restore script as only the user who creates/stores PSCredential objects can read from them (see Requirements under MSSQL Test Server)

Sample 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. Creating the restore script

We can be clever here and make the script dynamic enough to support multiple scenarios. For instance, you may want to support an array of different databases in one scenario but in another scenario you may want to restore a database individually ad-hoc.

The script will take the following form, the full code can be found on my GitHub or detailed below

• Input Parameters
  • $configFilePath – String – The full path of the file we created in step 2
  • $databaseScope – Array – An array of the databases we want to restore, this can contain multiple items or a singular database if required. It is important to use an array here so we can use foreach loops to iterate through reusable code. A value for this would be passed through the trigger.ps1 script for example
  • $logHistoryToKeepInDays – Int – How many days worth of logs we want to keep in the EngineManagementSystem database, in my example code I’ve used 45 days (-45). A value for this would be passed through the trigger.ps1 script for example
  • $sqlServiceCredential – PSCredential object – We use a credential object that consists of the username/password of the SQLAuthentication user we will be using the interface between the script and the SQL Server database engine
  • $triggerType – String – A simple string value we use to improve logging of the script, to help identify particular executions e.g. ad-hoc, daily, weekly or monthly
• Unpacking of configuration JSON file into $configurationSettings, this will parse the contents of the configuration file set at $configFilePath
• Set global variables that will be used multiple times in the script
  • Unpack all of the values from $configurationSettings into their own respective variable
  • $jobId which is going to be a New-Guid to help us identify each execution of the script
  • $jobType hardcoded as ‘PowerShell Script’ just incase other process logs are being written to the OperationLog table
  • $operationType hardcoded as ‘Database Restore’ just incase other process logs are being written to the OperationLog table
• Create a function (AddOperationLog) so each database that gets called can make use of this reusable code
  • This will be called in the below foreach loop to log data for both successful and failed outcomes via try/catch
  • Executes the [AddOperationLog] message in the EngineManagementSystem database to add the log message
• Create a foreach loop for each item specified in the $databaseScope array parameter
  • Create a script block that will start a background PowerShell job for each database in the $databaseScope array (multi-threading)
  • Set variables for the script block. Script blocks are essentially isolated pieces of code, hence the requirement of $using: if you need to use global variables you’ve already defined
    • We will reuse to following pre-defined variables
      • $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, this will identify each background job that runs underneath each $jobId
  • Each block of code will be placed into a try-catch block to assist with troubleshooting
  • Call the AddOperationLog function to begin the trace
  • Import the DBATools PowerShell Module
  • Set Az Recovery Services Restore Target Backup Container
  • Get backup item from Azure Recovery Services Target Container
  • Get a list of recovery points for the database where the recovery point type is Full
  • Filter database recovery point history to get the most recent recovery point
  • Create recovery point filter
  • Create recovery configuration for the database restore instance
  • Download backup file from Azure Recovery Services
  • Get the name of the .BAK file to restore
  • Compute full file path for the backup file
  • Restore the database
  • Remove backup files
• Wait for all PowerShell background jobs to complete and remove them from the session
• Remove all historic logs older than the value specified for $logHistoryToKeepInDays
• Complete the trace log for the instance of the script execution ($jobId)

Conclusion

To summarise, now we have a dynamic and reusable process for taking SQL database backups stored in Azure Recovery Services; then restoring them onto another SQL instance for further processing. I hope you found this useful and look forward to sharing more tips as and when I find them