Newbie Q regarding opening a connection to a database

Hello, Im new with databases.

I know that opening a connection to a DB is expensive. Usually I write a method that opens a connection to the DB then I execute a query and then close the connection. Pretty standard.

OK, so how do I handle opening connections to the database when I need to run multiple queries. For example, i have a webpage that need to query the database to see if the user has moderator privledges, then depending on that query I have to query the DB again for moderator specific information or non-modertaor information.

So in this case how do i handle opening connections to the DB. Is it ok to generally have to open a connection to a DB multiple times on a page load?

The obvious solution is to keep the connection open. That is, open a connection, query the Db, keep the connection open, do the conditional statment ( if is_Moderator) then query the DB again for the info that I need, and then close the connection. But, from all the books that Ive been reading this is not a good practice because business logic should not been in the dataAccess layer.

Any help would be much appreciated.You should be using stored procedures to do all of this. Then this wouldn't be a problem. You could have a "wrapper" stored procedure that figures out if it's a moderator or not, then calls the appropriate stored procedure based on that information. You then wouldn't have to worry about multiple calls to the database. This is a data logic procedure. It's not "always" bad to have business logic at the database layer. It's questionable that this is even business logic.

Make sense?|||yes it did make sense.

Thankyou.

Newb question regarding cluster load balancing

Hello to all,
I am currently working on creating a Windows 2003 server cluster (3 nodes)
with the intent that it serve our web app which requires multiple SQL
databases. The question I have is this: How can I configure load balancing
for this? It seems that clustering services only allows 1 of the nodes to
access the DB at any given time (which is logical). If this is true, how
will I be able to leverage the processing power of the other two nodes if
they are idle while the node that is hosting the DB does all the work?
Any help would be appreciated.
Michael
You can install multiple instances of SQL Server on a failover cluster. You
cannot natively load-balance the workload between these multiple instances.
They act as independent SQL servers. SQL Clustering is a failover
technology and does not natively load balance.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Michael Weyant" <mweyant@.themarlincompany.com> wrote in message
news:OshvlPrGGHA.312@.TK2MSFTNGP09.phx.gbl...
> Hello to all,
> I am currently working on creating a Windows 2003 server cluster (3 nodes)
> with the intent that it serve our web app which requires multiple SQL
> databases. The question I have is this: How can I configure load balancing
> for this? It seems that clustering services only allows 1 of the nodes to
> access the DB at any given time (which is logical). If this is true, how
> will I be able to leverage the processing power of the other two nodes if
> they are idle while the node that is hosting the DB does all the work?
> Any help would be appreciated.
> Michael
>
|||Thanks Geoff for the information. I thought as much but hoped otherwise.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:e9dbmVrGGHA.3856@.TK2MSFTNGP12.phx.gbl...
> You can install multiple instances of SQL Server on a failover cluster.
> You cannot natively load-balance the workload between these multiple
> instances. They act as independent SQL servers. SQL Clustering is a
> failover technology and does not natively load balance.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Michael Weyant" <mweyant@.themarlincompany.com> wrote in message
> news:OshvlPrGGHA.312@.TK2MSFTNGP09.phx.gbl...
>