0
MSSQL Failover and Connection String
New Here
,
/t5/coldfusion-discussions/mssql-failover-and-connection-string/td-p/516715
Dec 11, 2007
Dec 11, 2007
Copy link to clipboard
Copied
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 :)
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 :)
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
New Here
,
/t5/coldfusion-discussions/mssql-failover-and-connection-string/m-p/516716#M47310
Dec 11, 2007
Dec 11, 2007
Copy link to clipboard
Copied
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
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
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
spitfire01
AUTHOR
New Here
,
/t5/coldfusion-discussions/mssql-failover-and-connection-string/m-p/516717#M47311
Dec 11, 2007
Dec 11, 2007
Copy link to clipboard
Copied
Thanks ..that works really cool!
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
Community Expert
,
/t5/coldfusion-discussions/mssql-failover-and-connection-string/m-p/516719#M47313
Dec 26, 2016
Dec 26, 2016
Copy link to clipboard
Copied
Hi @spitfire01, please kindly mark the correct answer. It will surely help someone else in future.
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
New Here
,
/t5/coldfusion-discussions/mssql-failover-and-connection-string/m-p/516718#M47312
Dec 20, 2016
Dec 20, 2016
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
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more
New Here
,
LATEST
/t5/coldfusion-discussions/mssql-failover-and-connection-string/m-p/13201394#M193267
Sep 15, 2022
Sep 15, 2022
Copy link to clipboard
Copied
Thanks a lot this was really useful
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting.
Learn more

