MsSQL

Not much has changed since ColdFusion 10: How to configure data sources with Microsoft JDBC Driver 4.0 for SQL Server, but a small detail is now required to connect to your existing MsSQL Servers.

This article explains how you are able to use the Microsoft JDBC Driver for SQL Server under ColdFusion. The background why you should do this is the bug Macromedia database drivers leaking memory. This is a very serious bug as you can see in the linked article. Your servers will be very unstable if you run with the Adobe build in drivers for Microsoft SQL. Until today we have not found any single issue at all with the replaced drivers. It is really a drop in replacement without any side effect.

Adobe has released ColdFusion 2016 Final some time ago. Well we took some time to give it a try and since 3 days we do not have so much fun.

The most heavy bug I can share is that CF 2016 is failing to execute SQL statements that has comments inside. This happens only with -- comments. I'm not sure if this is limited to Microsoft SQL. It has for sure nothing to do with the Microsoft JDBC driver you are using. See Adobe bugtracker ColdFusion 2016 - Bug 4126398: SQL comments inside CFQuery are not ignored.

Broken:

-- Using a comment after the code on a line.
USE Northwind
GO
UPDATE Products
SET UnitPrice = UnitPrice * .9 -- Try to build market share.
GO

Workaround:

USE Northwind
GO
SELECT * FROM Employees
/* The
GO in this comment causes it to be broken in half */
SELECT * FROM Products
GO

Solution: You can change all your code... (just kidding) or ask support. They have a hotfix with file name chf20160001.jar that works and will be integrated in Updater 1.

This article explains how you are able to use the Microsoft JDBC Driver 4.0 for SQL Server under ColdFusion. The background why you should do this is the bug Macromedia database drivers leaking memory. This is a very serious bug as you can see in the linked article. Your servers will be very unstable if you run with the Adobe build in drivers for Microsoft SQL. Until today we have not found any single issue at all with the replaced drivers. It is really a drop in replacement without any side effect.

For about several months we are trying to figure out a bug with Adobe. Support is telling me they are unable to reproduce the issue as always. The problem is that the DataDirect Driver for Microsoft SQL that Adobe delivers with ColdFusion 10 and 11 product seems to have a major memory leak.

Heap with Adobe / DataDirect MsSQL Driver

Very high and bad memory usage compared to Microsoft driver. Memory usage will grow over longer period of time until system goes out of memory. CPU usage will also grow extremely over time to 50% or more where a normal load is 5-10% when the service is restarted. This is one extreme example:

If you are one of the HP DataProtector 6.11 customers and you have scheduled MsSQL transaction log and full backup to start simultaneously you will experience the below issue. We found this after an upgrade to latest 6.11 in November 2010 and a hotfix was build for us on 19. November 2010. We are running with this patch for 8 weeks successfully.

Cell Manager patch level:

Client SQL Server patch level: