Database experimentation assistant

Database experimentation assistant DEFAULT

Introduction

This is my second article about Database Experimentation Assistant (DEA). Please refer to my previous article to see how to download and install DEA.

The Database Experimentation Assistant is the new A/B testing solution for SQL Server upgrades. It enables customers to gather performance insights for upgrades by customers to conduct experiments on production database workloads across two versions of SQL Server.

Analysis metrics reports provided by the tool, will give information about queries that have compatibility errors, queries performance and other workload comparison data so it can transmit to the customer a higher confidence for a successful migration project.

It supports migrating from SQL Server 2005 or higher versions to SQL Server 2012 and higher versions.

Below is an image representing DEA’s high-level architecture. Any of those tasks are DEA’s application options and each of them will be explained in this article.

DEA: High-level Architecture

NOTE:In the time I’m writing this article, DEA still in Technical-Preview release, meaning that things can slightly change in future releases but in general do not expect big changes from what is described in this article.

How to use

After DEA has been installed (please refer to my previous article to see how to download and install DEA.) you can click on the icon in the desktop area

This will start Database Experimentation Assistant by loading the following windows as startup screen:

The tool has three main features:

  1. Capture Workload: Ability to automatically capture a production workload trace with only a few inputs.
  2. Replay Workload: Ability to replay a trace on current and new/proposed instances of SQL.
  3. Analysis Reports: Ability to generate new reports which provide insight into how workload performance changes across versions of SQL. In addition to generating a new report, there is also the ability to view reports which have been previously generated.

Capture Workload

Use this option to capture a workload from the source SQL Server instance by producing a trace file (.trc) containing a log of the events occurred on the specified server within a given period of time. This action will ask for the input of some necessary information to produce the necessary trace file:

You will need to provide the connection details to the SQL Server instance name where you want to run the trace to capture the Database activities. If you do not specify a database name the trace will capture the activities on all the databases on the SQL Server instance.

Note: Be sure that the user that is running the Capture has the necessary permissions to start a trace in the desired SQL Server instance. Also, confirm if there is any firewall protecting the related SQL Server, there are rules to permit the current machine to access the source SQL Server instance.

Give the Trace a name and specify the destination path for the trace file. Configure the max file size for rollover and duration of the trace in minutes. Default values are 200MB and 60 minutes, respectively.

Note: The file path to trace file must be on the source SQL Server machine the MSSQL instance is on. Write permissions to the specified folder from the SQL Service account it is needed in order for the trace file to be written.

Click on the Start button to initiate the new trace file capture. If there are invalid inputs they will be highlighted and you are able to provide the correct information and after that click again on the Start button to begin the capture trace process. You will be able to see a message stating that the trace has been started and running.

While waiting for the end of the capture trace process, you can monitor the progress of the capture by clicking on the bell icon at the bottom left of the navigation pane.

After successfully finished the Capture process, copy the generated trace file or files to the machine where you have the Distributed Replay Controller. You will need it for the next step.

Replay Workload

Replay trace allows you to replay the previously created trace file (.trc) on another SQL Server instance.

This action needs to be performed twice. Once for the source SQL Server instance (A) and once for the Target upgraded SQL Server instance (B).

Check the call for attention on this screen. It says that a backup of the database or databases from the source SQL Server instance needs to be restored in the target SQL Server instance. The necessary databases are the ones included in the trace captured in the previous step.

It also says that to perform this action you must manually set up machines to run distributed replay (D-Replay) traces. Read my article about how to set up a SQL Server Distributed Replay to know how to use it.

Assuming that the backup of the necessary databases have been restored and the Distribution Replay environment is set then is time to initiate a new replay.

In the Verify Setup tab, provide the SQL Server install path (actually is the root path for the Tools) and end it with the backslash (‘\’). Example for MSSQL 2014 default installation path: C:\Program Files (x86)\Microsoft SQL Server\120\

Provide the D-Replay Controller machine name and the respective Clients that you want to use by separating them with a comma (‘,’):

Press Next to continue.

On the second tab (Select Trace) provide the path for the trace captured before (you must include the trace file name in the path) and provide also the path where you want the intermediate file to be created by the preprocess task of the Distributed Replay.

NOTE: Those paths must exist in the SQL Server Distributed Replay Controller machine.

Press Next to continue.

