SQL Server Performance Forum – Threads Archive
How to increase SQL performance?
Please forgive my question but I am confused by information I have seen. Several articles state that clustering cannot be used to increase SQL performance. If that is the case, why would you ever need a single cluster with more than two nodes? If you had three nodes, would this be an Active/Active/Passive configuration? Would the two Active servers be capable of querying and writing to the same databases? If you run into a situation where SQL traffic is exceeding hardware performance, is there no choice but to replace the server with faster hardware since clustering does not improve performance? Is there any way with SQL to load-balance across systems that share the same database?You do not cluster for performance you cluster for redundancy. If any node in a three node cluster fails one of the other nodes can take over the role of that server. With more than two nodes you usually have more than one active instance in the cluster. For example Active/Active/Passive as you mention. The instances are seperate and do not share databases in any way. To improve performance you would have to code your application for load balancing. For example have one database that is read/write on a cluster and then replicate read only versions to X number of other servers. Then you either hardware load balance those servers or implement your own round robin load blancing or something in the application. You could also design your application so that you divide different data among multiple databases and put them on different servers. Or use merge replication (between stand alone or clustered instances) but this often requires high maintenance and you need to design for it when building the database as well. There is no fast and easy way to load balance SQL Servers as there is with for example web servers.
]]>