Blog Post

Microsoft Data Migration Blog
2 MIN READ

Release: Database Experimentation Assistant (DEA) v2.6

Jim_Toland's avatar
Jim_Toland
Icon for Microsoft rankMicrosoft
Mar 14, 2019

First published on MSDN on Aug 06, 2018

Authored by rajsell@microsoft.com

 

Overview

The Database Experimentation Assistant (DEA) is a new A/B testing solution for SQL Server upgrades. The utility will assist in evaluating a targeted version of SQL for a given workload. Customers who are upgrading from previous SQL Server versions (SQL Server 2005 and above) to any newer version of the SQL Server will be able to use the analysis metrics provided, such as queries that have compatibility errors, degraded queries, query plans, and other workload comparison data, to help them build confidence and better ensure a successful upgrade experience.

 

What's new in v2.6?

The v2.6 release of DEA has the following improvements:

- Capture and replay of production database(s) workloads through automated set up.

- Support for server side traces and XEvents.

- Perform statistical analysis on traces and/or XEvents collected using both old and new instances.

- Visualize data through analysis report via rich user experience.

- Use SQL Authentication to both capture and replay.

- An Inbuilt replay tool in addition to already supported SQL Server Distributed Replay for simple workloads.

- Removes the dependencies of R and R-Interop.

- Capture and replay workloads to Azure SQL Database , Azure SQL Managed Instance , and SQL Server on Linux.

- Reporting enhancements:

- New error categorization chart to easily find upgrade / migration blockers.

- New error pie chart grouped by error id to easily identify the root cause of the errors.

- Bug fixes and other performance improvements.

 

Tutorials

The following postings provide step-by-step guidance for leveraging DEA to perform a workload comparison:

- Overview of Database Experimentation Assistant

- Get started with Database Experimentation Assistant

- Capture a trace in Database Experimentation Assistant

- Configure replay in Database Experimentation Assistant

- Replay a trace in Database Experimentation Assistant

- Create analysis reports with Database Experimentation Assistant

- View analysis reports with Database Experimentation Assistant

- Run Database Experimentation Assistant at a command prompt

 

Installation

You can download and install DEA from the Microsoft Download Center. Run ‘DatabaseExperimentationAssistant.exe’ to install the Database Experimentation Assistant.

 

Supported sources and target versions

- Source: SQL Server 2005 and above
- Target: SQL Server 2005 and above
- Analysis: SQL Server 2008 and above

 

How to contact the DEA team?

Customers can submit feedback by using a simple interface within the DEA tool or by sending an email to Ask Azure Database Migrations <AskAzureDatabaseMigrations@service.microsoft.com>.

Updated Feb 21, 2023
Version 9.0
DEA
  • Kusza6's avatar
    Kusza6
    Copper Contributor
    Sep 18, 2020

    Hello,

    I am getting following error on DEA startup:

    Unhandled Exception: System.InvalidOperationException: GetExportedValue cannot be called before prerequisite import 'Microsoft.DEA.Logging.TraceLogger..ctor (Parameter="configManager", ContractName="Microsoft.DEA.ConfigurationManager.IConfigManager")' has been set.

    Is someone able to let me know what I am missing?

     

  • hahmeddigmap's avatar
    hahmeddigmap
    Copper Contributor
    Feb 10, 2020

    Thanks Jim_Toland! I was able to run the assessment on a different server without any issues or additional setup. Other server had SQL Server 2012 with SSMS 11 running, no additional configuration was needed.

  • Jim_Toland's avatar
    Jim_Toland
    Icon for Microsoft rankMicrosoft
    Feb 06, 2020

    hahmeddigmap, it should work with SQL Server 2019. The only issue would be DReplay, probably.

     

    The error below isn’t actually a real error – it’s just RML Utils saying it can’t launch the reporter, which isn’t needed for us anyway.  If you can share your  DEA logs (%temp%/DEA) with the Database Experimentation Assistant Feedback (deafeedback@microsoft.com) alias, the detail about any actual error should be more explicit.  It's possible that the issue relates to the step that publishes the dacpac/uses the SqlPackage.exe. We can be sure though without the logs.

     

    Try downloading the latest SSDT and repeating the process. 

    https://docs.microsoft.com/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15

  • hahmeddigmap's avatar
    hahmeddigmap
    Copper Contributor
    Feb 06, 2020

    Can this version be used for testing with SQL Server 2019?

     

    I am running into the same issue as JulianDixon, with the failure at 36%, the error is as below:

    02/06/20 12:23:47.058 [0X000011AC] The file [C:\Program Files (x86)\Microsoft Corporation\Reporter\Bin\Release\Reporter.exe] does not exist with operating system error 0x00000003 (The system cannot find the path specified)
    02/06/20 12:23:47.058 [0X000011AC] Attempt to launch Reporter failed. Check the error log and your RML installation.
    02/06/20 12:23:47.058 [0X000011AC] *******************************************************************************
    * ReadTrace encountered one or more ERRORS. An error condition typically *
    * stops processing early and the ReadTrace output may be unusable. *
    * Review the log file for details. *
    *******************************************************************************
    02/06/20 12:23:47.058 [0X000011AC] ***** ReadTrace exit code: -23

     

    I can't find a SQL Server 2019 Feature Pack.

     

  • sneharakesh2019's avatar
    sneharakesh2019
    Copper Contributor
    Jan 29, 2020
    Hi Team, I have installed the DEA tool for testing the upgraded environment. About the environment: I have installed SQL Server 2017 Developer edition and restored the production database from SQL server 2014 on it. On the same server I configured the Distributed Controller, distributed client and also installed the DEA tool. I can run the DEA tool successfully to capture the trace on Production server (SQL server 2014) and replay them on A (SQL server 2014 Dev server) and B (SQL server 2017 Dev server). As per the process, before I start the capture I take the transactional log backup from the production database and then restore it on both A and B dev servers and then start the replay. So far everything looks fine, except the pass% which does not increase more than 32% on both A and B Dev servers, due to which I think the analysis seems to be blank with no graphs or recommendations. Issue : I couldn’t get enough information on how to get pass rate above 90%. I tried doing everything but couldn't try to match the hardware requirements of production and development environment as mentioned in microsoft page https://docs.microsoft.com/en-us/sql/dea/database-experimentation-assistant-get-started?view=sql-server-ver15. Can I get some help to achieve the pass rate above 90%.
  • Jim_Toland's avatar
    Jim_Toland
    Icon for Microsoft rankMicrosoft
    Jan 23, 2020

    Yanek2412 , did you set up your DReplay environment per the detail in this article?

     

    https://docs.microsoft.com/sql/dea/database-experimentation-assistant-configure-replay?view=sql-server-2017

     

    If so, unless you migrate the permissions on your production database to the simulated production database in the DReplay environment, the permissions shouldn't be a factor.  If you have follow-up questions, please feel free to contact the Database Experimentation Assistant Feedback (deafeedback@microsoft.com) alias.  Thanks!

  • Yanek2412's avatar
    Yanek2412
    Copper Contributor
    Jan 23, 2020

    But how can it be? If sql user has only public and data reader rights for prod db.

  • Jim_Toland's avatar
    Jim_Toland
    Icon for Microsoft rankMicrosoft
    Jan 23, 2020

    Yanek2412, DEA will make modifications to the target db against which you are replaying the captured trace if the source capture traces from production database have insert / update/ delete statements.