AdventureWorks2025 to an Azure SQL VM, part 2: check what is there

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

Part 1 ended with a VM in Azure, a storage account, a Key Vault and the roles that tie them together. Nothing has moved yet, and nothing will in this post either: part B only looks. It records what the laptop has, measures what the drives on both sides can do, and then holds the VM against the laptop in ten go / no-go gates. If a gate fails, you fix it now, while it is cheap, not halfway through a restore.

Important! As with everything in life, use your judgement and know what to compare, what to change and what to keep. Remember, all cloud providers charge an extravagant amount of money (more so these days) for storage whose performance was good a decade ago. Moving on:

The facts, the rules and how to read the steps are in Part 0 of part 1. Nothing in part B changes Windows, SQL Server or Azure: the scripts only write their own result files (and 10 uploads its inventory).

Part B. Check what is there

B1. The scripts on both machines

Every later step runs a script from the same folder, C:\AW2025Migration\scripts, on the laptop and on the VM. Get them there once, from the release, and check them before anything runs.

  1. [laptop] Download aw2025-azure-migration-v1.0.zip from the v1.0 release and check its hash against the table at the end of this post. Then unblock it, unpack it and go to the folder:
Set-Location "$env:USERPROFILE\Downloads"
Get-FileHash .\aw2025-azure-migration-v1.0.zip -Algorithm SHA256
Unblock-File .\aw2025-azure-migration-v1.0.zip
New-Item -ItemType Directory -Force C:\AW2025Migration\scripts | Out-Null
Expand-Archive .\aw2025-azure-migration-v1.0.zip C:\AW2025Migration\scripts -Force
Set-Location C:\AW2025Migration\scripts
  1. [VM] Copy the same zip to the VM’s Downloads folder (copy in Explorer on the laptop, paste in Explorer in the Remote Desktop window), then run the same six lines there.
  2. [laptop] The laptop scripts talk to Azure through the Az modules. Windows PowerShell 5.1 needs TLS 1.2 and the NuGet provider before Install-Module works:
[Net.ServicePointManager]::SecurityProtocol = 'Tls12'
Install-PackageProvider NuGet -MinimumVersion 2.8.5.201 -Force
Install-Module Az.Accounts, Az.Storage, Az.KeyVault -Scope CurrentUser
  1. [laptop] Sign in to the lab subscription, by its tenant ID (Microsoft Entra ID > Overview in the portal), and check where you landed:
Connect-AzAccount -TenantId <your lab tenant ID>
Get-AzContext

The VM needs none of this: no Az module, no sign-in. Its managed identity does the talking (part 1, A3).

Two machines, two accounts. If the laptop already knows another Microsoft account (work, a client), a plain Connect-AzAccount can sign you in to that one instead. With -TenantId it cannot (I learned this the not-so-fun way). Get-AzContext tells you which subscription you are in: check it before every laptop script that talks to Azure.

Scripts that will not run. Windows marks every file that came from the internet, and PowerShell refuses a marked script (“is not digitally signed”). Unblocking the zip before unpacking it (step 1) avoids that. If the execution policy itself blocks scripts on the machine, allow them for this window only: Set-ExecutionPolicy -Scope Process Bypass.

Expected: on both machines, C:\AW2025Migration\scripts holds the 14 scripts, README.md and LICENSE; the zip’s hash matches the table. On the laptop, Get-AzContext shows the lab subscription and your lab account.

B2. Inventory of the laptop: script 10

The VM has to match the laptop where it matters, and you cannot match what you never wrote down. 10_Get-SourceInventory.ps1 writes it all down, once, before anything on the laptop changes:

  • the machine: Windows version, CPU cores, memory, time zone, every drive (size, free space, file system, allocation unit);
  • the instance: version and CU, edition, collation, authentication mode, the CPUs SQL Server may use, memory and other settings, services and their accounts, default paths, tempdb, full-text and FILESTREAM, logins by type, linked servers, server triggers, credentials, availability groups, jobs;
  • the database: size, files and their drives, recovery model, compatibility level, owner, collation, TDE, and everything that ties it to a target: Enterprise-only features, FILESTREAM, memory-optimized data, full-text, CDC, replication, CLR assemblies, Service Broker, users and the logins they map to, jobs that name it.

The result is a JSON file in D:\Backups\<laptop>\INVENTORY\, uploaded to inventory/<laptop>/ in the container, where script 12 on the VM picks it up in B4. It holds no passwords, keys or hashes.

[laptop, PowerShell as administrator] As a Windows login that is sysadmin in SQL Server, signed in to Azure (B1):

