De-risk SQL Server database migration with the Database Experimentation Assistant

What is it?

The Database Experimentation Assistant (DEA) is an A/B testing solution from Microsoft for quantifying the effects of SQL Server migration. At the time of writing, the latest version is 2.1.  The DEA tool helps to evaluate the effects on database performance, of database migration between different SQL Server instances. The analysis can be completed for a single or multiple SQL Server user databases. The use cases for this tool include;

  • Version Upgrade
  • Server Replacement/Reconfiguration
  • Migration to Azure IaaS (helping to determine just enough resource)
  • Feature Enablement, such as analysing the effects of enabling in Memory OLTP

For any of these use cases, organisations are able to use the analysis metrics built into the tool to analyse queries that have compatibility errors, have degraded queries, have changed query plans, and compare other workload data.  The tool highlights issues early in the migration cycle, making for a more successful upgrade experience.

How does it work?

The tool works by capturing a workload in the source environment, replaying the capture on the target environment, and then analysing the two data sets.  The tool can be used on any SQL Server version from SQL Server 2005 onwards.  The DEA tool does not need to be installed locally – it can be used to capture workloads from remote servers.  Ensure the user performing the analysis is connected to the source instance using Windows authentication, and that the SQL Server service account has permission to write to the folder on the source server where the workload capture trace is stored.

Before starting a capture, backup the database in the source instance, and restore this backup in the destination instance.  This ensures that you have the same database in both instances.

Start a capture of the source environment, specifying server and database name, path to store the trace file and duration.  Once the capture has been completed, you are ready to replay the capture on the destination environment.

Note:  The workload capture is replayed with the help of the Distributed Replay Controller. The Distributed Replay Controller will need to be configured in advance of the DEA tool being used.

Create a new replay, specifying your distributed replay controller, destination SQL Server instance, the path to you capture trace file and a folder to capture the replay trace.  Once the replay has completed, the DEA tool is then used to generate the analysis report by comparing the source and destination trace files.

The analysis report displays the server and build information for both the source and destination environments, and the report has a threshold setting which is used when comparing environments.  By default, it is set at 5%; any improvement in performance that is >= 5% is categorized as ‘Improved’.  This threshold allows you to evaluate the report with different performance targets.  Each query is then categorized into one of five categories;

  • Improved: statistically, the query performed better on Target 2 than on Target 1.
  • Degraded: statistically, the query performed worse on Target 2 than on Target 1.
  • Same: there is no statistical difference for the query between Target 1 and Target 2.
  • Cannot Evaluate: the sample size for the query is too small for statistical analysis; in order for A/B testing analysis DEA requires the same queries to have at least 30 executions on each target.
  • Error: the query errored out at least once on one of the targets.

The DEA Tool is used to compare query performance, with the analysis report presenting a table of queries and an analysis of how much time they took to run on the source and target databases, the difference between them, and a drill down to see which query plan was used for each.

The DEA tool can also be invoked from the command line, to allow for scheduled captures on the source environment, to sample workloads at different times of the day – for example, online day and batch processing times.

Thoughts

Whilst the tool probably wouldn’t be necessary for every migration, for SQL Server databases that run critical workloads, and whose performance needs to be preserved post migration, this is an excellent tool for identifying and quantifying the effects of a database migration on query performance.  Give DEA a try and take the risk out of your next migration.

Useful Links

Download the DEA Tool

Using DEA from the Command Line

Leave a Comment

Your email address will not be published. Required fields are marked *