Database Experimentation Assistant – Command Line


#2 below is a manual step to replay workloads. DEA command line support will be available in future releases. #1 and #3 for capturing trace and trace analysis is supported by DEA command line.

  1. Start a new workload capture (using DEA command)
    1. Deacmd.exe -o startcapturetrace -s <SQLServerInstance> -e <encryptconnection> -u <trustservercertificate> -d <database name> -p <trace file path> -f <trace file name> -t <Max duration>
    2. Example: Deacmd.exe -o startcapturetrace -s localhost -e -d adventureworks -p c:\test -f sql2008capture -t 60
  2. Replay workload
    1. Log in to Dreplay Controller machine
    2. Convert workload trace captured in step 1 to an IRF file
      DReplay preprocess -m "dreplaycontroller" -i "Path to first trace file" -d "<Folder path on controller>\IrfFolder"
    3. Start trace capture on target SQL instance using “StartReplayCaptureTrace.sql” .
      1. Open <Dea_InstallPath>\Scripts\StartReplayCaptureTrace.sql in SSMS
      2. Set @durationInMins=0 So that trace capture does not stop automatically after specified time.
      3. Set @maxfilesize - Max file size per trace file. Recommended size 200
      4. @Tracefile - unique name for your trace file
      5. @dbname - By default workload on entire server is captured. Specify database name if workload needs to be captured on a specific database only.
    4. Replay IRF file against target SQL instance
      DReplay replay -m "dreplaycontroller" -d "<Folder Path on Dreplay Controller>\IrfFolder" -o -s "SQL2016Target" -w "dreplaychild1,dreplaychild2,dreplaycild3,dreplaychild4"

      1. To monitor status, open a command prompt and run DReplay status -f 1
      2. To stop the replay, in case you see there is an issue (for example, pass % is lower than expected), open a command prompt and run DReplay cancel
    5. Stop trace capture on target SQL instance
    6. <Dea_InstallPath>\Scripts\StopCaptureTrace.sql in SSMS
    7. Edit @Tracefile to match trace file path on target SQL instance
    8. Run script against target SQL instance
  • Analyze traces (using DEA command)
    1. Deacmd.exe -o analysis -a <Target1 trace filepath> -b <Target2 trace filepath> -r reportname -s <SQLserverInstance> -e <encryptconnection> -u <trustservercertificate>
    2. Example: Deacmd.exe -o analysis -a C:\Trace\SQL2008Source\Trace.trc -b C:\ Trace\SQL2014Trace\Trace.trc -r upgrade20082014 -s localhost -e
    Comments (4)

    1. StartCaptureTrace.sql can be edited to allow parallel traces for other dbs. Edited Lines 26-33.

      –Raise a sql error if any existing traces are still running in the server
      declare @path nvarchar(300)
      set @path =’%DEAExperimentation_%’ + @dbname +’%’
      if exists( select 1 from sys.traces with (nolock) where path like @path and [Status] = 1)
      begin
      RAISERROR(‘50000’,16,-1)
      goto finish
      end

    2. Alessandro Musso says:

      It would be awesome if in the next release you allow me to make the analysis starting directly from the A \ B databases created by ReadTrace.exe instead of making me load the traces from the files every time…
      It would same a lot of time when testing big environments’ workload.
      Thanks

    3. pappasa says:

      I try to run deacmd but I get the following error:

      C:\Program Files (x86)\Microsoft Corporation\Database Experimentation Assistant>deacmd -o startcapturetrace -s localhost -d FTS_LIVE_LOCAL_2008 -p C:\TraceFiles -f Trace1 -t 1
      DEA Information: 0 : TraceEvent, 03/12/2018 08:53:41, Data: AppStarted
      DEA Information: 0 : DiagnosticEvent, 03/12/2018 08:53:41, Data: Entered GetSqlServerConnectionMetaData
      DEA Information: 0 : DiagnosticEvent, 03/12/2018 08:53:41, Data: Attempting to get the SQL server metadata.
      DEA Error: -2146232060 : ExceptionEvent, 03/12/2018 08:53:42, Data: Exception while executing query., Exception:
      [Exception Info]:System.Data.SqlClient.SqlException, Message:A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.)

      Connecting to that database with Windows authentication succeeds. Why does not DEA succeed?

      1. DEA expects a certificate. Can you try with checking “trust server certificate”

    Skip to main content