The info I've found on the web so far indicate that SQL Server 2008 does not support true load-balancing.
Is this true? I'm not able to find decent documentation on MS' site, so any links would be appreciated.
Also, the differences between Active/Active and Active/Passive.
I presume that you can't have more than one SQL Server instance connect to the same database, right? Cos that would indicate true load-balancing.
So Active/Active is just when you have two separate SQL Server instances accessing two COMPLETELY SEPARATE databases? If one fails, then it just shares the load onto the one remaining instance? This configuration is only actually any use if we have indeed two COMPLETELY SEPARATE databases then?
So in my case, where I have only one db, I have to go for Active/Passive as the High Availability option?
These are fairly basic questions, but I've not been able to find fairly basic answers!
Thanks Duncan
What your basically describing is called SQL Server Clustering. It refers to a group of two or more servers (nodes) that act together and are seen a single virtual server to clients.
SQL Server Clusters can be configured as Active/Active or Active/Passive in a two Server scenario. Either both nodes of the Microsoft SQL Server Cluster are dedicated to running at least a single SQL instance (Active-Active) or at least one of these nodes is reserved as a standby to accept failover of a failed SQL Server instance (Active-Passive).
Here are some articles you could read:
Some article describing other options (at application level though):
Clustering is a high availability solution, not a scalability solution. The so called 'Active/Active' is really a reuse of the standby nodes for deploying another, completely separate, instance.
For read-write Transact-SQL requests there is no load balancing in any form. For rad-only Transact-SQL (reporting) there is the option of the 'Scalable Shared Database'.
The only technology that support load-balancing 'out-of-the-box' in SQL 2005 and SQL 2008 is Service Broker, via the deployment of load-balancing routes. But I doubt this is of any interest for you.
Simply adding more nodes to a SQL Cluster does not add processing capacity, it just increases the availability as you have more nodes to remain online. Processing of read/write queries is limited to one node, there's no concept of round-robin load balancing.
Here is a whitepaper from Microsoft titled SQL Server 2008 Performance and Scale http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-performance-scale.aspx
This whitepaper discusses the differences between Scaling Up and Scaling Out of SQL Server. As pointed out by Remus there is a concept of a Scalable Shared Database for read only databases (think large data warehouses).
You could use peer to peer replication for distributed processing if that suits your needs. It introduce other complications of course.
You might want to read about MySpace and how they handled high loads with SQL Server. It's a series of tricks, but there is no load balancing.
http://highscalability.com/myspace-architecture
First: SQL Server doesn't support load-balancing out if the box, so let windows os do the work. You can install 2 SQL Server instances and configure "peer to peer replication" so that you have consistent data on both servers. You really work then with 2 (!) different databases. But then there might occur conflicts during DML statements on the same record from 2 users (one by chance is on server 1 and the other on server 2..). It is Your task to avoid such conflicts by Your planning and front-end programming. SQL server cannot take over this task, unfortunately. On the other side "Fail-over cluster" is possible when a SHARED STORE is used for all participating nodes. So the system then is working on ONE distinct Database an there is no load balancing (!) but only fail-over. Reflect that is not the same! If one node fails the another one takes over its tasks immediately. So far I hope this can help You. Thomas