SQL Server Driver question

[paulm1677332]

Hi,

Just wondering if anyone has any experience of upgrading the Microsoft SQL Server driver from the Adobe one to the Microsoft one and if there was any improvement in performance. We did it but ran into issues with identity fields not being returned from insert queries but if there was a noticeable improvement in performance from them its something we would revisit.

Thanks,

Paul

[Dave Watts]

I haven't done this recently, but have done it in the past. My general recommendation is that if the DataDirect driver works for you, you probably shouldn't switch unless you have a lot of free time. The DataDirect driver is sold by DataDirect to people who want the best performance, and the Microsoft driver is ... not. That doesn't mean that the Microsoft driver won't perform well, but it does mean that you're likely to get a better experience with DataDirect.

That said, a lot of performance issues really boil down to exactly what you're doing in your database calls, and there are probably plenty of cases where either one works better than the other. But generally, rather than do A/B testing between the two, you'd be better off just looking at how you're using the database in the first place and see if you can reduce or optimize that.

Dave Watts, Eidolon LLC

[James Moberg]

I've been using the Microsoft JDBC Driver with CF10, CF11 and CF2016 without any issues. I'm using Java 11 and currently use 8.4.1 in productions.  (I'm in the process of testing the v9.5 driver.) Switching to the JDBC Driver also enabled us to connect to newer SQL Servers that the DataDirect driver didn't support at the time.  You can install the JDBC driver and create additional datasources to run in tandem in order to compare functionality.  I haven't found any differences with existing apps since we started using it back in 2016.  Our applications support multiple languages, geodata datatypes, JSON queries (using JSON_VALUE) and perform bulk imports directly from physical files.

Here's the Microsoft JDBC driver:

https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server

Here's the basic connection information that we use in CFAdmin:

JDBC URL: jdbc:sqlserver://IP_or_HOSTNAME:PORT;databaseName=DBName_To_Use

Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver

More info (and the reason why we started using it):

• https://www.hass.de/content/coldfusion-10-how-configure-data-sources-microsoft-jdbc-driver-40-sql-se...
• https://www.hass.de/content/coldfusion-10-macromedia-database-drivers-leaking-memory