OLE DB .NET Data Provider

for RuBoard

The OLE DB .NET data provider pools connections by using the underlying services of OLE DB resource pooling. This is essentially the same thing, just worded differently. You will be able to use the connection string to configure, enable, or disable resource pooling (connection pooling), use the registry to configure OLE DB resource pooling (not recommended), and programmatically configure resource pooling. OK, so if you want to disable OLE DB .NET connection pooling and use the COM+ object pooling, you will have to supply a connection string keyword that turns it off ( OLE DB Services=-4 ) because it is "on" as the default. The connection would look like this:

 Dim nwindConn As OleDbConnection = New OleDBConnection        ("Provider=SQLOLEDB;OLE DB Services=-4;Data Source=localhost;         Integrated Security=SSPI;") NwindConn.Open() 

When a connection is opened and a pool created, multiple connections are added to the pool to bring the connection count to the configured minimum level (as specified with the Min Pool Size keyword). To establish a minimum pool size, there will be a small amount of overhead when the pool is initially created. These additional entries are serialized and will not bog down your server.

Connections can be subsequently added to the pool up to the configured maximum pool count (as specified with the Max Pool Size keyword). When the maximum count is reached, new requests to open a connection are queued.

Keep an eye on connection-pooling usage by using either Profiler or Performance Monitor. As you can see from Figure 17.2, you can monitor the User Connections entry of the General Statistics performance counters for SQL Server to see the level of connections established by all applications for a particular SQL Server instance. With the .NET Framework, you will also get many other performance counters. For monitoring connection pooling, you can focus in on the .NET CLR Data performance object.

Figure 17.2. The Microsoft Performance Monitor counters and profiler event classes for monitoring connection-pooling results.

In addition, you can also use the trace properties of Profiler for basically the same type of information by selecting the Security Audit event classes. We will see more on this later in this hour .

Managing Security?

When you get into the connection-pooling business, you will no longer be able to use security at the individual user and database level. It just isn't feasible any longer. Connection pooling relies on exact matches of connection strings, and if users' IDs are substituted into the connection strings, the resulting connection strings would not match any existing entries in the connection pool.

All of the examples shown in this chapter rely on Windows authentication and trusted connections.

The .NET Beta 2 performance results showed that it took longer to open a pooled database connection when using Windows authentication, compared to using SQL Server authentication. However, in general, it is better to use Windows authentication rather than SQL Server authentication for many other reasons.

In the following Visual Basic code sample (17conpool.vb), a series of connection strings are defined and used to establish connection pools for subsequent processing (Pool A and Pool B). In addition, several separate SQLConnection s will be made that will take advantage of the connection pools being established. This code was designed to require input from the console at certain points of its execution so that you can see the results of the performance counters along the way. Listing 17.1 provides the code.

Listing 17.1 Visual Basic Connection Pooling Code Sample (from file 17conpool.vb)

 . . . public class conpool   public shared sub Main()     Dim myconpool as conpool = new conpool()     myconpool.Run()   end sub   public sub Run()     try       Dim connString1 as String       connString1 = "server=localhost;Trusted_Connection=yes;              database=northwind;" & _                    "connection reset=true;" & _                    "connection lifetime=0;" & _                    "enlist=true;" & _                    "min pool size=1;" & _                    "max pool size=50"       Dim connString3 as String       connString3 = "server=localhost;Trusted_Connection=yes;database=pubs;" & _                    "connection reset=true;" & _                    "connection lifetime=0;" & _                    "enlist=true;" & _                    "min pool size=10;" & _                    "max pool size=50"       Dim myConnection1 as SqlConnection = new SqlConnection(connString1)       Dim myConnection2 as SqlConnection = new SqlConnection(connString1)       Dim myConnection3 as SqlConnection = new SqlConnection(connString1)       Dim myConnection5 as SqlConnection = new SqlConnection(connString3) . . . '********************** establish first connection pool **********************       Console.WriteLine ("Opening two connections in pool A")       myConnection1.Open()       myConnection2.Open() . . .       Console.WriteLine ("Now Returning both of the connections to pool A")       myConnection1.Close()       myConnection2.Close()       Console.WriteLine ("Open another connection from the same pool A")       myConnection3.Open() . . .       Console.WriteLine ("Returning this connection to pool A")       myConnection1.Close() '*************** establish a separate connection pool ***************       Console.WriteLine ("Opening a new connection in pool B")       myConnection5.Open() . . .       Console.WriteLine ("Returning this connection to pool B")       myConnection5.Close()     catch e as Exception       ' Display the error.       Console.WriteLine(e.ToString())     end try   end sub end class end namespace 

To execute this code, you must first compile it from a DOS command prompt. Change directories to the location of the VB source code that contains this example (17conpool.vb). Then just compile the code as follows :

 C:\ADOSAMPLES>  vbc.exe 17conpool.vb  /r:System.dll  /r:System.Data.dll   /r:System.Xml.dll  

After the code has compiled successfully, you will need to make sure that you have Microsoft SQL Server up and running and the Northwind database has been installed (usually by default). To execute this sample, just specify the sample name at the DOS command prompt and press Enter. Again, this VB program will be prompting you to press Enter at certain points so that you can go look at the performance and user connection information as it is executing. Figure 17.3 shows the actual execution of this sample VB program. You should be able to get the same results.

Figure 17.3. The Connection Pooling sample VB program executing at the DOS prompt.

After you have executed this sample once, open the Performance Monitor console and choose to add (Plus +) the following counters to the monitor console:

• ".NET CLR Data" performance object "SqlClient:Current # connection Pools counter

• ".NET CLR Data" performance object "SqlClient:Current # pooled connections counter

• ".NET CLR Data" performance object "SqlClient:Peak # of Pooled connections counter

• "MSSQL$NetSDK:General Statistics" performance object "User connections counter

For the ".NET CLR Data" performance objects, be sure to indicate the instance selection when adding these (the "17conpool" instance entry). These are all the connection pools being generated by this process.

Then run the sample VB program again and monitor the connection pool levels and user connections during execution. User connections will remain low, but the connection pools will grow and hopefully be utilized in the future.

In Figure 17.4, you can see the different connection counter levels during this execution. The top line in the monitor shows a stepped increase in the pooled connections ("SQLClient:Peak # pooled connections") counter. The first step was the open of the first connection string for Pool A processing. In that connection string, we had indicated a minimum pool size value of 1. The second, much larger, step is the second connection string open. In that connection string, we had indicated a minimum pool size value of 10. As the program ends (when the top line reaches a plateau), you can also see the user connections drop off to zero (the monitor line that looks like a mountain).

Figure 17.4. Performance Monitor showing connection pooling behavior.

for RuBoard