Publishing a PowerShell Azure SQL Server VM Deployment Toolkit

It used to take me almost an hour to deploy a SQL Server VM on Azure. Click through the portal, configure networking, set up Bastion, create a Key Vault, mount a file share, install tools, restore databases. Every single time.

In 2021, I wrote a PowerShell script that automated most of these steps, but not all of them. The deployment still took close to an hour, and the script was rigid. Changing the VM size or the Azure region meant editing the script itself.

Today, I am open sourcing a completely rewritten version. It deploys a fully configured SQL Server 2022 VM in about 30 minutes. Azure Bastion, Key Vault, Azure Files, development tools, everything. And the entire deployment is driven by a single YAML configuration file.

There is only one manual step left: logging into the VM and running a dbatools script to restore your databases. That is not for lack of trying. `Invoke-AzVMRunCommand` runs as the SYSTEM user, which is not what you want as the owner of your restored databases.

Why YAML?

A YAML file is easy to read, easy to edit, and familiar to anyone who has worked with infrastructure tools like Ansible or Terraform. If a DBA hands a YAML config file to an infrastructure team, they will know exactly what they are looking at.

For you as a DBA or SQL developer, it means you configure once and re-run the same script for every deployment. VM size, region, SQL Server version: change one line, no script modifications needed. Most of your settings will stay the same between deployments anyway, so you only touch what actually changes.

The Solution: Two Files

The solution is split into two files:

- config.yaml: a declarative configuration file that defines *what* to deploy

- vm_creation_with_bastion.ps1: the deployment script that reads the config and makes it happen

The Configuration File

Everything that might change between deployments lives in config.yaml:

resourceGroup:
  name: “YourResourceGroupName”
  location: “Switzerland North”
  tags:
    Purpose: “Demo”

vm:
  size: “Standard_DS13_V2”
  image:
    publisherName: “MicrosoftSQLServer”
    offer: “sql2022-ws2022”
    skus: “sqldev-gen2”
    version: “latest”

storage:
  resourceGroup: “yourstorageRG”
  accountName: “yourstorageaccount”
  fileShareName: “yoursqlbackupsharename”
  driveLetter: “Z”

credentials:
  username: “youradminuser”

keyVault:
  name: “yourkeyvault-kv”

softwareInstalls:
  installScript: |
    choco install vscode git powershell-core tabular-editor -y
    Install-Module -Name dbatools -Force

  logonScript: |
    code --install-extension ms-mssql.mssql

Want a different VM size? Change one line. Different region? One line. Different SQL Server version? One line. You can even define which software gets installed on the VM, all from the YAML file.

Architecture Overview

The deployment creates resources across two resource groups. This is intentional.

Azure Subscription

|
+-- Resource Group: YourResourceGroupName (disposable)
|   +-- Azure Bastion
|   +-- SQL Server VM (Managed Identity)
|
+-- Resource Group: yourstorageRG (persistent)
    +-- Key Vault (vm-admin-password, storage-account-key)
    +-- Storage Account
        +-- File Share (mounted as Z: on VM)
            +-- restore-databases.ps1 (uploaded by script)

Why two resource groups? The VM resource group is disposable. You can delete it entirely and rebuild from scratch without losing your Key Vault, storage account, or database backups. The persistent resource group survives VM rebuilds. This matters a lot when you frequently spin up and destroy dev environments.

Azure Bastion

The script deploys Azure Bastion so you can connect to the VM without exposing a public IP address. Bastion provides browser-based RDP access through the Azure portal.

Azure Bastion comes in different SKU tiers (Developer, Basic, Standard, Premium), each with different capabilities and price points. For a dev/test SQL VM, the Developer SKU is usually sufficient. Check the [Azure Bastion SKU comparison](https://learn.microsoft.com/en-us/azure/bastion/bastion-sku-comparison) for a full breakdown of what each tier offers.

Key Design Decisions

Idempotent Resource Creation

Every resource is checked before creation. If a resource already exists, the script skips it and moves on. Run the script twice and it will not fail or create duplicates. This is important for development environments where you iterate on the config and re-run the script multiple times.

Key Vault Soft-Delete Handling

Azure Key Vault has a soft-delete feature that retains deleted vaults for 90 days. If you delete a Key Vault and try to create a new one with the same name, Azure will return an error: *”The vault name is already in use.”*

The script handles this automatically. It checks whether the name is blocked by a soft-deleted vault and, if so, increments the name (e.g. `myvault` becomes `myvault1`). At the end of the deployment, the script tells you if the name changed so you can update your config for future runs.

Cryptographically Secure Password Storage

The script does not use `Get-Random` for password generation. `Get-Random` uses a pseudorandom number generator that is not suitable for security-sensitive use cases. Instead, the script uses `RNGCryptoServiceProvider` and guarantees that the generated password meets Azure complexity requirements (uppercase, lowercase, digits, special characters).

The password is stored in Key Vault immediately and never written to disk.

Managed Identity for Secrets Access

The VM uses a system-assigned managed identity to retrieve secrets from Key Vault at runtime. No credentials are hardcoded or stored on the VM. The file share mount script, which runs inside the VM, fetches the storage account key from Key Vault every time it needs it.

Even if someone gains access to the VM, they will not find long-lived credentials to extract. The managed identity token is short-lived and scoped to the specific Key Vault.

Persistent File Share Mount

The Azure Files share is mounted as a drive letter (default: Z:) using `New-SmbGlobalMapping`. This makes the share available to all users and services on the VM, not just the current session. A scheduled task re-mounts the share on every reboot, so the drive letter is always available.

This is where you put your database backups. The file share persists independently of the VM, so your backups are safe even if you delete and recreate the VM.

Automated Software Installation

The script installs development tools on the VM using `Invoke-AzVMRunCommand`. Which tools get installed is defined entirely in the YAML config:

softwareInstalls:
  installScript: 
    choco install vscode git powershell-core tabular-editor -y
    Install-Module -Name dbatools -Force

  logonScript: 
    code --install-extension ms-mssql.mssql

Some tools need a user session to install properly. VS Code extensions are a good example: they need to run in the context of a logged-in user. The script handles this by registering a one-time logon task that runs at first login and then removes itself.

Database Restore Script

A `restore-databases.ps1` script is automatically uploaded to the file share during deployment. After logging into the VM, restoring all `.bak` files from the share is a single command:

Z:\restore-databases.ps1

It uses dbatools to iterate over all backup files in the share and restore them to the local SQL Server instance.

Running the Deployment

Prerequisites:

- PowerShell 7+ with the `Az` module installed

- An active Azure session (Connect-AzAccount)

- The powershell-yaml module (Install-Module powershell-yaml)

Then run:

.\vm_creation_with_bastion.ps1

Or point it to a different config file:

.\vm_creation_with_bastion.ps1 -ConfigFile "my-environment.yaml"

At the end, the script prints the VM credentials and total deployment time:

Deployment completed in 00:18:42.

VM Login Credentials:
Username: youradminusername
Password: 

What is Next

I am working on a YAML writing helper for SQL VMs. It will let you pick a SQL Server image interactively and generate the config file for you, so you do not have to look up publisher names, offers, and SKU strings manually. Stay tuned for that.

The full source code is available on https://github.com/kaysauter/azure-sqlvm-toolkit.