SQL Server 2005 vs MS Access 2000

Question

SQL Server 2005 vs MS Access 2000

G Money-291556

SSChasing Mays

Points: 633
More actions
May 31, 2006 at 5:19 am

#113823

Hi,
I need to convince my manager why we should use MS SQL Server 2005 which we already have licenses for instead of MS Access as the backbend for our online DB.
My company is a small company experiencing some growth. The use for the database is to store database content, Generic User names for registered online newsletters and a online shopping cart that will not process the actual payment as this part will be done by a third party company World Pay.
can some DB guru tell me why I MS SQL Server is better for these requirements. The reasons that I think of are:
Peformance Monitoring
Security (Not sure exactly how security is better)
What are the other benefits. Can someone tell me why? MS SQL is better or provide some links please

Thanks
Gmoney

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Marshall Smith SSCrazy Points: 2041 More actions · Answer 1

Security is a major concern. Security in Access is essentially non-existent. There is no way to secure Access against a dedicated, knowledgeable hacker. It's pretty difficult to secure it against a casual hacker who knows the common tricks (such as opening it while holding down the shift key, to bypass the startup configuration).

SQL Server is much more flexible for a number of the things you want to do. It has transactions, DTS, jobs, etc. A lot of power. That power does cost money, usually. But, if you already have the licenses, then it's crazy not to use it.

The biggest reason is that Access is bad as a backend for web apps. (As I say this, keep in mind that I am a big fan of Access for many projects.) This is because Access is a file-based system. When you put it out there, you have to give read and write access to the folder that the database sits in, so that Access can create its locking file. I think pretty much anyone can see why this is bad. It is also terribly inefficient for the kind of traffic that you can expect from a web site (it needs to be pretty thoroughly tweaked by someone who knows what they are doing to handle significant traffic, say, more than twenty concurrent users).

For a web application, if you don't want to go SQL Server, I would strongly recommend going with MySQL. But, really, avoid Access. When running a web app, you slam up against every single one of Access' limitations (well, except probably the size limitation).

G Money-291556 SSChasing Mays Points: 633 More actions · Answer 2

Marshall,

Exatcly what I was looking for thanks a bunch!

I really do want to go with SQl Server I think it is silly sticking with Access, but these business guy's have no idea!!

Marshall Smith SSCrazy Points: 2041 More actions · Answer 3

The biggest thing to hit them with is that you already own the SQL Server. That's like insisting on leaving your pickup in the garage while you try to haul dirt in your sedan. Sometimes, it's not worth it to buy the pickup. But, if you already own it, use it!

G Money-291556 SSChasing Mays Points: 633 More actions · Answer 4

Hi Marshall,

Sorry but can you explain whay this is bad?

When you put it out there, you have to give read and write access to the folder that the database sits in, so that Access can create its locking file. I think pretty much anyone can see why this is bad

Thanks

David McFarland SSChampion Points: 11815 More actions · Answer 5

Performance, security, data integrity (Access corruption issues can be quite nasty), enterprise reporting, etc. If they are looking for ease of use for the end user, you can create a hybrid environment, where you use Access as a front end for linked SQL Server tables, as it's quite effective at that.

Even in an environment where Access is widely used, you'll find that having the data stored in a centralized, robust environment like SQL Server will pay for itself over and over again. Many people use Access purely for it's form building and control flow capabilities, while doing all of the heavy lifting on SQL Server via stored procedures. That also makes many changes much easier, as it often doesn't even require a change in the Access code.

It does sound like your needs could currently be met with Access, but you have the licenses already, and as your needs change, your data environment won't have to if you take the plunge now. This assumes you or someone on staff have the required knowledge to administer a SQL Server environment.

Marshall Smith SSCrazy Points: 2041 More actions · Answer 6

You are giving anonymous users read and write access to a folder on your website. Which means that they can see whatever files you put up there. And modify them (unless you put explicit file protection on each one). And add their own.

And delete your database entirely.

You are, at this point, trusting almost entirely to security by obscurity. You're hoping that no one decides to go poke at that folder, and trusting that those who do will leave it alone.

If you're comfortable with leaving the back door unlocked, then that's your choice. Make sure you have a really good backup plan, though.

G Money-291556 SSChasing Mays Points: 633 More actions · Answer 7

David and Marshall great for the input absolutly fantastic.

Thank you both so much!!!