In the third and last tab (Start Replay), provide the Trace name, Max file size and the target SQL Server instance as well the path to store the output trace files (should be a folder that exists in a SQL Server instance and a SQL Server service account needs to write permissions on it)

NOTE: At the first interaction you should provide the source SQL Server instance and then on the second interaction provide the target SQL Server instance name.

If the inputs are valid, click on the Start button so Replay process will start.

While waiting for the end of the Replay task to finish, you can monitor the progress of the task by clicking on the bell icon at the bottom left of the navigation pane.

After the Replay finishes running for the source SQL Server instance (A), repeat the steps for this task now providing the necessary information for the target SQL Server instance (B).

Analysis Reports

This option needs some components as prerequisites so when choosing this option, if you see a similar screen to the below, please refer to my previous article to see how to download and install DEA and read the respective Requested components section.

If or when you have all the requested components then you should see a similar screen as below.

Click on the + New analysis report to create a new report or to access to a previously created report, provide the SQL Server instance name in the Connect to server field a click on the Connect button. This will load the available analysis reports stored in the given SQL Server instance so you can choose which one you want to review.

By clicking on + New analysis report it will present the below screen and you need to fill the fields by providing a name for the Report and providing the SQL Server instance name where the Analysis databases will be created.

Also, provide the location and the file name for the Trace for source server (the firsttrace file name originated from the Replay task on the Source server) as well location and the file name for the Trace for target server (the firsttrace file name originated from the Replay task on the Target server).

Note: If those trace files are not stored on the local machine you will need to provide the full network path as \\servername\sharename$\tracefilename.trc.

Click the Start button to generate the Reports.

You can follow the progress on the right pane where you will see three phases:

  1. Import Trace A – Loads the trace originated from the Replay task on the Source server. In the SQL Server instance provided for the Analysis option, you will see the creation of a database called A<Report name><unique identifier>.
  2. Import Trace B – Loads the trace originated from the Replay task on the Target server. In the SQL Server instance provided for the Analysis option, you will see the creation of a database called B< Report name><unique identifier>.
  3. Prepare Analysis – Analysis the result of the previous two imports. In the SQL Server instance provided for the Analysis option, you will see the creation of a database called Analysis< Report name><unique identifier>.

Compare

When all the above phases finish successfully it will present the Analysis Report where you can compare the queries execution against the Source and Target SQL Server instances.

The Analysis Report let you drill down into each item to identify the problematic queries so they can be fixed. In my example, if I click in Error (red slice) I will get the following report:

In the query table, it is possible to see which queries returned errors and the respective error messages. In my case, the errors occurred because my target SQL Server instance does not have access to the referred SQL Server instance (MSSQLinstance3\Default).

Clicking in the Main Report button it will return to the main Analysis Report and we can click on other items. For example for Degraded queries (blue slice) where it will present a table with the queries and how much it took for running in Source and Target server and how much is the difference between them:

You can drill down even more by clicking in the query and you will have access to more information including the query plan for each server so you can look for issues and try to find a solution to solve them.

This is how you should evaluate the issues and accept them “as is” or try to solve them. You can return and run the Analysis Report as many times you want and need until all the issues are solved. IN this way DEA is a tool to attest a migration and provide information on expected issues so you can have time to solve those issues before advancing in definitive with the migration.

Other articles in this series:

References:


Experimentation Assistant (DEA)Sours: https://www.sqlshack.com/how-to-use-the-sql-server-database-experimentation-assistant-dea-tool/

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.

 

This entry was posted in Republished Content by Syndicated News. Bookmark the permalink. Sours: https://thewindowsupdate.com/2020/05/13/overview-of-using-dea-to-evaluate-azure-sql-managed-instance-or-azure-sql-database/
  1. Season 7 kuwtk
  2. Msi sdk initialization
  3. Auto chess tournaments
  4. Zulily vs amazon
  5. Bamboo floor transition

Replay a trace in Database Experimentation Assistant

titledescriptionms.customms.datems.prodms.prod_servicems.suitems.technologyms.tgt_pltfrmms.topicauthorms.authorms.reviewer

Replay a trace for SQL Server upgrades

Learn how to replay a captured trace with Database Experimentation Assistant for SQL Server upgrades.

seo-lt-2019

12/12/2019

sql

dea

sql

dea

conceptual

pochiraju

rajpo

mathoma

