Replay the trace against
the Development server
After capturing the trace file we can now replay that trace file in our Development
environment. There are several "gotchas" that you will want to keep in mind
when replaying a trace on another server. Most of these are mentioned in bol
(books online) but they bear repeating here:
Gotcha 1: Replaying
the trace will only work if the users used SQL Server authentication to login
to the Production server. Any users logging in with NT authentication will
not be able to login.
Gotcha 2: Users and logins that exist in the trace capture must also
exist on the Development server. They must have adequate permissions against
the objects referenced in the trace file.
Gotcha 3: The dbid of the database in Development must match the dbid
of the database in Production. You can determine the dbid of your database
with the following query:
Gotcha 4: The passwords used by SQL Server logins must match the password
of the SQL user replaying the trace. The easiest way to deal with this is
to set all of the user passwords involved in the trace to the same password.
Then register the server in Profiler as a user with that same password. For
example, there were 3 users executing queries in the trace I captured: emailer,
reporter, and marker. I set each of these users passwords to "blah". I then
create another user "admin", assign him to the sysadmin role, and set his
password to "blah". Then I register the Development SQL Server in Profiler
using the "admin" user. So my steps were as follows:
WHERE name = '<database name here>'
- change the 3 users passwords to "blah":
EXEC sp_password '' , 'blah' , 'email'
EXEC sp_password '' , 'blah' , 'report'
EXEC sp_password '' , 'blah' , 'marker'
- create another user just for the purposes of running Profiler with the same
password as the users above:
EXEC sp_addlogin 'admin' , 'blah' , 'master'
EXEC sp_addsrvrolemember 'admin' , 'sysadmin'
- register SQL Server in Profiler using the admin user. If you have not
registered this server in Profiler before then do the following:
- select "Register SQL Server" from the Tools menu
- enter the name of the Server
- click on the "Use SQL Server authentication" radio button.
- Enter "admin" in the "Login Name" field.
- Enter "blah" in the Password field.
- Click on the Ok button
Figure 3. Entering login name and password for SQL Server