I have Cold Fusion version 7 (32 bit) currently connected to SQL 2000 and 2005 (32 bit) using a DSN connected.
We are wanting to upgrade the databases to SQL 2012 which is (64 bit)
So we want to connect the (32 bit) Cold Fusion application server to a (64 bit) SQL database using a DSN connection.
Is this possible? If so how?
There is no real difference in connecting to 32-bit versus 64-bit SQL Server via the JDBC drivers for ColdFusion. Even if ColdFusion and SQL Server reside on the same computer (which is not generally recommended for performance and security reasons), there is no problem connecting 32-bit ColdFusion to 64-bit SQL Server, and vice-versa.
Thank you Carl. I talked to our Database Administrator. We will be connecting CF7 and SQL2012 using a DNS connection using ODBC connection inside the CF admin. The database administrator said no problem with connecting a 32 bit application to a 64 bit SQL 2012 database because the 64 bit database will read both 64 and 32 bit requests.
The possible issue is that the databases we currently have is SQL 2000 and 2005 (both 32 bit). We will need to convert these databases to SQL 2012 (64 bit).
The question is, that when the 64 bit database brings back the 64 bit data back to the 32 bit CF application software, that the CF application may not read correctly all the data since the data is now (64 bit) and that some of the pages will work and some may not. Since we have about 200 CF sites total running, it would be a nightmare to check and test each site before making the change.
Any comment about this?
There really is no such thing as a 32-bit SQL Server database vs. a 64-bit one - it's the SQL Server engine that is either 32- or 64-bit. You should be able to swap databases (depending on size) back and forth between 32- and 64-bit instances of SQL Server. You'll get better performance of large or heavily used databases on 64-bit because it can address much more memory than on 32-bit. As far as ColdFusion is concerned, it is just making a request to the database server - and the server sends back a response. There is no "bitness" on the response - 32-bit and 64-bit SQL Server should return data exactly the same way.
Your DBA mixed a lot of terms together in this statement:
We will be connecting CF7 and SQL2012 using a DNS connection using ODBC connection inside the CF admin.
I was at first going to say that you should be connecting via JDBC (as I said in my previous response). But since you are on CF7 (which is way beyond end-of-life at this point in time), the JDBC drivers for SQL Server 2012 likely won't work as CF7 runs on an old, unsupported version of Java. So ODBC may be your only viable option here. The DNS bit just means you are referring to the SQL Server by hostname rather than IP address, which is fairly normal.
I'm sure you've already considered this, but you should really consider upgrading to ColdFusion 10 or 11. CF7 is not secure, and you'll likely continue to run into compatibility issues with other systems going forward. CF10 and CF11 both natively support SQL Server 2012, 2012R2, and I believe 2014. They also run on the latest versions of Java, and are far more secure (especially if you follow the lockdown guides.
I checked out the system specs...........
Server Product ColdFusion MX
Operating System Windows 2003
Java Specification Version 1.4
Operating System Windows 2003
Is it possible to free a free upgrade to CF 8 or 9, since we already have a license for Version 7,0,2,142559?
Any chance in getting a free upgrade and doing more?
My guess is no, since CF7 has been off support for many years, and 8 and 9 are both beyond Adobe's support (they had reached End of Life). You would have to buy CF10 or CF11.