|
Access or SQL server
Hi all
I am in the process of developing a web application and I'd like to know how I can decide whether I can get away with using MS Access as opposed to SQL server. Is there any whitepaper out there with proper comparision, metrics, etc.
Cosmo Kramer
Tuesday, March 9, 2004
Sure....there is a comparison although it is a bit old
http://www.databasejournal.com/features/mssql/article.php/2204341
Code Monkey
Tuesday, March 9, 2004
"A web application"?
This sounds like one of those job interview questions where if you don't start asking clarification questions back, you get marked a "no hire."
Kyralessa
Tuesday, March 9, 2004
Use MSDE and get both
pdq
Tuesday, March 9, 2004
Access doesn't scale. I've run websites on it -- works great for small traffic but medium-sized traffic will drop it to it's knees and cut it's head off.
Almost Anonymous
Tuesday, March 9, 2004
"but medium-sized traffic will drop it to it's knees and cut it's head off."
And piss down its neck.
Krag
Tuesday, March 9, 2004
And fart in its face.
Brutalacus
Tuesday, March 9, 2004
Based on an e-mail he sent me, Cosmo seems to want some clarification of my comments above. So:
1) Is it a web application open to all and sundry? Or will it be deployed only within a company? Will it have hundreds of users all the time? Or just a few users occasionally? You can think of a number of other questions like this which could have a bearing on which database would be best. In my opinion SQL Server would be best regardless, but you have to decide if the added complexity is worth it.
2) What you're really asking about is not Access or SQL Server, but Jet or SQL Server. Jet is the database engine that comes with Access. However, Access can also be used as a front end to many other database engines, including SQL Server.
Kyralessa
Tuesday, March 9, 2004
Out of curiousity, why limit your choices to Access or SQL Server?
In any case, I am in absolute agreement with Kyralessa -- it is a completely vague question, and has no more substantiation than saying "I'm writing an app - should I use SQL Server or Access"? Just because something is a web app in no way implies high demand or high requirements (in fact it's often exactly the opposite -- I've found that where the jet engine falters for fat clients, as each client is through the network manipulating the database file, with a centralized website handling it locally, and with the intrinsic caching and sharing of the jet engine and OLEDB, it is sufficient for some fairly large tasks).
Dennis Forbes
Tuesday, March 9, 2004
Dennis has already asked the question I wanted to ask. Why on earth limit yourself to two products from the same vendor? Particularly given who that is :) What about Oracle, MySQL, postgres...?
Wednesday, March 10, 2004
"Particularly given who that is :) What about Oracle, MySQL, postgres...? "
I trying to read in between the lines on the original post, and I get the impression that this is either a new experience for him, he is a MS centric developer or this is a small project.
If either one of these is true, I don't think Oracle is really a good solution, do you? Beyond the cost issue, Oracle isn't exactly easy for new people to pick up. SQL Server is a helluva lot simpler for a newcomer to get running than Oracle.
mySQL is OK if you can't afford SQL Server and are stuck with Access. But if you are working with mainly MS products and can afford SQL Server, I don't see a reason to go to mySQL.
I'm guessing that anyone that ask a question like the OP did, doesn't have a ton of experience with databases so I would tend to lean more towards the one that are easier to pick up and work better with the environment. (Which I will assume is MS)
As far as scalability and performance goes...once again, I'm going to guess that if he has to ask about Access and SQL Server, then he isn't the lead architect on a huge web development project. But rather, probably a small application that is lightly used at best.
I Hate Whiners
Wednesday, March 10, 2004
Our college uses Access for the back-end server for the app that gives the students their marks. The marks are actually kept in an Oracle database, but are transferred to Access so that they can be accessed from the web using data access pages. We're talking about three thousand students accessing the web page within a one to two day period.
I have heard no complaints that Access isn't up to the job.
Stephen Jones
Wednesday, March 10, 2004
Microsoft Access is not a database. It is an application that can read and write .mdb files. Queries are not done by the server (there is none), but by the client application.
SQL Server (and Oracle, MySQL, PostgreSQL, Sybase, DB2, ...) are databases. They allow multiple connections, handle multiple different users, different rights per user, rights per table, rights per tablespace, ...
With Access, with a little bit of luck you can have more that one user connect to the same .mdb file. But don't try to have hundreds of users connecting to that same .mdb file.
With A Real Database(tm), you can connect hundreds of users, each with his specific rights, and have them execute queries (that are actually executed on the server, and only result sets are sent back to the client).
€0,02
Mark
Mark Tetrode
Wednesday, March 10, 2004
"Queries are not done by the server (there is none), but by the client application."
Regarding whether the queries are done by the server or client, the queries for mdbs are actually done by the jet engine which just happens to be instantiated in the client process space, but from a client perspective you are still just feeding it SQL and getting back results -- from an atomic application perspective it is pretty much the same thing and it's picking at nits differentiating. Regarding the redefinition of English, an MDB most certain is a database. A dbase file is a database. A text file in a defined format is a database.
"With Access, with a little bit of luck you can have more that one user connect to the same .mdb file. But don't try to have hundreds of users connecting to that same .mdb file."
"More than one" and "hundreds" are a vast chasm of separation. _Very_ few developers work on systems with hundreds of simultaneous connections. Refine that further, and with connection pooling most high traffic websites seldom have more than a dozen database connections simultaneously.
As I said before, if you have a centralized single web app on a single machine, using connection pooling, talking to Jet that is controlling a local mdb, it is actually a totally adequate solution for some pretty large scale systems. Effectively in that case you're virtualized a "real database" because your web app is the "database server" controlling and arbitrating requests to the mdb.
(Users) ---> Web App ==> MDB
(Users) ---> SQL Server ==> MDF/LDF
I'm not realistically saying that Access is a credible replacement for SQL Server, but that there's a lot of baseless anti-Access FUD -- There are a lot of low need systems where SQL Server is just a massive overkill if you don't already have a SQL box paid for and hanging around waiting for traffic.
BTW: MySQL is basically like a DCOM instantiated JET engine (if such a thing were possible over an MDB. Few people categorize it as a "real database", and instead it's a glorified MDB)
Dennis Forbes
Wednesday, March 10, 2004
If it's a web application then there probably is only one user connected to the file at one time; that is the web server.
Aren't you being a little lose with your terminology here. Don't you mean to say "MS Access is not a true databae server?"
Stephen Jones
Wednesday, March 10, 2004
Dennis,
point taken; what I actually ment was 'a database is a DBMS, a .MDB file is a datafile'.
I you put the database logic on the same machine as the web server, then having queries executed at the clients' site is not a problem (for the moment). But what if your web application is popular, and needs to grow? At one point you'll need to separate the database from the web server, and then the performance problems begin.
I've seen this too often, so therefor I would say, go with a real dbms.
Mark
Mark Tetrode
Wednesday, March 10, 2004
How about we agree on the definition that a DBMS is a DBMS, and a database is a database? :-)
The problem with planning for the optimistic future is that 99.9%* (*-number accurate +/- 0.1/99.9%) of the time it never comes to pass, and you have incurred time and effort costs, not to mention direct licensing and hardware costs (for instance once you go with a dedicated server, naturally you're going to want to split it onto a separate machine -- in fact a dedicated server often has resource needs that demand it -- and then create a whole backup system for it, maintain it, etc) for absolutely nothing if the unlikely high-demand future doesn't come. It is hedging that ends up cost your dramatically more than the bet loss would in the long run (switching a data layer over to a new database system as needs ramped up is generally low cost).
Let me give another example - I once worked with a group developing extremely vertical solutions for a very small group of very high value customers. With tens of millions in revenue, this web application only served maybe half a dozen actual interactive customers. A architectural astronaut then joined the firm, and decreed that we need to follow all of the paradigms for extremely large scale e-commerce sites to achieve `scalability'. Scalability by, for instance, separating all logic out into multiple extremely resource intensive out-of-process COM objects.
I guess my point is that one must justify decisions based on real needs, not hypothetical needs.
Dennis Forbes
Wednesday, March 10, 2004
MSDE. It has all the advantages of SQL Server plus some of the advantages of MS Access. You use MS Access as a front end.
MSDE will not require a dedicated server, the runtime license is free. I think you may need Visual Studio or something to get it.
If the day comes and you need to switch to SQL Server, you just copy the data files over to a real server and keep going.
The disadvantages to MSDE are that it won't accept more than 5 users, but that shouldn't be a problem with a web site.
pdq
Wednesday, March 10, 2004
"The disadvantages to MSDE are that it won't accept more than 5 users, but that shouldn't be a problem with a web site."
This is not precisely accurate. After 5 concurrent connections, it will start slowing itself down on purpose. I wouldn't equate users with concurrent connections. You could write a serialized caching data layer that ensured only one concurrent connection was ever used, regardless of the number of users that are hitting the site.
Brad Wilson (dotnetguy.techieswithcats.com)
Wednesday, March 10, 2004
 Recent Topics
Fog Creek Home
|
