Load balancing with Sql Server 2005 in Active/Active mode

This is in regards to the MS SQL

Server 2005 Cluster testing.

I have the set-up in

Active/Passive configuration.

We decided upon moving to the Active/Active

configuration, did some research on the internet for the additional features

which we get with this configuration and what could be the benefits of this over

the Active/Passive configuration. We have a query regarding the Active/Active

configuration. Please find below the details on the

same:

The Current Setup in Active/Passive

configuration has the following details:

1)

There are two nodes “node 1” and

“node 2” setup on Virtual Server 2005.

2)

A cluster has been set up using

“node 1” and “node 2”.

3)

A SQL server default instance is

installed on “node 1”. While SQL server 2005 was installed, it created a

resource SQL server IP.

To connect to this SQL server

instance, our application uses SQL server IP created in step 3. (Please note

that this is the only IP through which we are able to connect to SQL cluster).

The fail-over features are working fine in this configuration. If “node 1” goes

down then the SQL server instance runs in “node 2”. This is taken care of by

cluster and is transparent to our application.

However, there is no provision for

load balancing as only one node is active at a

time.

After some research, we came across

Active/Active configuration which is supposed to support load

balancing.

We understand that in this

configuration, Step 1and 2 are similar to the Active/Passive configuration. The

only difference is in step 3 where an instance of SQL server is installed on

each node, thus providing two active nodes at a time. The failover works just

like in Active/Passive configuration.

As per the above information, the

Active/Active configuration seems to be similar to two SQL server instances

running independently.There will be two seperate databases and on failure of one instance other instance wont be able to cater to the requests designed for first instance, Thus providing no extra benefits from

cluster.

We require the information on how to

take benefits of the load balancing features in this configuration.

SQL Server 2005 doesn't support load-balancing in the way that you are describing, the main problem being that only one instance of SQL Server can access a database's data and log files at a time.

Have a look at these links for more info as to how load balancing can be achieved:

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1127807,00.html

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1133488,00.html

Chris

|||As mentioned, MS SQL Server does not provide a way to do "active/active" cluster.

What you have installed would be a "high availablity" cluster, ie if one goes down, the other picks up.

If you need load balancing, you install 2 unique SQL installations and run "transactional replication" between them to make the databases the same. Then use a 3rd load balancing device to balance between them, problaby using "sticky sessions".

If you need both, high availabilty and load balancing, you install TWO 2 node clusters (4 servers + load balancing device) with transactional replication between cluster 1 and cluster 2.

I always recommend getting bigger/faster/better hardware and using 1 cluster of 2 servers.