In Database Experimentation Assistant (DEA), you can replay a captured trace file against an upgraded test environment. For example, consider a production workload that runs on SQL Server 2008 R2. The trace file for the workload must be replayed twice: one time on an environment with the same version of SQL Server that runs in production and a second time on an environment that has the upgrade target SQL Server version, such as SQL Server 2016.

[!NOTE] Replaying a trace requires that you manually set up virtual machines or physical computers to run Distributed Replay traces. For more information, see Configure Distributed Replay for Database Experimentation Assistant.

Configure a trace replay for target 1

First, you need to perform a trace replay against target 1, which represents your existing production environment.

  1. In DEA, on the left-hand navigation bar, select the arrow icon, and then on the All Replays page, select New Replay.

    Create a replay in DEA

    [!NOTE] The Distributed Replay controller computer requires permissions to the user account that you use to remotely connect.

  2. On the New Replay page, under Replay details, enter or select the following information:

    • Replay name: Enter a name for the trace replay.

    • Source Trace Format: Specify the format (Trace or XEvents) of the source trace file.

    • Full path to source file: Specify the full path to the source trace file. If using DReplay, the file must exist on the computer serving as the DReplay Controller and the user account requires access to the file and folder.

    • Replay Tool: Specify the replay tool (DReplay or InBuilt).

    • Controller machine name: Specify the name of the computer serving as the Distributed Replay Controller.

    • Replay Trace Location: Specify the path to store trace files/XEvents associated with the trace replay.

      [!NOTE] For an Azure SQL Database or an Azure SQL Managed Instance, you need to provide the SAS URI of the Azure blob storage account.

  3. Verify that you have restored the database(s) by selecting the Yes, I have manually restored the database(s) check box.

  4. Under SQL Server connection details, enter or select the following information:

    • Server Type: Specify the type of the SQL server (SqlServer, AzureSqlDb, AzureSqlManagedInstance).
    • Server name: Specify the server name or IP address of your SQL Server.
    • Authentication Type: For the authentication type, select Windows.
    • Database name: Enter a name for a database on which to start a server-side trace. If you don't specify a database, trace is captured on all the databases on the server.
  5. Select or deselect the Encrypt connection and Trust server certificate check boxes as appropriate for your scenario.

    New Replay page

Start the trace replay on target 1

  • After you enter or select the required information, select Start to initiate the trace replay.

    If the information you entered is valid, the Distributed Replay process starts. Otherwise, the text boxes that have incorrect information are highlighted with red. Make sure that the values you entered are correct, and then select Start.

    Replay progress against target 1

    You can monitor the process as necessary. When the replay is finished running, DEA will store the results in a file at the location you specified.

    Replay against target 1 complete

Perform the trace replay against target 2

After you finish performing the trace replay against target 1, you need to do the same against your second target, which represents the intended upgrade environment.

  1. Configure a trace replay, this time using details associated with your target 2 environment.

  2. Start the trace replay on target 2.

    You can monitor the process as necessary. When the replay is finished running, DEA will store the results in a file at the location you specified.

Frequently asked questions about trace replay

Q: What security permissions do I need to start a replay capture on my target server?

  • The Windows user that's running the trace operation in the DEA application must have sysadmin rights on the target computer running SQL Server. These user rights are required to start a trace.
  • The service account under which the target computer running SQL Server is running must have write access to the specified trace file path.
  • The service account under which the Distributed Replay Client services are running must have user rights to connect to the target computer running SQL Server and to execute queries.

Q: Can I start more than one replay in the same session?

Yes, you can start multiple replays and track them to completion in the same session.

Q: Can I start more than one replay in parallel?

Yes, but not with the same set of computers selected in Controller plus Clients. The controller and clients will be busy. Set up a separate set of computers under Controller plus Client to start a parallel replay.

Q: How long does a replay typically take to finish?

A replay typically takes the same amount of time as the source trace plus the amount of time it takes to preprocess the source trace. However, if the client computers that are registered with the controller aren't sufficient to manage the load that's produced from the replay, the replay might take longer to complete. You can register up to 16 client computers with the controller.

Q: How large are the target trace files?

The target trace files can be between 5 and 15 times the size of the source trace. The file size is based on how many queries are run. For instance, query plan blobs might be large. If the statistics for these queries change often, more events are captured.

Q: Why do I need to restore databases?

