Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2002-01-22 : 10:45:12
|
| HiI started an assigment in a company which works with tons of Access applications. They are all pretty bad written and interlinked with eachother. IT lost control all together and so I suggested to use SQL server to host all the tables. One advantage is Administration of users, and hopefully performance.I write SQL and stored procedures and love them, I love SQL server generally. So I want to switch to it (since I can make the decisions). I can change somer heavier Access query's to Pass-Through query's, and I know how to start SP's within Access. I also read some posts, so my question is:Can I keep the Access Application site and migrate the data to SQL?And more so, will this be a good decision?60 Users use Access now all day long. There are 10 Access applications and about 200 tables wich host about only a 100.000 records per database.I have the budget to buy a 4 Gb RAM server with 2 processors and 80 Gb harddiscs.My fear is that the applications will still be slow and still use a lot of network capacity.Do you reckon performance will increase (after some tuning on SQL server and making some SQL Pass through query's) ???If I stop using Access as the applications site, and I know the programming knowledge is not high, which software should I use to make the application with??? (I write ASP as well).For another assignment I will build a website which is very database driven (order processing), should I use ASP??? Is het powerfull enough for 600 people working on it at the same time (clicking 180.000 times a day to refresh the information)Thx!Henri |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-22 : 10:57:30
|
I think you have taken the right decision to move from Access to Sql Server. but couple of things to remember. 1)Would highly recommend you to recheck your Database design. now that you are moving to Sql server its right time you cross check the design and remove any loopholes in it.2)you can certainly keep your application on Access and any access to database is recommeded to be done using Stored Procedures. this should increase your query performace to multi folds. 3) Asp is very powerful all the major sites from microsoft to Sqlteam uses Asp , but it all boils down to your Server speed . and the methods you are pulling ur data from the DB.Happy Convertion --------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God is |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-22 : 12:20:05
|
I think if you feel comfortable with ASP development you should go that route. It will cut down tremendously on network traffic and resource utilization for the SQL Server. No matter how good you write the Access app, it'll still keep locks unnecessarily and cause a lot of overhead traffic through the network. This isn't necessarily bad, but you could quickly run into performance problems with too many users.Having said that, migrating to linked tables is fairly quick and easy. Changing the queries to pass-throughs is an excellent idea. The REALLY nice thing about going to linked tables is that you can now put individual copies of the Access database front end on the client's local hard drive, instead of having 60 people share a network file. That will speed up performance amazingly. I'll bet you need to do a compact/repair about once a week or more right now, huh?  ahhhhhh, the memories this brings back...You should also keep Nazim's advice in mind and really make an effort to re-examine and redesign as needed. This will do a lot more for you that anything else. Going the ASP route should make it easier for you to do this, since you pretty much have to start over anyway. |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-01-22 : 12:30:19
|
| If you're feeling brave you can even go the ASP.NET route. ADO.NET has new objects optimised for connecting to SQL Server which should make everything even faster.. |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2002-01-22 : 14:57:21
|
| Thx for the honest and good advice, this really gives me some backup and confidence.I noticed the speed of ASP and the wonderful thing about not having to do anything on any clients. But having read some former post, some drawbacks were written, and since I am on the start on something, I might change language. Oh, and I hate the convention of M$ Access. SQL is really easy to understand (though the posibilities are tremendous), and writing SP's is a sort of hobby.The only thing in this assignment is time, I only have a couple of weeks...Henri |
|
|
|
|
|
|
|
|
SQL server to service Access applications