Setting up Datasource For MSSQL with failover option

[Grant Griffith]

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]

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]

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]

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]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/3f742ab4-f0d9-4f6b-81b2-1bf244f548dc%40googlegroups.com.

[Andrew Rankin]

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]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/6e9c7065-08f5-4040-8a59-43bddd9cd935%40googlegroups.com.

[Andrew Rankin]

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]

Has anyone been able to get this to work?

[Grant Griffith]

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]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/6a7d0286-fed2-4e26-82ac-f0cd5bee6dc5%40googlegroups.com.

[Tim Holt]

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.