SQL Server is a stateful relational database management system. To properly run an A/B test, the state of the database must be retained at all times. Otherwise, you might see errors in queries during replay that won't appear in production. To prevent these errors, we recommend that you take a backup right before the source capture. Similarly, restoring the backup on the target computer running SQL Server is required to prevent errors during replay.

Q: What does "pass %" on the replay page mean?

Pass % means that only a percentage of queries passed. You can diagnose whether the number of errors is expected. The errors might be expected, or the errors might occur because the database has lost its integrity. If the value for pass % isn't what you expect, you can stop the trace and look at the trace file in SQL Profiler to see which queries didn't succeed.

Q: How can I look at the trace events that were collected during replay?

Open a target trace file and view it in SQL Profiler. Or, if you want to make modifications to the replay capture, all the SQL Server scripts are available at C:\Program Files (x86)\Microsoft Corporation\Database Experimentation Assistant\Scripts\StartReplayCapture.sql.

Q: What trace events does DEA collect during replay?

DEA captures trace events that contain performance-related information. The capture configuration is in the StartReplayCaptureTrace.sql script. These events are typical SQL Server trace events that are listed in the sp_trace_setevent (Transact-SQL) reference documentation.

Troubleshoot trace replay

Q: Why can't I connect to the computer that's running SQL Server?

  • Confirm that the name of the computer running SQL Server is valid. To confirm, try to connect to the server by using SQL Server Management Studio (SSMS).
  • Confirm that the firewall configuration doesn't block connections to the computer running SQL Server.
  • Confirm that the user has the required user rights.
  • Confirm that the Distributed Replay client's service account has access to the computer running SQL Server.

You can get more details in the logs in %temp%\DEA. If the problem persists, contact the product team.

Q: Why can't I connect to the Distributed Replay controller?

  • Verify that the Distributed Replay controller service is running on the controller machine. To verify, use the Distributed Replay Management Tools (run the command ).
  • If the replay is started remotely:
    • Confirm that the computer running DEA can successfully ping the controller. Confirm that firewall settings allow connections per the instructions on the Configure Replay Environment page. For more information, see the article SQL Server Distributed Replay.
    • Make sure that DCOM Remote Launch and Remote Activation are allowed for the user of the Distributed Replay controller.
    • Make sure that DCOM Remote Access user rights are allowed for the user of the Distributed Replay controller.

Q: The trace file path exists on my computer. Why can't Distributed Replay controller find it?

Distributed Replay can access only local disk resources. You must copy source trace files to the Distributed Replay controller machine before you start the replay. Also, you must provide the path on the DEA New Replay page.

UNC paths aren't compatible with Distributed Replay. Distributed Replay paths must be local, absolute paths to the first source trace file, including extension.

Q: Why can't I browse for files on the New Replay page?

Because we can't browse folders on a remote computer, browsing for files isn't useful. It's more efficient to copy and paste the absolute paths.

Q: I started replay with a trace but Distributed Replay didn't replay any events. Why?

This issue might occur because the trace file doesn't have either the replayable events or the have information about how to replay events. Confirm whether the trace file path provided points to a source trace file. The source trace file is created by using the configuration provided in the StartCaptureTrace.sql script.

Q: I see "Unexpected error occurred!" when I try to preprocess my trace files by using the SQL Server 2017 Distributed Replay controller. Why?

This issue is known in the RTM version of SQL Server 2017. For more information, see Unexpected error when you use the DReplay feature to replay a captured trace in SQL Server 2017.

The issue has been addressed in the latest Cumulative Update 1 for SQL Server 2017. Download the latest version of Cumulative Update 1 for SQL Server 2017.

See also

  • To create an analysis report that helps you gain insights on proposed changes, see Create reports.
Sours: https://github.com/MicrosoftDocs/sql-docs/blob/live/docs/dea/database-experimentation-assistant-replay-trace.md
SQL Server database migration performance testing

Introducing the Database Experimentation Assistant

In the Channel 9 studios this week is Harini Gupta, a Senior Program Manager on the SQL Server SEALS team, and Dinek Kothottil, a Senior Engineering Manager on the SQL Server SEALS team.

Both Harini and Dinek are in the studio today to show off a new tool currently in preview called the Database Experimentation Assistant, a tool that enables migrating from a lower version of SQL Server to the latest version of SQL Server. However, it is much different than the Data Migration Assistant in that the Database Experimentation Assistant provides clarity into the complexity and risks of upgrading.

