Export (0) Print
Expand All
Creating Audit Tables, Invoking COM Objects, and More
Exception-handling Techniques
Exploring SQL Server Triggers: Part 2
Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
SQL Server: Display Your Data Your Way with Custom Renderers for Reporting Services
Updating Data in Linked Servers, Information Schema Views, and More
XML Features in SQL Server 2000
Expand Minimize

Connection Pooling with SQL Server 2000 Analysis Services

SQL Server 2000
 

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.

Additional Information

SQL Server Books Online contains more information about Analysis Services. For additional information, see these resources:

Show:
© 2014 Microsoft