How to discover SQL Server instances on Azure VMs
Someone told you that migrating your SQL Server estate to the cloud would have lowered your costs.
So you migrate a few hundreds machines to Azure-VM and suddenly your wallet catches fire: you pay 3–4 times more than what you were paying on-prem.
Why? Because you did Lift & Shift.
Before your machines were virtualized on VMware on-prem, now they are virtualized on Azure-VMs on the cloud. Nothing has really changed.
You did it because it was the easiest way to bring up those old applications to the cloud. No time to modernize and update applications and databases, let’s move those SQL Server 2003 and those Microsoft Access application to the cloud now! Hop, hop, hop….
Have a look at this image I found on this infographic, it really explains where the problem is. We can create a golden rule for that:
“…the more you stay down-left the easier is, but costs are high;
the more you go up-right the harder is, but costs are lower.”
And there are plenty of companies out there doing Lift & Shift without realizing that being in the down-left zone is not a good place to be. You now have to extract the SQL Server instances from the Azure-VMs.
I call this manoeuvre Extraction.
If you search on Google “discover a SQL Server estate on Azure-VM” the results will mostly point you to:
Azure Migrate is not compatible with Azure-VM
Azure Migrate: It looks like the go-to solution but actually it cannot apply to this scenario. Azure Migrate is tailored for on-prem discovery. Once again Microsoft is not compatible with Microsoft: you can discover SQL Server instances on AWS or GCP but not on Azure.
This is so lame and it only reflects how Microsoft is sometimes so out of tune with their customers needs.
— DBAtools: Don’t get me wrong, I love DBAtools, but I need something with a GUI that could output the results in an Excel file and maybe give me some reports.
The solution is MAP Toolkit, which latest release dates back to 2/1/2021. Is a fairly old tool but is still promoted in the latest SSMS release.
I’m not saying that MAP Toolkit is a good tool. It’s old, slow and buggy but it’s the only thing that makes the job done in this particular scenario.
Let’s see some of its pros & cons
— Is versatile: it allows you to choose between a wide range of login options. But please, use AD.
— Is complete: It allows you to scan with WMI, SQL Windows, SQL Native login. It allows you to scan with several users at the same time. This is sort of a brute force out-of-the-box. Rest assure you will not miss instances.
— Raw data: You can access the raw data if you point SSMS to the Server Name (localdb)maptoolkit. But if you want you can also export the reports in Excel and there are a few graphical metrics that can show you the big picture.
— Is old: Microsoft is not paying much attention to it and in a few years will replaced by Azure Migrate as Venkata Raj Pochiraju commented.
— Is slow: Every time you scan the MAP Toolkit re-scan also ALL the machines it has saved in the database. The more you discover the slower it gets to discover.
— Is buggy: Sometimes the MAP Toolkit gets stuck on 1 collection remaining. If you open the file C:UsersAppDataLocalMicrosoftMicrosoft SQL Server Local DBInstancesMAPToolkiterror.log you will also find the name of the machine. Just close the MAP Toolkit and it will be fine.
And if you like raw data like me you can use my query to extract the juiciest one and store them in a table.
With this query you can consolidate information like Machine Type (physical or virtual), OS Familiy, Edition, Size, SQL Server version, Compatibility Level, Collation, etc…
I’ve later used them for PowerBI dashboards.
It’s old fashion but still makes the job done. Thanks to the ability of scanning the same network with multiple accounts I was able to discover more instances that otherwise I would have missed.
So overall: it looks like is deprecated, it quacks like is deprecated, so it’s probably deprecated. But what else have we got?
Because Venkata Raj Pochiraju commented that Azure Migrate is going to be compatible soon with Azure VMs I’m quite sure that if you give it a try you might be able to collect something. I didn’t had the chance to try so is up to you, sometimes Microsoft silently releases preview that works.
Comment if you find a better approach or if you have questions.