AdventureWorks2025 to an Azure SQL VM, part 6: check post-migration, and clean up

Part 6 of 6. The series: 1. The plan and the lab · 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 (this post).

Part 5 ended with AdventureWorks2025 ONLINE on the VM, encrypted with the laptop’s certificate, and its login back with the laptop’s SID. ONLINE is not the same as “the same database”, though. This post is where you prove it: one script, 50, holds the restored database against the facts the laptop wrote right after its last backup, in D2 of part 4.

Then the clean-up, because a lab left running is a bill: 99 puts the machines back as they were, 00 takes the Azure base down, and then the free account itself, which takes more steps (and more days) than I expected.

Part G. Check post-migration, and clean up

G1. The post-migration check: script 50

50_PostRestore-Check.ps1 runs on the VM. With the VM’s identity, as 40 and 41 did, it downloads the newest facts file the laptop uploaded (facts/<laptop>/AdventureWorks2025/ in the backups container), then works through it:

  1. The backup. The database here comes from the laptop’s last backup (the same last LSN as in the facts), and the laptop had no user writes after that backup. If it had, the row counts and checksums below can differ for that reason alone, and 50 says so. This is why the facts are taken after the last LOG backup, not after a tail-log (part 4).
  2. The owner, the only thing 50 changes. On the laptop the owner was a Windows login of the laptop, which cannot exist on the VM, so after the restore the owner is whoever ran it. 50 sets it to sa, or to the login you pass with -Owner.
  3. The settings of the database, one by one, against the laptop’s.
  4. DBCC CHECKDB, unless you pass -SkipCheckDb (for a database big enough that you check it another way).
  5. The tables: the same tables, the exact row count of each, and a checksum of the data of each.
  6. The objects and the code: the objects by type (tables, views, procedures, constraints, indexes and the rest), and one hash over the code of every module, so a procedure changed by a single character shows.
  7. TDE: the database is encrypted with the laptop’s certificate (same thumbprint).
  8. Reported, not changed: the SQL logins as on the laptop (SID, default database, policy, server roles), database users without a login, full-text indexes when Full-Text Search is not installed on the VM, and SQL Agent jobs on the laptop that name the database. Those are decisions for you; 50 lists them and leaves them alone.

[VM, PowerShell as administrator] As a Windows login that is sysadmin in SQL Server. -SourceServer is your laptop’s name, as in part 5:

.\50_PostRestore-Check.ps1 -StorageAccount aw2025mig12345 -SourceServer <your laptop's name> -WhatIf
.\50_PostRestore-Check.ps1 -StorageAccount aw2025mig12345 -SourceServer <your laptop's name>

Here -WhatIf runs every check and only leaves the owner as it is. So read the -WhatIf first: if anything other than the owner is off, stop there and find out why before the real run.

Mine, in the -WhatIf: the same last LSN, no writes on the laptop after it, 18 settings the same, CHECKDB clean in 2 seconds, 71 tables and 760,174 rows with the same counts and checksums, the objects by type the same, 51 modules with the same hash, the same TDE certificate, the login the same, no users without a login, Full-Text Search installed (the database has three full-text indexes), and no jobs. Only the owner was left, and the real run set it to sa.

What 50 cannot tell you: whether the applications connect, and whether the database runs as fast as it did. Those are tests for the application, not for the database.

Expected: a CHECK table of 15 lines, all OK, then All OK. AdventureWorks2025 on <VM> matches <your laptop's name>.

G2. Clean up: scripts 99 and 00

There are two ways out of the lab, and they share the first step:

  • To run it all again (what I did on 13 Nov, to redo the series end to end): 99 on both machines, then 00 and 01 in Cloud Shell for an empty Azure base, and start again from 10.
  • To take it all down: 99 on the laptop, then delete the resource group. The VM goes with it, so 99 on the VM is not needed.

99_Reset-Lab.ps1 puts one machine back as it was before the migration. It is for the lab only: it decrypts and changes the source database, drops the target database and deletes files. So it asks you to type the machine’s name before it changes anything.

-SideWhat 99 undoesWhat it keeps
Source (the laptop)TDE off (decrypted, then the encryption key dropped), recovery model back to SIMPLE, the certificate AW2025_TDE (only if no database uses it), the master key in master (only if nothing else in master needs it), the key files, and the backup, facts and inventory foldersthe database, its users and the SQL logins (they are what the migration moves), the backup history in msdb
Target (the VM)the database, the SQL logins in -Logins, the certificate and the master key in master (same rules), the staging folder F:\Restorethe authentication mode, SQL Server’s settings

[laptop, PowerShell as administrator] As a Windows login that is sysadmin in SQL Server:

.\99_Reset-Lab.ps1 -Side Source -WhatIf
.\99_Reset-Lab.ps1 -Side Source

[VM, PowerShell as administrator] Only if you are going to run the series again:

.\99_Reset-Lab.ps1 -Side Target -WhatIf
.\99_Reset-Lab.ps1 -Side Target

In a real migration, this is the step you skip. 99 drops the certificate and deletes its key files, and 00 (or the resource group’s deletion) takes the password with the vault. After that, nothing can read a backup encrypted with that certificate. In the lab that is the point. In real life the certificate, its private key and its password are kept for as long as any backup encrypted with it is kept.

