Connection Pool in SQLserver 2005

What is the performance cost of not using connection pool when connecting to
a sqlserver 2005 using windows identity.
We are planning an application where the users windows identity is forwarded
through all layers all the way into the sqlserver.
This gives security and auditing advantages.
But a pros/cons evaluation is always proper to do.
The cons include: the sqlserver connection pool cannot be used...(?) If
there is more problems, pls let me know.
This rises the question: what is the price for not using connection pools.
Who is paying: the sqlserver or the business layer on the IIS server or both?
regards
Jens Chr
If the users are constantly in/out of the server, you can still benefit from
using connection pooling. It's just that each user will have their own
connection pool. (Set the max connections in the pool to 2, and set the pool
timeout up to 10-20 minutes -or longer.)
Of course, this will require a bit more memory on the IIS box, and a bit
more on the SQL Server, but will greatly improve responsiveness.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"jcjj" <jcjj@.discussions.microsoft.com> wrote in message
news:CD85E9F3-21BE-42B6-AC23-EDBF0234C713@.microsoft.com...
> What is the performance cost of not using connection pool when connecting
> to
> a sqlserver 2005 using windows identity.
> We are planning an application where the users windows identity is
> forwarded
> through all layers all the way into the sqlserver.
> This gives security and auditing advantages.
> But a pros/cons evaluation is always proper to do.
> The cons include: the sqlserver connection pool cannot be used...(?) If
> there is more problems, pls let me know.
> This rises the question: what is the price for not using connection pools.
> Who is paying: the sqlserver or the business layer on the IIS server or
> both?
> regards
> Jens Chr

Connection Pool in SQLserver 2005

What is the performance cost of not using connection pool when connecting to
a sqlserver 2005 using windows identity.
We are planning an application where the users windows identity is forwarded
through all layers all the way into the sqlserver.
This gives security and auditing advantages.
But a pros/cons evaluation is always proper to do.
The cons include: the sqlserver connection pool cannot be used...(?) If
there is more problems, pls let me know.
This rises the question: what is the price for not using connection pools.
Who is paying: the sqlserver or the business layer on the IIS server or both
?
regards
Jens ChrIf the users are constantly in/out of the server, you can still benefit from
using connection pooling. It's just that each user will have their own
connection pool. (Set the max connections in the pool to 2, and set the pool
timeout up to 10-20 minutes -or longer.)
Of course, this will require a bit more memory on the IIS box, and a bit
more on the SQL Server, but will greatly improve responsiveness.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"jcjj" <jcjj@.discussions.microsoft.com> wrote in message
news:CD85E9F3-21BE-42B6-AC23-EDBF0234C713@.microsoft.com...
> What is the performance cost of not using connection pool when connecting
> to
> a sqlserver 2005 using windows identity.
> We are planning an application where the users windows identity is
> forwarded
> through all layers all the way into the sqlserver.
> This gives security and auditing advantages.
> But a pros/cons evaluation is always proper to do.
> The cons include: the sqlserver connection pool cannot be used...(?) If
> there is more problems, pls let me know.
> This rises the question: what is the price for not using connection pools.
> Who is paying: the sqlserver or the business layer on the IIS server or
> both?
> regards
> Jens Chrsqlsql

Connection between OLAP cubes and underlying tables

I am new to SQL Server 2005 and have a simple question/scenario. I would like to use cubes to improve query performance. If I build OLAP cubes with Analysis Services, will SQL Server automatically pick the 'best' cube to resolve user queries to the underlying tables?

For example: A cube is created to sum up sales by month from a fact table (Sales_Fact) called Monthly_Sales.

The user enters the query: Select datepart(month, Sales_Date) from Sales_Fact group by datepart(month, Sales_Date) .

Will the Monthly_Sales cube used to resolve the query?

Thanks

Johnsoi

You are bit far off on the learning side

To use Analysis Services to the most part you will need different client side applicaitons. The query language is different: it is MDX and not SQL.

Suggest you install SQL Server samples and go through tutorial see if you pick some of the concepts there.

HTH
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Thanks for the reply. So...it looks like SQL Server cannot rewrite queries (from Cognos, Crsytal, etc..) to use predefined OLAP cubes. I am looking for a way to add aggreagate tables (or other summarized objects) to our data warehouse and not require users to know of their existence. Is SQL Server 2005 aggregate aware?

Ike

|||

You are bit off again.

SQL Server ships several components in it. One is Analysis Services that allows for creating multidimensional models (OLAP cubes you call it). Analysis Services allow access to the data stored in cubes using MDX query langage.

That is completely different from SQL query language. To access data in Analysis Services you can use serveral client tools. If you are just interested in reports, you can install Reporting Services and use it to access data in cubes.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.