Access vs SQL Server
-
Do anyone any documentation on what the thresholds for using Access are before you should migrate to SQL?
We're facing situations where there are lots of departments using access databases for their daily functions and they are tasking their own staff with maintenance and development of these tools. In many cases we would like to provide them better solutions either on the GUI or database performance through proper utilization of IT resources both hardware and staff.
Any information you could provide would be of great assistance. Thanks as always!
-
Yeah. If you're using Access, stop it now. If you need cheap use MSDE. Access should never be used by more than one user at once, kind of a fundamental problem in most databases.
+ there are loads of posts on this topic already. Why don't you go read them before posting?
Keith Henry
DBA/Developer/BI Manager
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
Like this one:
Keith Henry
DBA/Developer/BI Manager
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
I found that 10-12 was it. MSDE is basically a "sql server lite" version...(my phrase, not microsoft's). Its a stripped down version.
You need to get Access2000 and install MSDE. And move forward from there. If you ever need to go sql server .. then MSDE to sql server is ... a snap. going from access to sql server isnt as easy..... especially with pre-defined views (access) vs stored procedures. If all you db access code is client based (vb or asp), then just switch out to MSDE.
all that microsoft is .. is a file on a shared drive. anytime you do a join between 2 tables. you have to send back ALL THE DATA in the two tables , then do the join locally. this is NOT a true RDBMS (relational database management system). Sql server IS a true RDBMS.. when you join two tables.. the SERVER does the work, and sends you back the result. ALOT BETTER.
if price is an issue, go msde. if price isn't an issue, go SQL SERVER ..
-
I'm going to agree with Keith. Stop using Access. You've fallen into a trap. Sql Server is not that expensive when you consider the cost savings long term.
BTW Keithh, there is no such thing as an unwanted question, on this forum. We are free to answer or ignore. I suggest we keep our egos where they belong.
-
quote:
We're facing situations where there are lots of departments using access databases for their daily functions and they are tasking their own staff with maintenance and development of these tools. In many cases we would like to provide them better solutions either on the GUI or database performance through proper utilization of IT resources both hardware and staff.I think you are about to open Pandora's box.
Status now is that the departments do their own stuff and maintain it and can obviously live with these results or did they call you?
If you want to provide them with 'better solution' who will be responsible for maintenance then. Each department? Don't expect this!
If you mean by 'better solution' developing in a 'grown-up' programming language, I don't think you can expect that this knowledge is readily available in these departments. So they call you for maintenance each time something is wrong or do you want to give them escalated permissions on the SQL Server.
Do you have such resources to manage this?
Programming in some kind of VBA dialect (Excel or Access) isn't really bad at all. At least this knowledge is available in some form in these departments.
Also, do you have the time to take care of each single Access application? Right now, they seem to work more or less good.
Changing this, is IMHO time and money consuming. You have to carefully weight cost and benefit.
Apart from this, SQL Server is the more 'grown-up' system. Not superior to Access by definition, but for different purposes.
Just my $0.02 cents
Frank
Edited by - Frank Kalis on 10/22/2003 02:14:28 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank you for all the feedback!!! I DID read other posts on this subject and found some really good info!!! We are trying to dig a little deeper to come up with good ways to convince different departments that have non-IT people supporting (sometimes large and mission critical) Access Databases to let us convert them to SQL Server. We have the staff to support them on SQL Server, but trying to manage all these renegade Access databases is a nightmare~
Thanks again
-
We have a similar situation here with departments writing their own access applications and then the person responsible leaves...
it then falls on the IT department / DBA to pick up the pieces.
We buy in many packages and some of these are access front ends with passthrough queries and linked tables to external databases (SQL Progress AS400 e.t.c) This ensures that the data (mission critical or not) is in an environment that can efficiently handle multi user access and be backed up effectively.
I for one am against users having access as it causes a lot of headaches..
Think about it
Andy
-
But I think you cannot prevent this process.
Different departments in different companies have different needs.
Each uses its own specialities and this makes it difficult to use a standard programm like Word for text processing.
What we try here, is to force the users to document their self made applications and file this with the IT department for just these reasons you've mentioned (holiday, leaving, illness...)
Frank
Edited by - Frank Kalis on 10/22/2003 02:10:37 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
[font=Comic Sans MS]Frank, how well does that self-documentation work?
I have been in several companies where someone with a bit of knowledge will create a homemade database. They will use it for six months and then their boss decides they want that one report.
The creator tries to write the report but after two weeks they realize that the database won't do what they want. The company hires a consultant.
The consultant spends time looking at the homemade database and decides this is not the day to give up drinking.[font=Comic Sans MS]
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
-
quote:
Frank, how well does that self-documentation work?the results are quite acceptable. No professional stuff, but at least something that describes what the main purpose is, who is working with and to whom the results are important. Well, sometimes IT have to kick a**, but it seems that everyone can see the advantage. And we do have some 500 employees.
quote:
I have been in several companies where someone with a bit of knowledge will create a homemade database. They will use it for six months and then their boss decides they want that one report.The creator tries to write the report but after two weeks they realize that the database won't do what they want. The company hires a consultant.
The consultant spends time looking at the homemade database and decides this is not the day to give up drinking.
<grin> Yes, I know, Access exposure can be treated this way! But it takes time.
Anyway, this consultant should be happy for earning easy money.
Frank
Edited by - Frank Kalis on 10/22/2003 06:36:23 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
I'm going to agree with Keith. Stop using Access. You've fallen into a trap. Sql Server is not that expensive when you consider the cost savings long term.BTW Keithh, there is no such thing as an unwanted question, on this forum. We are free to answer or ignore. I suggest we keep our egos where they belong.
I guess it did come out much harsher in plain text than it was in my head, sorry potucekj
Keith Henry
DBA/Developer/BI Manager
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
Laughing, Frank. Cleaning up a homemade database is not easy money. It is sitting down with the people and explaining that "No, I cannot write the magic report that solves all of your problems because the database is crap." speech.
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
-
Don't forget about the problems with Access and database corruption.
If you are sharing an MDB file across the network with multiple users, you WILL get a corrupted database. It's just a question of when.
Once you have a corrupted database, you will probably lose data, so make sure you have a good backup system in place.
Use SQL Server or MSDE - no question in my mind.
-
quote:
Don't forget about the problems with Access and database corruption.If you are sharing an MDB file across the network with multiple users, you WILL get a corrupted database. It's just a question of when.
Yes, Access really don't like it when someone turns off the PC without cleanly logoff first
At my former employer we did develop with Access as front-end and back-end.
One might argue about it, but that is what we did.
We had two main db's with several 'helper' db's spread on the customers network. Max. number of user were up to 10 or 12. The backend db of one customer was about 500MB without problems. The front-end db was about 30 MB and that was even more the problem. It could happen that a slight modification to some lines of source code, or a form or alike could not be saved anymore. You had to repair and/or compress the file to try again. And the most annoying problem was the memory leak in Office 97 that was finally fixed with SP2. Caused us severe problems.
I'm not saying that Access is the optimal solution for this. No way, but I guess it sounds familiar when I say, we started developing in Access, because the time to market was always short. And when we reached some critical mass, it was simply not possible to say: 'Sorry, we're going to rewrite our programm utilizing different tools. Wait some 12 months till we come back.'
quote:
Laughing, Frank. Cleaning up a homemade database is not easy money. It is sitting down with the people and explaining that "No, I cannot write the magic report that solves all of your problems because the database is crap." speech.You can't???? <*grin*>
Isn't that somehow what one normally does when meeting with customers to discuss features of a custom made app???
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply