Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 9, 2000

Using SQL Server Profiler - Page 3

By Andrew Wiegand

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:

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

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM