I work with a team that has a sharepoint site currently runnning and its lists are linked to an access DB. My question is really on the investment level, what would be the reasons to upgrade DB to sharepoint if only a few 100 users access this site. Is there a real benefit to replacing the DB with a version of SQL Server, escpecially if about to replace Access 2002 with 2007 some day. I know SQL Server can handle more memory and traffic for more users, but I'm looking for more reasons than that if there are any.
+1
A:
If you have multiple users accessing the database it's always worth it to replace Access. Maybe the newer versions aren't as bad but in my experience earlier versions (2002 and earlier) had some problems with data becoming corrupted in a multi-user environment. I'd rather use SQL Server Express than Access. SQL Server Express will give you all the benefits of SQL Server (there are some limitations like the maximum size of the database and the number of processors it can use) but allows an easy upgrade path to a full copy of SQL Server in the future.
TLiebe
2010-03-23 01:28:04
It is not *always* worth it -- categorical statements will almost always be wrong in some cases, so you get a downvote for making one.
David-W-Fenton
2010-03-23 21:59:51
A:
You might want to upgrade to the express version of SQL Server, which is free, and can handle databases up to 4GB. If you later decide that you want to move to another version of SQL Server, it will be much easier.
Kibbee
2010-03-23 01:28:33
+1
A:
The biggest reason to migrate away from Access is stability and maintainability. Any real DB (SQL Server, Sybase, Oracle, DB2, UDB, ...) will have better backup features, better management features, and provide much better control of access and table management.
C. Ross
2010-03-23 01:28:50
I'm guessing the Access fans didn't like the 'real DB' part of your post.
TLiebe
2010-03-23 23:29:42
@TLiebe I suppose I could phrase it differently, but I think people know what I mean.
C. Ross
2010-03-24 03:10:24
I know exactly what you mean, hence the downvote. And given that the way the question is presently worded indicates that Access is being used as front end only and not as data store, talking about Access as a database (when you really mean Jet/ACE) is not really relevant in the first place.
David-W-Fenton
2010-03-25 21:09:46
@David-W-Fenton I did not read it that way. I love access as a user/business reporting/research tool, but it sounds like it's being used as a data file. If it was being used as a front end, why would they even consider upgrading to SQL Server? With regards to Access as a datastore/backend (or even JET/ACE), I stand by my comment. I consider SQL CE and other data file databases on the same level if it makes any difference.
C. Ross
2010-03-25 22:56:21
To me the question reads as a choice between Sharepoint lists and SQL Server as data store. Jet/ACE as datastore doesn't seem to be involved. Then again, the question is quite unclear.
David-W-Fenton
2010-03-26 18:23:21
A:
Let me stress first - I use SQL Server, I like SQL Server and it's unquestionably a stronger DBMS than Access with better performance and reliability. There's also SQL Server Express as a low-end option which is quite good enough for many purposes but gives the architectural advantages of SQL Server over Access.
That said - way back in the day, when I were a lad and budgets were tight.... I have used Access as a back-end for interactive data storage on sites with significantly more than a few hundred users (total, never measured concurrent). It worked, I don't think we ever saw data issues and wasn't particularly slow. Remember a web app is a rather different usage case from a windows app because users aren't continually connected to the database so concurrency issues aren't quite the same.
Access will remain a weak point in the architecture and it's well worth understanding its limitations (transactional integrity in particular) so you can make informed decisions on its use, but I wouldn't regard it as a given that it must be replaced. If it does the job now and there's no reason to believe it won't continue doing its job for the foreseeable future, consider letting sleeping dogs lie. The ROI from replacing it may not be there.
eftpotrm
2010-03-24 12:36:59
So far as I can tell from the question, Access is being used only as front end, and the Jet/ACE database engine is not being used as a data store. So, really, all your comments about Access being inadequate are unwarranted.
David-W-Fenton
2010-03-25 21:11:15
David, you've clearly got a bee in your bonnet on this question from your response both to me and other questioners. You may be right on that but I invite you to reread my commentand revise your opinion accordingly - I emphatically did _not_ say Access was inadequate. Quite the reverse in fact, I stated I had used and found it a workable platform previously.
eftpotrm
2010-03-26 00:45:14
@David- I don't understand what you're saying. I am using Access as the datastore
Jake
2010-03-26 16:04:20
You are not using "Access" (i.e., Jet/ACE) as datastore according to your response to my comment on your original question. Either the data is in your Access database, or it's in a Sharepoint list. It's not both.
David-W-Fenton
2010-03-26 18:17:38
@eftpotrm: your answer says SQL Server is "unquestionably a stronger DBMS than Access with better performance and reliability", but you can't say that absent a clearly defined context for the requirements and operating environment. Jet/ACE can outperform SQL Server in some operations, but not in others -- there is no blanket statement possible without the qualifications of the context in which it's being made.
David-W-Fenton
2010-03-26 18:19:58
@eftpotrm: you claim you didn't say Access was inadequate, but you said "Access will remain a weak point in the architecture" but there's no real evidence for this in the context of the original question. Indeed, Access/Jet/ACE is not being used as datastore at all, unless the original questioner is even more confused than the wording indicates.
David-W-Fenton
2010-03-26 18:21:34
@David- listen.. I never said I was storing the data in my sharepoint list. I said I am storing it in Access and using access "feature to export data" to sharepoint list.
Jake
2010-03-26 19:12:47
David. You like Access and know more about Sharepoint intricacies than me but you need to take your blinkers off. SQL Server is designed for multi-user concurrent transaction safe data storage; Jet / ACE isn't to anywhere near the same degree, Jet isn't even thread safe. Access has many advantages over SQL Server, but to complain at my making such statements without qualification is perverse. From experience Access is very rarely stronger, more reliable or faster as a multi-user interactive data store than SQL Server and to complain that I have spoken from the strong majority case is silly.
eftpotrm
2010-03-27 21:07:53
@Jake: the first instance I see of the term "export" is in your comment here. Your question says nothing at all about exporting.
David-W-Fenton
2010-03-28 23:29:39
@eftpotrm: you keep saying "Access" when you mean Jet/ACE, so color me unimpressed by any of your evaluations of the comparative strengths of "Access" and SQL Server.
David-W-Fenton
2010-03-28 23:30:41
Oh for...... David. The questioner referred to Access so I've continued to follow his convention for clarity as a great many people refer to it as such, even if enlightened souls such as ourselves know otherwise. I've used Jet/ACE and SQL Server on large numbers of projects each, I know what the difference is between Access (front-end, forms engine etc) and Jet/ACE (back end RDBMS, Jet's the older version). I know very well from experience that for the caveats and circumstances I outlined, SQL Server is significantly stronger - as I said and as I think most professionals would agree.
eftpotrm
2010-03-29 11:39:03
The confusion in the use of the term "Access" is at the core of why the question is incomprehensible, so choosing to continue to use that confused usage doesn't help anything at all in regards to clarifying what's going on. My only explanation for the questioner's confusion is that he doesn't understand the difference between a local table and a linked table. If my interpretation of the question is correct, interpreting "Access" to mean "Jet/ACE" is actually incorrect. But the OP's clarifications have not helped at all.
David-W-Fenton
2010-03-29 17:54:31
Very possibly - but I'd still suggest you refrain from implying within the context most will read this that the underlying DBMS in Access (Jet/ACE) is likely to be superior in performance, reliability or other common benchmarks to SQL Server in any but a handful of production-scale cases.
eftpotrm
2010-03-30 16:21:13
I didn't imply anything at all about the relative number of cases. I avoided categoricals, because categoricals almost always get you in trouble.
David-W-Fenton
2010-04-01 04:34:34