.\10_Get-SourceInventory.ps1 -StorageAccount aw2025mig12345 -WhatIf
.\10_Get-SourceInventory.ps1 -StorageAccount aw2025mig12345

Again, this is on my laptop, where I have 3 separate drives: OS, DBData and DBLog. DBData is the data drive, where I also have the backups folder. If your drives are laid out differently, add -BackupRoot with a folder that has room for the backups in part D, and use the same value there.

What mine said about AdventureWorks2025: 328 MB of data (296 MB used), a 200 MB log, SIMPLE recovery, compatibility level 170, owned by my Windows login, no TDE yet, no Enterprise-only features, no FILESTREAM, three full-text indexes, Query Store on, one user mapped to the SQL login AW2025Migration, and no jobs that name it. Two of those come back as gates in B4: the full-text indexes (G8) and the SQL login (G7).

Note on a storage account that is not there. If you ran 00 and then 01 again with the same storage account name, the laptop may still talk to the old, deleted account for a while: 10 stops with “Container backups not reachable”. The laptop’s DNS cache remembers the old address. Run ipconfig /flushdns, wait a few minutes, and run 10 again.

Expected: a CHECK table of four lines, all OK: the inventory file written, the same file in Blob, SQL Server using every CPU of the machine, and no FILESTREAM. Then All OK. Next: 11_Test-DiskSpd.ps1 on this machine (optional), then 12_Test-TargetReadiness.ps1 on the Azure VM.

B3. DiskSpd on both machines: script 11 (optional)

The inventory says how big the drives are; it says nothing about how fast they are. 11_Test-DiskSpd.ps1 runs Microsoft DiskSpd in the I/O patterns SQL Server uses, the same seven tests on each machine, so the two can be put side by side:

TestPatternStands for
018K random readdata file reads
028K random writecheckpoint
038K random, 70% read / 30% writea mixed OLTP load
0464K sequential readscans, read-ahead
0564K sequential writebulk load
061M sequential readbackup and restore
074K write, 1 thread, 1 I/O at a timethe transaction log flush

Every test bypasses the Windows cache and writes through, as SQL Server does, and records latency as well as throughput.

Read this before you run it. It drives the disk flat out for about ten minutes. A drive that holds user database files is refused unless you add -Force: on a lab laptop that is fine, on a production server only in an agreed window, and better not at all. It also measures what a drive can do, not what the database needs: a laptop NVMe drive gives hundreds of thousands of I/Os per second, an Azure VM gives what its disks and its VM size allow. Size Azure on what the workload uses; use this test to see the difference in latency and to prove the VM’s disks deliver what you pay for.

  1. [laptop, PowerShell as administrator] The data drive. -Download fetches DiskSpd from Microsoft’s GitHub the first time; -Force because D: holds the database files:
.\11_Test-DiskSpd.ps1 -TargetDir D:\DiskSpdTest -Download -Force -WhatIf
.\11_Test-DiskSpd.ps1 -TargetDir D:\DiskSpdTest -Download -Force

The results land next to the script, in a folder named DiskSpd <computer> <drive> <date_time>: one .txt per test, 00 environment.txt and summary.csv.

  1. [VM] Copy that whole folder to the VM (Remote Desktop clipboard, as in B1), then test the VM’s data drive and compare:
.\11_Test-DiskSpd.ps1 -TargetDir F:\DiskSpdTest -Download -CompareWith "C:\AW2025Migration\scripts\<laptop folder>\summary.csv"

Repeat for the log drives (E: on the laptop, G: on the VM) if the log flush matters to you: test 07 is the one to look at.

For the record, my laptop’s data drive (a Samsung 990 Pro): 412,000 IOPS of 8K random reads at a p99 latency of 0.40 ms, 3,300 MiB/s of sequential reads and writes, and a log flush (test 07) of 0.03 ms on average. No Azure managed disk comes near that, and it does not need to: AdventureWorks2025 uses a tiny part of it. On the VM I ran a short smoke test only, to prove the script and the comparison work.

Note here: second-hand laptop with SSDs purchased when SSDs were affordable. Perhaps we will look back at this RAM-pocalypse and SSD-pocalypse in the future and laugh, but right now we can only dream of those prices. As of 2024 – early 2025, a VM with 2 GB/s throughput was your middle-of-the-road choice. These days is pushing again the higher end of pricing. I will close by pointing out that no, I am not using IOPS.

Expected: on each machine a CHECK table of three lines, all OK: every test ran without a DiskSpd error, every test gave IOPS and latency, the test file was deleted. Then All OK. Summary: <path>\summary.csv. With -CompareWith, a table of both machines, test by test.

B4. Readiness gates on the VM: script 12

