Load balancing.

Hi!
I have two sql servers. One raid rack in the middle for them both.
I installe two cluster groups, with two sql installation.
Now I can put a db on one server and it can fail over to the other one and
the other way around. Fine.
But how can I set it up so the same db is located one both cluster groups
(on the raid and shared by both cluster groups) and use some kind of load
balacing system to harvest the power of both servers for one db.
The only ways I have found so far is DTC and replikation. DTC no slack in
time for having the db exactly the same on both servers, but needs knowledge
and rewrite of code to use DTC transactions. Replikation, slack in time in
update between the cluster groups.
Is there another way to do this? We want to use the power of both servers in
a normal situation, have the fail over if one goes down, only one copy of
the db. No slack in time between the both servers.
regards
cj
SQL 2000 Clustering is not capable of load balancing a database.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
http://msmvps.com/clustering/archive.../20/58233.aspx NYC Clustering
class
"Carl-Johan Wik" <cjwik@.hotmail.com> wrote in message
news:%23X%2334HRpFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I have two sql servers. One raid rack in the middle for them both.
> I installe two cluster groups, with two sql installation.
> Now I can put a db on one server and it can fail over to the other one and
> the other way around. Fine.
> But how can I set it up so the same db is located one both cluster groups
> (on the raid and shared by both cluster groups) and use some kind of load
> balacing system to harvest the power of both servers for one db.
> The only ways I have found so far is DTC and replikation. DTC no slack in
> time for having the db exactly the same on both servers, but needs
> knowledge and rewrite of code to use DTC transactions. Replikation, slack
> in time in update between the cluster groups.
> Is there another way to do this? We want to use the power of both servers
> in a normal situation, have the fail over if one goes down, only one copy
> of the db. No slack in time between the both servers.
> regards
> cj
>

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.

Load balancing with SQl server 2005 cluster in active/active configuration

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.

Hi geetu...

