Archive data

Question

Archive data

jim.rasmussen

SSC Eights!

Points: 999
More actions
July 28, 2010 at 3:13 pm

#224644

I hope this is in the correct forum. I have heard that with migrating to Server 2005 that there is an auto archive process that is not available with Server 2000. I have been looking for information on this and all I can find is to use BizTalk( more specifically "BizTalk Tracking (BizTalkDTADb) database"). Is this correct? Where is this database? From what I have found BizTalk is more B2B. All I am looking to do is archive data.
Scenario:
I have data from the past 10 years. Performance is slow. I want to "archive" data up until the past 6 months. I have a stored proc that generates data for a report with criteria of start date and end date. What happens if the report is asking for labor summary for the past 12 months?
Any thoughts of a process that will achieve this?
Thanks,
Jim

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 1

I have never heard of an auto archive function. There is no way that microsoft would be able to implement something that audits every table in every user database that would intelligently archive off data for a particular period.

I would suggest that you find out from the person that told you about the feature and ask them for moreinformation on the subject. You may find out that it's a feature on a third party product that has an auto archive eature for their particular product.

Hope this helps,
Rich

[p]
[/p]

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 2

Scenario:
I have data from the past 10 years. Performance is slow. I want to "archive" data up until the past 6 months. I have a stored proc that generates data for a report with criteria of start date and end date. What happens if the report is asking for labor summary for the past 12 months?
Any thoughts of a process that will achieve this?
Thanks,
Jim

Something like this.

-Archive data to a seperate table/server.

-During archive process write to a custom parameter table a field call ArchiveDate which will contain the latest date of the archived data

-When you call the stored procedure evaluate the start date against you ArchiveDate

-You'll need to fork your stored procedure logic here. If you need your archived data you can UNION your archived data and productions data or just look at the production data as needed.

If performance is slow I would try to resolve this first before introducing the complication of having archived data.

jim.rasmussen SSC Eights! Points: 999 More actions · Answer 3

Thanks for the info. I am doing something similar already. But you raised a question about archiving to a different server. I am aware of getting data from different db's on the same server ie PROD..TABLE UNION ARCH..TABLE but how would I retrieve date from a server named ARCHIVE from server named PRODUCTION?

Thanks

Jim

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 4

You would use linked servers.

http://msdn.microsoft.com/en-us/library/ms188279(SQL.90).aspx

jim.rasmussen SSC Eights! Points: 999 More actions · Answer 5

I know this is the 2005 forum, but would this work in Server 2000?

Thanks

Jim

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 6

Yes linked servers existed in 2000.

http://www.extremeexperts.com/sql/yukon/ArchivingData.aspx

http://www.mssqltips.com/tip.asp?tip=1121

http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx