RE: Connection pooling

From: vishal subramaniam (vishalsu_at_microsoft.com)
Date: 05/12/04 

Date: Wed, 12 May 2004 12:29:25 GMT

vishalsu@online.microsoft.com

RESOLUTION:
=============
Connection Pooling with SQL Server 2000 Analysis Services
Dennis Kennedy
Microsoft Corporation

Originally published May 2001, updated November 2002

Applies to:
     Microsoft® SQL Server™ 2000 Analysis Services

Summary: Learn how to use the connection pooling objects included with
the Microsoft XML for Analysis Provider to develop scalable client and Web
applications for Microsoft SQL Server 2000 Analysis Services. (11 printed
pages)

Contents
Introduction
Audience
Connection Pooling Objects
Using the Connection Pooling Objects
Requesting and Returning Connections
Balancing and Shrinking the Connection Pool
ADOConPool Object
OLEDBConPool Object
Conclusion
Additional Information

Introduction
Resource management is an important consideration in the development of
scalable client and Web-based applications. In the construction of a client
application that might serve many concurrent users, the guideline for
resource management is to allocate resources as late as possible and
de-allocate resources as early as possible. The availability of resources,
such as memory, process threads, and network or database connections,
relates directly to the performance and user satisfaction of a client
application. Therefore, resource management becomes more and more important
as the client application is scaled up and out.

By providing more control over resource management, connection pooling can
reduce the impact of scalability. Connection pooling enables a client
application to use a connection to a given resource from a pool of
connections that do not need to be re-established for every use. After a
connection has been created and placed in a connection pool, a client
application can reuse that connection without performing the complete
connection process.

Using a pooled connection can result in significant performance gains
because client applications do not need to repeatedly establish and close a
connection. The time required by this process can be particularly
significant for client applications that use high latency resources, such
as Internet or network connections. After the client application no longer
needs a connection, the connection is simply returned to the connection
pool.

In addition to performance gains, connection pooling enables a resource to
be managed more effectively, without forcing the overhead of resource
management to the client application. The manager of the connection pool
can allocate and de-allocate connections as needed to maintain the pool,
and connections in a connection pool can be used repeatedly by multiple
applications.

To support the scalability needs of Web-based client applications that use
Microsoft SQL Server 2000 Analysis Services, connection pooling has been
implemented through the Microsoft XML for Analysis Provider. Although the
XML for Analysis Provider uses connection pooling automatically, you can
also use this feature for other client applications that may not need the
XML connectivity offered by the provider itself. The purpose of this paper
is to describe the objects that you can use to take advantage of connection
pooling in Analysis Services client applications.

Audience
This paper assumes the reader has a basic knowledge of SQL Server 2000
Analysis Services and either Microsoft ActiveX® Data Objects (ADO) or OLE
DB data access technologies. Examples are presented in Microsoft Visual
Basic® and Microsoft Visual C++®.

Connection Pooling Objects
Two objects, ADOConPool and OLEDBConPool, are available through the XML for
Analysis Provider. The ADOConPool object manages ADO connection objects.
The OLEDBConPool object manages OLE DB session objects. Although each
object supplies a different type of connection pool, they both use the same
underlying mechanisms to manage the connection pool. For the purposes of
this white paper, the term "connection" is used to describe both ADO
connection objects and OLE DB session objects when discussing such shared
mechanisms.

The connection pooling mechanism is intended for use only with the updated
Microsoft OLE DB Provider for OLAP Services 8.0 (MSOLAP.2) OLE DB provider
included with the Microsoft SQL Server 2000 Service Pack 1 (SP1).

Using the Connection Pooling Objects
You can use the ADOConPool and OLEDBConPool objects with any programming
language that supports ADO or OLE DB data access technologies. However, to
use these objects in a Visual C++ program, you must add the following
compiler directives to your program to include the correct headers and
attributes:

#include <windows.h>
#include <atlbase.h>
#import "<filepath>\\msxaserv.dll" rename("tag_inner_PROPVARIANT",
  "tagPROPVARIANT") rename("_LARGE_INTEGER","")
rename("_ULARGE_INTEGER","")
using namespace MSXmlAnalysisSCLib;

Requesting and Returning Connections
The mechanism used to request connections from the connection pool is
different from the mechanism typically employed by OLE DB resource pooling
to facilitate fast access for Web-based applications. The connection pool
object breaks the pool of active connections into two groups: free
connections and used connections. Free connections consist of connections
that are not currently allocated to a client application. Used connections
are currently allocated to and in use by client applications.

