At what point should a person consider upgrading Access 2000 database to SQL Server. The access database still seems to work fine, and the vb program that uses it still runs smoothly and not too slow yet, but the access database does have one table with 300,000 records in it and 3 other tables with 150,000-200,000 records in them. What considerations need to be made other than the cost to upgrade to SQL server?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
When to go from Access to SQL Server
- Thread starter barryna
- Start date
If you're considering Desktop SQL Server for your own use, I'm not sure how much that would help with file size. You'd be using same platform.
The advantage of SQLServer on a server system, is primarily to provide many live connections to multiple databases for multiple users. Typically a server machine has more disk and is faster than PC, so a performance improvement is likely, even with only one user.
Other advantages of SQLServer is that it provides excellent security (SQL Slammer worm attack not typical problem). Also, database backup and recovery are more sophisticated. Other applications that use SQLServer can run on top of it.
It uses ANSI SQL 92 standard, Scripted jobs can be run on scheduled basis, SQLMail reports job results and alerts such a log file full, etc.
Also, transactions (updates) can be logged and used for up to the minute recovery along with a prior db backup.
If this is not what you need, I'd suggest staying with Access and adding RAM and faster disk drives and a faster processor. I don't know if multiple processors would benefit Desktop SQLServer (they sure make the Server version rip) and I doubt if Access would. The Desktop version would benefit from adding RAM because it puts part of TEMPDB database in RAM for increased query parsing and execution speed.
Hope this helps
The advantage of SQLServer on a server system, is primarily to provide many live connections to multiple databases for multiple users. Typically a server machine has more disk and is faster than PC, so a performance improvement is likely, even with only one user.
Other advantages of SQLServer is that it provides excellent security (SQL Slammer worm attack not typical problem). Also, database backup and recovery are more sophisticated. Other applications that use SQLServer can run on top of it.
It uses ANSI SQL 92 standard, Scripted jobs can be run on scheduled basis, SQLMail reports job results and alerts such a log file full, etc.
Also, transactions (updates) can be logged and used for up to the minute recovery along with a prior db backup.
If this is not what you need, I'd suggest staying with Access and adding RAM and faster disk drives and a faster processor. I don't know if multiple processors would benefit Desktop SQLServer (they sure make the Server version rip) and I doubt if Access would. The Desktop version would benefit from adding RAM because it puts part of TEMPDB database in RAM for increased query parsing and execution speed.
Hope this helps
- Thread starter
- #3
I have been concered with speed of my execution of my queries. I have everything important indexed, but you can't index everything, so some queries that are complex or are not used often run extremely slow. So, added more RAM on my computer will aid in that problem? I don't have to worry about my Access 2000 database blowing up on me or becoming corrupt because of the size? If that is the case, I can stick with Access 2000 for quite a bit longer then.
If your main problem is query speed, and you're comfortable with Access, I'd consider adding as much RAM as you can. It's as cheap as it will ever be. A computer will put as much program in RAM as there is RAM, and runs about 100 times faster than disk. What it can't put in RAM, it leaves on disk and "pages" in as segments when needed.
My other thought is to review your queries design and see if there is a way you can get same results with either another design or by breaking them into sequential runs.
If your queries read large amounts of data (you update 70% of a table, for example), faster disk drives might help.
My other thought is to review your queries design and see if there is a way you can get same results with either another design or by breaking them into sequential runs.
If your queries read large amounts of data (you update 70% of a table, for example), faster disk drives might help.
- Status
Similar threads
- Question
- Locked
- Question