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.
| Part | Scripts | Runs on | Post |
|---|---|---|---|
| A. Prepare Azure | 01 (and 00 to take it down again) | portal, Cloud Shell | 1 (this one) |
| B. Check what is there | 10 inventory, 11 DiskSpd, 12 readiness gates | laptop, then VM | 2 |
| C. Export what the target needs | 20 TDE certificate, 21 logins | laptop | 3 |
| D. Back up | 30 backups, 31 cutover snapshot | laptop | 4 |
| E. Upload | 32 | laptop | 4 |
| F. Restore | 40 restore, 41 logins | VM | 5 |
| G. Check post-migration | 50, and 99 to reset the lab | VM | 6 |
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 laptop | Target: the Azure VM | |
|---|---|---|
| Name | PRECISION7760 | AW2025Migration (VM name and Windows name) |
| SQL Server | SQL 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 |
| Collation | SQL_Latin1_General_CP1_CI_AS | the same (the image’s default) |
| CPU / memory | record yours | 2 vCPUs, 16 GB (Standard E2ads v7) |
| Drives | C: Windows; D: data; E: log and tempdb | C: Windows; D: temporary disk, tempdb; F: data; G: log |
| Time zone | GTB Standard Time | UTC from the image, set to the laptop’s in A2 |
| Region | East US (see the region box in A2) |
0.2 Azure
| Piece | What it is for | Name in my lab |
|---|---|---|
| Subscription | a new free account, used for this and nothing else | |
| Resource group | holds everything below, so one delete removes it all | AW2025Migration |
| VM | the target SQL Server | AW2025Migration |
Storage account, private container backups | the meeting point: the laptop uploads, the VM downloads | aw2025mig12345 |
| Key Vault (RBAC permissions) | the passwords and the exported logins | aw2025-kv-1234 |
| The VM’s managed identity | lets the VM reach storage and Key Vault with no password or key on it | system-assigned, switched on by 01 |
What goes in the container, and who uses it:
| Blob path | What | Written by | Read by |
|---|---|---|---|
inventory/<laptop>/ | the inventory of the laptop | 10 | 12 |
keys/<laptop>/ | the TDE certificate and its private key (.cer, .pvk) | 32 | 40 |
<laptop>/AdventureWorks2025/FULL/ and /LOG/ | the backups | 32 | 40 |
facts/<laptop>/AdventureWorks2025/ | the cutover snapshot | 32 | 50 |
Who may do what (01 grants it):
| Who | Role | On | So it can |
|---|---|---|---|
| the VM | Storage Blob Data Contributor | the storage account | read and write the blobs |
| the VM | Key Vault Secrets User | the vault | read secrets, never write them |
| you | Key Vault Secrets Officer | the vault | create 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)
| Secret | Holds | Created by | Read by |
|---|---|---|---|
aw2025-source-dmk-password | the password of the database master key in master on the laptop | 20 | nobody else: it stays with the laptop |
aw2025-pvk-password | the password of the TDE private key file (.pvk) | 20 | 40, on the VM |
aw2025-logins | the SQL logins: names, SIDs, password hashes | 21 | 41 |
aw2025-login-<name>-password | optional: a login’s password, for the sign-in test in 41 | you, by hand | 41 |
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
| Where | What |
|---|---|
| GitHub, release v1.0 | all the scripts, with the run order in the README; check them first (see the end of this post) |
| Cloud Shell, your home folder | 00 and 01 (Manage files > Upload) |
laptop: C:\AW2025Migration\scripts | 10, 11, 20, 21, 30, 31, 32, 99 |
VM: C:\AW2025Migration\scripts | 11, 12, 40, 41, 50, 99 |
laptop: D:\Backups\<laptop>\ and D:\TDE-Keys | what 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.
-WhatIffirst, 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.
- Create the Outlook.com address, then the Azure free account with it.
- 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:
- 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. - 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.
- 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:
[VM]Connect with Remote Desktop (Connect > RDP in the portal).[VM, SSMS]Check the edition:SELECT SERVERPROPERTY('Edition');[VM, PowerShell]Time zone, then restart SQL Server and start Agent (Manual on the image):
Set-TimeZone -Id 'GTB Standard Time'Restart-Service MSSQLSERVER -ForceStart-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.
- Open Cloud Shell from the portal, in PowerShell mode (not Bash).
- Upload
01_New-AzureLabBase.ps1and00_Remove-AzureLabBase.ps1(Manage files > Upload) and check them against the table at the end of this post:Get-FileHash ./01_New-AzureLabBase.ps1. - Run
01with-WhatIffirst, 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.
| File | SHA-256 |
|---|---|
aw2025-azure-migration-v1.0.zip | 0db411127783f4f2c604211d8293b2854a7776b0289d54b57344890e73c45d4f |
01_New-AzureLabBase.ps1 | 3074b20bca2eea4612755a031b98c88230bc3ff48253f2b17e002cde881209cd |
00_Remove-AzureLabBase.ps1 | 61fe8c80e1836b8f5a43ff1af6e71c9a891e79e73881bf0fa0427a1aa914e644 |