wileedingo
asked on
Advice, please: MS Access vs. SQL Server
This may seem like a pretty basic question, but I'm willing to double the points if someone can give me a really good answer, preferrably with a few good links. Searching the web for this info is difficult because the word "Access" appears in many, many documents about SQL Server which have nothing to do with MS Access. I use Access a lot, but SQL Server is entirely outside my experience, so I'm sort of half in the dark here. And the Microsoft web site was not much help - I think they would rather sell you both than just one.
I have been asked to look into devloping a scheduling database for a medium sized ad agency. They expect the minimum number of concurrent users to be about 30 (that number will grow over time) and the maximum to be about 50. The database will have about 300 fields in total and will get about 1000 records added per year. They are in an NT environment.
I understand that a solution is to use SQL Server and that Access might not work well for more than about 15 users and that there may be security issues with Access.
My question: could someone give me a description of the differences and other issues between Access and SQL Server? If possible, please include the above issues (i.e. max # concurrent users, security concerns). When does MS Access stop being a viable solution (on its own)?
thanks,
wileedingo
I have been asked to look into devloping a scheduling database for a medium sized ad agency. They expect the minimum number of concurrent users to be about 30 (that number will grow over time) and the maximum to be about 50. The database will have about 300 fields in total and will get about 1000 records added per year. They are in an NT environment.
I understand that a solution is to use SQL Server and that Access might not work well for more than about 15 users and that there may be security issues with Access.
My question: could someone give me a description of the differences and other issues between Access and SQL Server? If possible, please include the above issues (i.e. max # concurrent users, security concerns). When does MS Access stop being a viable solution (on its own)?
thanks,
wileedingo
Wow. This topic comes up a lot around here. I'm sure you'll get lots of comments.
IMHO, a well-developed Access application is well suited for your purpose. It should be able to handle the user load if well-designed.
Another bonus: If you decide to switch to SQL server with an Access fron-end (interface), Access tables can be upsized to SQL pretty easily.
The number and complexity of records you mention won't even begin to strain Access, but you will have to re-think your database design if you need more than 255 fields in any one table. I can't imagine needing that number of fields in one table, but I suppose it could happen.
The official maximum number of concurrent users of Access is 255, though this is an unrealistic number. The "real" max number of concurrent users depends on what the users are doing in the database and how much you are willing to let performance deteriorate.
IMHO, Access security is robust enough for critical applications, but if you expect hacker attacks from international terrorists or corporate espionage, you might want something more. It all depends on the skill of the application developer.
I'm sure you'll get a LOT of input from experts in this topic. Good luck.
Wes
IMHO, a well-developed Access application is well suited for your purpose. It should be able to handle the user load if well-designed.
Another bonus: If you decide to switch to SQL server with an Access fron-end (interface), Access tables can be upsized to SQL pretty easily.
The number and complexity of records you mention won't even begin to strain Access, but you will have to re-think your database design if you need more than 255 fields in any one table. I can't imagine needing that number of fields in one table, but I suppose it could happen.
The official maximum number of concurrent users of Access is 255, though this is an unrealistic number. The "real" max number of concurrent users depends on what the users are doing in the database and how much you are willing to let performance deteriorate.
IMHO, Access security is robust enough for critical applications, but if you expect hacker attacks from international terrorists or corporate espionage, you might want something more. It all depends on the skill of the application developer.
I'm sure you'll get a LOT of input from experts in this topic. Good luck.
Wes
I'd agree that you may well get away with an access db for this application, but in a few words the benefits of SQL server are in power/scalability and reliability.
I've had bad experiences with Access, particularly with multiple users over WANs - field and db corruptions and 'niggly' problems which just waste time. SQL Server has been rock solid in comparison.
The slower your network and bigger your db, the bigger the SQL server advantage, because a propery written client-server or n-tier app on SQL server will make use of SQL features to keep the majority of the processing on the servers, minimising network traffic and allowing SQL Server and NT server to handle the multiuser issues. With the sort of application you are describing you will never hit SQL Server's limits on a decent server.
Downside: it's a big move to do properly. SQL server costs. It's more complicated to backup and administer, and if the company involved does not already have a decent database administrator (dba) support will be an issue if there are problems. Just frontending an access app onto SQL Server will loose many of the benefits; you will need to get down to stored procedures etc to get the benefits. With the app you're describing it's a difficult decision if the client is not already running SQL Server. If they are and have the support etc, go for SQL server.
I've had bad experiences with Access, particularly with multiple users over WANs - field and db corruptions and 'niggly' problems which just waste time. SQL Server has been rock solid in comparison.
The slower your network and bigger your db, the bigger the SQL server advantage, because a propery written client-server or n-tier app on SQL server will make use of SQL features to keep the majority of the processing on the servers, minimising network traffic and allowing SQL Server and NT server to handle the multiuser issues. With the sort of application you are describing you will never hit SQL Server's limits on a decent server.
Downside: it's a big move to do properly. SQL server costs. It's more complicated to backup and administer, and if the company involved does not already have a decent database administrator (dba) support will be an issue if there are problems. Just frontending an access app onto SQL Server will loose many of the benefits; you will need to get down to stored procedures etc to get the benefits. With the app you're describing it's a difficult decision if the client is not already running SQL Server. If they are and have the support etc, go for SQL server.
has anyone thought about the Access 2000 new product MSDE?
isn't this something somewhere's between SQL and Access? I'm not sure myself because we've had SQL before this new version came out with MSDE and all.
isn't this something somewhere's between SQL and Access? I'm not sure myself because we've had SQL before this new version came out with MSDE and all.
For more information about MSDE (which is really SQL Server 7... but just the engine, nothing else) vs. Jet, checkout the Microsoft's paper at:
http://www.microsoft.com/sql/productinfo/msdejet.htm
It gets into a lot of reasons why you should use one over the other. The big one that I would consider is the fact that SQL Server has a transaction log and overall is more reliable in mission critical systems.
http://www.microsoft.com/sql/productinfo/msdejet.htm
It gets into a lot of reasons why you should use one over the other. The big one that I would consider is the fact that SQL Server has a transaction log and overall is more reliable in mission critical systems.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
We didn't run into serious performance issues until we hit more than 100 users adding over 1000 records a day. At this point the Access mdb was growing at a rate of 10 MB per day (although the actual increase was significantly less after the nightly compact).
From the sounds of it, there is no reason why you'd need to go to SQL in the near future.
From the sounds of it, there is no reason why you'd need to go to SQL in the near future.
berg1375,
How is the performance on such an application? Especially over a WAN? Seems to me that your application would be the ideal candidate to move to SQL Server... that is, if you could change your application to use stored procedures and views instead of doing the processing all locally (heavy traffic) it would seem to me that the application would speed up tremendously (this is the consultant side of me speaking).
How is the performance on such an application? Especially over a WAN? Seems to me that your application would be the ideal candidate to move to SQL Server... that is, if you could change your application to use stored procedures and views instead of doing the processing all locally (heavy traffic) it would seem to me that the application would speed up tremendously (this is the consultant side of me speaking).
Truthfully, the performance is surprisingly well.
True, SQL would most likely make it more efficient, but for the money to by, and man hours to convert, the advantages are not worth the cost(right now)
I would imagine in the near future(year or so), I will be making my arguement to transfer one of mine to SQL7. With the rate of record adding, and editing, we will be in need of speed.
Other than that one database, the performance over or wan is more than acceptable. Of course it could be our fiber optic cables :)
Either way, the advantages of SQL are great when it comes to speed of large databases, but when it comes to large databases (I develop), I prefer Oracle. It has more power, and a better ability to hard more data better.
I am looking forward to migrating to SQL, but mainly for the experience (that is the "make myself more marketable" in me speaking :) )
True, SQL would most likely make it more efficient, but for the money to by, and man hours to convert, the advantages are not worth the cost(right now)
I would imagine in the near future(year or so), I will be making my arguement to transfer one of mine to SQL7. With the rate of record adding, and editing, we will be in need of speed.
Other than that one database, the performance over or wan is more than acceptable. Of course it could be our fiber optic cables :)
Either way, the advantages of SQL are great when it comes to speed of large databases, but when it comes to large databases (I develop), I prefer Oracle. It has more power, and a better ability to hard more data better.
I am looking forward to migrating to SQL, but mainly for the experience (that is the "make myself more marketable" in me speaking :) )
No one talked much about security so for no points here's my look at the security issue:
Password protecting databases doesn't help much because there are free tools available on the net to get the password
Using Access security should work just fine provided that the "enemy" can't capture the mdw and provided that the enemy is not as Wesley says "corporate espionage" Anyway this was a really good thread to read as most people don't conceive that Access is as good as it really is and the experiences realted here demonstrate that fact
Password protecting databases doesn't help much because there are free tools available on the net to get the password
Using Access security should work just fine provided that the "enemy" can't capture the mdw and provided that the enemy is not as Wesley says "corporate espionage" Anyway this was a really good thread to read as most people don't conceive that Access is as good as it really is and the experiences realted here demonstrate that fact
fedsuns: I don't think that you meant to hit 'answer' instead of 'comment'. Want to correct that? :-)
Jim
Jim
fedsuns,
I realize that you are new to E-E, but please realize that posting an "Answer" after there have been many valid comments is frowned upon by many "experts". I would suggest that you change your answer to be a comment before you are put on people's blacklist.
I realize that you are new to E-E, but please realize that posting an "Answer" after there have been many valid comments is frowned upon by many "experts". I would suggest that you change your answer to be a comment before you are put on people's blacklist.
fedsuns has never done this before so I am positive that this was an error. He said that it was for no points. Don't everybody jump on him please.
The thing that really speaks for looking into SQL Server is the very close connection to Access2000. Using ADPs (Access Database Projects) you use SQL Server as the storage device. You look right into the server from your applications file, no linked tables etc. With the great upsizing tools and the SQL Servers Enterprise Manager (as well as the ADP files familiar look on tables, views etc.) the step up does not need to be that big. Sure there are things that are done differently etc. but life as a IT developer is a continues learning process...
You can start by using the MSDE, which is a "full" version of the SQL Server, but optimized for five users. Any more will trigger some kind of brake pedal that decrease the performance. The good thing that moving to a real SQL Server later is no problem at all.
These days SQL Server is considered to match Oracle and the other "great database servers" with some exceptions: Very large number of users, Storage of large files into fields (scanned images etc.) and the different IT managers opinions on which is the better product :-)
You can start by using the MSDE, which is a "full" version of the SQL Server, but optimized for five users. Any more will trigger some kind of brake pedal that decrease the performance. The good thing that moving to a real SQL Server later is no problem at all.
These days SQL Server is considered to match Oracle and the other "great database servers" with some exceptions: Very large number of users, Storage of large files into fields (scanned images etc.) and the different IT managers opinions on which is the better product :-)
ASKER
Thanks for all the response so far.
I have a better idea of the concurrent users issue now (for Access, 255 is maximum, but <=20 is recommended by MSDN Library), but am still wondering about the security issues relating to Access. There is certainly a lot of information available on defeating Access security, and in the MSDE (SQL Server 7) info on the MS site, they really make a deal out of how MSDE integrates with Win2000 security, so it looks like security, with respect to an Office97/Win95 situation, is something to at least look at.
I agree with pmwoods comments. I once did time as a network admin, and my dept. had purchased an Access app from a firm across the country. I had my fill of corrupted files and exasperated users - and it wasn't even my headache. The company who sold the program was constantly flying someone out to fix it, but they never did find the problem.
BTW, thanks to dtomyn for the most helpful information so far with his link to the MSDN Online Library. I would like to leave this topic open for another 1/2 day or so...
wileedingo
I have a better idea of the concurrent users issue now (for Access, 255 is maximum, but <=20 is recommended by MSDN Library), but am still wondering about the security issues relating to Access. There is certainly a lot of information available on defeating Access security, and in the MSDE (SQL Server 7) info on the MS site, they really make a deal out of how MSDE integrates with Win2000 security, so it looks like security, with respect to an Office97/Win95 situation, is something to at least look at.
I agree with pmwoods comments. I once did time as a network admin, and my dept. had purchased an Access app from a firm across the country. I had my fill of corrupted files and exasperated users - and it wasn't even my headache. The company who sold the program was constantly flying someone out to fix it, but they never did find the problem.
BTW, thanks to dtomyn for the most helpful information so far with his link to the MSDN Online Library. I would like to leave this topic open for another 1/2 day or so...
wileedingo
ASKER
Sorry, hadn't received past berg1375 before posting previous.
Wouldn't MSDE require everyone upgrading to Office 2000/ Win 2000 to get the full benefits?
I have also read many times that it is easy to upsize from MSDE to SQL Server, as if you can almost expect to be doing this some time in the future, so why not just go straight to the real SQL Server and save that step?
wileedingo
Wouldn't MSDE require everyone upgrading to Office 2000/ Win 2000 to get the full benefits?
I have also read many times that it is easy to upsize from MSDE to SQL Server, as if you can almost expect to be doing this some time in the future, so why not just go straight to the real SQL Server and save that step?
wileedingo
The MSDE can be installed without your client needing to buy a SQL Server license.
Sorry about clicking on the answer. Not intended and of course I haven't been on line in a bit so I didn't realize I had done that
wileedingo: What version of Access are you developing your application in? I believe that MSDE is only an Access 2000 option.
You can convert an Access 97 DAO app to ADO with Jet4 but I remember Ken Getz saying that he did not recommend that you do so as there were too many gotchas involved.
Although Office 2000 is supposed to be more integrated with Windows 2000, the jury is still out since technically, Win 2000 hasn't even been formally released.
If you are trying to develop a runtime, the only way to use an app without having a full copy of Access loaded for each user, the runtime for Access 2000 is HUGH! It requires a complete copy of IE5 to be loaded in order to even run.
Straighten me out. This question started out talking about which was better Access or SQL Server and wound up having the best response pointing to a paper on the differences between MSDE and SQL Server. Not exactly the same animals.
Jim
You can convert an Access 97 DAO app to ADO with Jet4 but I remember Ken Getz saying that he did not recommend that you do so as there were too many gotchas involved.
Although Office 2000 is supposed to be more integrated with Windows 2000, the jury is still out since technically, Win 2000 hasn't even been formally released.
If you are trying to develop a runtime, the only way to use an app without having a full copy of Access loaded for each user, the runtime for Access 2000 is HUGH! It requires a complete copy of IE5 to be loaded in order to even run.
Straighten me out. This question started out talking about which was better Access or SQL Server and wound up having the best response pointing to a paper on the differences between MSDE and SQL Server. Not exactly the same animals.
Jim
JimMorgan,
Check near the end of the whitepaper and you will find comparisons of Jet and SQL Server (enterprise, standard, desktop, MSDE). Basically, the paper gives specs for all of these. Yes, it is a paper for MSDE, but it does answer part of the original question of:
"please include the above issues (i.e. max # concurrent users, security concerns)" as well as a number of other concerns (like backup, restore, transaction logs, etc). It also explains licensing issues for each of these options. To answer the question about needing Access 2000, well I think it is more that you need either Office 2000 Developer or Visual Studio 6 Enterprise(?) to freely distribute MSDE. As to upgrading to Windows 2000 to gain the benefits of some of the security features... I have not heard anything of this. That is, it was my understanding that it will be SQL Server 2000 that is to do this, not SQL Server 7. That is, SQL Server currently makes use of trusted connections, NT roles, application roles, multiple roles, etc.
Check near the end of the whitepaper and you will find comparisons of Jet and SQL Server (enterprise, standard, desktop, MSDE). Basically, the paper gives specs for all of these. Yes, it is a paper for MSDE, but it does answer part of the original question of:
"please include the above issues (i.e. max # concurrent users, security concerns)" as well as a number of other concerns (like backup, restore, transaction logs, etc). It also explains licensing issues for each of these options. To answer the question about needing Access 2000, well I think it is more that you need either Office 2000 Developer or Visual Studio 6 Enterprise(?) to freely distribute MSDE. As to upgrading to Windows 2000 to gain the benefits of some of the security features... I have not heard anything of this. That is, it was my understanding that it will be SQL Server 2000 that is to do this, not SQL Server 7. That is, SQL Server currently makes use of trusted connections, NT roles, application roles, multiple roles, etc.
ASKER
Jim
What I meant was that MSDE is Office 2000, which integrates into Win 2000 security. These clients want a database, not a software upgrade, so if I do the project, it will be in Office97. So I was still kind of wondering about the security Access97.
In any case, what everyone seemed to be saying here is that SQL Server would be the best, though most cost prohibitive, solution, however, many have tried and succeeded quite well with Access. It depends more on what the users are doing with the data.
At some point, I suppose, there is a trade-off between the higher cost of SQL and the time, effort, network bandwidth, etc. required to keep a larger Access db running.
wileedingo
What I meant was that MSDE is Office 2000, which integrates into Win 2000 security. These clients want a database, not a software upgrade, so if I do the project, it will be in Office97. So I was still kind of wondering about the security Access97.
In any case, what everyone seemed to be saying here is that SQL Server would be the best, though most cost prohibitive, solution, however, many have tried and succeeded quite well with Access. It depends more on what the users are doing with the data.
At some point, I suppose, there is a trade-off between the higher cost of SQL and the time, effort, network bandwidth, etc. required to keep a larger Access db running.
wileedingo
I say that pretty much sums it up.
Access 97 has much the same security of 2000. I'm not aware that 2000 had more but then too, I'm not as deeply involved with 2000 as I am with 97.
I'd suggest that you go to the old look and search for security questions regarding Access 97 first, to bring you up to date. Since September 1999, I have written quite a bit about security in the Access topic. When I get a chance, I'm going to incorporate all of my comments into a white paper.
Jim
Access 97 has much the same security of 2000. I'm not aware that 2000 had more but then too, I'm not as deeply involved with 2000 as I am with 97.
I'd suggest that you go to the old look and search for security questions regarding Access 97 first, to bring you up to date. Since September 1999, I have written quite a bit about security in the Access topic. When I get a chance, I'm going to incorporate all of my comments into a white paper.
Jim
The Security FAQ provided by Microsoft can be found at: http://support.microsoft.com/support/kb/articles/Q165/0/09.asp
I have many databases used by 20- 50 users (some in different states). We use Access97, and on an NT environment.
At any given time of the day, I could have 35 users hitting the same database from all over the country.
I have all table on the network, and then a front end with all queries, forms, macros, modules on the users hard drive. Linked to the network back end of course.
Each year, I would imagine we get well over 500,000 records added, updated, deleted... I have run into no problems, so I can't say you would need SQL Server for this.
Just remember to compact regularly.
berg