ColdFusion and SQL 2005 Mirroring
After much experimentation, I have finally managed to get Microsoft SQL 2005 Mirroring with automatic failover working with ColdFusion MX 6/7.
The following steps describe how to setup ColdFusion.
- Install the latest version of the DataDirect drivers by following these instructions on the Adobe web site – Updated DataDirect JDBC drivers (version 3.5)
- Create a new ColdFusion datasource of type “Other”.
- Enter the following settings, replacing XXXs with your details;
| CF Data Source Name | XXX |
| JDBC URL | jdbc:macromedia:sqlserver://192.168.1.XXX:1433; databaseName=XXX;SelectMethod=direct; sendStringParametersAsUnicode=false; MaxPooledStatements=1000; AlternateServers=(192.168.1.XXX:1433) |
| Driver Class | macromedia.jdbc.MacromediaDriver |
| Driver Name | SQL 2005 |
| User Name | XXX |
| Password | XXX |
You should now be able to manually failover the mirrored database and, after an initial ColdFusion connection reset error, your application to run as normal.
Simple when you now how! 
May 14th, 2008 at 8:16 pm
Cool one. it worked Thank you.
May 16th, 2008 at 12:44 am
Hi,
We find that you also have to untick “Maintain Connections”. Once you do this, you won’t even get an error message for the first request. All that will happen is the first request may take a couple of seconds longer and then start working.
Of course on a site with massive traffic, not maintaining connections, could cause a performance issue (but we haven’t experienced any problems).
Cheers
Mark
August 19th, 2009 at 3:05 pm
Have you had any experience with encrypting the EncryptionMethod Property of loginSSL (Microsoft SQL Server 2005 and higher: Login request is encrypted, but data is not encrypted)? Was wondering what the syntax was for enabling this? I’m thinkig it is EncryptionMethod=loginSSL but not sure. I’m using the docs here (http://media.datadirect.com/download/docs/jdbc/alljdbc/wwhelp/wwhimpl/js/html/wwhelp.htm) under Data Encryption.
January 3rd, 2012 at 10:56 pm
Have you tried updating the JDBC Drivers at all?
http://kb2.adobe.com/cps/421/ded4216b.html
We also found that maintaining connections can cause failed requests and are looking at checking into JDBC driver updates… anyone have experience with that?
Good and still very relevant post by the way.
Thank you,