May 23, 2005 at 8:46 am
Hi all.
Can someone help me here.
I have been asked to provide some info about why switching to a SQL Server based database would be a better option for a customer than using an existing access / foxpro based system.
The user has 100K records at the moment in their customer table and it is growing rapidly. Currently load times are in the region of a minute per screen! The user has three other users that access the system, but as they are about to start expanding this will grow to 20+ users.
What arguments can I use here for SQL Server 2000 and what other advantages can they expect.
I need to put it in laymans terms.
Thanks
May 23, 2005 at 10:00 am
Advantages:
It's more scalable and should handle larger loads. Access/Fox is a file based system, so it's like everyone is using not only the same files, like people sharing an Excel document. When you open a record, you wait on others, but you also use the resources on your machine to do the work of selecting the records, sorting them, etc. This means that if you want to order a set of records, you bring all the rows back to your workstation, lots of data across the network, and your machine then has to work on them. Eats up memory, cpu, etc. It's like asking for some data in a file to be sorted. You go get the file, bring it back to your desk and sort it v asking a secretary to go to the cabinet, sort the files, and drop them on your desk.
However, just like the secreatary can get busy, the server can get busy as well. Now the server does cache data, which is why memory is important, so things aren't quite as bad.
Keep in mind this will be more expensive, much more expensive than Foxpro. I ran an old Fox system that we converted to Vis Fox with SQL Server and while it had lots more functionality and worked quicker, it took applicaiton tweaking. Some things were slower when we started and the software cost + a big server was over $20k. Over time, six or so months, everyone really apprecaited the new system, but at first they weren't thrilled.
Administration is harder, you have to make sure you have backups, etc. The plus side is that if a server or machine crashes, you shouldn't have data corruption as I've had in Foxpro.
If you do not have control of the app, I'm not sure I'd do this. Some development time is needed. It will definintely scale to more people better than Access/Fox, but if you're going to 6 people, you might just pilot it with a small server and see. If you're going to 26 people, I'd really look at SQL Server.
May 23, 2005 at 11:08 am
I will just emphasize a point that Steve made. I have seen several projects both internal and vendors that have attempted to "port" an application from FoxPro or DBase to SQL Server. While desireable for many reasons, don't get fooled into believing that it is a simple matter of "pointing" the application at a new data source.
In every case where that was the mindset, the results varied from disapointing to catastrophic. On the other hand where the vendor or IT dept takes the time to review/revise the application to take advantage of a SQL database, the results have been much better, albeit with a larger budget and implementation window.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
May 24, 2005 at 1:13 am
And many things in file-based systems, like definite record numbers, simply do not exist in SQL server (and for good reason - it's a set of data. If you want it ordered, use an order by).
Also... many of the systems like Fox/Access use cursors a great deal - server side cursors are usually the substitute when moving from file-based DBs to server-based DBs, but these perform much more poorly than a select statement returning a set to a client-side cursor in most coses.
May 24, 2005 at 2:02 am
I would suggest that you need to look seriously at your application anyway. A minute to load a screen is horrendous - I would start getting worried at a second or two (I support a large, complex, multiuser FoxPro 2.6 application of similar data scale). FoxPro has the advantage that it's cheap to maintain and proper use of indexes gives fast data access. We have tables of several million records that perform perfectly well with careful coding. However, note that FoxPro has an upper limit on table size of 2Gb.
I have never used Access seriously and do not see it as a contender in any reasonable scale commercial application.
SQL Server (or Oracle) gives you an industrial strength, scalable system with all the benefits of server side processing, data integrity, security, database maintenance functionality etc but cost of ownership is much higher.
Depending on your circumstances, there also is the question of credibilty - some clients/customers (especially large ones) may see SQL Server with a .NET front end as more 'professional' than a FoxPro or Access application. I don't defend or support this - it's just the way of the world. 'Nobody ever got fired for buying IBM'!
May 24, 2005 at 5:12 am
How important is their data? BACKUP/RESTORE capability of Sql Server...recovery options.
May 24, 2005 at 8:24 am
Access can work fine up to about 10 users. But, only if it is programmed correctly. And I do mean programmed. You cannot use wizards to do it.
I stopped use FoxPro years ago when Windows came out. I used Clipper and dBase file format before Access.
I have been using SQL Server since its split from Sybase. I think the version was 3.51. Neither Fox or Access can touch it in what it does. But that comes at a cost. They will need a DBA. They will need to rewrite the current program to work better. A one minute load with 100,000 records is ridiculous, unless they are still using DOS. And, even then, my old Clipper applications took only a second to load data with that many records.
They have a poorly designed application. They will need to rework it. And my guess is to rewrite it completely. Just going to SQL Server will not help a poorly designed application.
I use nothing but SQL Server for a backend now, either the MSDE or the full SQL Server. There is no reason not to.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply