Right Size/Recommend Azure SQL Managed Instance

[Guest 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 (Download Microsoft® Data Migration Assistant v5.6 from Official Microsoft Download Center )
   
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 Configure public endpoint - Azure SQL Managed Instance)
   
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 (RallTheory/SQLManagedInstanceRightSizing at main · WernerRall147/RallTheory ) and copy them into “C:\Temp\SQL” or any directory of your choice.
   

 

 

 

 

 

 

1. 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 (

Get Azure SQL SKU recommendations (Data Migration Assistant) - SQL Server

)

 

 

 

1. 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
   

 

 

 

 

 

 

 

 

 

 

1. 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.
   

 

 

 

 

 

 

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

 

 

 

 

 

 

1. 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
   

 

 

 

 

 

 

 

 

1. 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 RallTheory/SQLManagedInstanceRightSizing at main · WernerRall147/RallTheory

 

Continue reading...