Archiving

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » Archiving

11 posts, Page 1 of 2

1 2 »»

Archiving

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Saturday, June 18, 2011 7:10 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

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

Post #1127816

Rich Weissler

Posted Monday, June 20, 2011 5:44 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:26 AM
Points: 1,581, Visits: 795

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?

Post #1128126

Eric M Russell

Posted Monday, June 20, 2011 7:51 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 1,185, Visits: 3,418

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.

"Winter Is Coming"

Post #1128256

Charles Kincaid

Posted Monday, June 20, 2011 8:37 AM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:56 AM
Points: 772, Visits: 1,828

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.

ATB

Charles Kincaid

Post #1128293

Steve Jones - SSC Editor

Posted Monday, June 20, 2011 9:27 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

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

Post #1128332

Nadrek

Posted Monday, June 20, 2011 10:19 AM

Say Hey Kid

Group: General Forum Members
Last Login: Monday, June 10, 2013 1:08 PM
Points: 679, Visits: 2,038

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.

Post #1128379

TravisDBA

Posted Friday, June 24, 2011 4:29 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 10:46 AM
Points: 1,290, Visits: 3,001

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. BigGrin

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ... BigGrin

Post #1131506

Charles Kincaid

Posted Friday, June 24, 2011 4:59 PM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:56 AM
Points: 772, Visits: 1,828

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

ATB

Charles Kincaid

Post #1131519

TravisDBA

Posted Friday, June 24, 2011 6:12 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 10:46 AM
Points: 1,290, Visits: 3,001

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". BigGrin

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ... BigGrin

Post #1131539

Jeff Moden

Posted Saturday, June 25, 2011 1:21 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112, Visits: 27,038

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. Tongue

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. Smile

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1131676

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions