Right Size/Recommend Azure SQL Managed Instance

[wernerrall]

Introduction

When doing SQL Migrations we have some wonderful tools at Microsoft that will assist you with the migration from On-Premise to Azure. But What happens down the line when you need to decide if you made the correct choice from the start? Or has your use case changed? Maybe a service costs more than you thought and now you want to understand what the alternatives are?

In my case I created a SQL Managed Instance without any tools and need answers to two questions.

Did I make the right Choice?

Are there any tools that can provide recommendations or right sizing on my Database?

Process Description:

I will run the Data Migration Assistant SQLAssessment Tool on my Azure SQL Managed Instance.

Requirements:

1. Download the Microsoft Data Migration Assistant (https://www.microsoft.com/en-my/download/details.aspx?id=53595 )
2. A SQL Sysadmin account to your Azure SQL Managed Instance (Create a new user for the purpose of the assessment and delete it afterwards)
3. A Machine or Server to run this Assessment on that can connect to the required Azure Managed Instance (You could also enable public endpoints https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/public-endpoint-configure?view=azu...)
4. [Optional] Download Visual Studio code (Visual Studio Code - Code Editing. Redefined)

Steps

1. Find the files required to run the assessment from my Github Repo (https://github.com/WernerRall147/RallTheory/tree/main/SQLManagedInstanceRightSizing ) and copy them into “C:\Temp\SQL” or any directory of your choice.

2. Open the config.json file and replace the sqlConnectionStrings with

• your SQL Managed Instance details
• your User ID
• your Password

Save and close the file.

There are some properties that can be modified, please refer to our official Docs Article (https://docs.microsoft.com/en-us/sql/dma/dma-sku-recommend-sql-db?view=sql-server-ver15 )

3. Change Directory to “C:\Program Files\Microsoft Data Migration Assistant\SqlAssessmentConsole” and then Open the “RunAssessment.ps1” PowerShell but only execute the Performance Data Collector line by highlighting and pressing “F8” in Visual Studio Code

4. Wait for the Script to finish (You can also press Enter to cancel). Your ouput might be longer depending on how you configured the JSON File. You will also see there new files that have been created. This will be used in our next script.

5. Now we are going to run the SkuRecommendation portion in the PowerShell by pressing F8 again in Visual Studio.

6. If we take a careful look at our output we can see The actual SKU Recommendation isn’t even Azure SQL Managed Instance which means my setup was overkill. My SKU Recommendation is Azure SQL Database

7. There is also a nifty HTML report we can look at and share with our colleagues. We even get Justifications for the results.

Find a copy of my code https://github.com/WernerRall147/RallTheory/tree/main/SQLManagedInstanceRightSizing

Disclaimer
The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.