Transactional Replication Across and Active/Active Cluster

[UpstateNYAdmin]

I have an SQL2000 Ent Ed.(os Win2K3) Active/active cluster that it was suggested that I use transactional replication to alleviate slow responses to reads when updates are occuring. Personally, I would rather just improve the code than resort to maintaining the replication. Is it even possible to have this sort of set up? Basically, the updates would be done on the Publisher and all reads would be done from the subcriber, distributor would be either on a 3rd server or on the subscriber. Its seems the overhead of of all the extra services would negate the benefits plus, in the event one node failed and everything was running on one server, I would have a ton of performance issues. Does anybody think this is worth the trouble?

Thanks,
Pat

 

[ptheriault]

I would not even consider setting up replication to fix performance issues. Fix the code first! Identify the bottle necks and poor performing queries.
You would also have to consider the latency. What if a user updates their data then wants to view it. Is that update going to be replicated fast enough? This solution would create more potential problems then it could solve.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

Replication is not a fix for performance problems.

You absolitly need to fix the code, check indexes, disk IO, memory, etc before you look to setting up replication to fix a performance problem.

Where is the performance issue comming from? What do the execution plans show? What does performance monitor show.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[UpstateNYAdmin]

Thanks, for the feedback guys! Basically, there are reads and writes going on concurrently and both the read and write procs use cursors to some extent to generate the data dynamicly based on whatever the app is passing. I ahve have found that one of the UDF is used by both and seems to be part of the problem, by using lots of cpu and i/o due to the several table variables used to weed thru the data that it needs to return.

 

[mrdenny]

Try rewriting the cursors as rowset operations. This will greatly increase performance.

Also try adding the nolock hint to all the the SELECT statements to reduce locking and blocking.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com