[Q]SQL Server 2000 Cluster Load Balancing

[Brian Rhee]

Hey guys,

Lately we have come across a problem where our application is undergoing some extreme load against the SQL 2000 server database we have setup, where the server is hitting 100% CPU utilization each time. Currently the box is a 2 processor box.

Here is the question I have. I have seen under most SQL Server clusters that an active/passive setup is implemented. Where the passive server just exists as a failover mechanism. What I am looking for is some information on how to setup active/active setup where each server receives processes to handle.

Has anyone created a setup like this? Are there any standard benchmarking tools that can be used to see how this configuration increases performance? Is this setup more favorable than going to a 4 processor server as oppossed to our current 2 processor server?

BTW: We have noticed that after a web application where the user sits idle for a while the SQL Server application loses the connection with the application user...Is this the SQL Timeout causing the connection to disconnect?

Thanx for any response...

Bri

[Mike Prince]

Hi Brian,
I haven't tried to setup an active/active but I have seen a few people fall to the wayside trying. They weren't DBAs by any stretch, however. What I do know about active/active is that you have to employ some time of replication (probably transactional) and your schema will need to be designed to properly support replication (particularly in terms of identity columns). If you factor in the amount of time all of this might take to get working properly and the additional maintenance and calculate what it will cost your company in terms of resources and wages, your company might be happier paying for the 4 way machines. Good luck and if you get it setup, please post back telling us how it went.

Also, you might want to look at partitioning the servers (federated) to spread the load. For example, sql box 1 would contain all userids from 1-200000 and sql box 2 supports userids 200001-400000.

And not to overlook the obvious, make sure your db is tuned properly. It is amazing what indexes can do both good and bad...

Thanks,
Mike


------------
Brian Rhee at 11/8/01 2:25:19 PM

Hey guys,

Lately we have come across a problem where our application is undergoing some extreme load against the SQL 2000 server database we have setup, where the server is hitting 100% CPU utilization each time. Currently the box is a 2 processor box.

Here is the question I have. I have seen under most SQL Server clusters that an active/passive setup is implemented. Where the passive server just exists as a failover mechanism. What I am looking for is some information on how to setup active/active setup where each server receives processes to handle.

Has anyone created a setup like this? Are there any standard benchmarking tools that can be used to see how this configuration increases performance? Is this setup more favorable than going to a 4 processor server as oppossed to our current 2 processor server?

BTW: We have noticed that after a web application where the user sits idle for a while the SQL Server application loses the connection with the application user...Is this the SQL Timeout causing the connection to disconnect?

Thanx for any response...

Bri