A special authentication and impersonation mechanism is employed for
connection requests. When a connection is requested by an application
(using either the GetConnection method for the ADOConPool object or the
GetSession method for the OLEDBConPool object), the connection pool
attempts to retrieve a free connection that uses the same domain and user
name as the security identifier (SID) used by the client application. If a
match is found, the free connection is returned to the client application.

If a match based on the client SID information is not found, the connection
pool object parses the connection information that was passed in the client
request to determine whether a free connection for the same requested
database already exists in the connection pool. If a database match is
found, the connection pool object attempts to match the role security of
the client request to the role security of the existing free connection. If
a role security match is found, the connection pool object compares the
user name on the free connection to the user name of the client request. If
the user names match, the free connection is returned to the client
application. If the user names do not match, the free connection is
re-authenticated against role security on the Analysis server, using the
domain and user name of the client request, and then returned to the
requesting client application.

If a role security or database match is not found, a new connection is
created in the connection pool and allocated to the requesting client
application.

Unlike typical approaches to resource sharing, this approach has the
benefit that a requesting client application can reuse an existing active
connection that has identical role security privileges, even if a different
user originally requested that connection. The new user name associated
with the free connection is still authenticated, and therefore maintains
security, but the connection can be provided to the client. This reduces
connection time and overhead for a client application servicing a large
number of concurrent users.

For client applications that perform many operations and repeatedly request
and return connections, the mechanism is even more efficient. The same
active and authenticated connection can be returned to the requesting
client application.

Returning connections to the connection pool is a simple process for the
client application. The client application passes the connection reference
back to the connection pool object (using either the ReturnConnection
method for the ADOConPool object or the ReturnSession method for the
OLEDBConPool object). The connection pool object verifies that the
connection object that was passed back actually belongs to the connection
pool, and then places it back in the available pool of free connections.

Usage Considerations
If the user has requested a connection, released it, and then requested
another connection from the connection pool object, the impersonation
mechanism used to re-authenticate users against active connections in the
connection pool returns the same connection, without requiring a round trip
to the Analysis server. If the role permissions of the user were changed
after the first connection request was released, the second request returns
the same connection with the original role permissions.

For example, a user is assigned to a role, named Role A, on an Analysis
server. Role A gives its users permission to run queries against two cubes,
Cube A and Cube B. When the client application on which this user is
working requests a connection for the first time, the returned connection
has access to Cube A and Cube B. The client application runs the query and
then releases the connection. The administrator of the Analysis server now
changes Role A so that it has access only to Cube A. If the client
application for this user requests another connection, the connection
created on the first request is again returned to the client application,
but it still has access to Cube A and Cube B. Even though the user, through
Role A, now has access only to Cube A, a query executed against Cube B will
still execute as though the user still has access to the cube.

This issue occurs only if an active connection is reallocated; newly
created connections are always validated against the Analysis server. If
the active connection first requested by the client application in the
previous example had timed out, the client application would have been
allocated a newly created connection with the correct role permissions.

For Web applications, the easiest way to resolve this issue is to restart
Microsoft Internet Information Services (IIS) whenever a role is changed on
the Analysis server, forcing applications to reload and use the new role
permissions when requesting connections.

Because of the nature of IIS thread management, when you create Web-based
applications, you should use the ADOConPool and OLEDBConPool connection
pool objects in Active Server Pages (ASP) Web applications with special
consideration. IIS checks each COM component to determine its agility (the
threading and marshalling abilities of a COM component). The XML for
Analysis Provider supports the free-threading model, but does not aggregate
the free-threaded marshaler (FTM). Because of this, the XML for Analysis
Provider is considered non-agile by IIS 5.0 or later.

This means that if the default settings for IIS 5.0 or later are used, the
ADOConPool and OLEDBConPool objects will use the system security context
when cached at application or session scope in ASP applications (in other
words, cached in ASP Application or Session object variables). The
impersonation mechanism, described in Requesting and Returning Connections,
will no longer function correctly. The connection pool object will use the
default IIS user instead of the currently connected user when attempting
authentication for all active connections.

To correct this, change the ASPTrackThreadingModel setting in the metabase
for IIS 5.0 or later to True. Changing this setting prevents IIS from
checking COM components for agility and incurs a minor performance hit due
to marshaling and serialization, so you should change this setting only in
the virtual directory or Web directory that contains the Web application.

Balancing and Shrinking the Connection Pool
The number of connections allowed in the connection pool is not rigidly
enforced, because the underlying management mechanism was designed to be
non-blocking — a client application should be able to get a connection when
requested. Because of this non-blocking behavior, both objects use the same
passive techniques for managing connections.

