Has web application become too successful for SQL Anywhere?

[Former Member]

One of our websites (which until recently was using SQL Anywhere 10 for storage) is now used by 10s of people at a time and the database has grown to 48 MB; I recently moved the data to SQL Anywhere 16 because we were beginning to get "Timed out trying to establish connection" errors and I hoped that might help. Doubters amongst us think that we have to move the data to SQL Server .. that SQL Anywhere 16 is not robust enough to do the job. Horrors.. what say you? I'd appreciate the perspective of those "in the know"

(We also upgraded Windows Server for the database VM from 2003 to 2012)

[JasonHinsperger · Accepted solution]

SQL Anywhere has customers running with 1000's of users and 100's of gigabytes of data, so it  should be able to easily handle the load you are describing, unless you have some sort of bottleneck in the database schema design that is causing an issue.

I would recommend you spend some time understanding the root cause of the timeout problem, as it it is likely resolvable.  For example, are you using database connection pooling on the web server?  If not, you should be.

If you would like further feedback on where the problem may lie, post some more detail on the environment, how connections are made and what kinds of operations are going on

--Jason Hinsperger

Product Manager

SQL Anywhere

[Former Member · Accepted solution]

Thanks Jason.. I was hoping I'd get that response.

Since March 2011, the production setup has consisted of 2 VMWare VMs.. each running Windows 2003 R2 SP2

the webserver running IIS 6 and ColdFusion 8 (Java Version: 1.6.0_01)**

the database server running SQL Anywhere 10

I was never able to get the SQL Anywhere 10 JDBC 3 driver to work with ColdFusion 8 so backed off to the jdbc-odbc bridge.

Making the jdbc-odbc bridge functional required

1> putting dbjodbc10.dll and jodbc.jar into a folder in the ColdFusion class path* and

2> When defining Data Sources in ColdFusion I had to

a>  choose "Other" from the driver list (since Adobe has not tested any SQL Anywhere drivers) and

b>  fully specify the URL: jdbc:ianywhere:driver=SQL Anywhere 10;dbn=demo;links=tcpip(HOST=nn.nn.nn.nn;PORT=2620);eng=demo  

  [where nn and demo are as you would expect]

c>  specify ianywhere.ml.jdbcodbc.jdbc3.IDriver as the Driver Class

d>  provide username and password where requested

I've continued to use same setup even after moving the data to a W2012 Server and SQL Anywhere 16 when I couldn't get the JDBC4 to load (seems ColdFusion does NOT want to have a blank in the Driver Class field of the DataSource definition screen)

Relevant ? settings?

Simultaneous request limit: 50

CFThread Pool Size:  10

Max number of running JRUN threads: 50

Max number of queued JRUN threads: 1000

Database specifics for the largest database involved (there are several):

Login timeout:  30 seconds

Timeout:  1200 seconds

Maintain connections across client requests:  Yes**

Interval:  420 seconds

Disable connections:  No

I believe the connection timeout tends to occur when there is a loop of activity which includes saving a copy of an html page (which is dynamically constructed) as well as associated database updates for each page ..for more than 5 html pages in a row

*I believe I got the SQL Anywhere 10 ODBC driver installed by installing SQL Anywhere 10 on the webserver; I was thus enabled to use the Windows Driver Manager to verify that a connection could be made

** from http://helpx.adobe.com/coldfusion/kb/database-connections-handled-coldfusion.html

"Maintain Database Connections" is set for a data source, ColdFusion keeps the connection open after its first connection to the database. It does not log out of the database after this first connection.

[JasonHinsperger · Accepted solution]

Unfortunately I don't know anything about ColdFusion, so I am not sure why the JDBC drivers are not working.  I do know that the jdbc-odbc bridge is significantly slower than the SQL Anywhere JDBC driver though.  Have you tried the ColdFusion forums?  If you find someone with ColdFusion knowledge, I can help with SQL Anywhere knowledge to perhaps solve the problem.

Maybe the connection cache needs to be bigger?  Also, if it is not very smart, database connections could time out if they weren't used very often.  To prevent this you can turn off the connection timeout (set the IDLE connection parameter value to 0 in the connect string/datasource)

--Jason

[Former Member · Accepted solution]

Thanks for the followup, Jason. I installed a copy of the newest ColdFusion which is 11, on Windows 2012 and with it I'm able to connect using sybase.jdbc4.sqlanywhere.IDriver in the driver class.and URL jdbc:sqlanywhere:driver=SQL Anywhere 16 etc etc  (I did discover that I needed to run the SQL Anywhere 16 install program on my ColdFusion server.. it wasn't enough to move sajdbc4.jar and dbjdbc16.dll  (and a few others I can't now name) over to that server )

Am I correct in assuming this should be better than the JDBC-ODBC bridge?

Once we get thru testing our code on CF11 and moving it up to production, we'll get a chance to see if the connection problems are still there and if we need to try tuning the

Reuse Connection

Connection pool size

Connection pool timeout

parameters

Thanks again

[JasonHinsperger · Accepted solution]

Yes, using the SQL Anywhere driver will be faster than the bridge.

--Jason