Setting up Datasource For MSSQL with failover option

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.

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 <@Grant_Griffith> 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+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/4a46e9e7-2d28-4127-8fcc-c10a39741a9c%40googlegroups.com
https://groups.google.com/d/msgid/lucee/4a46e9e7-2d28-4127-8fcc-c10a39741a9c%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

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.On Tuesday, September 29, 2015 at 2:52:16 PM UTC-4, Grant Griffith 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.

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!
GrantOn Monday, October 12, 2015 at 3:26:03 PM UTC-4, Terry Whitney wrote:

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.

On Tuesday, September 29, 2015 at 2:52:16 PM UTC-4, Grant Griffith 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.

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-masterOn Wed, 2 Dec 2015 15:21 Grant Griffith <@Grant_Griffith> wrote:

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

On Monday, October 12, 2015 at 3:26:03 PM UTC-4, Terry Whitney wrote:

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.

On Tuesday, September 29, 2015 at 2:52:16 PM UTC-4, Grant Griffith 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.


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+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/3f742ab4-f0d9-4f6b-81b2-1bf244f548dc%40googlegroups.com
https://groups.google.com/d/msgid/lucee/3f742ab4-f0d9-4f6b-81b2-1bf244f548dc%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

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,
GrantOn Wed, Dec 2, 2015 at 1:42 PM, Andrew Rankin <@Andrew_Rankin> wrote:

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

On Wednesday, December 2, 2015 at 7:21:35 AM UTC-8, Grant Griffith wrote:

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

On Monday, October 12, 2015 at 3:26:03 PM UTC-4, Terry Whitney wrote:

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.

On Tuesday, September 29, 2015 at 2:52:16 PM UTC-4, Grant Griffith 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.


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+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/6e9c7065-08f5-4040-8a59-43bddd9cd935%40googlegroups.com
https://groups.google.com/d/msgid/lucee/6e9c7065-08f5-4040-8a59-43bddd9cd935%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

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.0On Wednesday, December 2, 2015 at 1:29:24 PM UTC-8, Grant Griffith wrote:

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

On Wed, Dec 2, 2015 at 1:42 PM, Andrew Rankin <and...@shabbaweb.com <javascript:>> wrote:

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

On Wednesday, December 2, 2015 at 7:21:35 AM UTC-8, Grant Griffith wrote:

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

On Monday, October 12, 2015 at 3:26:03 PM UTC-4, Terry Whitney wrote:

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.

On Tuesday, September 29, 2015 at 2:52:16 PM UTC-4, Grant Griffith 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.


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 <javascript:>.
To post to this group, send email to lu...@googlegroups.com <javascript:>
.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/6e9c7065-08f5-4040-8a59-43bddd9cd935%40googlegroups.com
https://groups.google.com/d/msgid/lucee/6e9c7065-08f5-4040-8a59-43bddd9cd935%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

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
AndrewOn Wednesday, December 2, 2015 at 7:21:35 AM UTC-8, Grant Griffith wrote:

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

On Monday, October 12, 2015 at 3:26:03 PM UTC-4, Terry Whitney wrote:

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.

On Tuesday, September 29, 2015 at 2:52:16 PM UTC-4, Grant Griffith 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.

Has anyone been able to get this to work?On Tuesday, September 29, 2015 at 11:52:16 AM UTC-7, Grant Griffith 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.

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,
GrantOn Wednesday, October 26, 2016 at 8:27:59 PM UTC-4, Brook Davies wrote:

Has anyone been able to get this to work?

On Tuesday, September 29, 2015 at 11:52:16 AM UTC-7, Grant Griffith 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.

Cool thank you I will try this. Does this require a certain Java version?
Lucee version?On Thu, Oct 27, 2016 at 5:22 AM, Grant Griffith <@Grant_Griffith> wrote:

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

On Wednesday, October 26, 2016 at 8:27:59 PM UTC-4, Brook Davies wrote:

Has anyone been able to get this to work?

On Tuesday, September 29, 2015 at 11:52:16 AM UTC-7, Grant Griffith 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.


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 lucee@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/
msgid/lucee/6a7d0286-fed2-4e26-82ac-f0cd5bee6dc5%40googlegroups.com
https://groups.google.com/d/msgid/lucee/6a7d0286-fed2-4e26-82ac-f0cd5bee6dc5%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.

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.On Tuesday, September 29, 2015 at 12:52:16 PM UTC-6, Grant Griffith 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.