Using SQL Server Profiler - Page 3

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:

SELECT dbid
FROM master.dbo.sysdatabases
WHERE name = '<database name here>'

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:

1. change the 3 users passwords to "blah":
   

   EXEC sp_password '' , 'blah' , 'email'
   EXEC sp_password '' , 'blah' , 'report'
   EXEC sp_password '' , 'blah' , 'marker'

2. 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'

3. register SQL Server in Profiler using the admin user. If you have not registered this server in Profiler before then do the following:

1. select "Register SQL Server" from the Tools menu
   
2. enter the name of the Server
   
3. click on the "Use SQL Server authentication" radio button.
   
4. Enter "admin" in the "Login Name" field.
   
5. Enter "blah" in the Password field.
   
6. Click on the Ok button
   

Figure 3.     Entering login name and password for SQL Server