I am wondering wether I should migrate to SQL server or not. What would the benefit be ? My main concern of course is what happen if today 30 or 40 visitors connect at the same time and make the same request. Is there a critical level where I should switch to SQL ?

Although I have already interrogated my hosting provider about that, its answers where quite unclear.
What would your advice be ?

------------------
Marc Van Uytvanck
Brussels

i would work on switching it over now.

------------------
storm...
"Someone put forth the proposition that you can patition the lord with prayer, patition the lord with prayer...YOU CANNOT PATITION THE LORD WITH PRAYER"

quote:

---

Originally posted by storm:
no offense, but even with 45 hit's a day i'm surprised access hasn't crached already. access is usually good for 3-5 concurrent connections over the web before unpredictable results start to occur.

i would work on switching it over now.

---

"Microsoft Access is a desktop database, and not intended or designed for high-volume, multi-user use. Its performance under such conditions frequently becomes unpredictable and unstable. The simplicity of creating and maintaining databases in Access, and the facility of deployment entices many users to employ it for Web application serving in spite of its limitations. Even Microsoft recommends that Access be used "solely for development purposes and not for production". In short, there is no substitute for robust client/server database server products like Microsoft SQL Server, Oracle, Sybase, Informix and DB2 in anything but light-service Web applications.

Keeping these inherent limitations in mind, there are many different configurations for Microsoft Access, of varying robustness. The primary differentiator is the connection mechanism employed. ColdFusion natively supports the three primary connection mechanisms for Access connectivity. They are (in order of robustness):

OLE DB – ColdFusion Administrator Configured (requires MDAC 2.1)
COM Objects (via the CFOBJECT tag)
ODBC – ColdFusion Administrator or ODBC Control Panel (System DSN) Configured"

the fact that yours hasn't crashed is good for you and i don't have a definitive answer other than there are a lot of variables invloved, how you have your site designed the type of content being pulled from the database, number of concurrent connectios, any customizations you may have done to th db, etc..

quote:

---

Originally posted by storm:
here's a good response from a cold fusion kb article:

"Microsoft Access is a desktop database, and not intended or designed for high-volume, multi-user use. Its performance under such conditions frequently becomes unpredictable and unstable. The simplicity of creating and maintaining databases in Access, and the facility of deployment entices many users to employ it for Web application serving in spite of its limitations. Even Microsoft recommends that Access be used "solely for development purposes and not for production". In short, there is no substitute for robust client/server database server products like Microsoft SQL Server, Oracle, Sybase, Informix and DB2 in anything but light-service Web applications.

Keeping these inherent limitations in mind, there are many different configurations for Microsoft Access, of varying robustness. The primary differentiator is the connection mechanism employed. ColdFusion natively supports the three primary connection mechanisms for Access connectivity. They are (in order of robustness):

OLE DB – ColdFusion Administrator Configured (requires MDAC 2.1)
COM Objects (via the CFOBJECT tag)
ODBC – ColdFusion Administrator or ODBC Control Panel (System DSN) Configured"

the fact that yours hasn't crashed is good for you and i don't have a definitive answer other than there are a lot of variables invloved, how you have your site designed the type of content being pulled from the database, number of concurrent connectios, any customizations you may have done to th db, etc..

---

------------------
Marc Van Uytvanck
Brussels

Here is the opinion of Charles Carroll a leading ASP/Database expert. You can find him at www.learnasp.com He has a test on his site which usually shows a max of 64 connections in a couple of seconds.

Date: Wed, 08 Apr 1998 10:08:45 -0700
From: Charles Carroll <cc@thebestweb.com>
Subject: Re: [asp advanced] Access vs. SQL Server

Note to all on the list. This topic has been beaten to death on all other lists with few positive results so I think it should NOT BE CONDUCTED AGAIN on this list.

The overall conclusion is:

There is anecdotal evidence that Access can be used in medium to large websites. I.e. "I use Access with no trouble on my website with ____ users and ____ numbers of hits per day". Anecdotal evidence needs to be taken
with a grain of salt generally though it may be true for the speaker.
There is anecdotal evidence it can be a disaster depending on your users's viewing/editing/traffic patterns. "I had severe problems with concurrent users/memory leaks/ using Access on my website with ____ users and ____
numbers of hits per day. I switched to SQL server and the trouble went away"
There is quite a bit of hard evidence SQL Server is more robust and reliable and can scale quite large. It's advantages are numerous:
a. You can remotely attach tables via TCP/IP with SQL server as long as you have installed the "client tools" (just install SQL Server and ask for "client tools only" it will even work on a Win95 machine. You could have a local ASP script (being served on PWS) or a VB program that had a DSN to a live SQL server database (for example a 5 meg+ table) at your ISP. In Access you would actually have to FTP THE WHOLE DATABASE to YOUR machine since there is no TCP/IP binding to the ODBC driver with Access.
b. You have very low overhead tools like I/SQL W that can initiate all their tasks from your local machine even though you may have no SQL server software on your machine only at your host. With Access all tools require all of Access on your machine.
c.
It was built for multiple users and offers many tools to pinpoint what user is doing what to a database and what kind of locks they have at a given moment. Access just fails when concurrency problems occur and there are no tools to see which connections caused it.
SQL Server IS much better but requires a BETTER ISP and a more expensive license. If a host limits you to Access it is a SEVERE limitation as everyone eventually moves up (i.e. Access to SQLServer) but no one moves down (SQL server to Access), and the switch usually is time consuming in terms of converting queries and the learning curve.