SQL Server 2008 Load-Balancing

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

Solution 1:

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:

• An Introduction to SQL Server 2005 Clustering Basics
• SQLServerpedia.com - Replication Overview - Faul Tolerance
• Load balancing with SQl server 2005 cluster in active/active configuration

Some article describing other options (at application level though):

• MSDN - Scaling Out SQL Server 2005
• Scaling Out SQL Server with Data Dependent Routing

Solution 2:

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.

Solution 3:

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.