Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Archiving


Archiving

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36074 Visits: 18736
Comments posted to this topic are about the item Archiving

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Rich Weissler
Rich Weissler
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2015 Visits: 954
I start many of my mornings looking forward to my daily reading of the SQLServerCentral email, and frequently use it to leap into a new topic and spend twenty of thirty minutes learning something new. Over the years, I've occasionally help deal with archiving issues, and often the question arises: "Is the data older than 'x' still of any business use, or is the data still loved?" Very often, old data is maintained because it is forgotten and only the dba sees the slow accumulation.

This morning, I can't help feeling that the there is a link missing attached to the sentence in today's editorial. Should "This article talks about the problem of data access from a storage point of view,[...]" point to something?
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4590 Visits: 9529
One of my primary projects is developing and maintaining a reporting datamart for the accounting department. When a report is run, a dataset containing key and fact columns are queried from the corporate ODS (operational data store) into what we call interface tables and retained there for archival purposes. Each reporting dataset is keyed on a report header table with columns that include the runtime parameters, time the query started, when it completed, and text of the SQL SELECT statement. When called upon, we can go back and retreive the exact dataset for any report produced in the past as well as answer questions about it's runtime performance or changes in SQL coding over time. That's a technique that I developed early in my career and it has proven it's worth on many projects. The key point here is that we're not tracking every change that occurs in the ODS (that's more or less maintained by the ODS team), the accounting datamart only archives data aggregated for the department's purpose, so it's kept relatively small and manageble.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 2383
I get wrapped into a tight loop on this subject. As an industry we have data retention rules and regulations, either on us directly or the customers we serve. I'm OK with that (like there was a choice) as far as it goes. Still a close examination of the requirements serves well. If microfilm or other offline storage is allowed -- do it. Often what is allowed is that the information must be electronically available but its OK to make it the subject of a targeted search. That looks like a separate database to me.

For many of us we have huge servers with tremendous storage capabilities. For folks like me we are often confronted with Express or the Compact Edition. In those environments size matters. The smaller the better. We also are in a position such that large parts of the database are intermediary in nature. We take a sales order in the field on a device and upload that to our server, then it gets exported to the back end system (SAP, PeopleSoft, Dynamics (Great Plains), whatever). The order gets delivered to the customer and order confirmation get funneled up the same way. The back end system has it, why is it still in my database after 6 or more years?

Then if you get bored try this. In a design meeting when the organizer asks if there is anything not covered in the specifications, raise your hand an utter the three deadly words. DATA REMOVAL POLICY. Then duck as stuff will come flying in your direction.

ATBCharles Kincaid
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36074 Visits: 18736
Rich Weissler (6/20/2011)

This morning, I can't help feeling that the there is a link missing attached to the sentence in today's editorial. Should "This article talks about the problem of data access from a storage point of view,[...]" point to something?


My apologies. The cut/paste from OneNote last stopped bringing cross links for some reason.

I've edited things and here is the link http://itknowledgeexchange.techtarget.com/storage-soup/dont-turn-data-archiving-into-data-hoarding/?track=NL-52&ad=835610&asrc=EM_NLN_13976274&uid=2052671

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 2673
Charles Kincaid (6/20/2011)
In a design meeting when the organizer asks if there is anything not covered in the specifications, raise your hand an utter the three deadly words. DATA REMOVAL POLICY. Then duck as stuff will come flying in your direction.


I see your Data Removal Policy, and raise you a Legal Hold. Removing old data is "good"; removing old data that is subject to discovery in a multi-year lawsuit is... "not good".

In general, I agree that archiving (not backups, but archiving) and removing data past its retention policy is a good idea, but this gets very deep into data classification, very fast; it's important, but often "too difficult" to deal with, and as such it never gets done.
TravisDBA
TravisDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1462 Visits: 3069
Data Archival is a process that should always be given as much weight at the original design of the database as other factors are, but sadly is not. I am not sure whether this due to indifference or complacency at design time, or just flat out ignorance. :-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 2383
I agree with points from both Nadrek and TravisDBA.

Right on with having to hold for legal reasons. I got that. Many of my customers are food producers or food handlers. A lot of the time they deal in frozen product. Looking at the work of Clarence Birdseye it is easy to see where one could have frozen product in inventory for over a hundred years. Then if you sell that product to a retailer who keeps in in good frozen condition it might be on their shelves for another hundred years. In the case of a product recall I might be forced to keep that data the whole time. Medical and Tax records have to be retained. Each with their own legal requirements.

Travis, I fear that it is fear that drives the resistance. Fear generated by the above. Developers don't want to raise costs by having a data removal policy in the specifications somebody has to go research the legal requirements

ATBCharles Kincaid
TravisDBA
TravisDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1462 Visits: 3069
Charles Kincaid (6/24/2011)
Travis, I fear that it is fear that drives the resistance. Fear generated by the above. Developers don't want to raise costs by having a data removal policy in the specifications somebody has to go research the legal requirements


Very good point Charles, but your point is making my point on ignorance. In the immortal words of Arnold Heny Glasow, an American Humorist (1905-1998) "Fear is the lengthened shadow of ignorance". :-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45006 Visits: 39880
Charles Kincaid (6/24/2011)
Looking at the work of Clarence Birdseye it is easy to see where one could have frozen product in inventory for over a hundred years. Then if you sell that product to a retailer who keeps in in good frozen condition it might be on their shelves for another hundred years.


Heh... my first point is... sometimes it tastes that way. :-P

Although I certainly understand the jist of the Birdseye example, my suggestion is that it's not a practical example. If they have such old inventory, then they have a much larger problem than figuring out when to archive data and take it off-line. :-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search