Pro and Cons of Ms Access vs SQL Server 2

[whloo]

Hi! I am in the stage of considering to use MS Access or SQL Server as my backend DB. Anyway know the pro and con for each of them on security issue? support for number of users at the same time? and some other important issues? Feel free to contribute your thought and idea of it.
Thanks!

 

[humbleprogrammer]

Access doesn't even compare to SQL Server. I have used both and if I have the choice, it's SQL Server hands down.

Pros for SQL Server:
- Way faster
- Pretty much unlimited users
- Much tighter security
- You don't have to download the DB to your machine whenever you want to make table changes.
- Outstanding backups and data recovery.

Cons for SQL Server
- Can be expensive
- Not as easy as Access but worth the learning curve.

Pros for Access
- Cheap
- Easier to use

Cons for Access
- Slow over the web
- Very slow once you get 15 or more users hitting it at one time.
- Have to download it to your machine if you want to make any major table structure changes. This can cause downtime when uploading it back to the web.

Hope this helps.

 

[whloo]

good job humble:
my add one to it.

Pro for MS Access
- can run it almost every Window version compare to SQL Server.

anyone that have new thought, please contribute

 

[whloo]

a star for humble

 

[baddos]

SQL supports way more functions than access does.

-Bad Dos

 

[Genimuse]

Since this is an ASP forum, I'll add that the UI in Access is pretty much meaningless except when you create the initial database, and for that SQL Server uses a nearly identical interface.

SQL Server is also more flexible and capable. Today you may not need, say, triggers, but you might very well tomorrow.

Here are more things SQL Server has that Access does not:
[ul][li]SMP Support
[li]Triggers
[li]Temporary Tables
[li]Stored Procedures (via T-SQL, faster and more capable)
[li]User-Defined Functions
[li]Views
[li]Integration with NT Security[/ul]
That said, I still use Access for low-volume sites on shared hosts because it's much, much cheaper (and often included in the hosting price). For anything else, though, it's SQL Server all the way.

 

[onpnt]

I had to add that comparing access to SQL Server is like comparing a text file to a DataBase. There is no comparison really. Either you buy a neon (access) or you by a Hummer (SQL Server)

humbleprogrammer nice list.
one thing though.
Cons for Access
- Have to download it to your machine if you want to make any major table structure changes. This can cause downtime when uploading it back to the web.


No DB needs to be downloaded to be configured as far as I know. All configurations can be made with SQL. Unless you are referencing relationships and such that should be done on DB creation with in the design layout.
table creation
data type changes
field additions
table deletion
etc.. can all be done with a few lines of SQL.


_________________________________________________________
_{$str = "sleep is good for you. sleep gives you the energy you need to function";
$Nstr = ereg_replace("sleep","coffee",$str); echo $Nstr;}

http://www.onpntwebdesigns.com

[/sub]

 

[onpnt]

also, if you want a larger DB with more capabilities then you should look into MySQL instead of SQL Server as it is free and much more then Access. Still may not be able to stay 12 rounds in a ring with SQL Server but a great choice for major needs in a DB.

_________________________________________________________
_{$str = "sleep is good for you. sleep gives you the energy you need to function";
$Nstr = ereg_replace("sleep","coffee",$str); echo $Nstr;}

http://www.onpntwebdesigns.com

[/sub]

 

[Tarwn]

Concerning OS choices:
Access doesn't run in any Window OS, it runs in any Windows OS where MS Office is installed
Thankfully Office 2000 and Office XP are mostly interchangeable and don't generaly complain about versioning.

SQL Server is much more limited, if you buy SQL Server enterprise you are stuck with either NT4 or 2000 Server as your OS, make sure you get one that is compatible

Bonus to SQL Server: Once it is set up wherever it is going to live, the same software can support both the development and the production databases because you can remotely connect to it since it is an actual server rather than a somewhat flat, driver driven file.

Access also gets slower as you add more records to it. I don't remember the exact numbers/versions but at one point the limit was 65,000 records and it slowed down considerably after only a few to 10 thousand records.

-Tarwn

_{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

 

[Bullschmidt]

You might also want to consider MySQL which is basically free.

Best regards,
J. Paul Schmidt

http://www.Bullschmidt.com

- Freelance ASP Web Developer

http://www.Bullschmidt.com/DevTip.asp

- ASP Web Developer Tips

 

[Bullschmidt]

Oh, yes, a link for you:

mySQL

http://www.mysql.com

Best regards,
J. Paul Schmidt

http://www.Bullschmidt.com

- Freelance ASP Web Developer

http://www.Bullschmidt.com/DevTip.asp

- ASP Web Developer Tips