Best Approach to Archieve DB

  • Example -

    We have a DB which have loads of data & we want to archiev it to some Server in such a way that

    Scenario - Source DB have 5 yrs Data in it, we want to move the 5 yrs Data to Destination DB, & then will keep only one month data in Source DB, that means on monthly basis we will have less data in Sorce DB.

    Please help me a best approach??

    ************************************
    Every Dog has a Tail !!!!! :-D

  • 4 View ... 0 replies :w00t:

    ************************************
    Every Dog has a Tail !!!!! :-D

  • We have done similar things in the past, essentially using SSIS to archive/move data to a new server. On enterprise you could partition the data instead.

    'Only he who wanders finds new paths'

  • David, can you tell me how did u perform this activity via SSIS... please explain in details

    ************************************
    Every Dog has a Tail !!!!! :-D

  • If you can make changes to your architecture, you might want to look into partitioning the data. That makes it very easy to move data out of one database and into another. That's pretty much what it's designed to do. But, it has a number of caveats, the biggest being that you can ensure that retrieval of your data is always going to filter by the same structure that you use to partition the data. If you can't guarantee that, I wouldn't suggest this approach. Otherwise, it's a very viable method. This is an introductory article from Microsoft. There are other resources online to learn more.

    "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

  • I did a Import export activity for transfeering the Tables/data to other DB/server and saved the Packgae as SSIS package to the File Location..

    Now how do i move ahead...

    1) Tables & data imported to destination server via import export

    2) SSIS package stored in some location.

    So the destination server have till date data from source server..

    after a month how should i nmake changes & execute the package, so that the Only Updated or new data should be imported to destination server :w00t:

    ************************************
    Every Dog has a Tail !!!!! :-D

  • Apologies, I am not sure what you are asking.

    If it what action to take next, then it depends on the requirements of the business perhaps. Is the data that you have moved still 'active', if so then yes you want the up to date data on there and perhaps some form of audit trail to determine the changes.

    As for saving the package, there is a specific area in msdb for packages that you can utilise when saving packages.

    'Only he who wanders finds new paths'

  • david.alcock (3/18/2013)


    Apologies, I am not sure what you are asking.

    If it what action to take next, then it depends on the requirements of the business perhaps. Is the data that you have moved still 'active', if so then yes you want the up to date data on there and perhaps some form of audit trail to determine the changes.

    As for saving the package, there is a specific area in msdb for packages that you can utilise when saving packages.

    David, can you tell me how did u perform this activity via SSIS... please explain in details

    ************************************
    Every Dog has a Tail !!!!! :-D

  • Grant Fritchey (3/18/2013)


    If you can make changes to your architecture, you might want to look into partitioning the data.

    You can't switch partition to a switching table that is in different database. Switching table and partition must reside on the same filegroup in order for switch partitions to work.

    The only benefit would be much faster delete (drop partition) of the data you already copied - worth using.

    I would do a partition switch, bcp-out switching table to a file in native format, bcp-in that file to destination db switching table, and then switch that into destination partitioned table. That is by far the fastest method.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • Well we were mainly moving to new structures so the migration was two part, moving recent data to a faster server and the older data shifted to cheaper archive storage. SSIS was utilised in very simple form, a data flow with source, destination and lookup tasks were the main components as well as script tasks for some customisation. Lookup tasks are very handy for incremental loads as you may be doing.

    Highly recommend you give this a read if you havent already: http://www.sqlservercentral.com/articles/Stairway+Series/76390/

    We didnt have enterprise otherwise I would have implemented some form of partitioning for sure.

    'Only he who wanders finds new paths'

  • runal_jagtap (3/18/2013)


    4 View ... 0 replies :w00t:

    14 minutes... broad subject. Need more patience. 😉

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • runal_jagtap (3/18/2013)


    Example -

    We have a DB which have loads of data & we want to archiev it to some Server in such a way that

    Scenario - Source DB have 5 yrs Data in it, we want to move the 5 yrs Data to Destination DB, & then will keep only one month data in Source DB, that means on monthly basis we will have less data in Sorce DB.

    Please help me a best approach??

    Will you ever have the need to access the data that's more than a month old through a GUI or stored procedure?

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, No we would never need a data a month old, incase if someone needs the data we can retrieve it from Archive DB..

    but whats the best approach???:w00t:

    ************************************
    Every Dog has a Tail !!!!! :-D

  • One very effective approach would be to "partition" the table but that's only if you have the Enterprise Edition and, IIRC, if you don't mind the "archive" being in the same database. What that will do for you is make it almost instant to "archive" a previous month and it will reduce the nightly maintenance because you won't have to rebuild or reorganize the idexes for the entire table because, if you do it right, the indexes are partitioned, as well.

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff 🙂

    Currently me planning to go with SSIS Package Automation...

    Well see how i succedd.. incase any query i will come here again.. stating

    65 views & 0 replies :w00t: ( 😀 )

    ************************************
    Every Dog has a Tail !!!!! :-D

Viewing 15 posts - 1 through 15 (of 33 total)

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