Two different techniques are used to manage the connection pool: balancing
and shrinking.

Balancing the Connection Pool
Balancing is employed whenever a connection is returned to the connection
pool (using either the ReturnConnection method for the ADOConPool object or
the ReturnSession method for the OLEDBConPool object). The connection pool
object compares the total number of active connections, used and free, to
the MaxSessions property value to determine whether balancing the
connection pool is necessary. If the total number of active connections is
greater than the MaxSessions property value, balancing is necessary.

To balance the connection pool, the connection pool object sorts the group
of free connections on the number of elapsed seconds since the last access
time for each free connection. The object then removes the free connections
with the oldest elapsed times, one by one, until either the total number of
used and free connections is under the MaxSessions property value or no
active free connections remain.

Note When balancing, the Timeout property is not used.
Shrinking the Connection Pool
Shrinking is employed whenever the client application calls the Shrink
method of either the ADOConPool or OLEDBConPool object. In this technique,
the free connections are expired; the connection pool object compares the
last access time for each free connection against the current system time
and removes the free connection if the difference in seconds is greater
than the Timeout property value.

Neither technique manages used connections. It is the responsibility of the
client application to return a connection to the connection pool after an
operation is completed, so the used connection can be reassigned as a free
connection. The connection pool object does not attempt to manage used
connections, but performs both balancing and shrinking only on free
connections. This approach allows for a flexible balance between
performance and resource management.

ADOConPool Object
The ADOConPool object supplies connection pooling for client applications
that use ADO data access technology, maintaining a collection of ADO
connection objects.

The ADOConPool object has the following properties and methods:

MaxSessions Property
The MaxSessions property is used to limit the number of ADO connection
objects, both free and used, in the connection pool.

Data type
Long integer

Access
Read/write

Remarks
Because the connection pooling mechanism is designed to be nonblocking, the
MaxSessions property is not used to directly limit the growth of the
connection pool. Instead, this value is used by the ReturnConnection and
Shrink methods to balance and shrink the connection pool. For more
information about balancing and shrinking, see Balancing and Shrinking the
Connection Pool earlier in this paper.

Sessions Property
The Sessions property returns the number of active ADO connection objects
in the connection pool.

Data type
Long integer

Access
Read-only

Remarks
The Sessions property reports the total number of connections, both used
and free, managed by the ADOConPool object.

Timeout Property
The Timeout property sets or returns the number of seconds a free ADO
Connection object should remain active.

Data type
Long integer

Access
Read/write

Remarks
As with the MaxSessions property, the Timeout property is used by the
Shrink method to identify active free connections to be removed from the
connection pool. For more information about shrinking, see Balancing and
Shrinking the Connection Pool.

GetConnection Method
The GetConnection method, given a connection string, returns an ADO
Connection object.

Syntax
C++

HRESULT GetConnection([in] BSTR in_bstrCn, [out,retval] IDispatch**
io_ppADOConnection)

Visual Basic
Set io_ppADOConnection = object.GetConnection(in_bstrCn As String)

object

A valid reference to an ADOConPool object.
in_bstrCn

The connection string for the ADO Connection object.
io_ppADOConnection

The returned ADO Connection object reference.
Remarks
This method attempts to request an existing free connection from the
connection pool, by matching connection and security information, before
creating a new connection. For more information on requesting connections,
see Requesting and Returning Connections.

ReturnConnection Method
The ReturnConnection method returns an ADO Connection object to the
connection pool.

Syntax
C++

HRESULT ReturnConnection([in,out] IDispatch** io_ppADOConnection)

Visual Basic
object.ReturnConnection io_ppADOConnection

object

A valid reference to an ADOConPool object.
io_ppADOConnection

The ADO Connection object to be returned to the connection pool.
Remarks
The connection pool object automatically balances free connections after a
connection is returned using this method. For more information about
balancing connections, see Balancing and Shrinking the Connection Pool.

Shrink Method
The Shrink method, when called, expires and removes free ADO connection
objects from the connection pool.

Syntax
C++

HRESULT Shrink()

Visual Basic

object.Shrink

object

A valid reference to an ADOConPool object.
Remarks
Client applications should regularly call this method to expire and remove
free connections that have timed out. For more information about shrinking
the connection pool, see Balancing and Shrinking the Connection Pool.

OLEDBConPool Object
The OLEDBConPool object supplies connection pooling for client applications
that use OLE DB data access technology, maintaining a collection of OLE DB
session objects. The OLEDBConPool object is intended for use by
applications that directly employ OLE DB to provide client data access;
most Web-enabled applications should instead use the ADOConPool connection
pool object.

