ODBC Problems CF 8.01 and IIS 7

[ToeJam]

I am having serious problems with Coldfusion ODBC service with CF 8.01 on a win 2008 box with IIS 7.5. Every so often CF ODBC services peg my Xeon processor to 100% and cause connectivity issues with CF sites and cause 2008 MSSQL DSN's to stop working . Where do I need to look to diagnose the issue or is this a known issue.

Thanks

[Guest]

Are you using any Access databases?  If not, disable that service.

If so, migrate your databases from Access to SQL.  Access sucks when it comes to piping it in via CF.

[ToeJam]

Yes we unfortunately have 2 Access DB's running on server. They are seldom used but needed by clients who don't want to pay for upsizing. Is there anything that can be done mitigate the existing problem. CF 7.02 did not have this issue.

Thanks

[Guest]

The short answer?  Migrate them to SQL server.  Of some flavor.

If you want to trace the problem, you're going to have to look at what is going on behind the scenes.  Look at the logs, see if they indicate anything.  Has the database changed recently?  The queries?

Is the code optimized?

Consider using the built in server monitor or getting something like Fusion Reactor and turning on the database logging to monitor what is going on with those databases.

Are the databases local?  Or on a remote network?  If remote, how's the latency to the remote network?

[JR__Bob__Dobbs-qSBHQ2]

If you are already using SQL Server you might consider querying your Access databases by using a linked server configured in your MS SQL Server instead of though CFQUERY.  This would avoid the need to use CF ODBC services. All of your queries would be handled through SQL Server.

"Linking Servers" from Microsoft SQL Server documentation
http://msdn.microsoft.com/en-us/library/ms188279.aspx

[Guest]

MS Access is a poor choice to run with any appserver product (it is single threaded and just cannot keep pace with CF). Microsoft themselves recommends against using it with a server (that's why they never created a 64 bit driver for it.)

But if you have to use it:

1) backup your database files

2) click optimize in the ODBC manager driver dialog for Access.

or

3) If these are really old msaccess files like from Access95, update them to a more recent Access version.

Also, you could try using the "MS Access for Unicode" driver.  It is a JDBC/OLEDB bridge to Access. It may run better then the bridge.

If you have any datasources to MS SQL that are ODBC datasources, get rid of them!  You should be using JDBC connections either through ColdFusion's own driver or using Microsoft's latest JDBC driver for MS SQL.

Ultimately though there is no way to make a desktop database like Access work well when you have it attached to an appserver.  The appserver processes much faster than Access can.  If you hookup your kid's little red wagon to the trailer hitch of your Ford F150, the wagon most likely will fail at some point.