List availables tables: nlb

Showing posts with label nlb. Show all posts

Wednesday, March 28, 2012

Load balancing for read-only databases

We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?
See inline
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)

> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.

> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...

> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)

> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)

Load balancing for read-only databases

We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)

Load balancing for read-only databases

We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be abl
e
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)

> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.

> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...

> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)

> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)sql

Load balancing

Hi,
Is it possible to use windows NLB for Sql 2000 enterprise edition servers.
We are using 300gb database in one server and the performance is very slow.
Any how we can't get load balancing in SQL clustering, If it is possible
with NLB please let me know how to configure it.
We are planning to put the database in SAN ,so both the servers will use
central database instead of individual one.
Thanks
Murali
"Space is not available in TempDB"
<SpaceisnotavailableinTempDB@.discussions.microsoft .com> wrote in message
news:FB6ABB56-D877-47E4-8C44-B79BD593CA20@.microsoft.com...
> Hi,
> Is it possible to use windows NLB for Sql 2000 enterprise edition servers.
No.

> We are using 300gb database in one server and the performance is very
slow.
> Any how we can't get load balancing in SQL clustering, If it is possible
> with NLB please let me know how to configure it.
> We are planning to put the database in SAN ,so both the servers will use
> central database instead of individual one.
This wont' work. Each instance of SQL server needs access to its own copy
of the database files.
What you can try is log-shipping or replication from one server to another.

> Thanks
> Murali
|||Hi,
Thanks for your response .
log shipping may not suitable to our requirement.
we are using on CRM application , from the application server through odbc
it will contact the sql server.
We can't specify multiple servers in odbc connection for sqlserver even if
we use log shipping.
Please give some other resolution which will distribute the load between 2
servers.
Thanks
Murali
"Greg D. Moore (Strider)" wrote:

> "Space is not available in TempDB"
> <SpaceisnotavailableinTempDB@.discussions.microsoft .com> wrote in message
> news:FB6ABB56-D877-47E4-8C44-B79BD593CA20@.microsoft.com...
> No.
> slow.
> This wont' work. Each instance of SQL server needs access to its own copy
> of the database files.
> What you can try is log-shipping or replication from one server to another.
>
>
>
|||Buy a bigger server. NLB will split the query load between several servers,
but there is no way to coalesce the changes on the back end. Each server
instance has a unique, independant set of databases that are only accessable
through that instance. In a clustered environment, eacn instance can move
between hosts, but can only live on one host at a time. SQL scales up, not
out.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Space is not available in TempDB"
<SpaceisnotavailableinTempDB@.discussions.microsoft .com> wrote in message
news:90501899-D509-4B25-AB9F-8021E0F90350@.microsoft.com...[vbcol=seagreen]
> Hi,
> Thanks for your response .
> log shipping may not suitable to our requirement.
> we are using on CRM application , from the application server through odbc
> it will contact the sql server.
> We can't specify multiple servers in odbc connection for sqlserver even if
> we use log shipping.
> Please give some other resolution which will distribute the load between 2
> servers.
> Thanks
> Murali
> "Greg D. Moore (Strider)" wrote:
servers.[vbcol=seagreen]
possible[vbcol=seagreen]
use[vbcol=seagreen]
copy[vbcol=seagreen]
another.[vbcol=seagreen]

Load Balancing

Hi,
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
Floris
Load balancing when you own data (like a DBMS does) and you also want to modify that data is far
from trivial.
I don't think any system does that transparently (you either use shared disk, where you till only
have one copy of data or you do shared nothing where you only have a set of data on one location).
First determine what modifications are to be performed. Easiest is of you can separate read-only
applications (reports etc). If so, you can replicate (using built-in transactional replication) from
the server where you do modifications to the read-only server.
If you cannot separate read-only applications, you can look into merge replication. However, if you
modify the same data at the same time on two nodes, one will win and the other modification will be
lost. Unacceptable in many scenarios.
I suggest you read up in Books Online about Replication (there are different types of replications)
as well as "federated servers" (also known as distributed partitioned views).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available with Windows Server
> 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our sql servers?
> And i will create a replication on the background between the 2 sql servers so they will both
> serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>
|||Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMficaSjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Load balancing when you own data (like a DBMS does) and you also want to
> modify that data is far from trivial.
> I don't think any system does that transparently (you either use shared
> disk, where you till only have one copy of data or you do shared nothing
> where you only have a set of data on one location).
> First determine what modifications are to be performed. Easiest is of you
> can separate read-only applications (reports etc). If so, you can
> replicate (using built-in transactional replication) from the server where
> you do modifications to the read-only server.
> If you cannot separate read-only applications, you can look into merge
> replication. However, if you modify the same data at the same time on two
> nodes, one will win and the other modification will be lost. Unacceptable
> in many scenarios.
> I suggest you read up in Books Online about Replication (there are
> different types of replications) as well as "federated servers" (also
> known as distributed partitioned views).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Floris" <meNOSPAM@.here_my_place.nl> wrote in message
> news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>