MaxSessions Property
The MaxSessions property is used to limit the number of OLE DB session
objects, both free and used, in the connection pool.

Data type
Long integer

Access
Read/write

Remarks
Because the connection pooling mechanism is designed to be nonblocking, the
MaxSessions property is not used to directly limit the growth of the
connection pool. Instead, this value is used by the ReturnSession and
Shrink methods to balance and shrink the connection pool. For more
information about balancing and shrinking, see Balancing and Shrinking the
Connection Pool.

Sessions Property
The Sessions property returns the number of active OLE DB session objects
in the connection pool.

Data type
Long integer

Access
Read-only

Remarks
The Sessions property reports the total number of connections, both used
and free, managed by the OLEDBConPool object.

Timeout Property
The Timeout property sets or returns the number of seconds a free OLE DB
session object should remain active.

Data type
Long integer

Access
Read/write

Remarks
As with the MaxSessions property, the Timeout property is used by the
Shrink method to identify active free connections to be removed from the
connection pool. For more information about shrinking, see Balancing and
Shrinking the Connection Pool.

GetSession Method
The GetSession method, given an array of OLE DB properties, returns an OLE
DB session object.

Syntax
C++

HRESULT GetSession([in] int in_cPropSets, [in] DBPROPSET* in_pPropSets,
  [out,retval] IDBCreateCommand** io_ppSession )

in_cPropSets

The length, in bytes, of the tagDBPROPSET type structure referenced in the
in_pPropSets parameter.
in_pPropSets

A pointer to the tagDBPROPSET type structure used to identify and, if
needed, create the OLE DB session object. For more information about the
tagDBPROPSET type structure, see DBPROPSET Structure in the OLE DB
documentation.
io_ppSession

The returned OLE DB session object reference. The object reference is cast
to the IDBCreateCommand OLE DB interface. For more information about the
IDBCreateCommand interface, see IDBCreateCommand in OLE DB documentation.
Remarks
This method attempts to request an existing free connection from the
connection pool, by matching connection and security information, before
creating a new connection. For more information about requesting
connections, see Requesting and Returning Connections.

ReturnSession Method
The ReturnSession method returns an OLE DB session object to the connection
pool.

Syntax
C++

HRESULT ReturnSession([in,out] IDBCreateCommand** io_ppSession);

io_ppSession

The OLE DB session object to be returned to the connection pool.
Remarks
The connection pool object automatically balances free connections after a
connection is returned using this method. For more information about
balancing connections, see Balancing and Shrinking the Connection Pool.

Shrink Method
The Shrink method, when called, expires and removes free OLE DB session
objects from the connection pool.

Syntax
C++

HRESULT Shrink()

Remarks
Client applications should regularly call this method to expire and remove
free connections that have timed out. For more information about shrinking
the connection pool, see Balancing and Shrinking the Connection Pool.

Conclusion
Connection pooling is an effective method of resource management. The use
of connection pooling objects that are provided as part of the Microsoft
XML for Analysis Provider can extend this resource management method to
client applications that use Microsoft SQL Server 2000 Analysis Services,
reducing overhead and increasing performance at little cost in terms of
development and implementation time.

LINKS
=======
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/sql2k_connpooling.asp
This posting is provided "AS IS" with no warranties, and confers no rights.

Relevant Pages

  • Re: "Redirecting" an incoming socket connection?
    ... So you think my first proposal (which would, to the server application, make ... >> to two different applications to be able to accept connections. ... Upon accepting the connection, it would then ... the first communication is done by the client). ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Connection refusals
    ... The fact that the server is calling out to other code could certainly lead ... Remember if the client (the ASP ... reuse the connection but has to create another. ... However in my experience filling the connection pool ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Compact Edition - Connection Pool --> Slow!
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... mind (--> get a connection, use it and release it as soon as possible ... about the connection pool for Compact Edition 3.1. ...
    (microsoft.public.sqlserver.ce)
  • Re: SQL Compact Edition - Connection Pool --> Slow!
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... mind (--> get a connection, use it and release it as soon as possible ... about the connection pool for Compact Edition 3.1. ...
    (microsoft.public.sqlserver.ce)
  • Re: ASP.NET SQL Processes
    ... Microsoft Online Support ... when we use the ADO.NET's connection ... | connection pool, when we create SqlConnections, the ADO.NET will retrieve ... | | SQL server doesn't keep any processes attached to the application. ...
    (microsoft.public.dotnet.framework.aspnet)