Azure SQL Managed Instance / Migration / Database Experimentation Assistant (DEA) / Evaluate your workload before you migrate your database to Azure SQL Managed Instance

Using Database Experimentation Assistant (DEA) you can evaluate your current production workload from your current environment and analyze how will that workload perform in your new targeted environment.

For this example we are going to use following: 

Source: SQL Server 2017, 32CPU, 256GB Memory

Target: Azure SQL Managed Instance, BC Gen5 (16 vCores, 80GB Memory)

DEA VM: SQL Server 2017,  4CPU, 32GB Memory

Workload: 10minutes XE trace (XEL), size 19.9GB

I will use DEA to capture, analyze and compare source and target workloads. 

More details about DEA and how to download and install you can find in this MS article.

I will use VM with DEA installed to capture workload from the production SQL Server and replay that workload against Azure SQL Managed Instance

First step will be capturing the workload (Extended Event trace) from the source SQL server.

Open DEA and select Capture Traces option

For this example I choose Extended Events trace option 

Capture Location is a valid folder path on the SQL Server source machine where server side Extended Events XEL files can be stored. Make sure SQL Server account has write permissions on the folder specified. 

If you are using Azure SQL Managed Instance or Azure SQL Database as a source server you need to provide blob storage account as a Capture Location. 

For this example I captured 10 minutes trace from production server. Exactly 42 files, size 19.9 GB (That depends on the traffic at the time)  

Next step will be replaying the captured workload trace against your target environment.

Source Trace Location will be location of your captured XE XEL files. After I run replay, DEA tool will create a new sub folder within your  Source Trace Location named PreProcessOutput, all Replay Markup Language (RML) files will be stored there.

Replay Trace Location will be blob storage account (URL + Shared Access Signature) since we are using Azure SQL Managed Instance as our targeted instance.

After replay is completed you should have your XE XEL files in specified Replay Trace Location (Blob Storage Account)

Next step will be analyzing the replayed collected workload traces.

With DEA you can generate an analysis report which will help you understand what will be performance impact after migration.
DEA analyze your workload and provides performance details for each query run on both Source SQL Server and target Azure SQL Managed Instance.

Trace for Target 1 SQL Server is actually path to the source server trace location.

BLOB URL for Target 2 SQL Server is BLOB URL used when you replayed the workload trace against your target Server (Azure SQL managed Instance).

Path to store Target 2 files on SQL Server Machine is location where DEA is going to copy files from the URL BLOB storage, so DEA can analyze those workload traces.   

After providing all necessary information DEA will generate an analysis report using the replay traces and at the end will give you insights into your workload performance impact after migration. As well, DEA will create DB on your local SQL Instance (DEA VM).

DEA Analysis Report example with the insights into your workload performance impact.

You can select improved queries by execution count from query distribution list to see more detailed information about specific queries.

You can select specific query for more detailed information

I will not go too detailed into explaining how to interpret those results. This DEA post was meant to be just a part of the migration sequence.

  • DMA (Assessment)
  • DEA (Capture the trace / Replay the trace)
  • DMS (Migrate database)

If you would like to read more about what DEA analysis metrics include and how to evaluate your targeted version of SQL Instance for a specific workload read this Microsoft Article, there you may find all necessary information about DEA usage.

More details about DMA with usage example you can see on the following post.

Azure SQL Managed Instances / Migration / Data Migration Assistant (DMA) / Assess on-premises SQL Server instance

Similar Posts:

One Response to “ “Azure SQL Managed Instance / Migration / Database Experimentation Assistant (DEA) / Evaluate your workload before you migrate your database to Azure SQL Managed Instance”

  1. Michael Ou says:

    Nice.

Leave a Reply

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