What should I choose Load Balancing or Data Partitioning?
One of our client had such requirement to cater a load balancing for their application and needed to upgrade to higher versions of SQL Server & Operating systems.
The current setup at their end is a SQL Server 2000 Active/Passive Cluster and the transactions per minute were good enough with this setup, due to the recent campaign of the product they are project with massive amount of transactions, for this reason there is a need to move to high end specification of servers.incase the new cluster also doesnt stands the load what approach i should use similar to load balancing. So for a moment they were struck with whether to choose Load balancing or perform data partitioning.
Personally I feel the similar options of differentiating the load are available for a non-clustered system. If the application allows then you can split your data into two separate sets on two separate instances with the help of Distributed Partitioned Views. Further for the better aspect on high volume data you can make use of Peer-to-peer transactional replication to work with the data on each instance. Both of these options are dependant upon the data routing that will be used to have a client connect to the instance that likely has the data they want. Further to complicate you can go with Linked Servers and Distributed partitioned views, where I don't want to recommend on personal experience for a volatile application.
So in theory for above requirement, replication does have some amount of latency, and though it might only be sub-second, you do need to evaluate your real-time requirements. Whereas partitioned views can be obtained with the actual data in its original location so there's no latency there, further by taking this approach with Clustered environment would definetly help in retaining optimum performance for your application.