00_Remove-AzureLabBase.ps1 removes what 01 created in part 1: the role assignments, the Key Vault (deleted and purged, so its name can be used again), the storage account with every blob in it, and the VM’s managed identity. It keeps the resource group and the VM. Nothing it does can be undone, so it lists what would go and asks you to type the resource group’s name first.

[Cloud Shell, PowerShell] Signed in to the lab subscription:

./00_Remove-AzureLabBase.ps1 -StorageAccount aw2025mig12345 -KeyVault aw2025-kv-1234 -WhatIf
./00_Remove-AzureLabBase.ps1 -StorageAccount aw2025mig12345 -KeyVault aw2025-kv-1234

To take everything down, delete the resource group instead: in the portal, or in Cloud Shell:

Remove-AzResourceGroup -Name AW2025Migration

The vault goes with the group, but only into soft delete: its name stays taken for 90 days unless you purge it too:

Get-AzKeyVault -InRemovedState
Remove-AzKeyVault -VaultName aw2025-kv-1234 -Location <the vault's region> -InRemovedState

Mine, on 13 Nov: 99 -Side Source on the laptop (TDE off, SIMPLE, the certificate and the master key dropped, the key files, backups and inventory deleted; the database, its user and the login kept). Then the resource group deleted, with the VM, its three disks, the network, the SQL virtual machine resource, the extensions, the storage account and the vault, and the vault purged. All that was left was NetworkWatcherRG, empty: Azure creates it on its own with the first virtual network in a region.

Expected: for 99, a CHECK table all OK (on the laptop: the database ONLINE and not encrypted, in SIMPLE, the certificate gone, the files deleted). For 00, a CHECK table of four lines, all OK: the storage account gone, the vault gone, no managed identity on the VM, the resource group kept.

G3. Closing the free account

The lab ran on an Azure free account, on an Outlook.com address made for it and nothing else (part 1). Deleting the resources stops the costs; it does not close the account. That takes four more steps, in this order, and the order is forced on you:

  1. Cancel the subscription. [portal] Subscriptions, the lab subscription, Cancel subscription. Billing stops at once. Check Cost Management first, so that nothing you forgot is still running.
  2. Wait three days, then delete it. The Delete subscription option only shows up three days after the cancel. If you never press it, Azure deletes the subscription itself 90 days after the cancel.
  3. Then remove the card. The portal will not let a card go while a subscription is tied to it, even a cancelled one. Delete the subscription first, check that the last invoice is 0, and then the card can be removed.
  4. Close the Microsoft account. [browser] account.live.com/closeaccount.aspx, signed in as the lab account. You choose a reopen window of 30 or 60 days; after it, the account and its data are deleted for good.

Signing in reopens it. During the reopen window, signing in to the closed account is how you reopen it. So don’t sign in “just to check” once it is closed, and take the lab account off any browser, password manager or Cloud Shell session that might sign in on its own.

Mine: subscription cancelled on 13 Nov, a few minutes after the resource group was gone. A week later it is still there, Disabled, with Delete available since 16 Nov. The whole lab cost €58.42, nearly all of it the VM (€43.64) and its two data disks (€6.80 each), and all of it came out of the free credit: the account was never upgraded to pay-as-you-go, so nothing went to the card, and €121.13 of the credit was left unused. The portal says the free account itself will be deleted on 10 January 2027. Deleting the subscription, removing the card and closing the account are still on my list.

Expected: the subscription gone from Subscriptions, no card on the account, and a closure date from the closing page.

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 the laptop and on the VM:

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

In Cloud Shell, after the upload: Get-FileHash ./00_Remove-AzureLabBase.ps1.

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
00_Remove-AzureLabBase.ps161fe8c80e1836b8f5a43ff1af6e71c9a891e79e73881bf0fa0427a1aa914e644
50_PostRestore-Check.ps1d4bcab3967355383fd607f4c665d39a15d9c387f07f16cb1233257978324069a
99_Reset-Lab.ps102817baa5c5a0c875ec6e3943861f93fbe5944837ed5b99fe14760ef29b903b3

Looking back

Six posts, fourteen scripts, one database. Every script has a -WhatIf if it changes anything, every one ends with a table of what is OK and what is not (the CHECK table, or the gates of 12), and every step of the run is in these posts with what I got. The order did most of the work: look before you change anything, export what the target cannot get any other way (the certificate, the logins), take the facts right after the last backup, and prove the result against those facts instead of trusting that ONLINE means done.

What I would add before using this for a real database:

  • A tail-log cutover. v1.0 ends with a normal LOG backup, so the facts can still be read from the source (part 4). A real cutover ends with a tail-log WITH NORECOVERY, and the facts have to come before it.
  • Tighter storage. The key files in a container of their own, where the VM can only read; soft delete on blobs and containers; a storage firewall that lets in only the source and the VM’s network.
  • The source on the latest CU, before the first backup, not after.
  • The VM stopped (deallocated) between sessions. A SQL Server VM with a pay-as-you-go licence pays for the licence by the hour while it runs.

Of course, the true power of the runbook is once you can automate it, but that is another story for another time.

Thank you for reading all six. If you run it, I would like to know where it broke for you.

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.