Access vs MSSQL

[sportypants]

I know MSSQL is more efficient than Access2000 but can anyone tell me if I would notice much difference on a smallish .asp site? My site uses a few Access expressions to work our prices, store invoices and members details and is lightly scripted.

I know its difficult to get an idea how much the db is really used but im probably pushing access a little hard, are there any speed examples someone can point me to to see how good MSSQL is?

Hope someone can point me in the right direction.

Thanks in advance,

SP

 

[sguslan]

SQL is truely a speed demon! In the latest third party benchmarks(

http://www.TPC.org)

it beats even the big boys, aka, Oracle. Access does not come even close. But, will you need it? It depends on how much traffic you have and whether you have actually experinced any bad performance with Access !

I would give it a test and see ! By the way Access is used only on small sites like small discussion groups and by sites that can not afford the price of SQL, or may be sites that are published on free web hosting servers that may not have SQL or it may be for pay ...

________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[sportypants]

Thanks for the reply and the link, Ive just converted the access db to MSSQL and Im using it locally at the moment, I must admit I can see the difference on my local machine so Im sure its going to be much faster when its on the remote server.

Thanks again for the reply

 

[averase]

In the first place, SQL Server (v 7.0 or higher) is pretty pricey. Add to that, bumps or sessions. You have to pay for sessions. For example, one person logged on is TWO sessions. That's right. TWO.

Ok, now, unless you are a SQL Administrator, SQL Server is a daunting experience for the novice. There are some visual tools provided, but you still must understand database administration. Then, you have accounts, object permissions,etc. Organizations spend thousands to train SQL admins.

Access, while somewhat "clunky" is a useful platform, but not very fast. Of course, you can speed things up with clustered indexes and other tricks, but you need to know a bit about indexing and complex keys. The subject isn't trivial. BUY A BOOK! The Access Bible is a good one. Then READ THE BOOK. You can do some pretty good things with Access if you are a small shop.

Finally, and I can't stress this enough, Relational Theory is a must. If you don't understand Codd's rules, or at least have a good foundation in set theory, you are not going to achieve the best results.

Good luck to you.

 

[sportypants]

I agree with some of your comments regarding the understanding of DB Admin, SQL Server seems quite 'Full On' as apposed to Access, We have a license for MSSQL already and I have successfully converted the access DB from our website and switched to SQL with no problems. I have used windows authentication and connected to the DB and everything seems good so far.

I have read a few articles on using Access and agree that buying books is a must to understand the full potential of these packages, however, I believe that hands on experience and watching people who use this day in and day out is better experience than any book you can buy

Thanks for your reply, its nice to find a place where people actually read your post and not just reply post haste

Thanks again, much appreciated,

SP

 

[SemperFiDownUnda]

Get the best of both worlds. Use MSDE. A SQL Server 7 Engine that is free from M$. MSDE is the new replacement for JET. Jet being the database that is actually used when people think of ACCESS. ACCESS is NOT a database engine but a front end to a database. With ACCESS 2000 you can choise from JET or MSDE as your database engine.

MSDE is free and just as easy to administer as JET if you don't want to tweak it at all but it gives you the ability to tweak it just like SQL server. And agian is a true SQL server with transactions etc.

MSDE drawback is that since it is free microsoft have "Tuned" it to 5 users. I can tell you that if you connect to it you only use up 1 session. If you monitor it via something like enterprise manager that would take up a session too.

Note that sguslan statement while true must be taken into context. SQL Server in some test will out preform Oracle but don't take that as it is better then Oracle in All or even many cases. Oracle and SQL server are geared to different markets. But that is another story.

My advice if you are a "Small Shop" is listen to M$ and go with MSDE. If you find you out grow it you would have long since outgrown ACCESS and have the option of going to a licenced version of SQL server for little to no migration issues.

 

[sportypants]

Looking for download right now

Thanks for the advice, I will use MSDE at home after d/l.

Thank you once again

 

[SemperFiDownUnda]

If you have Office 2000 MSDE is distributed on it also