Does the DEA A/B testing (using trace replay) require the target database migrated/restored and does it update the target database data?

Question

I am exploring this link: https://learn.microsoft.com/en-us/sql/sql-server/migrate/guides/sql-server-to-sql-server-upgrade-guide?view=sql-server-ver15#optional-ab-testing

The goal is to migrate sql server 2017 to sql server 2019, as well as the new resources on the target.

To perform the A/B testing I have downloaded the DEA tool and captured a trace of 8 hours that covers the OLTP workload.

Now I am on step 3.c (Run a trace replay->New replay), but my SQL server is on a different domain and network so I cannot connect to it from here.

To be able to do the A/B test:

1. Do I need to also install the DEA tool on a computer belonging to the new domain and copy the captured trace file, and then follow the steps?
2. Do I need to backup the source database prior to capturing the trace and then restore that to target computer? or does the replay work without having any target database? This information isn't mentioned in the steps.
3. Will replaying the trace update data in the target database?

• SqlWorldWide: Some of the answers to your questions will depend on what you are trying to measure. New hardware or newer version of SQL Server? Can you mention that in your question?
• variable: The goal is to migrate sql server 2017 to sql server 2019. Updated in question.

Answer 1

Ref: Overview of Database Experimentation Assistant

The advantage of using Database Experimentation Assistant (DEA) is that you can do everything from one place. You do not have to create Redline Markup Language (RML) files for replay. The analysis is done automatically for you. Since you are not doing a hardware refresh and only looking for comparing performance comparison between two major versions, I suggest setting up another SQL2019 instance in the same domain, restoring that database, and using that as a target.

Do I need to also install the DEA tool on a computer belonging to the new domain and copy the captured trace file, and then follow the steps?

No, you do not need to install DEA on the target.

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.

Do I need to backup the source database prior to capturing the trace and then restore that to target computer? or does the replay work without having any target database? This information isn't mentioned in the steps.

Yes, you need to backup the source if that is what you are going to use for the destination setup. Often time people will take a backup before capturing a trace and also deploying a new release, so you can revert the changes once the test is completed.

Will replaying the trace update data in the target database?

Yes, if your trace has calls that will modify/insert or delete data.

• variable: My target sql sever is on a different network not accessible from source machine. The goal is to A/B test the sql server 2019 as well as the resources on the target
• SqlWorldWide: I know that, and that is why I have a suggestion for you in the answer. Otherwise, you have to do it old style. Manually replay the trace in both places, collect data and compare using SSRS, Excel, PowerBI, etc.
• variable: Why not replay only on target machine?
• SqlWorldWide: As I said you can replay it manually. In that case, you will need to collect the data during replay (another trace) and compare it with the source run manually, to know the performance of each call.
• variable: Is there a way to capture 1 trace for all databases on the instance?