Right Size/Recommend Azure SQL Managed Instance

This post has been republished via RSS; it originally appeared at: Core Infrastructure and Security Blog articles.

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=azuresql)
  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.

 

wernerrall_0-1658494757903.png

 

  1. Open the config.json file and replace the sqlConnectionStrings with
  • your SQL Managed Instance details
  • your User ID
  • your Password

 

wernerrall_1-1658494757913.png

 

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 )

 

  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

 

wernerrall_2-1658494757918.png

 

 

 

  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.

 

wernerrall_3-1658494757928.png

 

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

 

wernerrall_4-1658494757938.png

 

  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

 

wernerrall_5-1658494757939.png

 

 

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

 

wernerrall_6-1658494757941.png

 

 

wernerrall_7-1658494757945.png

 

 

wernerrall_8-1658494757951.png

 

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

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.