locked
Best approach to manage large volumes (each table size is around 150 GB) of data RRS feed

  • Question

  • Folks,

    I have a requirement to manage the older data by moving from current table to its history table in other database. Can someone advise me what is the best approach to move high volumes of data (each table size is around 150 GB)?

    And also the current table should have only the recent 1 month data. If the data becomes one month older, it should automatically move to the history table.

    Thanks in advance.


    Regards Chenchi MSSQL Server DBA


    • Edited by Chreddy S Friday, March 1, 2013 5:08 PM
    Friday, March 1, 2013 5:04 PM

Answers

  • Hi,

    Have you looked into Table partitioning ? This will make more sense, but again "it depends" on your need.

    http://technet.microsoft.com/en-us/library/dd578580%28v=sql.100%29.aspx

    http://msdn.microsoft.com/en-us/library/ms345146%28v=sql.90%29.aspx

    If you want to have the data is a different database called "archive database" then use an SSIS package scheduled from SQL Agent job to archive the data.

    HTH


    \\K

    Friday, March 1, 2013 5:18 PM
  • Hello,

    Unfortunately it isn't possible to use table partitioning across databases.

    What could work for you with little to moderate effort could be either partitioned views or distributed partitioned views at the object level. In terms of how will you move the data, sql_quest29's suggestion applies if it's possible inside the same database. Switch the partition with the older data out and then either use DPVs or whatever your favorite data movement styles are to move teh data around. The biggest thing is you don't want to impact the workload on the main table too much. Since lock escalation will be a killer to concurrency using small delete batches might be a good work around if you're using standard edition.

    Other will have different opinions based off of environments and usage, all are fair. You'll need to find one that suits your business needs, style, and infrastructure.

    -Sean


    Sean Gallardy | Blog | Twitter

    Friday, March 1, 2013 7:39 PM
    Answerer

All replies

  • Hi,

    Have you looked into Table partitioning ? This will make more sense, but again "it depends" on your need.

    http://technet.microsoft.com/en-us/library/dd578580%28v=sql.100%29.aspx

    http://msdn.microsoft.com/en-us/library/ms345146%28v=sql.90%29.aspx

    If you want to have the data is a different database called "archive database" then use an SSIS package scheduled from SQL Agent job to archive the data.

    HTH


    \\K

    Friday, March 1, 2013 5:18 PM
  • Hello,

    Unfortunately it isn't possible to use table partitioning across databases.

    What could work for you with little to moderate effort could be either partitioned views or distributed partitioned views at the object level. In terms of how will you move the data, sql_quest29's suggestion applies if it's possible inside the same database. Switch the partition with the older data out and then either use DPVs or whatever your favorite data movement styles are to move teh data around. The biggest thing is you don't want to impact the workload on the main table too much. Since lock escalation will be a killer to concurrency using small delete batches might be a good work around if you're using standard edition.

    Other will have different opinions based off of environments and usage, all are fair. You'll need to find one that suits your business needs, style, and infrastructure.

    -Sean


    Sean Gallardy | Blog | Twitter

    Friday, March 1, 2013 7:39 PM
    Answerer
  • Here is a suggestion:

    1. Backup current database, restore it as history database

    2. Rename current_table as zzz_current_table in current database

    3. SELECT * INTO current_table from zzz_current_table where {what you want to keep}

    4. Setup indexes, constraints on current_table

    5. Develop a stored procedure or SSIS package for archiving

    6. Setup a daily archiving job with SQL Server Agent


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Saturday, March 2, 2013 9:15 AM