Technical Preview: Database Experimentation Assistant


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 (15)

  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

Skip to main content