Data Overload, How Do I Manage?
December 9, 2005 7:04 AM
How can I manage my databases that are exploding in size? Can I run SQL Server on my laptop?
I have many, many, many Access databases which drive my work. One of these databases is currently split across several MDB files, since they are so large. One year of data is just under 2GB, which is the threshold for Access.
I thought about several options, but the one that came to mind that I thought might work best would be to run SQL Server on my laptop, and simply run the databases through it.
Has anyone had any experience with these types of enlarging databases, and might be able to offer either an alternative, or your experience on running SQL server on your production machine. (It would be MSSQL).
I have many, many, many Access databases which drive my work. One of these databases is currently split across several MDB files, since they are so large. One year of data is just under 2GB, which is the threshold for Access.
I thought about several options, but the one that came to mind that I thought might work best would be to run SQL Server on my laptop, and simply run the databases through it.
Has anyone had any experience with these types of enlarging databases, and might be able to offer either an alternative, or your experience on running SQL server on your production machine. (It would be MSSQL).
I think you definitely want to get away from Jet (the DB backend used by Access) ASAP.
posted by Rhomboid at 7:38 AM on December 9, 2005
posted by Rhomboid at 7:38 AM on December 9, 2005
Access quickly shows its weaknesses once you reach this size (scability, speed, indexing issues, need of use of the "repair and compact" function, osteoporisis...). You absolutely need to move off of Access since you've more than outgrown it.
A client I'm working for used an Access database accessed by several users within the company, for a rebate fulfillment system. One database had approximately 60,000 records and was already up to 300+ MB. It would frequently corrupt, because Access doesn't handle table locking, or frankly, large table sizes very well. We've moved them to a single MySQL system and built a web application around it.
The transition was a bit annoying at first, but now there are over 3,000,000 records in the space of the original 60,000, with over a hundred users accessing the backend, and thousands more on the web-based rebate fulfillment front end. We never have to use anything called "repair and compact". :-)
This is all by way of saying that, yes, you can and should move to something like MSSQL, and do so as soon as possible. Make sure any forms or stored queries or all those other pleasantries Access tries to simplify will still be reproducible first, and try toying around with the trial version of MS SQL first, to make sure you can copy your data safely and see that things will transition nicely.
Also, consider the benefits, if it's at all feasible, of running this on a web server, instead of locally on your laptop. You'll have a machine dedicated to storing and serving your data, securely and safely. Backup solutions are much more easily automated and offered by many web hosts and colocation services. Of course, the need of an Internet connection whenever you want access to the database might be reason enough not to for you, but you won't have to worry about a fickle laptop taking down your business some day, either.
MySQL is a great, free solution, but it's not as much for beginners. It doesn't have a viable front end, and is entirely command-line based. (It does have PHPMyAdmin, which is a PHP-based front end, requiring you run PHP and a web server on your local machine if you're hosting MySQL locally. This is nice, but an awful lot of configuration just to avoid using MSSQL's front end.) And frankly, you're probably going to be more familiar with MSSQL, since they have tutorials and lesson for those transitioning from Access.
Good luck and godspeed. Get off Access as soon as humanly possible, lest your data trip into the rift.
posted by disillusioned at 9:04 AM on December 9, 2005
A client I'm working for used an Access database accessed by several users within the company, for a rebate fulfillment system. One database had approximately 60,000 records and was already up to 300+ MB. It would frequently corrupt, because Access doesn't handle table locking, or frankly, large table sizes very well. We've moved them to a single MySQL system and built a web application around it.
The transition was a bit annoying at first, but now there are over 3,000,000 records in the space of the original 60,000, with over a hundred users accessing the backend, and thousands more on the web-based rebate fulfillment front end. We never have to use anything called "repair and compact". :-)
This is all by way of saying that, yes, you can and should move to something like MSSQL, and do so as soon as possible. Make sure any forms or stored queries or all those other pleasantries Access tries to simplify will still be reproducible first, and try toying around with the trial version of MS SQL first, to make sure you can copy your data safely and see that things will transition nicely.
Also, consider the benefits, if it's at all feasible, of running this on a web server, instead of locally on your laptop. You'll have a machine dedicated to storing and serving your data, securely and safely. Backup solutions are much more easily automated and offered by many web hosts and colocation services. Of course, the need of an Internet connection whenever you want access to the database might be reason enough not to for you, but you won't have to worry about a fickle laptop taking down your business some day, either.
MySQL is a great, free solution, but it's not as much for beginners. It doesn't have a viable front end, and is entirely command-line based. (It does have PHPMyAdmin, which is a PHP-based front end, requiring you run PHP and a web server on your local machine if you're hosting MySQL locally. This is nice, but an awful lot of configuration just to avoid using MSSQL's front end.) And frankly, you're probably going to be more familiar with MSSQL, since they have tutorials and lesson for those transitioning from Access.
Good luck and godspeed. Get off Access as soon as humanly possible, lest your data trip into the rift.
posted by disillusioned at 9:04 AM on December 9, 2005
Even MSDE 7.0 will provide you leaps and bounds of power and capability in storing information above Access. As disillusioned mentioned, Access is prone to corruption, especially at the size you mention. It's a hell of a thing, seeing all that data just go away...
You can find MSDE sitting around on any large-scale application that requires MS SQL support. Typically it's bundled under license as a backup program in case your company doesn't already use SQL Server. It came with both Veritas Backup Exec and Blackberry Enterprise Server for me.
If you don't want to go with the latest versions of SQL Server, you can always manage to find a version of 7.0 or 2000 up on eBay. Make sure that once you install it, you patch it up to the most recent patches immediately. You don't want to become a carrier for the SQL Slammer virus.
posted by thanotopsis at 9:09 AM on December 9, 2005
You can find MSDE sitting around on any large-scale application that requires MS SQL support. Typically it's bundled under license as a backup program in case your company doesn't already use SQL Server. It came with both Veritas Backup Exec and Blackberry Enterprise Server for me.
If you don't want to go with the latest versions of SQL Server, you can always manage to find a version of 7.0 or 2000 up on eBay. Make sure that once you install it, you patch it up to the most recent patches immediately. You don't want to become a carrier for the SQL Slammer virus.
posted by thanotopsis at 9:09 AM on December 9, 2005
The problem with MSDE (or SQL Server 2005 Express Edition, which is what they call it now) is that it only will handle databases up to 4GB in size. The upside is that it's free, and its easy to move data up to the "real" version of MSSQL once 4GB gets to be too much.
But I agree with everyone above who says that you should get off of Access as quickly as possible.
posted by stupidcomputernickname at 10:10 AM on December 9, 2005
But I agree with everyone above who says that you should get off of Access as quickly as possible.
posted by stupidcomputernickname at 10:10 AM on December 9, 2005
I guess I should mention that I inherited these databases, just so everyone doesn't think I'm insane enough to have 10's of gig big Access databases lying around.
I am concerned because I don't have SP2 on my machine, corporate won't support it, but its needed to run the new version of SQL, either standard or express. I had a machine crash once on an SP2 install.
Is there a non-ghost way to image my machine to an external hard drive prior to doing this?
A web server isn't an option. I need to have these databases with me, even if I am not on a network. I also can't obtain more than once machine at a time without some unreasonable amount of fighting and paperwork.
posted by benjh at 10:28 AM on December 9, 2005
I am concerned because I don't have SP2 on my machine, corporate won't support it, but its needed to run the new version of SQL, either standard or express. I had a machine crash once on an SP2 install.
Is there a non-ghost way to image my machine to an external hard drive prior to doing this?
A web server isn't an option. I need to have these databases with me, even if I am not on a network. I also can't obtain more than once machine at a time without some unreasonable amount of fighting and paperwork.
posted by benjh at 10:28 AM on December 9, 2005
I think you definitely want to get away from Jet
This is a valid answer for almost any database question imaginable.
posted by normy at 2:08 PM on December 9, 2005
This is a valid answer for almost any database question imaginable.
posted by normy at 2:08 PM on December 9, 2005
Another non-MS db that might be worth looking into for your purposes would be Firebird.
posted by normy at 2:15 PM on December 9, 2005
posted by normy at 2:15 PM on December 9, 2005
This thread is closed to new comments.
posted by stupidcomputernickname at 7:15 AM on December 9, 2005