Hi,
Would it be safe to say that SQL server does not allow me to do load
balancing of a single database? We have an application that uses a single
database. Currently I have a setup of 2 decent servers (HP ML570). They are
setup with Windows 2003 server failover clustering. I wanted to find out
whether I can change the cluster to an active/active cluster and use the
same database for load balancing using both my servers a bit more
efficiently.
Thank you.
Clustering is a hardware fail over solution only. It does not in any way
shape or form do load balancing.
Andrew J. Kelly SQL MVP
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:u%23NrpfDjEHA.1712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Would it be safe to say that SQL server does not allow me to do load
> balancing of a single database? We have an application that uses a single
> database. Currently I have a setup of 2 decent servers (HP ML570). They
are
> setup with Windows 2003 server failover clustering. I wanted to find out
> whether I can change the cluster to an active/active cluster and use the
> same database for load balancing using both my servers a bit more
> efficiently.
> Thank you.
>
|||Thank you Andrew for your reply.
Is there any other way to do SQL load balancing for a single database?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ReExkDjEHA.636@.TK2MSFTNGP12.phx.gbl...
> Clustering is a hardware fail over solution only. It does not in any way
> shape or form do load balancing.
> --
> Andrew J. Kelly SQL MVP
>
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:u%23NrpfDjEHA.1712@.TK2MSFTNGP09.phx.gbl...
> are
>
|||Not easily unless the database is read only. There are some 3rd party tools
that claim to help some like http://www.xprime.com/ but I can't vouch for
them as of yet. I haven't actually seen one in action yet. I know it
sounds obvious enough but what is the reason for wanting to do load sharing?
Scaling up is a lot easier and usually cheaper than scaling out.
Andrew J. Kelly SQL MVP
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:e5aO3pDjEHA.3664@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Thank you Andrew for your reply.
> Is there any other way to do SQL load balancing for a single database?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23ReExkDjEHA.636@.TK2MSFTNGP12.phx.gbl...
way[vbcol=seagreen]
single[vbcol=seagreen]
out[vbcol=seagreen]
the
>
|||Just to add to Andrew's note. The key in performance tuning is to first
determine what component is underperforming, then improve that component.
If you are memory bound, add more ram, disk too slow, move to from Raid 5
to Raid 10. TempDB I/O bound, move it to a separate drive, etc.
Some of the SQL Server performance tuning books can help scale-up the SQL
Server hardware subsystems long before you'll probably need to "scale out".
Chris Skorlinski
Microsoft SQL Server Support
Please reply directly to the thread with any updates.
This posting is provided "as is" with no warranties and confers no rights.
Showing posts with label clustering. Show all posts
Showing posts with label clustering. Show all posts
Wednesday, March 28, 2012
Load balancing and clustering
When a web application becomes overloaded with traffic, one can offload it
by load balancing and clustering the front end web servers. What happens
when the back-end MSSQL database becomes overloaded? Does MYSQL offer load
balancing and clustering?"Shabam" <info@.pcconnect.net> wrote in message
news:U4ydnZzBnMTWcO_dRVn-tA@.adelphia.com...
> When a web application becomes overloaded with traffic, one can offload it
> by load balancing and clustering the front end web servers. What happens
> when the back-end MSSQL database becomes overloaded? Does MYSQL offer
load
> balancing and clustering?
by load balancing and clustering the front end web servers. What happens
when the back-end MSSQL database becomes overloaded? Does MYSQL offer load
balancing and clustering?"Shabam" <info@.pcconnect.net> wrote in message
news:U4ydnZzBnMTWcO_dRVn-tA@.adelphia.com...
> When a web application becomes overloaded with traffic, one can offload it
> by load balancing and clustering the front end web servers. What happens
> when the back-end MSSQL database becomes overloaded? Does MYSQL offer
load
> balancing and clustering?
I'm not sure if you're talking about MSSQL (Microsoft SQL Server) or MySQL.
In the case of MSSQL, clustering is supported for failover only, not for
load-balancing. If you need load-balancing, then it can be implemented in a
middle-tier.
Simon|||> I'm not sure if you're talking about MSSQL (Microsoft SQL Server) or
MySQL.
> In the case of MSSQL, clustering is supported for failover only, not for
> load-balancing. If you need load-balancing, then it can be implemented in
a
> middle-tier.
What do you mean middle tier?|||"Shabam" <info@.pcconnect.net> wrote in message news:<9oKdnTRxs9oRou7dRVn-uQ@.adelphia.com>...
> > I'm not sure if you're talking about MSSQL (Microsoft SQL Server) or
> MySQL.
> > In the case of MSSQL, clustering is supported for failover only, not for
> > load-balancing. If you need load-balancing, then it can be implemented in
> a
> > middle-tier.
> What do you mean middle tier?
I mean an application server, such as WebSphere, WebLogic, Microsoft
Transaction Server etc., which sits between the web server and the
database server. Clients connect to the application server, which
manages all the database connections, so you can use the application
server to spread database access across several physical databases.
Simonsql
Load balancing
I'm wondering if its reasonable to use clustering combined with table
partitiions to distribute a heavy load? (OLTP app)
My basic thought is that I want to distribute product data across many hard
drives (and I/O busses) such that the database server knows which drives
contain the data it needs (and therefore doesn't acces the drives it doesn't)
as well as using the cluster to distribute load to multiple servers.
I've seen a reference to a "federated" database, but am not sure what that
means.
absolutely. Clustering won't do much for you performance wise though.
I think you probably want to look at Distributed Partitioned Views for what
you want to do though. Clustering is used for high availability. IE Should
one node fail, your clients will be disconnected and when they reconnect
they will be accessing the same SQL Server but running on a different node.
With Distributed Partitioned Views their query will be directed at a view on
one server, but the data will be accessed on a different server.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:282512C2-0242-40A6-93BD-010E155398EA@.microsoft.com...
> I'm wondering if its reasonable to use clustering combined with table
> partitiions to distribute a heavy load? (OLTP app)
> My basic thought is that I want to distribute product data across many
> hard
> drives (and I/O busses) such that the database server knows which drives
> contain the data it needs (and therefore doesn't acces the drives it
> doesn't)
> as well as using the cluster to distribute load to multiple servers.
> I've seen a reference to a "federated" database, but am not sure what that
> means.
>
|||> With Distributed Partitioned Views their query will be directed at a view on
> one server, but the data will be accessed on a different server.
Thank you.
There is a rather criptic statement in the 2000 BOL that concerns me though.
"The most important goal is to minimize distributed processing in such a
system. You must be able to collocate related data on the same member server,
and then route each SQL statement to a member server that contains most, if
not all, of the data required to process the statement. "
Does this mean that I should put all (or as much) of the total data to be
retervied on one server?
For example, in a title/item lookup all item records related to a title
should be on the same server/partition? All other things being equal (which
they are probably not), I would prefer to put them on seperate servers and
have seperate NIC's for the DB's to talk to each other. Still, either would
work.
|||I believe it means that if your distributed partition view has three member
servers and you issue a query on servera which has a where clause pointing
to the data stored on serverb, and a join on another table, that second
table should be on serverb as well.
So if you partitioned on date. Servera might contain last years data,
serverb, the prior year, and serverc this years data. A search on this years
data might originate on servera, and will be directed to serverc by the
distributed partition view.
In your case perhaps titles beginning with a-h might be on servera, i-q
might be on serverb, r to z on serverc. Items related to titles in servera
should be on servera, and so on for serverb and c. There could be some
duplication of item data across all three servers.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:E798A0E7-6392-4DD2-9D53-18C43E4C1DE0@.microsoft.com...
> Thank you.
> There is a rather criptic statement in the 2000 BOL that concerns me
> though.
> "The most important goal is to minimize distributed processing in such a
> system. You must be able to collocate related data on the same member
> server,
> and then route each SQL statement to a member server that contains most,
> if
> not all, of the data required to process the statement. "
> Does this mean that I should put all (or as much) of the total data to be
> retervied on one server?
> For example, in a title/item lookup all item records related to a title
> should be on the same server/partition? All other things being equal
> (which
> they are probably not), I would prefer to put them on seperate servers and
> have seperate NIC's for the DB's to talk to each other. Still, either
> would
> work.
|||My understanding is that this is related to the selectivity of your partition
function. In other words, if your queries always have a where clause that
states "WHERE [YEAR] = 2007" and each partition has data for a separate year,
then things will go well. However if peaple can do "WHERE [YEAR] IN (1999,
2000, 2007) and Forename = 'Harry'" then there are problems. The data has to
be unioned from the 3 partitions and the processing won't be on a separate
server. Performance in this type of scenario can become worse than not having
the partition at all...
HTH,
Paul Ibison
|||No, my understanding is the this query will be sent to all partitions and
then unioned. This will be efficient.
However a query on a DPV partitioned on data which is not aligned with any
of these partitions won't be as efficient. For example this query
select * from MyDPV where forname='Harry'
will result in index scans of all partitions and then bookmark lookups to
return all the matching rows. It is important to note that processing is
distributed amoung the member servers. If the DPV was all on one server (ie
each partition in a different table or database) all the processing will be
on the same server, and the cpu hit of this server doing that bookmark
lookup will be intense.
If it was distributed over different servers each server will crunch their
part of the query and the cpu hit will be there. The cpu hit on the server
where you issue the DPV query will be minimal - its only aggregating the
results.
DPV are great if the majority of the queries align themselves with your
partitions. If they don't they aren't so great as it involves table/index
scans.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:EA4B50B2-A9C4-45EE-AE79-9B76088027A2@.microsoft.com...
> My understanding is that this is related to the selectivity of your
> partition
> function. In other words, if your queries always have a where clause that
> states "WHERE [YEAR] = 2007" and each partition has data for a separate
> year,
> then things will go well. However if peaple can do "WHERE [YEAR] IN (1999,
> 2000, 2007) and Forename = 'Harry'" then there are problems. The data has
> to
> be unioned from the 3 partitions and the processing won't be on a separate
> server. Performance in this type of scenario can become worse than not
> having
> the partition at all...
> HTH,
> Paul Ibison
|||What my example was trying to (incorrectly!) show was that if the query
doesn't relate to the partition function (startup filter), the query will get
sent to each server and processed there, then the results unioned. There will
be cases where this is worse than processing on one box, which avoids the
network round trips and the final unioning of datasets.
Cheers,
Paul Ibison
sql
partitiions to distribute a heavy load? (OLTP app)
My basic thought is that I want to distribute product data across many hard
drives (and I/O busses) such that the database server knows which drives
contain the data it needs (and therefore doesn't acces the drives it doesn't)
as well as using the cluster to distribute load to multiple servers.
I've seen a reference to a "federated" database, but am not sure what that
means.
absolutely. Clustering won't do much for you performance wise though.
I think you probably want to look at Distributed Partitioned Views for what
you want to do though. Clustering is used for high availability. IE Should
one node fail, your clients will be disconnected and when they reconnect
they will be accessing the same SQL Server but running on a different node.
With Distributed Partitioned Views their query will be directed at a view on
one server, but the data will be accessed on a different server.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:282512C2-0242-40A6-93BD-010E155398EA@.microsoft.com...
> I'm wondering if its reasonable to use clustering combined with table
> partitiions to distribute a heavy load? (OLTP app)
> My basic thought is that I want to distribute product data across many
> hard
> drives (and I/O busses) such that the database server knows which drives
> contain the data it needs (and therefore doesn't acces the drives it
> doesn't)
> as well as using the cluster to distribute load to multiple servers.
> I've seen a reference to a "federated" database, but am not sure what that
> means.
>
|||> With Distributed Partitioned Views their query will be directed at a view on
> one server, but the data will be accessed on a different server.
Thank you.
There is a rather criptic statement in the 2000 BOL that concerns me though.
"The most important goal is to minimize distributed processing in such a
system. You must be able to collocate related data on the same member server,
and then route each SQL statement to a member server that contains most, if
not all, of the data required to process the statement. "
Does this mean that I should put all (or as much) of the total data to be
retervied on one server?
For example, in a title/item lookup all item records related to a title
should be on the same server/partition? All other things being equal (which
they are probably not), I would prefer to put them on seperate servers and
have seperate NIC's for the DB's to talk to each other. Still, either would
work.
|||I believe it means that if your distributed partition view has three member
servers and you issue a query on servera which has a where clause pointing
to the data stored on serverb, and a join on another table, that second
table should be on serverb as well.
So if you partitioned on date. Servera might contain last years data,
serverb, the prior year, and serverc this years data. A search on this years
data might originate on servera, and will be directed to serverc by the
distributed partition view.
In your case perhaps titles beginning with a-h might be on servera, i-q
might be on serverb, r to z on serverc. Items related to titles in servera
should be on servera, and so on for serverb and c. There could be some
duplication of item data across all three servers.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:E798A0E7-6392-4DD2-9D53-18C43E4C1DE0@.microsoft.com...
> Thank you.
> There is a rather criptic statement in the 2000 BOL that concerns me
> though.
> "The most important goal is to minimize distributed processing in such a
> system. You must be able to collocate related data on the same member
> server,
> and then route each SQL statement to a member server that contains most,
> if
> not all, of the data required to process the statement. "
> Does this mean that I should put all (or as much) of the total data to be
> retervied on one server?
> For example, in a title/item lookup all item records related to a title
> should be on the same server/partition? All other things being equal
> (which
> they are probably not), I would prefer to put them on seperate servers and
> have seperate NIC's for the DB's to talk to each other. Still, either
> would
> work.
|||My understanding is that this is related to the selectivity of your partition
function. In other words, if your queries always have a where clause that
states "WHERE [YEAR] = 2007" and each partition has data for a separate year,
then things will go well. However if peaple can do "WHERE [YEAR] IN (1999,
2000, 2007) and Forename = 'Harry'" then there are problems. The data has to
be unioned from the 3 partitions and the processing won't be on a separate
server. Performance in this type of scenario can become worse than not having
the partition at all...
HTH,
Paul Ibison
|||No, my understanding is the this query will be sent to all partitions and
then unioned. This will be efficient.
However a query on a DPV partitioned on data which is not aligned with any
of these partitions won't be as efficient. For example this query
select * from MyDPV where forname='Harry'
will result in index scans of all partitions and then bookmark lookups to
return all the matching rows. It is important to note that processing is
distributed amoung the member servers. If the DPV was all on one server (ie
each partition in a different table or database) all the processing will be
on the same server, and the cpu hit of this server doing that bookmark
lookup will be intense.
If it was distributed over different servers each server will crunch their
part of the query and the cpu hit will be there. The cpu hit on the server
where you issue the DPV query will be minimal - its only aggregating the
results.
DPV are great if the majority of the queries align themselves with your
partitions. If they don't they aren't so great as it involves table/index
scans.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:EA4B50B2-A9C4-45EE-AE79-9B76088027A2@.microsoft.com...
> My understanding is that this is related to the selectivity of your
> partition
> function. In other words, if your queries always have a where clause that
> states "WHERE [YEAR] = 2007" and each partition has data for a separate
> year,
> then things will go well. However if peaple can do "WHERE [YEAR] IN (1999,
> 2000, 2007) and Forename = 'Harry'" then there are problems. The data has
> to
> be unioned from the 3 partitions and the processing won't be on a separate
> server. Performance in this type of scenario can become worse than not
> having
> the partition at all...
> HTH,
> Paul Ibison
|||What my example was trying to (incorrectly!) show was that if the query
doesn't relate to the partition function (startup filter), the query will get
sent to each server and processed there, then the results unioned. There will
be cases where this is worse than processing on one box, which avoids the
network round trips and the final unioning of datasets.
Cheers,
Paul Ibison
sql
Load Balance Clustering on 2005?
My company is planning on using a datacenter for our customers. These are manufacturing plants from which we collect batch and trend data (currently stored in SQL Server)
If we move to a centralized database model we may need Load Balancing. I read that SQL 2005 will accommodate this so I called MS Support and no one could confirm this.
My dilemma is that I need to report upon what our options are for the datacenter. So I need to know if load balancing will be an offering of SQL 2005.
SQL Server 2005 provides failover clustering, not load-balancing clustering.Network Load-balancing in Windows works well with multiple read-only servers each containing the same data, but this is not provided directly by SQL Server.
SQL Server 2005 has several features that provide increased scalability (table partitioning, SQL Server Service Broker, XML indexes, query notifications, CLR stored procedures, and others), enhancing the scale-up abilities and further reducing the need for such load-balancing capabilities, but no, load-balancing is not provided.
Don|||Thanks for the clarification.|||SQL不支持负载均衡的设置,仅支持故障转移.如果想实现的话,需要其他的软硬件和其他的配置可以实现一定程度上的负载均衡.如果想提高I/O性能可以试一下SAN存储网络,如果实现CUP等的性能可能需要实现分区或者镜像或者复制等技术,使数据在其他服务器上保持一致的副本,然后在WIN上实现负载均衡.或者还有其他的办法.目前我知道的就|||Don Vilen - professor at CSU Chico circa 1982 - that Don Vilen?
|||
Hi Dave -
Yep, that one 
Don (DonV)
Load Balance Clustering on 2005?
My company is planning on using a datacenter for our customers. These are manufacturing plants from which we collect batch and trend data (currently stored in SQL Server)
If we move to a centralized database model we may need Load Balancing. I read that SQL 2005 will accommodate this so I called MS Support and no one could confirm this.
My dilemma is that I need to report upon what our options are for the datacenter. So I need to know if load balancing will be an offering of SQL 2005.
SQL Server 2005 provides failover clustering, not load-balancing clustering.Network Load-balancing in Windows works well with multiple read-only servers each containing the same data, but this is not provided directly by SQL Server.
SQL Server 2005 has several features that provide increased scalability (table partitioning, SQL Server Service Broker, XML indexes, query notifications, CLR stored procedures, and others), enhancing the scale-up abilities and further reducing the need for such load-balancing capabilities, but no, load-balancing is not provided.
Don|||Thanks for the clarification.|||SQL不支持负载均衡的设置,仅支持故障转移.如果想实现的话,需要其他的软硬件和其他的配置可以实现一定程度上的负载均衡.如果想提高I/O性能可以试一下SAN存储网络,如果实现CUP等的性能可能需要实现分区或者镜像或者复制等技术,使数据在其他服务器上保持一致的副本,然后在WIN上实现负载均衡.或者还有其他的办法.目前我知道的就|||Don Vilen - professor at CSU Chico circa 1982 - that Don Vilen?|||
Hi Dave -
Yep, that one
Don (DonV)
Load Balance Clustering on 2005?
My company is planning on using a datacenter for our customers. These are manufacturing plants from which we collect batch and trend data (currently stored in SQL Server)
If we move to a centralized database model we may need Load Balancing. I read that SQL 2005 will accommodate this so I called MS Support and no one could confirm this.
My dilemma is that I need to report upon what our options are for the datacenter. So I need to know if load balancing will be an offering of SQL 2005.
SQL Server 2005 provides failover clustering, not load-balancing clustering.Network Load-balancing in Windows works well with multiple read-only servers each containing the same data, but this is not provided directly by SQL Server.
SQL Server 2005 has several features that provide increased scalability (table partitioning, SQL Server Service Broker, XML indexes, query notifications, CLR stored procedures, and others), enhancing the scale-up abilities and further reducing the need for such load-balancing capabilities, but no, load-balancing is not provided.
Don|||Thanks for the clarification.|||SQL不支持负载均衡的设置,仅支持故障转移.如果想实现的话,需要其他的软硬件和其他的配置可以实现一定程度上的负载均衡.如果想提高I/O性能可以试一下SAN存储网络,如果实现CUP等的性能可能需要实现分区或者镜像或者复制等技术,使数据在其他服务器上保持一致的副本,然后在WIN上实现负载均衡.或者还有其他的办法.目前我知道的就|||Don Vilen - professor at CSU Chico circa 1982 - that Don Vilen?
|||
Hi Dave -
Yep, that one
Don (DonV)
Subscribe to:
Posts (Atom)