Harini and Dinek explain that the DEA focus is on key upgrade and migration areas such as understanding breaking changes or performance implications through looking at production workload comparisons between your current version and SQL Server 2016.

At the [03:15] mark we get into the high-level architecture and how the Data Experimentation Assistant to capture, replay, and analyzes workloads across SQL Server versions. Harini and Dinek explain how this works and what to expect during the process.

At the [05:35] mark it is DEMO TIME! Dinek spends the remaining 15 minutes of the show demoing the Database Experimentation Assistant tool, including capturing a trace, replaying the trace, and interrogating traces and tests to gain insights into the workload comparisons.

The DEA is a GREAT tool and we look forward to having Harini and Dinek back to provide updates to the tool. They are also looking for feedback on the tool, so download it and provide feedback via the links below!

You can download the latest DEA from here: https://www.microsoft.com/en-us/download/details.aspx?id=54090

Data Migration Team blog: https://blogs.msdn.microsoft.com/datamigration/2016/10/24/database-experimentation-assistant-v1-0-preview/

Questions/Feedback: [email protected]

 

 

Sours: https://channel9.msdn.com/Shows/Data-Exposed/Introducing-the-Database-Experimentation-Assistant

Assistant database experimentation

Overview of Database Experimentation Assistant

Database Experimentation Assistant (DEA) is an experimentation solution for SQL Server upgrades. DEA can help you evaluate a targeted version of SQL Server for a specific workload. Customers upgrading from earlier versions of SQL Server (starting with 2005) to more recent versions of SQL Server can use the analysis metrics that the tool provides.

DEA analysis metrics include:

  • Queries that have compatibility errors.
  • Degraded queries and query plans.
  • Other workload comparison data.

Comparison data can lead to higher confidence and help ensure a successful upgrade experience.

Get DEA

To install DEA, download the latest version of the tool. Then, run the DatabaseExperimentationAssistant.exe file.

Solution architecture for comparing workloads

The following diagram shows the solution architecture for a workload comparison. The workload comparison uses DEA and Distributed Replay during an upgrade from SQL Server 2008 to SQL Server 2016.

Workload comparison solution architecture

DEA prerequisites

Following are some prerequisites for running DEA:

  • Minimum hardware requirement: A single-core machine with 3.5 GB of RAM.
  • Ideal hardware requirement: An eight-core CPU (with 3.5 GB of RAM or more). Processors with more than eight cores don't improve DEA run times.
  • An additional 33% of performance trace size is needed to store A, B, and report analysis databases.

Configure DEA

In the prerequisite environment architecture, we recommend that you install DEA on the same machine as the Distributed Replay controller. This practice avoids cross-computer calls and simplifies configuration.

Required configuration for workload comparison using DEA

DEA connects to database servers using Windows authentication. Be sure that the user running DEA can connect to database servers (source, target, and analysis) using Windows authentication.

Capture configuration requirements

Capturing a trace requires that the user running DEA:

  • Can connect to the source database server using Windows authentication.
  • Has sysadmin rights on the source database server.

In addition, the service account running the source database server requires write access to the trace folder path.

For more information, see Frequently asked questions about trace capture.

Replay configuration requirements

Replaying a trace requires that the user running DEA:

  • Can connect to the target database server using Windows authentication.
  • Has sysadmin rights on the target database server.

In addition, replaying a trace requires that:

  • The service account running the target database servers has write access to the trace folder path.
  • The service account running Distributed Replay clients can connect to the target database server using Windows authentication.
  • TCP ports are opened for incoming requests on the Distributed Replay controller. DEA communicates with the Distributed Replay controller by using COM interfaces.

For more information, see Frequently asked questions about trace replay.

Analysis configuration requirements

Performing the analysis requires that the user running DEA:

  • Can connect to the analysis database server using Windows authentication.
  • Has sysadmin rights on the source database server.

For more information, see Frequently asked questions about analysis reports.

Set up telemetry

DEA has an internet-enabled feature that can send telemetry information to Microsoft for use in enhancing the product experience. The information that's collected is also saved on your computer for local audit, so you can always see what's collected. All DEA log files are saved in the %temp%\DEA folder.

