Finding the one item that makes SQL superior to Access '97greenspun.com : LUSENET : SQL Server Database Administration : One Thread
I am trying to find the best database to develop applications in a multi user environment for my company. We have been trying to use ACCESS '97, but the files have a tendence to go corrupt. I am looking to find the one reason that will vastly seperate ACCESS '97 from another database. (cost does not matter) Please help me in this matter. Thank you in advance for any help.
-- Anonymous, October 01, 1998
People try to justify the move to SQL Server based on performance. This is hard to do straight out. SQL Server's performance vs a file based DB is going to rely on multiple user access, available network bandwidth as well as what types of queries/udpates are run and when.
These are hard things to measure in a convincing way for a client or employer.
SQL Server's real advantage is it's insulation against file corruption, the ability to do hot backups in a 7x24 shop, the ability to use rollback/rollforward to recover data to within minutes of a disk failure and built-in utilities to handle tasks such as replication and disk balancing. These, I think, are the things to weigh against it's increased cost and complexity of management.
Also (so I've heard) Access 97 has a database size limit of 1 GB. I have worked in environments with databases from 2 to 9 GB in size. I don't think I could do that with Access.
-- Anonymous, October 02, 1998
Since I answered you last, I have had discussions with colleagues on the topic of SQL Server versus Access. The trouble with Access is that when a desktop client accesses the Access database, the whole Access database is copied to the desktop. SQL Server, on the other hand, sends just the query and gets back the rows that satisfy the query. This, as you can easily imagine, requires much less of the network bandwidth than Access.
-- Anonymous, October 28, 1998
I wish I could find the article from microsoft. They actually had it on their web site explaining why "not" to use Access. They simply stated that is was meant for "non" production environments. If I ever find it I'll post. To your question - Its a no brainer if money is no matter and your dealing with sufficient amount of data. Storing 1000 records in a MSSQL Database does not do it justice. We are currently storing Resumes online that are upwards of 50 pages long (each) in a text file and I'm able to do full text searches on that. Sql Server 2000 now has the ability to store excel, word.... docs in a defined field. Pretty neat.
-- Anonymous, April 17, 2001