inContact Developer Discussion

[cmarkle]

I'm trying to implement a Backup DB Provider for our database integration.  Currently, we have a single DB Provider that is using the "Microsoft OLE DB Provider for ODBC Drivers" Provider.  This was set up before I took over administration of the system and seems to work well most of the time.  This installation uses an older version of the DBProvider (6.6.8.7) and I'd like to upgrade to the latest version (7.5.22.7).  I'm connecting to a SQL Server 2000 database (soon to be SQL Server 2005). 

1)       From what I understand, I might have a little better performance using the "Microsoft OLE DB Provider for SQL Server" Provider since there is one less level of translation.  Is this the case in your experience? I have configured a 2nd DB Provider using the "Microsoft OLE DB Provider for SQL Server" Provider for testing purposes.  I notice that it consistently keeps 50 connections open to my SQL Server (My primary DB Provider does this too).  The following information was provided to me: 

We have acknowledged the DBProvider maintaining open threads longer than necessary as an issue. Currently we are encouraging the use of a modification to the ADO connection string entry in the DBProvider itself.   Add the following phrase to the end of your ADO connection string: ;Extended Properties="Max Pool Size=20"   This is designed to set a maximum connection limit. This together with the ThreadPoolSize setting should help in the mean time.  

2)       I have added the ;Extended Properties="Max Pool Size=20" parameter to my connection string, but it does not seem to have any effect.  I was told that the default number of connection pools is 20, yet I still have 50 open connections to my database.  What am I doing wrong?  Below is the ADO connection string I am using:

Provider=SQLOLEDB.1;Password=********;Persist Security Info=True;User ID=UCNWebAgent;Initial Catalog=MetWeb;Data Source=PROV_MAIN;Network Library=dbmssocn;Application Name=inContact DBProvider;Extended Properties="Max Pool Size=20" 

3)       What is the recommended setting for “ThreadPoolSize”? 

4)       Do you have any experience on which Network Library is best to use?  In the connection string above, I am using the TCP/IP library.  The default library seems to be Named Pipes for the "Microsoft OLE DB Provider for SQL Server" Provider.

5)       I was hoping to set up automatic failover between my 2 DB Providers since a priority can be set in Web Manager for each DB Provider.  While this does seem to work, there does not appear to be a way to set the timeout for the DB connection using this method.  I tried setting the timeout in the ADO Connection String and this seemed to have no effect.  Is there a way to set the connection timeout using this method? 

6)       Assuming that I can’t use the automatic failover of DB Providers and I have to modify all of my scripts to handle the failover, what is the best method of doing this?  Do I add a SETDB task for my primary DB Provider and if this task fails, call a SETDB task for my 2nd DB Provider?  Or do I set up an ONERROR branch to handle a failed SETDB task and set the 2nd DB Provider and return to the point of failure in the main script. 

Thanks,

Chad  

[ShaneKelly]

1) Yes, the Microsoft OLE DB Provider for SQL Sever will provide better performance, however the performance difference is very small unless you are doing a large volume of queries.  As a best practice you should use "Microsoft OLE DB Provider for SQL Server", which uses the new ADO model to access data. ADO is the successor to Microsoft's earlier object layers for accessing data such as ODBC. The "Microsoft OLE DB Provider for ODBC Drivers" is meant to provide backwards compatibility with databases that do not provide a newer native OLE DB Provider. Most modern databases provide a native OLE DB driver and generally you would only need to use the OLE DB ODBC Driver for old or unusual database types. Using the OLE DB ODBC driver not only introduces an unneeded layer, if forces the newer technology to use and older subset of methods to access the data. This bypasses all the enhancements and optimizations built into the new ADO model.

2) The syntax ;Extended Properties="Max Pool Size=20" is incorrect.

To limit the size of the connection pool you add the following to the connection string: ;Max Pool Size=20;

3) What is the recommended setting for “ThreadPoolSize”? 

The optimal number of threads is equal to the maximum number of concurrent db requests that could be sent to the db provider. The db provider uses a separate thread for each concurrent request it receives. If a request is received and there is no available thread to service the request, the db provider will queue up the request for the next available thread.

To ensure the best performance, the ThreadPoolSize should be large enough to ensure that there is always a free thread available to immediatly handle an incoming request.

By the same token, the Max Pool Size should be equal to or greater than the ThreadPoolSize.

4)       Do you have any experience on which Network Library is best to use?  In the connection string above, I am using the TCP/IP library.  The default library seems to be Named Pipes for the "Microsoft OLE DB Provider for SQL Server" Provider.

Typically, you would not specify the library to use in the OLD DB connection string, rather this is generally controlled and determined by the "SQL Native Client Configuration". For SQL Server 2005 you would use the "SQL Server Configuration Manager" to determine which protocols are enabled on the computer and in which priority they will be used. For instance, the configuration will generally be specified to attempt the protocols "Shared Memory", "Named Pipes", and "TCP/IP"  (in order) to ensure the best performance for the client. 

Specifying the library directly in the connection string overrides the Native Client Configuration,and also bypasses the automatic selection of the best protocol for the client/server.

5)       I was hoping to set up automatic failover between my 2 DB Providers since a priority can be set in Web Manager for each DB Provider.  While this does seem to work, there does not appear to be a way to set the timeout for the DB connection using this method.  I tried setting the timeout in the ADO Connection String and this seemed to have no effect.  Is there a way to set the connection timeout using this method?

The ADO connection string supports the "Connection Timeout" setting (15 seconds by default), which should timeout when attempting the initial connection to the database. It is important to note that the "connection timeout" is different than the "query timeout" (which is not configurable).

One way to test the Connection Timeout is to specify a non-existent server in the connection string.

6) Professional services will need to answer the scripting question.

[TimHarris]

As far as number 6.  Using a SetDB and then on the "Not Available" branch going to the backup SetDB is the recommended practice.  Failure mid script is a bit harder to handle.  You can place a SetDB before the database script action each time.  If that is unwieldy you could also do what you described and on the error branch go to a SetDB and then to the database action.  Normally, the standard is just to use the built in failover setup.  Otherwise, using SetDB actions at the beginning of the script is sufficient.

[cmarkle]

Thanks for all the answers!  I made the correction for MaxPoolSize, however I still have 50 open conenctions to my SQL Server.  It would appear that something is not working properly.