Load Balancing

need advice for below scenario

currently am having a active / passive cluster sql 2000 server, due to the amount of transactions am moving to new high end servers with sql 2005 cluster.

incase the new cluster also doesnt stands the load what approach i should use similar to load balancing.

with regards

alby peter

The same options are available that would be available for a non-clustered system. Perhaps you can split your data into two separate sets on two separate instances and use distributed partitioned views (DPVs) or perhaps Peer-to-peer transactional replication to work with the data on each instance. Perhaps data dependent routing (DDR) can be used to have your client connect to the instance that likely has the data you want, and linked servers and DPVs can be used to access data that's on the other instance (or split across the two instances).

Don

|||

Dear Don,

thanks for the response.

i read about the above solutions is this type of real time replication in practice for real time servers. in peer to peer transactional replication how many servers could be idle for the best result.

thanks in advance

alby

|||

Replication does have some amount of latency, and though it might only be sub-second, you do need to evaluate your real-time requirements. DPVs access the actual data in its original location so there's no latency there.

I'm not sure what you mean by 'how many servers should be idle' .. I'd think you'd distribute the data across as many instances as are appropriate to meet your retrieval requirements for the given hardware. If your idle servers are 'passive nodes' within a failover cluster, then that depends on the reliability of your hardware and how crippled you'd be if two (or more) of your instances had to run on the same node.

Don