MSCS (Microsoft Cluster Services) is not a load-balancing product, it is simply a high-availability solution, period...load balancing does not come in to play at all with clustering a Sql database...the term Active/Active seems to imply that this would be the case, which is why we typically try to refer to them as Multi-instance clusters now instead of the Active/Active label. Active/Active in the MSCS world basically means that you have 2 independent Sql Server instances running on 2 cluster nodes - these instances are independent of each other in all respects, obviously unless you link them in some manner with custom business logic, replication, etc. Think of them for all intents and purposes as 2 seperate instances running on seperate servers at all times (as that's what they really are)...the only difference being that in case of a physical node failure (or service failure on a node), the instance will be moved to and hosted on a second physical server.

I'd be curious to know what research you came across that implied load-balancing as a feature with MSCS so we can try and get it corrected, or possibly clarify what position the author was taking.

To support load-balancing, or scaling out in a Sql Server environment you have a couple of different options, depending on your edition, environment, version of Sql, etc. Take a look at the following articles for a start:

http://msdn2.microsoft.com/en-us/library/aa479364.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/scddrtng.mspx

HTH

|||

Hi Geetu,

Basically, Active/Active Cluster is two Active/Passive Clusters.

As mentioned by Chad, they are completely independent of each other.

HTH

Jag

|||

I hope you got more information from Chad's reply here and as suggested you might need another solution, cluster will not provide the load balancing.

These 2 links shoudl give you information in this regard:

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog04.mspx

http://www.microsoft.com/technet/community/chats/trans/sql/sql0513.mspx

http://www.sql-server-performance.com/dk_massive_scalability.asp

|||

It's pretty clear what geetu is trying to get at. Here is your answer geetu:

1. Instead of having the 2nd node idle until something happens to node1, you can split the databases between the 2 nodes.

2. While this is not the definition of classic Load Balancing, it does help balance the load between the two nodes while they are both up.

We have several customers who are configured like this and it works well.

The downside? The “size” of each of the 2 servers in terms of CPU, memory etc must be enough to handle all the databases that are normally served from the 2 nodes in case one node fails.

While this is the same requirement in the active/passive configuration, there is a danger that over time you will keeps adding load to both nodes independently and at time of failure, the failover node will die as well because of a sudden overwhelming load.

Sometimes when smarty MSFT employees respond, they should be more respectful to the person who is posting a question, instead of being arrogant.

sql

Load balancing with SQl server 2005 cluster in active/active configuration

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.

Hi geetu...

MSCS (Microsoft Cluster Services) is not a load-balancing product, it is simply a high-availability solution, period...load balancing does not come in to play at all with clustering a Sql database...the term Active/Active seems to imply that this would be the case, which is why we typically try to refer to them as Multi-instance clusters now instead of the Active/Active label. Active/Active in the MSCS world basically means that you have 2 independent Sql Server instances running on 2 cluster nodes - these instances are independent of each other in all respects, obviously unless you link them in some manner with custom business logic, replication, etc. Think of them for all intents and purposes as 2 seperate instances running on seperate servers at all times (as that's what they really are)...the only difference being that in case of a physical node failure (or service failure on a node), the instance will be moved to and hosted on a second physical server.

I'd be curious to know what research you came across that implied load-balancing as a feature with MSCS so we can try and get it corrected, or possibly clarify what position the author was taking.

To support load-balancing, or scaling out in a Sql Server environment you have a couple of different options, depending on your edition, environment, version of Sql, etc. Take a look at the following articles for a start:

http://msdn2.microsoft.com/en-us/library/aa479364.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/scddrtng.mspx

HTH

|||

Hi Geetu,

Basically, Active/Active Cluster is two Active/Passive Clusters.

As mentioned by Chad, they are completely independent of each other.

HTH

Jag

|||

I hope you got more information from Chad's reply here and as suggested you might need another solution, cluster will not provide the load balancing.

These 2 links shoudl give you information in this regard:

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog04.mspx

http://www.microsoft.com/technet/community/chats/trans/sql/sql0513.mspx

http://www.sql-server-performance.com/dk_massive_scalability.asp

|||

It's pretty clear what geetu is trying to get at. Here is your answer geetu:

1. Instead of having the 2nd node idle until something happens to node1, you can split the databases between the 2 nodes.

2. While this is not the definition of classic Load Balancing, it does help balance the load between the two nodes while they are both up.

We have several customers who are configured like this and it works well.

The downside? The “size” of each of the 2 servers in terms of CPU, memory etc must be enough to handle all the databases that are normally served from the 2 nodes in case one node fails.

While this is the same requirement in the active/passive configuration, there is a danger that over time you will keeps adding load to both nodes independently and at time of failure, the failover node will die as well because of a sudden overwhelming load.

Sometimes when smarty MSFT employees respond, they should be more respectful to the person who is posting a question, instead of being arrogant.

Load balancing in SQL Server

We are doing a feasilibility study of using SQL Server cluster for load balancing.
Our clients will be running on Linux/Apache, connecting to SQL Server using JDBC.
Some c++ client tools will connect using ODBC.
Based on my limited knowledge of SQLServer clustering technology, it seems that
it does not have load balancing cluster with more than one node. In other
words, it will not allow connections to the same database from more than one
node. Only one node can own up the database, which to means that it will do
all the work pertaining to that database.
I also understand that if our goal is to balance the load amongst various nodes,
then Federated server is a better option. Can anyone explain this. Did I understand
it correctly.
I tried to find information about this in books online, but couldn't find it.
Were else can I get the information.
TIA.
SQL Server clustering is not for load balancing. It's for high
availability. Using federated servers is for load balancing. Check out
distributed partitioned views in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:c49pju$2g12jc$1@.ID-75254.news.uni-berlin.de...
We are doing a feasilibility study of using SQL Server cluster for load
balancing.
Our clients will be running on Linux/Apache, connecting to SQL Server using
JDBC.
Some c++ client tools will connect using ODBC.
Based on my limited knowledge of SQLServer clustering technology, it seems
that
it does not have load balancing cluster with more than one node. In other
words, it will not allow connections to the same database from more than one
node. Only one node can own up the database, which to means that it will do
all the work pertaining to that database.
I also understand that if our goal is to balance the load amongst various
nodes,
then Federated server is a better option. Can anyone explain this. Did I
understand
it correctly.
I tried to find information about this in books online, but couldn't find
it.
Were else can I get the information.
TIA.

load balancing cluster

need the concpet of load balancing cluster ?

can any one help me out with any link...?"Nit" <chat2martin@.gmail.comwrote in message
news:160cafd7-3fd1-4a2b-9eab-38d66213654f@.b40g2000prf.googlegroups.com...

Quote:

Originally Posted by

>
>
need the concpet of load balancing cluster ?
>
can any one help me out with any link...?

For SQL Server not relaly because that exact model doesn't exist. In MS SQL
Server, a cluster is a DR/failover mechanism. I.e. the DB is live on only
ONE node of the cluster at a time.

A common solution though is to use things like replication or even
log-shipping to create read-only copies of the DB and point some of your
load there.

Quote:

Originally Posted by

>
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Load Balancing

need advice for below scenario

currently am having a active / passive cluster sql 2000 server, due to the amount of transactions am moving to new high end servers with sql 2005 cluster.

incase the new cluster also doesnt stands the load what approach i should use similar to load balancing.

with regards

alby peter

The same options are available that would be available for a non-clustered system. Perhaps you can split your data into two separate sets on two separate instances and use distributed partitioned views (DPVs) or perhaps Peer-to-peer transactional replication to work with the data on each instance. Perhaps data dependent routing (DDR) can be used to have your client connect to the instance that likely has the data you want, and linked servers and DPVs can be used to access data that's on the other instance (or split across the two instances).

Don

|||

Dear Don,

thanks for the response.

i read about the above solutions is this type of real time replication in practice for real time servers. in peer to peer transactional replication how many servers could be idle for the best result.

thanks in advance

alby

|||

Replication does have some amount of latency, and though it might only be sub-second, you do need to evaluate your real-time requirements. DPVs access the actual data in its original location so there's no latency there.

I'm not sure what you mean by 'how many servers should be idle' .. I'd think you'd distribute the data across as many instances as are appropriate to meet your retrieval requirements for the given hardware. If your idle servers are 'passive nodes' within a failover cluster, then that depends on the reliability of your hardware and how crippled you'd be if two (or more) of your instances had to run on the same node.

Don

Load Balancing

need advice for below scenario

currently am having a active / passive cluster sql 2000 server, due to the amount of transactions am moving to new high end servers with sql 2005 cluster.

incase the new cluster also doesnt stands the load what approach i should use similar to load balancing.

with regards

alby peter

The same options are available that would be available for a non-clustered system. Perhaps you can split your data into two separate sets on two separate instances and use distributed partitioned views (DPVs) or perhaps Peer-to-peer transactional replication to work with the data on each instance. Perhaps data dependent routing (DDR) can be used to have your client connect to the instance that likely has the data you want, and linked servers and DPVs can be used to access data that's on the other instance (or split across the two instances).

Don

|||

Dear Don,

thanks for the response.

i read about the above solutions is this type of real time replication in practice for real time servers. in peer to peer transactional replication how many servers could be idle for the best result.

thanks in advance

alby

|||

Replication does have some amount of latency, and though it might only be sub-second, you do need to evaluate your real-time requirements. DPVs access the actual data in its original location so there's no latency there.

I'm not sure what you mean by 'how many servers should be idle' .. I'd think you'd distribute the data across as many instances as are appropriate to meet your retrieval requirements for the given hardware. If your idle servers are 'passive nodes' within a failover cluster, then that depends on the reliability of your hardware and how crippled you'd be if two (or more) of your instances had to run on the same node.

Don

sql

load balanced scale out deployment issues

Hello,
I have a scale out deployment of Reporting Services consisting of 2 Report
Servers in a load balanced cluster using an F5 BigIP setup in RoundRobin
mode, and the report catalog db sits on on a separate sql server. We use a
vIP with a DNS name that hits the F5. The F5, in turn, is setup to "choose"
one of the 2 Report Servers using the machine name address. Note that this
is for an intranet.
We are not currently using HTTPS (SSL) in our environment, but eventually
we'll turn it on, so ignore that piece of the puzzle for this scenario.
When configuring the scale-out deployment, we followed the instructions on
the Books Online article "Configuring a Report Server Scale-out Deployment."
The last piece of this document instructs us to modify the
RSWebApplication.config file for all the servers in the scale out deployment,
setting the <ReportServerUrl> element to use the virtual server name (vIP
DNS, we assumed), instead of the machine name. However, this element did not
contain a value originally.
The next step was to modify the RSReportServer.config file, to set the
<UrlRoot> element with the virtual server name (again, vIP dns). UrlRoot's
original value as the url <servername>/reportserver.
Once this change was made, I reset IIS on both servers, and hit each
localhost's Report manager to fire up the applications. However, I received
the following error "ReportServerUrl Configuration element is not a valid
configuration..." on both Report Manager web apps.
Note that before we made the above change to ReportServerUrl and UrlRoot,
both servers worked fine. So, I went back in and removed the value from
ReportServerUrl, but left the UrlRoot with the vIP name. Reset IIS, and the
error went away!
I am a little confused as to what these two settings are used for, since the
original value of ReportServerUrl was blank (UrlRoot actually had the machine
name address).
Could someone explain the cause of this error, and suggest a best practice
for this scenario. I would like to use the most efficient configuration
between the Report Servers and the F5 load balancer, and what other changes
would we be looking at once we turn on SSL?
Thanks!
--
Regards,
Thiago Silvabtw, this is using SSRS 2005 (Enterprise Edition, of course, since it's a web
farm).
--
Regards,
"HC" wrote:
> Hello,
> I have a scale out deployment of Reporting Services consisting of 2 Report
> Servers in a load balanced cluster using an F5 BigIP setup in RoundRobin
> mode, and the report catalog db sits on on a separate sql server. We use a
> vIP with a DNS name that hits the F5. The F5, in turn, is setup to "choose"
> one of the 2 Report Servers using the machine name address. Note that this
> is for an intranet.
> We are not currently using HTTPS (SSL) in our environment, but eventually
> we'll turn it on, so ignore that piece of the puzzle for this scenario.
> When configuring the scale-out deployment, we followed the instructions on
> the Books Online article "Configuring a Report Server Scale-out Deployment."
> The last piece of this document instructs us to modify the
> RSWebApplication.config file for all the servers in the scale out deployment,
> setting the <ReportServerUrl> element to use the virtual server name (vIP
> DNS, we assumed), instead of the machine name. However, this element did not
> contain a value originally.
> The next step was to modify the RSReportServer.config file, to set the
> <UrlRoot> element with the virtual server name (again, vIP dns). UrlRoot's
> original value as the url <servername>/reportserver.
> Once this change was made, I reset IIS on both servers, and hit each
> localhost's Report manager to fire up the applications. However, I received
> the following error "ReportServerUrl Configuration element is not a valid
> configuration..." on both Report Manager web apps.
> Note that before we made the above change to ReportServerUrl and UrlRoot,
> both servers worked fine. So, I went back in and removed the value from
> ReportServerUrl, but left the UrlRoot with the vIP name. Reset IIS, and the
> error went away!
> I am a little confused as to what these two settings are used for, since the
> original value of ReportServerUrl was blank (UrlRoot actually had the machine
> name address).
> Could someone explain the cause of this error, and suggest a best practice
> for this scenario. I would like to use the most efficient configuration
> between the Report Servers and the F5 load balancer, and what other changes
> would we be looking at once we turn on SSL?
> Thanks!
> --
> Regards,
> Thiago Silva