April 14, 2008 at 3:16 am
Yeah, archiving is a pain in the backside.
I think the main problem is that you have to understand your relationships before you can hive off data to elsewhere, and as our whole application is a bit of a code and fix job we haven't really got a well defined relationship between our business process and the data that relates to it.
For example, if we decided to hive off our sales orders from 2005, you have to find the main records, work out what other data is needed to give these records the correct context, work out the dependencies to other data that we aren't removing then essentially do a date based update/delete transaction. We have got a few of these archiving routines defined for various things.
We cope with this by simply copying to a different db and having a seperate "archive" client which points to that DB, so the data structure remains intact, although there are some bits of our database where archiving isn't relevant, so we just ditch the records. Because the archive copy is missing these ditched records it does fall over if you press the wrong buttons, but it works.
All a bit of a hack then. Oh give me the day when we've developed our app to the point where all our data is organised properly, but it's kind of difficult to sell "ease of maintenance", which I guess is why SOA is such a useful buzzword for the stressed DBA..... Having said that I'm using Microsoft Navision and that isn't really any better organised from a relational point of view...
April 14, 2008 at 3:21 am
Hi Steve,
I agree with you on this subject. I am working on a database with data that dates back to 1994 but the bosses will not let go of it. This is quite a problem and when someone came up with a solution then pleas let me know 'cause I have mulled over this problem for many hours already and could still not come up with a solution.:P
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
April 14, 2008 at 5:49 am
Regarding having full data loads in dev & QA. While it does eliminate the issues around testing with poor or incorrect data distribution and size, it introduces a whole slew of other problems. For example, we used to refresh a development environment from production in about 20 minutes. It's now taking an hour and that time is only going to go up because it's the data growth that's causing the problem. Not to mention needing lots more storage allocated for Dev & QA, having to lean on the Dev & QA teams to clean up the databases that they're not using because allocating them another 100gb of space when they have that much wasted is silly... While it does solve quite a few problems, it introduces others. Unfortunately TANSTAAFL still applies.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 14, 2008 at 8:15 am
Somewhere along the line, business users got real scared about deleting data, especially with all this post-Enron regulation, and application architects have never really made data archiving a priority in their designs. I myself have come in after others to see the horrible mess many application databases have become. Even if I could come up with a plan to cleanly lob off a portion of the database, I'm not really sure I could restore that data back into a working state should someone need it; and, I certainly couldn't do it at a cost that is cheaper than a SATA-based storage unit and some partioning schemes.
April 14, 2008 at 8:26 am
I'm on the side of thinking that keeping business-relevant data, even stuff that was relevant once but may no longer be so, is more important than the minor costs of keeping it.
Sure, archive it to a "historical" database, to keep the current OLTP database leaner and faster, but don't get rid of the data.
It may be possible to compress the data in various ways. It may be possible to normalize it for storage efficiency (if current data has been denormalized for performance, historical data can be renormalized for storage). But don't get rid of it.
Reports against old data can shed important light on current situations. Comparisons against old data often given current data vitally important context. From a management perspective, I'd rather have a slight cost of storage, than end up in a business that suffers from senile dementia (no memory).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2008 at 9:39 am
I guess what I would ask is are we providing a service or are we the business?
If we provide service we serve at the will and desire of the business and we keep the data 'they' want.
Miles...
Not all gray hairs are Dinosaurs!
April 14, 2008 at 10:15 am
We need to archive data properly and let them be accessible.
My insurance deductibles were archived by the Health Insurance company (one of the biggest ones, don't want to mention the name) and they tried to make us pay more deductible when the claim was finally supposed to be paid. I had all explanations of benefits that said that we did pay. The insurance representative was running a summary view on the archived claims and the deductibles would not show. They did finally show when I told them the claim numbers (from more then a year ago) and they found them opening one claim at a time. Took me 4 long calls and a search for the proof. This was certainly the application that could not handle the archived data properly, the data thankfully were there.
Regards,Yelena Varsha
April 14, 2008 at 2:37 pm
One of my customers produces frozen meat products. Thanks to Clarence Birdseye this stuff can stay around for a very long time. The late Col. David Hackworth made an appearance on a History Channel presentation about frozen foods. He related a story about getting turkeys for Christmas dinner for the troops in Vietnam. He was surprised that the birds were code dated prior to 1948. They had been in cold storage for twenty years and were just fine when cooked.
Some of you are in the phone business and have to keep records of every call. Sometimes from both ends. Do you have to keep records after the call is billed. Privacy advocates hope that you don't while law enforcement hopes that you do.
Then there are medical records. Insurance records have been mentioned. Each of the areas we touch with data have their own requirements. We have to help the customs keep relevant data for as long as possible. We also have to be able to help them determine what relevant data means as well as what "for as long as possible" means.
ATBCharles Kincaid
April 18, 2008 at 10:43 am
People at my company keep everything: emails, files that have been corrected and saved with a new name, etc. There's a CYA issue or the user gets asked for something that was done once years ago. We hate to say no so we make sure next time you have it. Personally, I have deleted five year old excel documents and within three days someone asks me for it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply