AdventureWorks2025 to an Azure SQL VM, part 1: the plan and the lab

Part 1 of 6. The series: 1. The plan and the lab (this post) · 2. Check what is there · 3. Export what the target needs · 4. Back up and upload · 5. Restore · 6. Check post-migration, and clean up.

A week ago I wrote about site-to-site migration and rollback: the method, in one long T-SQL script (apologies for that). This series does the whole thing end to end, using AdventureWorks2025, which goes from my laptop (SQL Server 2025 Enterprise Developer) to SQL Server on an Azure VM, with TDE on and a SQL login that has to keep working on the other side.

Everything is scripted in PowerShell, every script is on GitHub (link in each post), and the gotchas, caveats, notes worth knowing about are in the posts.

The method comes from a migration toolkit I put together for a SQL Server move to cloud (again, provider not important here). Azure has other names for the same pieces: a managed identity instead of an instance role, Key Vault instead of Secrets Manager, Blob storage instead of S3 – see? I gave away the other cloud provider name. The order of the work does not change.

The plan

The whole migration is one runbook in seven parts, A to G, spread over six posts. The numbers are the scripts; the first digit tells you the part: 0 is A, 1 is B, and so on.
One note of caution here – full disclosure and transparency, the real migration I did have a ton of other checks and scripts. What you will see here has been generated and adapted using Claude, who happens to be far more adept at this than I will ever be.
Having said that, make sure you run these in your own test environment (more on that later). You can freely adapt and use any of them in your work but if something goes wrong, don’t come knocking on my door because I did not do your due diligence.

PartScriptsRuns onPost
A. Prepare Azure01 (and 00 to take it down again)portal, Cloud Shell1 (this one)
B. Check what is there10 inventory, 11 DiskSpd, 12 readiness gateslaptop, then VM2
C. Export what the target needs20 TDE certificate, 21 loginslaptop3
D. Back up30 backups, 31 cutover snapshotlaptop4
E. Upload32laptop4
F. Restore40 restore, 41 loginsVM5
G. Check post-migration50, and 99 to reset the labVM6

Part 0. The facts

Before anything runs, write down what you are moving and where it goes. The tables show my lab; fill in your own values as you go. Storage account and vault names must be unique across Azure, so yours will differ from the examples.

0.1 The two servers

Source: the laptopTarget: the Azure VM
NamePRECISION7760AW2025Migration (VM name and Windows name)
SQL ServerSQL Server 2025 Enterprise Developer, RTM (17.0.1000.7)SQL Server 2025 Enterprise Developer from the Azure image, CU10 (17.0.5045.3): same major version, newer CU
CollationSQL_Latin1_General_CP1_CI_ASthe same (the image’s default)
CPU / memoryrecord yours2 vCPUs, 16 GB (Standard E2ads v7)
DrivesC: Windows; D: data; E: log and tempdbC: Windows; D: temporary disk, tempdb; F: data; G: log
Time zoneGTB Standard TimeUTC from the image, set to the laptop’s in A2
RegionEast US (see the region box in A2)

0.2 Azure

PieceWhat it is forName in my lab
Subscriptiona new free account, used for this and nothing else
Resource groupholds everything below, so one delete removes it allAW2025Migration
VMthe target SQL ServerAW2025Migration
Storage account, private container backupsthe meeting point: the laptop uploads, the VM downloadsaw2025mig12345
Key Vault (RBAC permissions)the passwords and the exported loginsaw2025-kv-1234
The VM’s managed identitylets the VM reach storage and Key Vault with no password or key on itsystem-assigned, switched on by 01

What goes in the container, and who uses it:

Blob pathWhatWritten byRead by
inventory/<laptop>/the inventory of the laptop1012
keys/<laptop>/the TDE certificate and its private key (.cer, .pvk)3240
<laptop>/AdventureWorks2025/FULL/ and /LOG/the backups3240
facts/<laptop>/AdventureWorks2025/the cutover snapshot3250

Who may do what (01 grants it):

WhoRoleOnSo it can
the VMStorage Blob Data Contributorthe storage accountread and write the blobs
the VMKey Vault Secrets Userthe vaultread secrets, never write them
youKey Vault Secrets Officerthe vaultcreate and read secrets

The laptop is not in Azure, so it has no identity: the laptop scripts use your own sign-in (Connect-AzAccount).

0.3 Secrets in Key Vault (names only)

SecretHoldsCreated byRead by
aw2025-source-dmk-passwordthe password of the database master key in master on the laptop20nobody else: it stays with the laptop
aw2025-pvk-passwordthe password of the TDE private key file (.pvk)2040, on the VM
aw2025-loginsthe SQL logins: names, SIDs, password hashes2141
aw2025-login-<name>-passwordoptional: a login’s password, for the sign-in test in 41you, by hand41

The TDE certificate is AW2025_TDE. The VM’s own master key password is generated by 40, used once and never stored: the certificate files and their password are all a rebuilt VM needs.

0.4 The database

The source is my laptop: SQL Server 2025 Enterprise Developer, AdventureWorks2025 (328 MB of data, 200 MB of log – most of it empty, but still), and one SQL login, AW2025Migration, mapped to a user in the database (db_ddladmin, db_datareader, db_datawriter). Out of the box the database is in SIMPLE recovery and not encrypted: 20 switches on TDE and FULL recovery, so the migration has a certificate and a log chain to carry.

0.5 Where the scripts are

WhereWhat
GitHub, release v1.0all the scripts, with the run order in the README; check them first (see the end of this post)
Cloud Shell, your home folder00 and 01 (Manage files > Upload)
laptop: C:\AW2025Migration\scripts10, 11, 20, 21, 30, 31, 32, 99
VM: C:\AW2025Migration\scripts11, 12, 40, 41, 50, 99
laptop: D:\Backups\<laptop>\ and D:\TDE-Keyswhat the laptop scripts write: inventory, backups, facts; the key files (locked to Administrators and SYSTEM)

0.6 Rules for the whole job

Every script plays by the same rules, because I did not want to babysit any of them:

  • PowerShell only: Windows PowerShell 5.1 on the laptop and the VM, PowerShell in Cloud Shell. No Bash, no paste-this-block – I tried, was not really smooth sailing for me.
  • Prerequisites first: modules, sign-in, rights, the right machine. It stops before it changes anything.
  • -WhatIf first, always. It shows what it would do. If something goes wrong, this is the first line of defense – it’s not like you want to throw some weird output directly to prod, right? RIGHT???
  • Safe to run again: it looks before it acts and creates only what is missing.
  • It stops at the first error and names the step.
  • It ends with a CHECK table: one line per thing that must be true, OK or MISSING.
  • No password is ever shown or typed. The script generates it, stores it in Key Vault first, then uses it.

0.7 How to read the steps

Each step says where it runs: [browser], [portal], [Cloud Shell], [laptop] or [VM]; and with what: PowerShell (elevated on the laptop and the VM, unless the step says otherwise) or SSMS. Expected says what a correct result looks like. Write your own values down as you go (names, counts, sizes): later steps compare against them.

Part A. Prepare Azure

A1. The account

[browser] The target lives in a brand-new Azure free account, on an Outlook.com address made for this and nothing else. The free account gives $200 of credit for 30 days, so prepare the scripts before you sign up, not after.

  1. Create the Outlook.com address, then the Azure free account with it.
  2. Set up MFA on the new account before anything else (next box).

Important: MFA is not optional any more. The portal and every Azure PowerShell sign-in ask for it. Set up the Authenticator app or a passkey on the new account before anything else. Managed identities, which do most of the work later, are not affected.

Expected: you sign in to the portal with MFA, and the new subscription shows under Subscriptions.

A2. The VM

[portal] Put the VM in a new resource group, AW2025Migration, and give the VM the same name: 01 and the later scripts expect those names unless you pass others.

Note: you might encounter “not accepting new customers”. My first VM went to West Europe, and every resource of the deployment was refused (RequestDisallowedByAzure). New subscriptions are not welcome in every region. I ended up in East US; North Europe or Sweden Central are worth a try first if you are in Europe.

The VM itself, from the portal: a SQL Server 2025 image on Windows Server 2025, two vCPUs and 16 GB (Standard E2ads v7). Three things to get right in the wizard:

  1. The image. You want Free SQL Server License: SQL Server 2025 Enterprise Developer on Windows Server 2025. I got the paid Enterprise one, billed per hour, and found out from SELECT SERVERPROPERTY('Edition'): Enterprise Edition: Core-based Licensing. Run that query first thing on any new VM. The credit covered my lab; it would not cover a mistake on a real subscription.
    For someone who makes a living working with cloud providers, you would expect to know how to choose a VM. Apparently not.
  2. The disks. The SQL image gives you a 1 TB data disk and a 1 TB log disk by default, billed even while the VM is stopped, and a managed disk never shrinks. Set small ones on the SQL Server settings tab.
  3. The time zone. The VM comes up in UTC. My backup file names carry the laptop’s local time and the restore reads them, so I set the VM to the laptop’s zone: Set-TimeZone -Id 'GTB Standard Time', then restart SQL Server. Of course, your time zone might be different, so there is that.