Now the VM is held against the laptop. 12_Test-TargetReadiness.ps1 reads the laptop’s inventory from Blob (B2), looks at the VM and runs ten go / no-go gates. A gate that says CHECK is either fixed now or decided and written down, before a single backup is restored.

GateChecksWhy it matters
G1server collation as the laptop’stempdb and the system databases use it; temporary tables follow it
G2SQL Server version the same or newera backup never restores to an older version
G3the edition takes the databasean Enterprise-only feature in use needs Enterprise (or Enterprise Developer)
G4@@SERVERNAME is the machine namejobs, linked servers and some tools rely on it
G5time zone as the laptop’sbackup file names and job schedules are in local time (A2)
G6free spacethe data and log drives take the database, the staging drive takes the backups
G7SQL Server authentication, if SQL logins are useda SQL login cannot sign in to a Windows-only server
G8full-text and FILESTREAM, where the laptop uses themthe restore needs the same features installed
G9SQL Server uses every CPU of the VMan edition or VM layout can hold it to fewer
G10the VM’s identity reaches Blob and Key Vaultparts F and G depend on it (the roles from A3)

It also shows, without gating on them: VM size and region, the licence (free Developer or paid), memory settings, tempdb and the services.

[VM, PowerShell as administrator] As a Windows login that is sysadmin in SQL Server. No Az module and no sign-in: the VM’s managed identity reads Blob and Key Vault. -SourceServer is your laptop’s name (mine is the default):

.\12_Test-TargetReadiness.ps1 -StorageAccount aw2025mig12345 -KeyVault aw2025-kv-1234 -SourceServer <your laptop's name>

It changes nothing, so there is no -WhatIf.

Mine came back with nine gates OK and one CHECK: G7. The laptop’s database has a user mapped to a SQL login, and the Azure image installs SQL Server with Windows authentication only (configuring SQL Server post-install is another series in itself, but we will not cover it here). Script 41 in part F can switch SQL Server authentication on (-EnableSqlAuth), but for a one-off it is quicker by hand, and it is better fixed now than discovered at the end:

  1. [VM, SSMS] Right-click the server > Properties > Security > SQL Server and Windows Authentication mode > OK.
  2. [VM, PowerShell] Restart SQL Server, and Agent with it:
Restart-Service MSSQLSERVER -Force
Start-Service SQLSERVERAGENT
  1. Run 12 again.

max server memory. On the image it is not set, so SQL Server may take all of the VM’s 16 GB. 12 warns about it but does not gate on it. Set it before a real workload arrives: on a small VM, physical memory minus 4 GB is a fair start.

Important: as of 2026, Microsoft recommends 75% of the available system memory not used by other processes (https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver17). Of course, you are also expected to know the limitations of SQL Server Editions (read Enterprise and Standard, the most used ones).
Where am I going with this? Standard Edition supports 128 GB of RAM for the buffer pool in SQL Server 2022 and 256 GB in SQL Server 2025. Use your common sense here.
Alright, moving on:

Expected: all ten gates OK, then Ready. <VM> can take AdventureWorks2025 from <laptop>. Next: 20_Enable-SourceTde.ps1 on the source. If you picked the paid image in A2, a yellow licence line says so here too.

Check the scripts before you run them

The scripts of this post, from the v1.0 release (immutable: nobody, me included, can change its files or its tag). On each machine, after B1:

Get-FileHash C:\AW2025Migration\scripts\1*.ps1 -Algorithm SHA256 | Format-Table Hash, Path -AutoSize

Every hash must match its line below; SHA256SUMS.txt on the release has all of them. If one differs, don’t run it.

FileSHA-256
aw2025-azure-migration-v1.0.zip0db411127783f4f2c604211d8293b2854a7776b0289d54b57344890e73c45d4f
10_Get-SourceInventory.ps16f0367eb3b5996181a99476b9f36950e295d41b5419b3a8c331ced5c6f66d80f
11_Test-DiskSpd.ps1b3ff522514242b4ec524ee5dc67ca3b77ebc5cab67cce17294dd921226f6ac42
12_Test-TargetReadiness.ps16f0765525138cd5f27d3bfc30b215238eee6f7ac9fdf68fef09322faa9f2b930

Next

Part 3 is part C, and the first one that changes something on the laptop: script 20 switches on TDE, exports the certificate and proves the export opens, with every password going straight into Key Vault; script 21 exports the SQL login with its SID and password hash, so the user in the database still maps to it on the VM.

The scripts are on GitHub: calinsqlplay/aw2025-azure-migration (the version these posts use is the v1.0 release).

Leave a Reply

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