Unanswered: Should I upsize Access/Jet to Access/SQL?
I have an application Frontend and Backend. Currently runs Access XP and Jet 4.
2 heavy users (8 hours/ day solid )and, 1 light. 16 hours per day system is shut down so I compact and backup with FMS Visual Agent every night. Works just fine. Want to upsize to larger environment, probably 6 heavy users and 2-3 light. Recent changes in hdwe and software have cut processing time on heavy queries from 18sec to 2 sec. Solution was a combination of Desktop and Server upgrades as well as unbinding the form. Haven't done this to every routine, rather just the slower ones.
Currently Desktops P4-2.8, 7200rpm, 800fsb, Server Xeon 2.8, 1.25gb, 15,000 rpm scsi drive. 100mb ethernet
Currently about 50 tables, total 120,000 rows.
Bigger environment would be closer to 400,000 rows. Same table count.
The question: Should I consider MS SQL Server Upsizing (only changing the slower routines to pass through queries and a few stored procedures on the heavier use processes). I realize this is a non-trivial project and a non-trivial question I am asking. I have read a lot of the postings and white papers on upsizing and, yes I can see the effort is significant.
But... what if I throw a lot of hardware at the problem. Gigabit ethernet cards, high speed switches etc. And still stay with Jet 4.
I know this is far from a black and white question, but it is a question of bang for the buck. By the way, the code is reasonably good.
The cost concern of the upsizing project is not the licensing, but rather the time necessary to convert, test and retest.
Do you think that the lion's share of the "time delaying" work is in getting the information to the PC or working with the data once it's there?
In otherwords, what's your bottleneck: Jet or Access?
No argument that MS SQL is better/faster than Jet, but what you describe shouldn't be too much for Jet to handle either.
There would be a lot of work in rewiring your front nd to work with MS SQL - you'll soon find EVERY place you have a SQL statement that Jet tolerates, but MS SQL will not.
There are a lot of little ways to tweak performance as well; sounds like you have pursued some of them, others include:
1) streamlining code - especially functions and large procedures (move pieces of duplicate code to a module to reduce compile time)
2) try to eliminate as many query based calculations as possible by
a) doing the calculation in the form/report
b) using a module based function instead of directly calculating in the query.
3) splitting your back end into several related groups of tables
4) use code based recordsets to perform very complicated operations instead of queries
5) use temp tables/temp databases created on local workstations - especially if your users may be running the same query multiple times with different filters (there's a thread in this forum about temp databases)
6) Look into unbound forms, using recordsets to move the data
This is from a shopping list of things I have needed to do with my application as it grows in size and complexity (of course, I didn't get to do the hardware fixes!)
Your ideas are good. The one I hadn't thought about was multiple different db's on the backend. What benefit do you think that would give. Why? My current db is about 60mb and the larger environment would be about 200mb. Most of it would load to server cache. Could you explain the benefit of the multiple db's? By the way I think the time delay is both the workstation processing and the data transmission. Going from a PIII 650 workstation to a P4 2.8 gave about the same benefit as going from a PII server to a Xeon Server. (a not insignificant benefit) Only hardware thing to try now will be the Gigibit ethernet. Do you have any opinions on that before I throw the money at it?
A strong reason for moving the back end to SQL server is security and safety.
If the backend database .mdb is on a box that has write caching enabled and the data is mission critical you are going to get a rather nasty wakeup call someday. Been there, done that - lost more than a few nights sleep along with some chunks of you know what. It will take time to get everything moved over but worth it.
With SQL Server you can get more granular with perms. and can push long running processes to the server to boost performance some as well. I've seen companies really push Access harder than they should and it usually bites them in the end. Job security I guess.
If you approach the conversion right, it might not be as tough as you think. I've done major conversions that took as little as six weeks.
Multiple back-ends is a matter of "splitting the work" - and there are a lot of factors with how your app is setup and used that come into play.
For example, my application has 3 distinct types of users (reference users, document issuance users and data management users), and 3 distinct sets of data (tooling, product and documentation). Although there are crossovers (tooling is assigned to prodcuts, for example) it is more efifcient with multiple back-ends becasue in many instances, different users are using seperate resources.
One thing I noticed in another recent thread is using ASP in IE for users who are looking, but not touching, to take Access out of the picture. This would work nicely with MS SQL.
In any case, I agree with AZ KC on these points:
"A strong reason for moving the back end to SQL server is security and safety. "
"I've seen companies really push Access harder than they should and it usually bites them in the end"
I have not handled the conversion myself (but it is on my radar screen as my current project is an example of pushing Access too far and it has been on the table for my client - they're trying to weigh the cost versus benefit themselves).
The IT manager for my client has been pushing SQL Server ever since I set foot in the place - the upper management is baulking at the cost becasue upgrades would be required to the servers and network to support it.
Again to quote "If you approach the conversion right, it might not be as tough as you think." it sounds like AZ KC has some experience.
By the way KC, can you explain "If the backend database .mdb is on a box that has write caching enabled and the data is mission critical you are going to get a rather nasty wakeup call someday." please?
I have an Access Database with about 20 tables and maybe 5000 records. Do you think I should move to SQL Server? I had one person come in and quote, he quoted 6500$ to transfer it all to SQL Server and said it would probably take 1 month.
I am not sure which way to advise my boss. I dont have to push Access to keep my job. I am leaning towards access because it would save the money and I dont see this database getting too much larger.