Once it is up:

  1. [VM] Connect with Remote Desktop (Connect > RDP in the portal).
  2. [VM, SSMS] Check the edition: SELECT SERVERPROPERTY('Edition');
  3. [VM, PowerShell] Time zone, then restart SQL Server and start Agent (Manual on the image):
Set-TimeZone -Id 'GTB Standard Time'
Restart-Service MSSQLSERVER -Force
Start-Service SQLSERVERAGENT

Expected: the VM is running in AW2025Migration; the edition is a Developer edition, not Enterprise Edition: Core-based Licensing; the data and log disks have the size you chose; Get-TimeZone shows the laptop’s zone.

A3. The Azure base: script 01

[Cloud Shell, PowerShell] With the VM running, 01_New-AzureLabBase.ps1 builds everything else the migration needs:

  • a storage account (TLS 1.2, no public blob access) with a private container, backups, for the backups and the TDE key files;
  • a Key Vault with RBAC permissions, for the passwords and the logins (you did not really think you get to copy paste passwords, did you?);
  • the VM’s system-assigned managed identity, switched on;
  • who may do what: the VM gets Storage Blob Data Contributor on the storage account and Key Vault Secrets User on the vault; I get Key Vault Secrets Officer on the vault.

Before any of that, it registers the resource providers a new subscription does not have yet (storage, Key Vault and three more): without them, the first Key Vault fails with MissingSubscriptionRegistration. Every step looks first and creates only what is missing.

The managed identity is the part worth understanding. The VM asks Azure’s metadata endpoint for a token and uses it against Blob storage and Key Vault. No key, password or certificate for Azure ever sits on the VM, and the restore scripts in part F need no Az module at all. The laptop is not in Azure, so it uses my sign-in instead.
That reminds me: it is very useful to install the Azure PS module.

  1. Open Cloud Shell from the portal, in PowerShell mode (not Bash).
  2. Upload 01_New-AzureLabBase.ps1 and 00_Remove-AzureLabBase.ps1 (Manage files > Upload) and check them against the table at the end of this post: Get-FileHash ./01_New-AzureLabBase.ps1.
  3. Run 01 with -WhatIf first, then for real, with your own names (both must be unique across Azure):
./01_New-AzureLabBase.ps1 -StorageAccount aw2025mig12345 -KeyVault aw2025-kv-1234 -WhatIf
./01_New-AzureLabBase.ps1 -StorageAccount aw2025mig12345 -KeyVault aw2025-kv-1234

The eagle-eyed among you will notice that the above script is the same except for the -WhatIf part. Ideally, you will run the first script, check what is what, then run it without -WhatIf.

01 assumes the resource group (for me, yours might be different) and the VM are both called AW2025Migration, in East US. If yours are not, add -ResourceGroup, -VmName and -Location (the VM’s region, for example northeurope) to both lines.

Expected: it ends with a CHECK table: resource group, storage account, private container, vault with RBAC, the VM’s identity, and one line per role. All of them should say OK, then:

All OK. Storage account: aw2025mig12345 Key Vault: aw2025-kv-1234 Container: backups

Kind of important: roles take a moment. A role assignment can take a minute or two to apply, so script 01 retries. If a later script says you cannot read the vault’s secrets, wait a few minutes before you look for anything else.

00_Remove-AzureLabBase.ps1 takes all of this down again, and purges the vault so its name can be used again. More on that, and on a purge that is slower than it says, in part G.

Next

Part 2 is part B, and it looks before it touches anything: an inventory of the laptop, DiskSpd on both machines, and ten go / no-go gates on the VM. One of them failed, and I fixed it by hand.

The scripts are on GitHub: calinsqlplay/aw2025-azure-migration (the version these posts use is the v1.0 release). As in my last post, Claude wrote much of the scripts with me, and its comments are more thorough than mine would have been. The runs and the decisions are mine.

Check the scripts before you run them

Download aw2025-azure-migration-v1.0.zip from the v1.0 release. That release is immutable: nobody, me included, can change its files or its tag. Before you unzip it:

Set-Location "$env:USERPROFILE\Downloads"
Get-FileHash .\aw2025-azure-migration-v1.0.zip -Algorithm SHA256

The hash must match the zip’s line below. Each post lists the scripts it uses, and SHA256SUMS.txt on the release has all of them. If one differs, don’t run it.

FileSHA-256
aw2025-azure-migration-v1.0.zip0db411127783f4f2c604211d8293b2854a7776b0289d54b57344890e73c45d4f
01_New-AzureLabBase.ps13074b20bca2eea4612755a031b98c88230bc3ff48253f2b17e002cde881209cd
00_Remove-AzureLabBase.ps161fe8c80e1836b8f5a43ff1af6e71c9a891e79e73881bf0fa0427a1aa914e644

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.