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