We had this issue with Adobe ColdFusion 2016 and MSSQL Server 2008 R2.
I’ll post here in hopes it might help someone else figure this out too.
We finally got the connection string in place so that the application is communicating with the mirrored servers.
The first step is to create a user credential in SSMS that is allowed to access the database(s) in question. With one extra quirk. You must set up the user credential on the Principal server then run a command to fetch the username, hashed password, and *SID *then inserting those three things into the Mirrored server. (see this page for details: Login Failures After SQL Server Database Mirroring Failover)
In ColdFusion, the database connection string is almost identical to the regular MSSQL server connection string but has only one extra parameter in the neo-datasource.xml configuration file. The only difference is this:
AlternateServers=(xxx.xx.xxx.xxx:1433);
You can edit the new-datasource.xml file to add this or when creating the datasource in CF Admin use the type “Other” as the driver type to manually enter the JDBC URL.
Here is the JDBC URL:
jdbc:macromedia:sqlserver://xxx.xx.xxx.XXX:1433;databaseName=theDatabaseName;
SelectMethod=direct;sendStringParametersAsUnicode=false;
MaxPooledStatements=1000;AlternateServers=(xxx.xx.xxx.YYY:1433);jdbcbehavior=0
The Driver Class is macromedia.jdbc.MacromediaDriver and the Driver Name doesn’t seem to be used.
Use the user credential username and password that you created that has the same SID on both servers.
The reason that the SID is important is that if a failover happened (or were manually triggered from SSMS) then the user credential used by the application would lose all of its mappings and permissions in the database.
Once that was done, we could trigger a failover manually with only one interruption in the application as CF dropped one connection to get the other connection.
I hope this helps someone.On Tuesday, September 29, 2015 at 12:52:16 PM UTC-6, Grant Griffith wrote:
Looking to find a driver that allows the setup of failover for a MS SQL
database connection. Used “com.seefusion.Driver” Driver Class in Cold
Fusion 9 environment but can’t seem to find this option on CentOS/Lucee
setup. Using that there was a option for AlternateServers with JDBC URL.
Anyone have any insight on option it would be greatly appreciated.