Mikael,
That is not a stupid idea and it's a problem that faces database designers all the time. Does it make sense for an OLTP system like a POS station to store all the transactions from day 1? No it doesn't. There are a couple of ways to solve your problem. First you could ship your transaction data from your first database to an ODS (Operational Data Store) at the time of the transaction. (Assuming you perform a onetime "clean up" or copy of data to your "slower" system. Then on a routine basis (nightly/weekly/monthly), you purge the unnecessary data from your transactional system. As to your compression/key index problem. if you have some maintenance windows established, after you do a purge/compress, try dropping and rebuilding the indexes on the fly. HTH -Mikey PS. I said that there are other ways to do this, but the solution I mention seems to be the easiest to implement and maintain. There are of course different methods to handle the shipping of transactions too. Good Luck! _____ From: Mikael Sundberg [mailto:[EMAIL PROTECTED] Sent: Monday, November 10, 2008 2:20 AM To: [email protected] Subject: store old seldom used data? Hi, i have a pretty big (20gig+)database that grows pretty fast. This makes it pretty slow in some of the bigger columns. So we have been thinking about creating a storage DB with the data that's hardly used (data is mostly used the first week of its presence. After that it hardly gets used again. But it happens so cant delete it). So the plan was to copy the data from one database to another slower database server with less expensive disk space and so on. But the more I work on it the stupider it feels. Al the foreign keys and regenerated keys and so on must be dealt with somehow. We are pretty bad at rebuilding indexes and stuff so maybe if we just got around to do that more often maybe we wouldn't need the storage DB? Or is there a good way to do this? I am pretty sure im not the first one with this problem. And also, we have had some problem with compressing tables that they don't seem to get the same generated keys anymore. And screws up al the foreign keys and so on. Is there a solution for this? /Micke
