Technical Preview: Database Experimentation Assistant


DEA 2.1 GA version is now available! Learn more here.

Overview

Database Experimentation Assistant (DEA) is a new Experimentation solution for SQL Server upgrades. It will assist in evaluating a targeted version of SQL for a given workload. Customers who are upgrading from previous SQL server versions (starting 2005 and above) to any new version of the SQL server will be able to use these analysis metrics provided by tool, such as, queries that have compatibility errors, degraded queries and query plans, and other workload comparison data to help them build higher confidence, making it a successful upgrade experience.

What’s new?

The tool offers the following capabilities for workload comparison analysis and reporting:

  • Automated script to set up workload capture and replay of production database (using existing SQL server functionality Distributed Replay & SQL tracing)
  • Perform statistical analysis model on traces collected using both old and new instances
  • Visualize data through analysis report via rich user experience

Supported versions

Source: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016
Target: SQL Server 2012, SQL Server 2014, and SQL Server 2016

Installation

You can install from Microsoft Download Center. Run ‘DatabaseExperimentationAssistant.msi’ to install Database Experimentation Assistant tool.

Resources

Database Experimentation Assistant: How to use the tool

Database Experimentation Assistant: Command line support

Database Experimentation Assistant: FAQs

Channel9 video

Comments (28)

  1. Mike Lawell says:

    Does this work on Azure SQL Database? I assume so, but need verification.

    1. Harini Gupta says:

      Hello Mike, Thanks for your comment. DEA currently only supports SQL on premise and SQL on VM database migrations/upgrades. We are looking into supporting Azure SQL DB migrations in the future though.

      1. I second Mike on this. It would be awesome to have Azure SQL Db support to do a comparison of one service tier vs the other, impact of increase or decrease of DTUs, single database vs elastic pools, etc.

        1. Harini Gupta says:

          Thanks Warner, We are looking into Azure Db support.

  2. Nabilah says:

    Hi – has this been used in actual environments? I plan to recommend this to my customer but need a confirmation.

    1. Harini Gupta says:

      Yes we have had customers use in the real environments for their upgrade to 2016 projects. Feel free to recommend to your customer and let us know of feedback you hear from them.

  3. Dave Hughes says:

    The workload capture doesn’t currently work against a SQL Server 2005 instance, because the sp_trace_create script command assigns default values to local variables (which isn’t supported in SQL Server 2005).

    1. Harini Gupta says:

      Hi Dave, We are aware of the sql2005 issue with workload capture and working on fixing it. The support will be available in future releases. Sorry about the inconvenience.

  4. Patrick Bye says:

    How do I connect to a server using SQL authentication?

    1. Rohit Joy says:

      Hi Patrick, Thank you for using DEA and providing your feedback! SQL authentication support is on our list of features to support. It should be available in a future release that we will publish with release notes.

  5. Abhishek says:

    I am not able to perform Replay Workload, it is giving me error “Cannot find DReplay.exe. Please check if DReplay is installed on the machine”, though i can see the DReplay.exe at the given path “.:\Program Files (x86)\Microsoft SQL Server\110\”. I have done replay workload using command line but not from the tool itself.

  6. AbhishekSoft says:

    Hi,
    I am trying to perform replay trace using DEA, but getting error “Cannot find DReplay.exe. Please check if DReplay is installed on the machine”.
    I have Sql server 2012 installed on D drive and providing the path in D:\Program Files (x86)\Microsoft SQL Server\110 in Sql server install path field.
    Distributed replay enviornment is completely set.
    Machine One having following components
    1. Controller
    2. Client
    3. Management Tools
    4. Sql server 2008 (on C drive)
    5. Sql server 2012 (on D drive)
    6. DEA installed
    Machine two have only Sql server 2012 installed which is my target machine
    This is done using command line but not able to perform replay through DEA.

    Please help

    1. DinekK says:

      Abhishek – we can get on a call with you to troubleshoot the issue. Please send an email to deafeedback@microsoft.com

  7. suresh says:

    For upgrading from SQL Server 2012 to 2016, I have captured Trace from Sql Server 2012 express edition and replayed into 2016 Dev.edition & 2012 express edition.
    Usng the ReplayResult.trc files generated from 2016 & 2012 ,trying to anaylyse using the DEA tool. It is creating A database,B database & Analyse database but on the analysis report no information are showing. The Workload comparision shows no data available. Pls. help

    1. DinekK says:

      Suresh – I believe we are working offline with you on this. Let us know if you are still having issues. You can reach out to us at deafeedback@microsoft.com

  8. Rathish V says:

    Cannot able to capture the trace while doing D-replay in Microsoft SQL Server 2016 (RTM) -Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 using the Pre-Processor .irf files as input. But I can able to capture in Microsoft SQL Server 2008 R2 (SP1) Developer Edition (64-bit) using the same Pre-processor Irf file. Pls. help.

    1. DinekK says:

      Rathish – just to clarify, your target server in this case is SQL 2016 – Developer edition? Does log show any errors ? Please send the logs to deafeedback@microsoft.com to troubleshoot this issue.

  9. Aditya Kota says:

    I’m trying to use this for an upgrade and capacity planning. Unable to make it work, Here is my environment, SQL 2016 DEVELOPER and windows server 2016. Installed Distributed Replay controller and Client Services. I dont think i have the Distributed replay controller working, this is what i see in the logs “Failed to get DmpClient. [HRESULT=0x8007007F]”. I have tried multiple options and still no luck. It would be better for DEA team to put together the pre-requisites for DEA like Distributed Replay setup in your blog.

    1. Harini Gupta says:

      Hi Aditya,

      Thanks for trying out DEA for you capacity planning. Sorry to hear about the issues you ran into with Dreplay setup. Have you followed the steps in the blog: https://blogs.msdn.microsoft.com/datamigration/2016/10/25/database-experimentation-assistant-replay-trace/? Specifically, Read the blog on how to set up distributed replay controller and client – https://blogs.msdn.microsoft.com/datamigration/distributed-replay-controller-and-client-setup/

      1. Aditya Kota says:

        Thanks for your prompt response! i was following the blogs you provided for setup but figured out the issue. I have been giving the controller name something else apart from the Servername. So, when i changed the controller name to the Servername it started working. Will give a shot at DEA, thanks!

        1. Harini Gupta says:

          Good to hear – let us know if you have other questions.

  10. There is a performance hit while running this tool, any particular reason for not leveraging Extended Events?

    1. DinekK says:

      Hi Paulo, Extended events are in our backlog and will be incorporated in future releases.

  11. Larry says:

    1. It would be nice if it support sql server 2000.
    2. I would like to be able to capture dynamic SQL and a replay directly to the target servers without having to setup a controller, agent, and the additional source database. I just want to see if the queries error without the performance metrics.

    1. DinekK says:

      Hi Larry, Thanks for using DEA. Your requests have been added to our backlog and will be incorporated in future release.

  12. BJ says:

    The web access requirement for even setting up the trace workloads is a show stopper. The error message is not clear however the log shows the attempts failing to access the web. This prevents running DEA as a different account with server access that doesn’t have web access. It also prevents using the tool on servers which do not have web access. A tool that monitors our data access patterns and then uses the internet to analyze it is not one we are likely to adopt. The concept of the tool is awesome and needed, just needs to be standalone.

    1. DinekK says:

      Hi BJ, appreciate your feedback.The only web requirement for DEA is jsonlite R package download and in future versions we will eliminate that requirement as well. I am not quite sure if I understood your scenario, could you please reach out to me at dinek@microsoft.com so that we can have an in depth conversation on how to add this to our feature backlog.

      Regards,
      Dinek

  13. Rupesh Behera says:

    Hi,
    I am trying to install DEA2.0 , installation is successful but while opening following error occurred. Please let me know if any prerequisite required.

    —————————
    Application Error
    —————————
    System.InvalidOperationException: GetExportedValue cannot be called before prerequisite import ‘Microsoft.DEA.Logging.TraceLogger..ctor (Parameter=”configManager”, ContractName=”Microsoft.DEA.ConfigurationManager.IConfigManager”)’ has been set.

    at System.ComponentModel.Composition.ReflectionModel.ReflectionComposablePart.EnsureGettable()

    at System.ComponentModel.Composition.ReflectionModel.ReflectionComposablePart.GetExportedValue(ExportDefinition definition)

    at System.ComponentModel.Composition.Hosting.CompositionServices.GetExportedValueFromComposedPart(ImportEngine engine, ComposablePart part, ExportDefinition definition)

    at System.ComponentModel.Composition.Hosting.CatalogExportProvider.GetExportedValue(CatalogPart part, ExportDefinition export, Boolean isSharedPart)

    at System.ComponentModel.Composition.Hosting.CatalogExportProvider.CatalogExport.GetExportedValueCore()

    at System.ComponentModel.Composition.Primitives.Export.get_Value()

Skip to main content