This article is part of the serie on the SQL Server Upgrade Assistant:

Upgrade Assistant Tool for SQL Server 20121.0 Introduction

   1.1 Upgrade Assistant Process 

   1.2 Upgrade Assistant Results

   1.3 Required Knowledge and Skills

2.0 System Requirements

3.0 Create a Test Environment 

   3.1 Single Computer Test Environment 

   3.2 Multiple Computer Test Environment 

   3.3 Install SQL Server Upgrade Assistant 

4.0 Capture a Test Workload

5.0 SQL Server 2005/2008 Baseline System

6.0 Run Upgrade Advisor

7.0 Replay Baseline Trace on SQL Server 2005/2008

8.0 Set Up Playback Test System

9.0 Replay Trace on SQL Server 2012

10.0 Compare Trace Files

11.0 Advanced Topics

12.0 Fixing Differences

13.0 Troubleshooting 

14.0 Support  


This chapter contains:


4.0 Capturing a Test Workload

Upgrade Assistant uses workload testing to identify potential application compatibility issues.  A test workload is created by restoring a copy of your production database to your test environment, starting a SQL Server Profiler trace, and then exercising functionality in the application either manually or by running existing test scripts.  The resulting trace will include Transact-SQL commands generated by your application source code.  The combination of your production database backups and the resulting trace files are called a “playback”.  The Capture Playback workflow in Upgrade Assistant automates the workload trace capture process to be used for replay later. The details of how this is accomplished are covered in the following sections.  Please read the following section on how to capture a high quality playback before starting with the capture process.

4.1 Important information on capturing a playback

As previously stated, application compatibility testing should be conducted in a test environment.  If you lack the hardware or capacity to create a test environment and you must capture the playback from a production environment, please ensure that you understand the potential impact described in this section. Some users will experience little to no impact while others may encounter significant issues.

 The capture playback process utilizes SQL Server’s Profiler tool to record the workload for replay later. Depending on the system’s capacity, workload characteristics, storage sub-system performance and current activity level, the server where you are running the capture may experience mild to moderate performance impact. In some very rare cases the performance impact may be significant.

 Part of the playback capturing process includes backing up all system and user databases on the SQL Server instances you run the playback capture on. This is a full database backup. As such, if run on a production system, this process has effectively altered regular database backup sequence. This may have significant impact on your system’s high availability and disaster recovery strategy. Please make sure you understand the impact that an unscheduled full database backup may have in your environment.  The WITH COPY_ONLY option in the backup statement will prevent problems with breaks in the backup chain. 

4.2 Guidelines for capturing a high quality playback

Playback testing is most valuable when the playback captures a wide diversity of application behavior. For this reason we recommend capturing your playback against a test system by executing automated and/or manual test procedures that exercise most of the functionality of your application. This type of playback is known as a “code coverage” playback because it contains a wide diversity of Transact-SQL interaction that occurs between your application and databases. A high quality code coverage playback should contain 90% or more of the different types of Transact-SQL commands generated by your application. 100% is ideal.

While production playbacks are interesting, they usually do not provide good code coverage since all of the features of your application may not be in use during playback capture period, for example, period ending processes that run once a week or less often. If you run the playback capture process for extended periods to obtain good code coverage, the size of the playback capture may become unmanageable. Finally, as indicated earlier, the playback capture process may interfere with your production server operations.

If you do not have a test environment and decide to create your playback using a production system, capturing your playback overnight or on weekends may result in the least impact on performance of your production system, but may not provide good code coverage.

Use the following guidelines to judge the quality of your playback:

The playback provides good code coverage and was created against a test system using automated and/or manual testing procedures that exercise most of the features of your application.

  • Database objects should be unencrypted, if possible.
  • The majority of the commands captured do not depend on external resources such as other SQL Server instances, extended stored procedures, distributed partitioned views, linked servers, distributed transactions or replication.
  • The majority of the commands captured do not depend upon bulk copy operations performed during trace capture. Bulk copy operations cannot be replayed in the test environment.
  • The trace capture began as soon as database backups were complete ensuring that there are no gaps in activity missed during playback capture that may cause failures during replay.

4.3 Capture playback process

This tool will capture a Playback by performing the following steps:

  • Query information about the specified server's configuration
  • Backup all databases in the specified instance
  • Begin a trace capture of your server's workload
  • Await user intervention to manually stop trace capture
  • Report on the capture process

4.4 Before starting the capture playback process

