Copy link to clipboard
Copied
Copy link to clipboard
Copied
Copy link to clipboard
Copied
Copy link to clipboard
Copied
Hi @spitfire01, please kindly mark the correct answer. It will surely help someone else in future.
Copy link to clipboard
Copied
To expand on this answer, I'd like to add the following:
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: https://www.mssqltips.com/sqlservertip/1166/login-failures-connecting-to-new-principal-after-sql-ser...)
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
Copy link to clipboard
Copied
Thanks a lot this was really useful