Load Balancing

Hi,
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
FlorisLoad balancing when you own data (like a DBMS does) and you also want to modify that data is far
from trivial.
I don't think any system does that transparently (you either use shared disk, where you till only
have one copy of data or you do shared nothing where you only have a set of data on one location).
First determine what modifications are to be performed. Easiest is of you can separate read-only
applications (reports etc). If so, you can replicate (using built-in transactional replication) from
the server where you do modifications to the read-only server.
If you cannot separate read-only applications, you can look into merge replication. However, if you
modify the same data at the same time on two nodes, one will win and the other modification will be
lost. Unacceptable in many scenarios.
I suggest you read up in Books Online about Replication (there are different types of replications)
as well as "federated servers" (also known as distributed partitioned views).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available with Windows Server
> 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our sql servers?
> And i will create a replication on the background between the 2 sql servers so they will both
> serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>|||Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMficaSjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Load balancing when you own data (like a DBMS does) and you also want to
> modify that data is far from trivial.
> I don't think any system does that transparently (you either use shared
> disk, where you till only have one copy of data or you do shared nothing
> where you only have a set of data on one location).
> First determine what modifications are to be performed. Easiest is of you
> can separate read-only applications (reports etc). If so, you can
> replicate (using built-in transactional replication) from the server where
> you do modifications to the read-only server.
> If you cannot separate read-only applications, you can look into merge
> replication. However, if you modify the same data at the same time on two
> nodes, one will win and the other modification will be lost. Unacceptable
> in many scenarios.
> I suggest you read up in Books Online about Replication (there are
> different types of replications) as well as "federated servers" (also
> known as distributed partitioned views).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Floris" <meNOSPAM@.here_my_place.nl> wrote in message
> news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> In windows 2003 server you have NLB (Network Load Balancing).
>> Can i use this to load balance my traffic to our sql server?
>> I know you also have "Microsoft Cluster Service" but that's only
>> available with Windows Server 2003 Enterprise Edition.
>> So is it possible to load balance my traffic from our webservers to our
>> sql servers?
>> And i will create a replication on the background between the 2 sql
>> servers so they will both serve the same data.
>> I hope someone has an answer for me.
>> Thanks in advance.
>> Floris
>sql

Load Balancing

Hi,
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
FlorisLoad balancing when you own data (like a DBMS does) and you also want to mod
ify that data is far
from trivial.
I don't think any system does that transparently (you either use shared disk
, where you till only
have one copy of data or you do shared nothing where you only have a set of
data on one location).
First determine what modifications are to be performed. Easiest is of you ca
n separate read-only
applications (reports etc). If so, you can replicate (using built-in transac
tional replication) from
the server where you do modifications to the read-only server.
If you cannot separate read-only applications, you can look into merge repli
cation. However, if you
modify the same data at the same time on two nodes, one will win and the oth
er modification will be
lost. Unacceptable in many scenarios.
I suggest you read up in Books Online about Replication (there are different
types of replications)
as well as "federated servers" (also known as distributed partitioned views)
.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx
.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server
> 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our sq
l servers?
> And i will create a replication on the background between the 2 sql server
s so they will both
> serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>|||Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMficaSjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Load balancing when you own data (like a DBMS does) and you also want to
> modify that data is far from trivial.
> I don't think any system does that transparently (you either use shared
> disk, where you till only have one copy of data or you do shared nothing
> where you only have a set of data on one location).
> First determine what modifications are to be performed. Easiest is of you
> can separate read-only applications (reports etc). If so, you can
> replicate (using built-in transactional replication) from the server where
> you do modifications to the read-only server.
> If you cannot separate read-only applications, you can look into merge
> replication. However, if you modify the same data at the same time on two
> nodes, one will win and the other modification will be lost. Unacceptable
> in many scenarios.
> I suggest you read up in Books Online about Replication (there are
> different types of replications) as well as "federated servers" (also
> known as distributed partitioned views).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Floris" <meNOSPAM@.here_my_place.nl> wrote in message
> news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>

Wednesday, March 28, 2012

Load balancing for read-only databases

Load balancing for read-only databases

Load balancing for read-only databases

Load balancing

Load Balancing

Load Balancing

Load Balancing

Load Balancing

Load Balancing

List availables tables

Blog Archive

About Me