Please review the following before starting the capture playback process: 

  • The default maximum size for the trace file is 1 gigabyte (GB). Large workload captures can take a significant amount of time to replay and are generally not required for good code coverage testing.
  • If you are capturing multiple playbacks, the folder must be empty each time the capture starts.
  • If possible, run the Upgrade Assistant Playback Capture on the same physical computer where your SQL Server 2005/2008 instance is located.
  • When specifying a path in Upgrade Assistant Playback Capture, local paths will be mapped to the machine on which the tool is being run. For example, specifying a path of C:\Playback while running the capture Playback tool on a machine named “Client” would map to: \\Client\c$\Playback. If your path is on another machine, ensure that you give the full network path.
  • Please ensure that your SQL Server service account has write access to the directory specified. This may require running your server as a domain user, or running the capture tool on the same machine as your server instance.
  • All database backups, as well as the trace file, will be stored in a single location. Ensure that the path you specify has enough disk space for full database backups and the Profiler trace file.
  • Stop SQL Agent, replication and other services that generate connections if possible.  Stop all management tools and applications that have active connections to your SQL Server instance and restart SQL Server before running Upgrade Assistant Playback Capture.  Then, start your application.  If you are capturing against a production instance you can skip this step.  This will ensure that the activity in your playback was generated by your application, not other management related services running in the background.
  • If you accidentally clicked the “Stop” button and are not done with the capture process, just click on “No” in the confirmation dialog.
  • The login ID used for the Playback Capture Wizard tool must be a member of the Sysadmin role or have ALTER TRACE permissions in order to capture a Playback. Use a SQL Server Login or Built-in Administrator if possible, to reduce the occurrence of a non-critical error (when attempting to set the user name) during replay.

4.5 Capturing a playback

There are 4-steps to complete the playback capture process. The UAFS Playback Capture provides detailed information about each step and the specific parameters and/or options for each step. Please refer to the help documentation if you are uncertain about the data to provide at each step.

4.5.1: Launch Upgrade Assistant AppCompatLabWizard (appcompatlabwizard.exe)

Figure 1 . Reporting Settings


Enter the name of a SQL Server 2012 server (and instance if applicable using the server\instance format) and login information here. Check the “Generate Merge Result Trace” check box if you want to generate a merged result trace. If the “Generate Merge Result Trace” check box is checked, input a valid folder path for “Result Trace Folder Path”, or you can simply click the “Browse…” button to choose the path.

Please note that Reporting Settings does not mean a Report Server installation, but simply the SQL Server 2012 instance you would like to use for report collation.

Figure 2 . Capture playback 


Enter the name of your server (and instance if applicable using the server\instance format) and login information here. Both Windows and SQL Server logins are supported but using a SQL Server login will avoid some non-critical error messages during playback. Click on the “Test Connection” button to verify that you can connect to the server/instance you just specified.

Enter the location where your playback files (database backups, environment configuration and trace files) will be stored. Make sure you have enough disk space to store all the files that will be placed there. Use local or SAN storage instead of a network share to avoid potential performance problems and/or trace capture corruption due to network errors.

If you select the “Select Databases to Backup” checkbox, a list of the databases on the SQL Server instance will be displayed and you can select which databases you want to backup before the playback trace is captured.  All the databases that will be used in the tests you run to capture the playback trace must be selected.  These backups are used to ensure that when you playback the trace, the state of the databases will be the same as when it was captured.  If you do not select the “Select Databases to Backup” checkbox, all the databases on the instance will be backed up before the playback trace is captured.  If you are not  sure which databases are required it would be wise to use the default and back them all up.  You must ensure that the selected database(s) do not change between the time they are backed up and the time the trace starts so that the playback runs against databases that are the same as the databases where the trace was captured.

Click on the “Capture Playback” button when you have entered all the correct values and are ready to begin the playback capture process.

4.5.2 Capture Playback

Figure 3 . Playback Capture In Progress


After you click on the “Capture Playback” button in Step 1 above, the tool will proceed with some basic checks and obtain system configuration information. It will then backup all databases in the instance or the databases you selected. This may take several minutes to hours depending on the size of the databases, number of databases and hardware performance.   The Results:  Window will display each database as the backup starts so you can monitor progress.

When the backups are completed, a dialog box will appear as shown in the image above. This indicates that the trace process has been started. If you are capturing from a production server, no further action is necessary at this time. If you are capturing from a test/staging environment, you may now run your workload generators (scripts, test routines, users, load tools, etc…).

You do not need to monitor this window during the capture process as no progress information will be provided. However, please check on this regularly to ensure there are no issues on the machine or infrastructure that has interfered with the captured process (e.g. ran out of disk space).

When you have run the Capture Playback process long enough on the production system to capture a good representative workload,  or you have finished running the relevant load generation activities (if capturing from a test/staging server), click on the “Ok” button in the dialog box. You will then be prompted with another dialog box to confirm that you are ready to stop the trace. Click on “Yes” to stop the capture process or “No” if you want to continue running the capture process.

4.5.3 Completed Capture Playback

Figure 4 . Capture Complete


When you are done with the capture process, the tool will post messages of the tasks successfully completed (as shown above). This concludes the playback capture process using the UAFS Playback Capture.

You should now review the files in your playback folder to ensure the relevant database backups, trace and log files are present. Also verify that your trace file size is consistent with what you would expect from the workload captured. If your trace file is very small (less than 128K), you may not have a good workload for testing or a problem may have occurred during the capture process. You may review its contents by opening the file with SQL Profiler to ensure the TSQL statements from your workload were captured. To verify the backup files, you may test by performing a RESTORE VERIFYONLY.

You now have a test workload ready for replay.  The next step is to set up the system to use as the baseline or "before" playback target:  SQL Server 2005/2008 Baseline System