Cloud transient DB connection issues

[Arthur Blake]

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.

[Joseph Gooch]

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.

-G

--
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+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/04a594d2-fdf1-4b75-a0d0-07d059ec839e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Bilal]

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

Bilal

[Arthur Blake]

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.