Migrate SQL workloads to the cloud with confidence
Wouldn’t it be great if you could see how a workload will perform in a new environment before migrating a database to the cloud? Then you could fix any queries that have compatibility errors or move forward with the migration with confidence.
That type of crystal ball is essentially what Microsoft gives us in the form of Microsoft Database Experimentation Assistant (DEA). To learn about the benefits of other Azure database capabilities, read Future-Proof Your Data Infrastructure with Azure: A Business Case for Database Administrators.
The Database Experimentation Assistant allows you to evaluate a targeted version of SQL Server for a specific workload. By conducting A/B testing, you can see how the workload on the source server in your current environment will perform in the new environment. The Database Experimentation Assistant has the ability to capture and replay on Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Linux.
The Database Experimentation Assistant provides analysis metrics to identify potential issues when upgrading from earlier versions of SQL Server starting with 2005 to more recent versions. Database Experimentation Assistant analysis metrics include:
- Queries that have compatibility errors.
- Degraded queries and query plans.
- Other workload comparison data.
Prerequisites for running Database Experimentation Assistant
Make sure you meet the following prerequisites before running Database Experimentation Assistant:
- The minimum hardware requirement is a single-core machine with 3.5 GB of RAM.
- The ideal hardware requirement is an eight-core CPU (with 3.5 GB of RAM or more). Note: Database Experimentation Assistant run times do not improve on processors with more than eight cores.
- An additional 33 percent of performance trace size is needed to store A, B, and report analysis databases.
- The user running Database Experimentation Assistant must be able to connect to the database servers using Windows authentication.
- The user must also have sysadmin rights on both the source and target database servers.
Running Database Experimentation Assistant
Database Experimentation Assistant guides you through an A/B test of your source and proposed servers in three stages:
1. Capture a workload trace
The first stage is to capture a workload trace on the source server, which is usually the production server. Trace files capture the entire query workload on that server, including timestamps. Before you start, back up the databases.
Note: A query must execute at least 15 times during the capture period for the Database Experimentation Assistant to determine whether performance will improve or degrade in the new environment.
2. Replay a workload trace
Next, replay the trace file on two target servers. Target one will mimic your source server, and Target two will mimic the proposed target environment. The hardware configurations of the targets should be as similar as possible to enable SQL Server to analyze the effect your proposed changes have on performance. To replay a workload trace, your computers must be set up to run distributed replay traces.
3. Analyze the replayed workload traces
Finally, generate an analysis report using the replay traces, and review the report for insights about potential performance implications in the new environment.
Learn more
Migrating SQL Server to the cloud can be a major undertaking. The more assurance you have that the migration will be a success, the better. As an experimentation solution for SQL Server upgrades, Database Experimentation Assistant can help you evaluate a targeted version of SQL Server for a specific workload. You can then rest assured that the workload will perform as expected and can proceed confidently with your migration.
To learn more about moving your databases to the cloud, download Future-Proof Your Data Infrastructure with Azure: A Business Case for Database Administrators.
