Cloud transient DB connection issues

I have a large application that has thousands of cfquery tags throughout
the code base. One version of it runs in a datacenter with MS Sql Server,
and another version of it is running in the Azure cloud.
The datacenter version is very solid with no connection issues but in the
cloud version,

Azure SQL DB connections are a little more transient and subject to minor
outages, and I have connection problems every so often.
Azure Support says this is normal for this cloud environment and that I
should recode my app with retry logic to account for it.

I was wondering if there was anything already built into Railo/Lucee
middleware to assist globally for transient connection errors, rather than
having to put retry logic everywhere throughout my app.

Surely someone else has run into this and had to deal with it?

See https://azure.microsoft.com/en-us/documentation/articles/sql-database-connectivity-issues/

I was considering writing a custom JDBC driver to do the retry logic right
in the driver, but maybe there is a better way?
And not sure what the implication would be for a transaction that has many
cfqueries in it, but I’m betting that 99.9% of the time the failure would
occur on the first operation in a transaction (given that transactions are
relatively short) and could be fully recovered via the middleware level.

You may want to try the MSSQL 6.0 driver.
https://github.com/goochjj/JDBC-extension-factory/tree/MSSQL-6.0.7507

I haven’t issued a pull for this yet until I can deal with MSSQL 2005
compatibility. Checkout, ant, pull the 6.0.7507 lex from dist and dump in
lucee-server/deploy

https://msdn.microsoft.com/en-us/library/aa342325(v=sql.110).aspx

I don’t know if it’ll help or not. I do know the changelog has items with
Azure Active Directory, always encrypted, and availability groups.

-GOn Thu, Jul 21, 2016 at 11:02 AM, Arthur Blake <@Arthur_Blake> wrote:

I have a large application that has thousands of cfquery tags throughout
the code base. One version of it runs in a datacenter with MS Sql Server,
and another version of it is running in the Azure cloud.
The datacenter version is very solid with no connection issues but in the
cloud version,

Azure SQL DB connections are a little more transient and subject to minor
outages, and I have connection problems every so often.
Azure Support says this is normal for this cloud environment and that I
should recode my app with retry logic to account for it.

I was wondering if there was anything already built into Railo/Lucee
middleware to assist globally for transient connection errors, rather than
having to put retry logic everywhere throughout my app.

Surely someone else has run into this and had to deal with it?

See
https://azure.microsoft.com/en-us/documentation/articles/sql-database-connectivity-issues/

I was considering writing a custom JDBC driver to do the retry logic right
in the driver, but maybe there is a better way?
And not sure what the implication would be for a transaction that has many
cfqueries in it, but I’m betting that 99.9% of the time the failure would
occur on the first operation in a transaction (given that transactions are
relatively short) and could be fully recovered via the middleware level.


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/04a594d2-fdf1-4b75-a0d0-07d059ec839e%40googlegroups.com
https://groups.google.com/d/msgid/lucee/04a594d2-fdf1-4b75-a0d0-07d059ec839e%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

Thanks Joseph, Bilal for the ideas.
I’m on an older version of Railo but hope to update to the latest Lucee at
some point.
Some of the same ideas still apply though to the older version so I may try
them out.

Arthur:
As far as I know Azure instances do not allow continuous connections. This
is not related to outages, that is by design as far as I understand. You
will get disconnected automatically if idle too long. So, you cannot
maintain the connections in open status.
In ACF you needed to uncheck the “maintain connection” checkbox under the
datasource.
In Lucee I would suggest you lower the connection timeout to zero minutes
and also check the Validate connection before use option.

This should ensure that your application is unaware of the transient nature
of connections.

HTH
BilalOn Thursday, July 21, 2016 at 11:02:45 AM UTC-4, Arthur Blake wrote:

I have a large application that has thousands of cfquery tags throughout
the code base. One version of it runs in a datacenter with MS Sql Server,
and another version of it is running in the Azure cloud.
The datacenter version is very solid with no connection issues but in the
cloud version,

Azure SQL DB connections are a little more transient and subject to minor
outages, and I have connection problems every so often.
Azure Support says this is normal for this cloud environment and that I
should recode my app with retry logic to account for it.

I was wondering if there was anything already built into Railo/Lucee
middleware to assist globally for transient connection errors, rather than
having to put retry logic everywhere throughout my app.

Surely someone else has run into this and had to deal with it?

See
https://azure.microsoft.com/en-us/documentation/articles/sql-database-connectivity-issues/

I was considering writing a custom JDBC driver to do the retry logic right
in the driver, but maybe there is a better way?
And not sure what the implication would be for a transaction that has many
cfqueries in it, but I’m betting that 99.9% of the time the failure would
occur on the first operation in a transaction (given that transactions are
relatively short) and could be fully recovered via the middleware level.