Overview of using DEA to evaluate Azure SQL Managed Instance or Azure SQL Database

This post has been republished via RSS; it originally appeared at: Microsoft Data Migration articles.

 (Reviewed by: Ajay Jagannathan, Mark Jones, Mohamed Kabiruddin, Neetu Singh, Sreraman Narasimhan, Venkata Raj Pochiraju)

 

Database Experimentation Assistant (DEA) is a powerful tool used for comparing performance of existing SQL Server workload against a specific target version. Typically, DEA can be used in upgrade and migration scenarios when you intend to upgrade from older version of SQL Server to the latest version or migrating to Azure SQL. DEA does comparison of workloads at query performance level and thus provides greater confidence when performing upgrade or migration. To install DEA, download the latest version of the tool.

 

As customers evaluate to move SQL Server workloads to Azure and look for Platform as a Service (PaaS) capability they can choose either Azure SQL Database Managed Instance (MI) that provides instance level features and near 100% compatibility with on premise SQL Server or Azure SQL Database (DB) suited for modern cloud applications. Before embarking on this route, it is crucial to comprehend how existing SQL Server workload would perform on MI or DB and DEA tool can be leveraged to get deeper level of understanding. This article lists the sequence of steps to follow when evaluating MI or DB as a target.

 

Concepts and terminology used in DEA are explained in detail in the DEA documentation page and is outside the scope of this article. At a high-level following steps are involved when evaluating SQL Server workload using DEA.  

1. Capture workload on source SQL Server
2. Replay workload on Target 1 that mimics source SQL Server
3. Replay workload on Target 2 (MI or DB)
4. Analyze Target 1 with Target 2

 

1. Capture workload on source SQL Server

Source SQL Server would be the current production instance that’s being considered for migrating to MI or DB. To evaluate workload against MI or DB, we need to capture existing workload at source in format instead of traditional SQL Server profiler . Ensure that the user running DEA can connect to source SQL Server and has SYSADMIN privileges.

 

Figure 1 shows a new capture (Open DEA | Click Capture Traces from the left side menu | Click on + New Capture) and annotations for key options

Allen_0-1588809870422.png

Figure 1: New Capture

 

  1. Format: To evaluate the workload against MI or DB the format at source SQL Server must be extended events.
  2. Duration: Typically, the duration for capture should cover portion of workload that has to be evaluated in target. While setting Duration, take into consideration how busy the source SQL Server instance is, as it has direct effect on the number of files being generated by capture. It’s good to start with 5 -15 minutes and adjust it in subsequent captures.
  3. Capture Location: Pre created location with sufficient free space to store extended event files generated by capture and it’s recommend to store it locally on the same server rather than pointing to network shares which might be slower.

Once capture has started and the duration has elapsed, DEA would show the confirmation for completed capture. Next step is to replay the captured traces on Target 1 and Target 2.

 

Allen_1-1588809870431.png

Figure 2: Completed Capture

 

2. Replay workload on Target 1 that mimics source SQL Server

In this step, the captured traces have to be replayed against Target 1, a SQL Server instance that mimics source SQL Server both in terms of system resources like CPU, Memory, Disk configuration and SQL Server version. The step is relevant is to avoid impacting production SQL Server instance and perform the experimentation against a separate instance.

 

Figure 3 shows a new Replay (Open DEA | Click Replay Traces from the left side menu | Click on + New Replay) and annotations for key options

 

Allen_2-1588809870449.png

Figure 3: Replay against Target 1

 

  1. Source Trace Format: Since traces were captured in the form of extended events, it is set to XEvents
  2. Source Trace Location: Location for the extended events captured on source. It can be Local or Blob (Azure Storage). It’s recommended to copy the Extended Event files from source server to Target1 server and set the location to “Local”
  3. Full path to source file: Specify full path to the captured extended event file. If there are multiple extended event files, it is required to point to first file and the remaining files will be automatically picked up during replay process.
  4. Replay Tool:  There are two options available for Replay tool – DReplay or Inbuilt. DReplay only supports SQL Server profiler trace files and so, for extended events it must be Inbuilt replay .
  5. Replay Trace Location: Pre created location with sufficient free space to store extended events  files generated by replay and it’s recommend to  store it locally on the same server rather than pointing to network shares which might be slower.
  6. Database restore confirmation: Check this box to confirm that source database has been restored on Target 1
  7. SQL Server connection details: Specify the details to connect to Target 1 SQL Server instance.

Once replay finishes, DEA would show the confirmation for completed replay.  

 

Allen_3-1588809870456.png

Figure 4: Completed replay against Target 1

 

Inbuilt replay tool

Inbuilt replay tool uses ReadTrace.exe and OStress.exe under the hood to pre process and replay respectively. Readtrace and OStress are robust and popular tools included in Replay Markup Language (RML) utilities for SQL Server. OStress can be used in stress or replay mode and DEA uses it in replay mode. Providing detailed overview of ReadTrace and OStress is outside the scope of this article and we would recommend referring the help file included in RML installation.

 

