MSSQL Failover and Connection String

[spitfire01]

Hi,
I am using Coldfusion 8. In CFIDE I saw "Connection String" parameter under Advanced setting for MSSQL Data connection.

I was wondering if we can use it for failover using a connection string like:
"Data Source=server1;FailoverPartner=server2;Initial Catalog=db_name;Uid=db_user;Pwd=db_pass;"

Where server2 is mirrored database.

thanks in advance :)

[Sébastien_Close]

Hi,

You have to create a specific dsn to use this feature.

First 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://ipaddress_server1:1433;databaseName=XXX;
SelectMethod=direct;sendStringParametersAsUnicode=false;
MaxPooledStatements=1000;AlternateServers=(ipaddress_server2:1433)

Driver Class : macromedia.jdbc.MacromediaDriver
Driver Name: SQL2005
User Name : XXX
Password : XXX

If you use unicode for string (nvar, ntext, ...), you have to change sendStringParametersAsUnicode=false to true

Enjoy!
Sébastien

[spitfire01]

Thanks ..that works really cool!

[BKBK]

Hi @spitfire01, please kindly mark the correct answer. It will surely help someone else in future.

[holttim]

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

[leon26134105y72y]

Thanks a lot this was really useful