Telemetry data can be collected on four types of events:

  • TraceEvent: Usage events for the application (for example, "triggered stop capture").
  • Exception: Exception thrown during application usage.
  • DiagnosticEvent: An event log to assist with diagnosis when problems occur (not sent to Microsoft).
  • FeedbackEvent: User feedback that's submitted through the application.

Collecting and sending telemetry data is optional. To specify which events are collected and whether collected events are sent to Microsoft, use the following steps:

  1. Go to the location in which DEA is installed (for example, C:\Program Files (x86)\Microsoft Corporation\Database Experimentation Assistant).
  2. Open and modify the .config files DEA.exe.config (for the application) and DEACmd.exe.config (for the CLI) to address your scenario as appropriate:
    • To stop collecting a type of event, set the value of event (for example, TraceEvent) to false. To start collecting the event again, set the value to true.
    • To stop saving local copies of events, set the value of TraceLoggerEnabled to false. To start saving local copies again, set the value to true.
    • To stop sending events to Microsoft, set the value of AppInsightsLoggerEnabled to false. To start sending events to Microsoft again, set the value to true.

DEA is governed by the Microsoft Privacy Statement.

See also

Sours: https://docs.microsoft.com/en-us/sql/dea/database-experimentation-assistant-overview
SQL Server Migration Assistant Demo - Resources at Pythian

Introduction to the SQL Server Database Experimentation Assistant

Overview:

Date: Monday, November 23, 2020
Time: 11am ET
Length: 1 hour, 30 minutes

Summary:

Digital marketers and other technologists have long enjoyed the benefit of tools that conduct “A/B Tests,” which automate ways to test the efficacy of two or more alternative ways to write tweets and content. For years, the only way that we could do our own A/B tests on SQL Server or Azure SQL was though an arduous and manual process of benchmarking. Wouldn’t it be great if there was an easier way?

There is! It’s time to learn about the SQL Server Database Experimentation Assistant (DEA)! In this session, you will learn how to:

  • Capture your workload database on-premise or in the cloud
  • Replay your workload on-demand as needed
  • Analyze and compare results, giving you full confidence in the best outcome for Azure migrations and implementing new on-prem hardware

 

If you want to save money, validate performance, and make sure you do not have errors while migrating to the cloud, then you need to learn about the DEA today. Once you’ve added the DEA to your toolkit, you'll look like the rockstar while the business saves money and makes the customers happy.

 

Interested in additional SentryOne webinars?  Check our webinar library.

Sours: https://info.sentryone.com/webinar-introduction-to-the-sql-server-database-experimentation-assistant

You will also like:

Create analysis reports in Database Experimentation Assistant (SQL Server)

titledescriptionms.datems.prodms.prod_servicems.suitems.technologyms.tgt_pltfrmms.topicauthorms.authorms.reviewerms.custom

Create analysis reports

Generate an analysis report in Database Experimentation Assistant (DEA). Analysis reports provide insights about the performance implications of proposed changes.

01/24/2020

sql

dea

sql

dea

conceptual

pochiraju

rajpo

mathoma

seo-lt-2019

After you replay the source trace on both of your target servers, you can generate an analysis report in Database Experimentation Assistant (DEA). Analysis reports help you gain insights about the performance implications of proposed changes.

Create an analysis report

  1. In DEA, select the list icon, specify the server name and authentication type, select or deselect the Encrypt connection and Trust server certificate check boxes as appropriate for your scenario, and then select Connect.

    Connect to server with trace files

  2. On the Analysis Reports screen, select New analysis report.

    Create new analysis report

  3. On the New analysis report screen, specify a name for the report, the storage location and path to the Target 1 and Target 2 trace files, and then select Start.

    Specify new analysis report details

    If the information you entered is valid, the analysis report is created.

    Newly created analysis report

    [!NOTE] If any of the the information you entered is invalid, the text boxes containing the incorrect information are highlighted in red. Make any necessary corrections, and then select Start again.

Frequently asked questions about analysis reports

Q: What does my analysis report tell me?

DEA uses statistical tests to analyze your workload and determine how each query ran from Target 1 to Target 2. It provides performance details for each query. Learn more about DEA in Get started.

Q: Can I create a new analysis report while another report is being generated?

No. Currently, only one report can be generated at a time to prevent conflicts. However, you can run more than one capture and replay at the same time.

Q: Can I generate an analysis report by using the command prompt?