To better understand what happens behind the scenes with Inbuilt replay tool, there are log files available in “PreProcessOutput” folder that gets created under the folder containing source extended event files (#3 in Figure 3).

 

As mentioned, replaying extended event traces involves two steps

Step 1: Pre process

ReadTrace.exe is used to pre process extended event files and generate bunch of .rml files. Each RML file corresponds to one SPID in the captured workload. As shown below, ReadTrace.log file contains the command line parameters used by ReadTrace.exe and the verbose log.

 

Allen_4-1588809870458.png

 

Step 2: Replay

OStress.exe is used to replay the *.rml files against target SQL Server and it generates *.out files for each corresponding rml file.

As shown below ostress.log file contains the command line parameters used by OStress.exe and the verbose log. The command line parameter includes the configuration file (-c parameter) used by OStress and the default value is

-cC:\Program Files (x86)\Microsoft Corporation\Database Experimentation Assistant\Dependencies\ReplayConfig.ini

ReplayConfig.ini file contains all the options related to connection, query & replay used by OStress. 

 

Allen_5-1588809870463.png

 

 

Next step is to replay the captured traces on Target 2.

 

3. Replay workload on Target 2 – Azure SQL Managed Instance or Azure SQL Database

In this step, the captured traces from source must be replayed against Target 2, an Azure SQL Managed Instance (MI) or Azure SQL Database (DB ) that has the number of vCores and amount of memory similar to source SQL Server. For the purpose of this article we choose MI but the steps equally apply to DB as well.

 

Figure 5 shows the Replay (Open DEA | Click Replay Traces from the left side menu | Click on + New Replay) against MI and annotations for key options

 

Allen_6-1588809870477.png

Figure 5: Replay against Target 2

 

  1. Source Trace Format: Since traces were captured in the form of extended events, it is set to XEvents
  2. Source Trace Location: Location for the extended events captured on source. It can be Local or Blob (Azure Storage). It’s recommended to copy the extended event files from source server to Target1 server and set the location to Local. To replay against MI or DB we can reuse Target 1 server where the captured files from source have already been copied.
  3. Full path to source file: Specify full path to the captured extended event file. If there are multiple Extended Event files, it is required to point to first file and the remaining files will be automatically picked up during replay process.
  4. Replay Tool:  As explained earlier we will have to use Inbuilt replay tool to replay extended event files against MI.
  5. Replay Trace Location: Since in this case, Target 2 is MI we need to provide SAS URI of Azure Blob storage account container to store extended events files generated by replay. Follow the steps in the article Manage Azure Blob Storage resources with Storage Explorer to create SAS URI. In order to get the replay time closer to captured duration time, its recommended to use General Purpose V2 Azure Storage account in Premium performance tier. Here’s the screenshot from Azure portal with the desired configuration while creating a storage account.

 

Allen_7-1588809870479.png

 

  1. Database restore confirmation: Check this box to confirm that source database has been restored on Target 2
  2. SQL Server connection details: Specify the details to connect to Target 2 which in this case is Azure SQL Managed Instance.  

Once replay finishes, DEA would show the confirmation for completed replay.

 

Allen_8-1588809870487.png

Figure 6: Completed replay against Target 2

 

You will find the extended files generated by replay in Azure Storage account container. These files should be downloaded for the final analysis step.

 

Allen_9-1588809870491.png

Figure 7: Extended event files generated by replay

 

4. Create analysis report

Final step is to analyze the extended event files generated by replay on Target 1 and Target 2 so that we can compare how SQL Server workload performed.

 

To create analysis report DEA needs to connect to a SQL Server instance for storing analysis results and this can be a separate instance, or you can use the Target 1 instance. For the purpose of this article we are using the SQL Server instance in Target 1 as the files generated by replay on Target 1 already exist and we downloaded the files generated by replay on Target 2 from Azure storage.

 

Figure 8 shows New Analysis Report (Open DEA | Click Analyze Traces from the left side menu | Connect to SQL Server instance | Click on + New Report) and annotations for key options

 

Allen_10-1588809870499.png

Figure 8: New Analysis Report

 

  1. Server name: Name of the SQL Server instance where the analysis report data would be stored. For the purpose of this article we are using the SQL Server instance on Target 1.
  2. Storage location for Target 1 traces: Set to Local as the replay files already exist in Target 1 server.
  3. Trace for Target 1 SQL Server: Specify full path to the extended event file generated by replay. It is required to point to first file and the remaining files will be automatically picked up.
  4. Storage location for Target 2 traces: Set to Local. Download the extended event files generated by replay on Target 2 from Azure storage.
  5. Trace for Target 2 SQL Server: Specify full path to the extended event file generated by replay.

 

Once the report is generated you can review the report to better understand how SQL Server workload performed against the desired target (Target 2) in comparison to the existing environment (Target 1).

 

Best practices

Here are some best practices to follow while performing workload comparison using DEA:

  1. In the Capture step, ensure that relevant workload from source SQL Server is captured. If the server is extremely busy, then it might generate lot of extended event files. In that case, we would suggest replaying in batches of files with each batch comprising of 25 GB. This will ensure successful workload comparison.
  2. In the replay step against Azure SQL target, use General Purpose V2 Azure Storage account in Premium performance tier for the Replay Trace Location to get the replay time closer to captured duration time. Azure Premium storage provides consistent low latency and throughput performance.

 

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.