Capturing, Measuring, and Replaying a Workload Using SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 326

To replay a workload on the target server, the following requirements must be met:

All logins (both SQL and Windows) and database users contained in the workload must exist
already on the target and in the same database as the source server where the workload data was
collected. Otherwise, you need to create them in the target SQL server instance and
database.

❑

❑ All logins and users in the target must have the same permissions they had in the source.

❑ All login passwords must be the same as those of the user who executes the replay.

❑ The database IDs on the target ideally should be the same as those on the source. However, if

they are not the same, matching can be performed based on DatabaseName if it is present in the
trace.

The default database for each login contained in the trace must be set to the respective target
database of the login. To set the default database of the login, use the sp_defaultdb system stored
procedure.

❑

If you are new to performing the SQL Server administration tasks listed above, you should capture a
small workload (with one or two logins in the workload data), practice the Profiler replay, and become
comfortable with each requirement listed above.

For a large set of trace files with many different logins, it might be time-consuming to go through
all the logins in the workload files and recreate them in the target server. To transfer logins and
passwords between different versions of SQL Server, refer to the Microsoft article, ''How to Trans-
fer Logins and Passwords Between Instances of SQL Server,'' at http://support.microsoft.com/kb/
246133 .

To replay all database query events correctly, the database must be reset to its original state. This means
that if a workload contains multiple databases, you will need to create a backup for each database in the
workload.

Capturing aWorkload
The steps for defining and capturing a workload are the same as the ones used in creating a SQL trace. Be
sure your workload definitions have met the minimum replay requirements. Save the workload output to
a file or a set of rollover files. Define a time to stop the trace. To schedule a job to capture the workload,
it is advisable to save these definitions to a T-SQL script file. With minimum modifications for output
file specification and the stop time, you can use SQL agent job to facilitate the workload data collection.
Using server-side traces is considered to be the best practice. Chapter 5 contains a description of how to
create server-side traces.

Figure 10-4 is an example of using SQL Profiler to create a workload using the following settings:

Trace provide type: Microsoft SQL Server 2005

❑

Use the template: TSQL_Replay

❑

Save to File: x \ temp \ myWorkload004.trc with max file size 200 MB, file rollover has been
enabled.

❑

Enable trace stop time: 6/17/07, 1:54:58PM

❑