Post reply

Archive data

  • 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

  • July 28, 2010 at 5:59 pm

    #1199554

    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]

  • July 29, 2010 at 9:46 am

    #1199890

    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.

  • July 29, 2010 at 9:56 am

    #1199904

    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

  • July 29, 2010 at 12:06 pm

    #1199978

    You would use linked servers.

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

  • July 29, 2010 at 12:15 pm

    #1199989

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

    Thanks

    Jim

  • July 29, 2010 at 12:24 pm

    #1199994

    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

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

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