Yes. You can generate an analysis report at the command prompt. You can then view the report in the UI. For more information, see Run at command prompt.

Troubleshoot analysis reports

Q: What security permissions do I need to generate and view an analysis report on my server?

The user who is logged in to DEA must have sysadmin rights on the analysis server. If the user is part of a group, make sure the group has sysadmin rights.

Possible errorsSolution
Unable to connect to the database. Make sure you have sysadmin rights for analyzing and viewing the reports.You might not have access or sysadmin rights to the server or database. Confirm your login rights and try again.
Unable to generate Report Name on the server Server Name. For details, check the Report Name report.You might not have the sysadmin rights needed to generate a new report. To see detailed errors, select the errored-out report and check the logs in %temp%\DEA.
The current user doesn't have the required permissions to run the operation. Make sure you have sysadmin rights for performing trace and analyzing the reports.You don't have the sysadmin rights needed to generate a new report.

Q: I can't connect to the computer running SQL Server

  • Confirm that the name of the computer running SQL Server is valid. To confirm, try to connect to the server by using SQL Server Management Studio (SSMS).
  • Confirm that your firewall configuration doesn't block connections to the computer running SQL Server.
  • Confirm that the user has the required user rights.

You can see more details in the logs in %temp%\DEA. If the problem persists, contact the product team.

Q: I see an error when I generate an analysis report

Internet access is required the first time you generate an analysis report after installing DEA. Internet access is required to download packages that are required for statistical analysis.

If an error occurs while the report is created, the progress page shows the specific step at which analysis generation failed. You can see more details in the logs in %temp%\DEA. Verify that you have a valid connection to the server with the required user rights, and then retry. If the problem persists, contact the product team.

Possible errorsSolution
RInterop hit an error on startup. Check RInterop logs and try again.DEA requires internet access to download dependent R packages. Check RInterop logs in %temp%\RInterop and DEA logs in %temp%\DEA. If RInterop was initialized incorrectly or if it initialized without the correct R packages, you might see the exception "Failed to generate new analysis report" after the InitializeRInterop step in the DEA logs.

The RInterop logs also might show an error similar to "there's no jsonlite package available." If your machine doesn't have internet access, you can manually download the required jsonlite R package:

  • Go to the %userprofile%\DEARPackages folder on the machine's file system. This folder consists of the packages used by R for DEA.

  • If the jsonlite folder is missing in the list of installed packages, you need a machine with internet access to download the release version of jsonlite_1.4.zip from https://cran.r-project.org/web/packages/jsonlite/index.html.

  • Copy the .zip file to the machine where you're running DEA. Extract the jsonlite folder and copy it to %userprofile%\DEARPackages. This step automatically installs the jsonlite package in R. The folder should be named jsonlite and the contents should be directly inside the folder, not one level below.

  • Close DEA, reopen, and try analysis again.

  • You can also use the RGUI. Go to packages > install from zip. Go to the package you downloaded earlier and install.

    If RInterop was initialized and set up correctly, you should see "Installing dependent R package jsonlite" in the RInterop logs.
    Unable to connect to the SQL Server instance, make sure the server name is correct and check for the required access for the user who is logged in.You might not have access or user rights to the server, or the server name might be incorrect.
    RInterop process timed out. Check DEA and RInterop logs, stop the RInterop process in Task Manager, and then try again.

    or

    RInterop is in faulted state. Stop the RInterop process in Task Manager, and then try again.
    Check logs in %temp%\RInterop to confirm the error. Remove the RInterop process from Task Manager before you try again. Contact the product team if the problem persists.

    Q: The report is generated, but data appears to be missing

    Check the database on the analysis computer that's running SQL Server to confirm that data exists. Check that the analysis database exists and check its tables. For example, check these tables: TblBatchesA, TblBatchesB, and TblSummaryStats.

    If data doesn't exist, the data might not have copied correctly or the database might be corrupt. If only some data is missing, the trace files created in capture or replay might not have captured your workload accurately. If the data is there, check the log files in %temp%\DEA to see if any errors were logged. Then, try again to generate the analysis report.

    More questions or feedback? Submit feedback through the DEA tool by choosing the smiley icon in the lower-left corner.

    See also

    Sours: https://github.com/MicrosoftDocs/sql-docs/blob/live/docs/dea/database-experimentation-assistant-create-report.md


    1116 1117 1118 1119 1120