Azure Data Studio - Azure SQL Migration Extension SKU Recommendations

Microsoft recently announced the Public Preview of SKU recommendations in the Azure SQL Migration extension in Azure Data Studio. This enables us to use a performance assessment on our databases to provide us with correct sizing for an Azure SQL Managed Instance (or Azure SQL VM).

I'm using the latest version, 1.35.0 which you can download from here and if you're wondering what Azure Data Studio is, you can find out more information from this link.

The Azure SQL Migration extension is available to install via the extensions marketplace:

To perform a migration you will need Azure account details but if you're just performing an assessment like we are in this post then they're not required. Once installed we use the Manage option on an available connection as follows:

This opens up a new screen which shows some basic information about the selected SQL instance, under General the Azure SQL Migration option will now be available:

This will open the Azure SQL Migration screen, one of the things I love about Azure Data Studio is its interactivity options; we can link an Azure account from here, view migration tutorials and even open a support request but for now we'll proceed with the assessment process:

We'll now be presented with the databases on our selected instance that we wish to assess for migration, I've selected the WideWorldImporters sample database:

On the next screen we can see two migration options; Azure SQL Managed Instance and SQL Server on Azure Virtual Machine, at the bottom of the screen we'll click Get Azure Recommendation to start the performance data collection, as I haven't previously ran the collector I'll select Collect performance data now and specify a local folder where the data will be saved (once collected I can use the "I already have the performance data" option at a later time).

The data collector will run for about 10 minutes. It's worth mentioning that to be as accurate as possible the collector should be ran on "real life" workloads, if you run the collector during periods of low activity then the chances are the recommendations will be for a lower specification than what you might actually require in Azure. 

Once the data collection has completed the recommendations are automatically refreshed under each of the migration targets. In this case for Azure SQL Managed Instance it has recommended the Gen5 General Purpose 4 vCore option (32 GB) and for SQL Server on Azure Virtual Machine the E2ads_v5 2 vCPU option. 

But...

Under the Azure SQL Managed Instance option the assessment results show that 0/1 databases can be migrated. To see any issues I need to select the Managed Instance option and click Next (if you don't select a target you'll get a nice red banner telling you off).

I expected to go the the next screen but actually I couldn't and instead had to select a database and it's assessment button from this button at the bottom of the screen (in truth I found it a bit fiddly, but never mind):

The next screen shows that I have a potential migration issue on the WideWorldImporters database, I can select the database using the checkbox next to it to view the relevant information: 

Here's the problem:

In terms of performance data the recommendation is for the General Purpose service tier of Azure SQL Managed Instance however as the WideWorldImporters database makes use of in-memory tables these are not supported and is actually only supported on the Business Critical service tier.

But that's the whole point of a migration assessment tool, not only does the extension gauge database performance and recommend the correct SKU it also identifies potential migration issues such as unsupported functionality, and without a doubt the more you know beforehand the better!