[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

Replicate SQL activity from production to pre-production server

Production DB: 15GB
ServerA: Windows Server 2000 Standard, SQL 2000 Standard, Quad Xeon MP 1.8ghz,  4 GB RAM
ServerB: Windows Server 2003 Enterprise 64bit, SQL 2005 Standard 64bit, Quad Xeon MP 3.16ghz,  8 GB RAM

Question:
How can I replicate the activity on ServerA to ServerB (transaction by transaction) so we can observe, over a time, the performance differences from ServerA compared to ServerB?

Additional Information:
Our production DB is running on ServerA. Given the RAM limitations of ServerA's OS/SQL software, we cannot use the entire 4 GB of ram. The DB is about 15 GB and we need to have as much in RAM as possible. This leads to significant performance problems, locking, blocking, headaches and user complaints.

Segway to ServerB.

We want to migrate to ServerB as soon as possible; however, we want to test ServerB's capabilities to determine the amount of performance improvements this will give us before actually migrating. It is configured with the same drives, RAID, just updated software and MUCH faster hardware.
There is a monitoring workstation running Idera SQL Diagnostic Manager so we can look at a side-by-side comparison.

Next Step:
Upgrading ServerA to Windows Server 2003 Enterprise, SQL 2005 Stanard (32-bit). The idea is to better leverage the hardware and create a mirror instance and another instance for development staff to use.

We are not married to this plan and open to suggestions if there is a better one which can be suggested.
0
Admin_IT
Asked:
Admin_IT
  • 9
  • 8
  • 2
  • +2
2 Solutions
 
twoboatsCommented:
Capture a transaction trace using profileer, and replay it against the pre-production box
0
 
BrughCommented:
^ yip.
0
 
Bird DogCommented:
Are you sure your problems don't have to do some what with the network. ie do you have multiple nics activated on server A to handle the high traffic.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Admin_ITAuthor Commented:
Will this allow for *real-time* activity replication? We need to be able to look at graphs (taskmgr, idera, etc) from ServerA and graphs from ServerB of said activity which occurs at the same time-intervals. I understand capturing the activity, saving it, replaying...wouldn't just go through the transactions like a queue instead of occurring at the same time-intervals as the production server?
0
 
Admin_ITAuthor Commented:
Welmore: There just isn't any free ram available, so when the backups kick off or the system has to do whatever it needs to do...oh, and this box is also hosting ASP's which our internal app runs on. This will not be the case after we migrate. ASP will be moved to a dedicated intranet server and the LAN will be upgraded to gigbit to eliminate any possible network bottlenecks. At this point there are no known network problems. This is 100mb/full and average usage is less than 1mb/sec and never has gone above 15 aside from when backups are running at night and there is no user-activity.
0
 
BrughCommented:
Of course not, the new server is going to proccess things faster(better hardware)

Do this.

Run the Trace after hours against both Boxes at the same time.

That will give ya real time performance of the Statements.

0
 
twoboatsCommented:
"Will this allow for *real-time* activity replication?"

Yes.

"We need to be able to look at graphs (taskmgr, idera, etc) from ServerA and graphs from ServerB of said activity which occurs at the same time-intervals"

Think so - though your record will begin a time, and the playback started at another time

"I understand capturing the activity, saving it, replaying...wouldn't just go through the transactions like a queue instead of occurring at the same time-intervals as the production server?"

You've captured them as they come in - essentially, they come in a queue - so you're playing them back the same kinda way

There are 3rd party tools out there that will do similar, but profiler is the only way that comes with SQL.


0
 
Admin_ITAuthor Commented:
Well, we need to replicate production activity for the entire day, 99% of the activity is between 6am-6pm.
0
 
twoboatsCommented:
"Our production DB is running on ServerA. Given the RAM limitations of ServerA's OS/SQL software, we cannot use the entire 4 GB of ram"

Why not? Set sql to dynamically manage ram, and it will use all available but 4-10mb (which it leaves free).

Also, consider using the /3Gb switch in boot.ini

32 bit processes can address  up to 4Gb. That 4Gb is split by the OS in to 2Gb application, 2Gb kernel. The /3gb changes it to a 3/1 split.
0
 
twoboatsCommented:
Leave the trace running - you can schedule it to stop.
0
 
Admin_ITAuthor Commented:
Twoboats:

Windows 2000 Stand = 2gb
Windows 2000 Ent = >2gb
0
 
twoboatsCommented:
Oh and watch out for this - just been working in a place that upgraded their server (because the original was too slow). With a faster server with more memory, the whole thing fell on it's ass, because it was no longer disk bound - and so instead, sql died under the weight of pish poor queries that were running on it (comms failures caused by trying to transfer so much data that was now available instantaneously becuase it was in RAM)
0
 
twoboatsCommented:
According to this

http://support.microsoft.com/kb/555223

Windows 2000 Stand (which I assume is Server) = 4Gb

See here for upping SQL memory

http://support.microsoft.com/kb/274750/en-us
0
 
Admin_ITAuthor Commented:
Is there a way to do this in real-time, so I can have, for example taskmgr open on each server and literally see it as it happens?
0
 
twoboatsCommented:
"Is there a way to do this in real-time, so I can have, for example taskmgr open on each server and literally see it as it happens?"

See what? The transaction replay?

If yes, then just open another trace, and watch the transactions coming in.
0
 
twoboatsCommented:
For watching memory - downlaod a trial copy od spolight on sql enterprise from quest.com - nice tool
0
 
Admin_ITAuthor Commented:
Windows Server 2000 *Standard* = /3gb switch isn't available.
SQL Server 2000 *Standard* = 2gb max

Kinda a moot point, eventually we are going to a Server 2003 Enterpise and SQL 2005 Standard as the uniform platform.
0
 
twoboatsCommented:
Fair enough, but wonder why this

http://support.microsoft.com/kb/555223

says


Here's a list of how much RAM the various Windows versions and editions support (as of Nov 2004):
 
     Windows NT 4.0: 4 GB
     Windows 2000 Professional: 4 GB
     Windows 2000 Standard Server: 4 GB <====
     Windows 2000 Advanced Server: 8GB
     Windows 2000 Datacenter Server: 32GB
     Windows XP Professional: 4 GB
     Windows Server 2003 Web Edition: 2 GB
     Windows Server 2003 Standard Edition: 4 GB
     Windows Server 2003 Enterprise Edition: 32 GB
     Windows Server 2003 Datacenter Edition: 64 GB
0
 
Admin_ITAuthor Commented:
So outside of using SQL Profiler to grab activity and then replay afterwards, there isn't a way (included or via 3rd party) which would allow us to see the same transactions run on both servers at the same time?
0
 
David ToddSenior DBACommented:
Hi,

I don't get the need to see whats happening on the two boxes immediately side-by-side.

Here is the way I'd do it:
Get Performance monitor to monitor some of the key counters and save to a csv file. (6am - 6pm) I'd get the sample at no more frequently than 1 minute.
Get Profiler to record 1 days trace (6am - 6pm)

You should now have performance figures for a day, and the SQL that created that performance.

Using the same counters on ServerB, replay the trace and observe record the performance.

Does anybody know what happens to the gaps between the queries that occur naturally? I imagine that they are ignored and the trace is replayed without those gaps.

So now you should have two CSV files which can be loaded in Excel and graphed (charted) and compared. Easy.

HTH
  David

PS See http://www.sql-server-performance.com/sql_server_performance_audit.asp for clues on which counters to use and how to interprit the results.
0
 
Admin_ITAuthor Commented:
Thank you, everyone. The prompt responses are very appreciated.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 8
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now