Setting up Datasource For MSSQL with failover option

588 views
Skip to first unread message

Grant Griffith

unread,
Sep 29, 2015, 2:52:16 PM9/29/15
to Lucee
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.

Chris Blackwell

unread,
Oct 10, 2015, 1:12:35 PM10/10/15
to Lucee

The way to do it would be to add the appropriate jdbc driver jar file to the class path and create a datasource of type 'other'. Then manually enter the driver class name and jdbc url with whatever failover options it permits.

 


On Tue, 29 Sep 2015 19:52 Grant Griffith <ggri...@gmail.com> 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.

--
See Lucee at CFCamp Oct 22 & 23 2015 @ Munich Airport, Germany - Get your ticket NOW - http://www.cfcamp.org/
---
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/4a46e9e7-2d28-4127-8fcc-c10a39741a9c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Terry Whitney

unread,
Oct 12, 2015, 3:26:03 PM10/12/15
to Lucee
You could create an MS SQL Cluster, at the very least mirror databases
then assign svr records to the cluster, and point your DSN source at the svr record.

With Dynamic DNS management controls (there are many) you could always be pointed to an "up" server provided your cluster is up, or your web farm has internet access.

Grant Griffith

unread,
Dec 2, 2015, 10:21:35 AM12/2/15
to Lucee
Thanks for the info all, but does anyone have a driver that works with Lucee and supports AlternateServers option?  It does not appear "com.microsoft.jdbc.sqlserver.SQLServerDriver" support this option.  I have a mirrored setup and need to auto failover when one server is down.

Thanks!
Grant

Chris Blackwell

unread,
Dec 2, 2015, 12:15:23 PM12/2/15
to Lucee

No idea if it works with with sql server but with mysql we've had good results using haproxy in front of a pair of servers in master-master


--
Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html

---
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

Andrew Rankin

unread,
Dec 2, 2015, 1:42:14 PM12/2/15
to Lucee
I have had success with MS SQL server 2012 in HA mirror with automatic failover using a witness.  In Lucee admin, I created the datasouce using the 'Other' datasource type and then entering the class path of the ms jdbc driver (com.microsoft.jdbc.sqlserver.SQLServerDriver) with a connection string similar to this jdbc:sqlserver://xx.xx.xx.xx:1433;instanceName=NameOfSQLInstance;DATABASENAME=myDatabase;sendStringParametersAsUnicode=true;SelectMethod=direct;failoverpartner=xx.xx.xx.xx:1433

HTH
Andrew

Grant Griffith

unread,
Dec 2, 2015, 4:29:24 PM12/2/15
to lu...@googlegroups.com
Thanks Andrew,

Seems I can connect fine when the primary is up, but fails if it is the secondary that is active.  If I change the string to have the active server as primary it works just fine...

Can you let me know what version of the jdbc driver you are running?

Thanks Again,
Grant

--
Love Lucee? Become a supporter and be part of the Lucee project today! - http://lucee.org/supporters/become-a-supporter.html
---
You received this message because you are subscribed to a topic in the Google Groups "Lucee" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/lucee/fYVcCAESXew/unsubscribe.
To unsubscribe from this group and all its topics, send an email to lucee+un...@googlegroups.com.

To post to this group, send email to lu...@googlegroups.com.

Andrew Rankin

unread,
Dec 2, 2015, 5:36:01 PM12/2/15
to Lucee
Hi Grant,

Have you synced the logins on both your SQL server instances (primary and mirror) as documented in this article https://www.mssqltips.com/sqlservertip/1166/login-failures-connecting-to-new-principal-after-sql-server-database-mirroring-failover/ ?

I am running Lucee 4.5.2.018 final.  The jar manifest for the mssqlserver driver is  version 1.0

Brook Davies

unread,
Oct 26, 2016, 8:27:59 PM10/26/16
to Lucee
Has anyone been able to get this to work?

Grant Griffith

unread,
Oct 27, 2016, 8:22:40 AM10/27/16
to Lucee
Class: com.microsoft.jdbc.sqlserver.SQLServerDriver
Driver: jdbc:sqlserver://SERVER\INSTANCE;databaseName=DBNAME;SelectMethod=direct;sendStringParametersAsUnicode=true;querytimeout=0;MaxPooledStatements=1000;failoverPartner=SERVER\INSTANCE

Just replace SERVER\INSTANCE and DBNAME to fit your solution.

This works for us mostly.  We do have rare instances where during failover Lucee gives up and shutdown and a restart is required.  Still working to find out why this occurs but think it is related to how long the failover takes.

Hope this helps,
Grant

Brook Davies

unread,
Oct 28, 2016, 1:23:10 PM10/28/16
to lu...@googlegroups.com
Cool thank you I will try this. Does this require a certain Java version? Lucee version?

--
Get 10% off of the regular price for this years CFCamp in Munich, Germany (Oct. 20th & 21st) with the Lucee discount code Lucee@cfcamp. 189€ instead of 210€. Visit https://ti.to/cfcamp/cfcamp-2016/discount/Lucee@cfcamp
---
You received this message because you are subscribed to the Google Groups "Lucee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lucee+unsubscribe@googlegroups.com.

To post to this group, send email to lu...@googlegroups.com.

Tim Holt

unread,
Dec 20, 2016, 3:08:39 PM12/20/16
to Lucee
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-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.
Reply all
Reply to author
Forward
0 new messages