Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Archiving Data in SQL Server

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (6)   |   Related Tips: More > Database Administration

Problem
Every system has it, do not feel like you are the only one.  Step up to the plate and correct it.  That is large amounts of data that were once needed for the business that can now be archived.   This data could be a large performance issue, because you are not able to perform maintenance on the table(s) because the time needed exceeds your maintenance window.  As a DBA or developer just because you do not think the data is needed may not mean that it can just be deleted.  Many industries have legal requirements to maintain the data.  On the other side of the coin though, just because a business person says the data is needed does not mean it has to be kept in the primary OLTP database that your entire company uses.  Seek out the facts and determine which strategy is right for your organization.

Solution
When it comes to archiving data, many options are available, but some preliminary items need to be addressed.  These items include:

  • Research the legal requirements - Make sure you understand your industry requirements, some have no data retention requirements while others require 7 years.  Make sure you understand your requirements first and then design a solution.
  • Data analysis - Review the data in your application to determine the key tables and dependencies between the tables when archiving.  Be sure to also capture the distribution of data on a monthly, quarterly or yearly basis to understand how much data can be archived.
  • Understand the data usage - Meet with the users to understand how they use the potentially archived data and if it is archived how the applications will need to change.  If you are not responsible for the application development, ensure you meet with the development team to ensure the changes are feasible.
  • Provide a business case - Justification for archiving the data is essential when taking on this project.  These items are typically attractive to business and IT management:
  • Automate the data archiving process - Setup rules with the users to be able to automate the archive process so it occurs regularly on a weekly, monthly or quarterly basis

Once you understand how the data can be archived, now the technical decisions need to be made.  Depending on the answers to the questions above, one or a combination of the following approaches can be taken:

  • If the data needs to reside in the same database:
    • Move (insert and delete) the archive data to separate table(s) with a prefix to denote the tables are archive related such as 'arc_'
    • Create the table(s) in separate filegroups on separate disks to improve IO performance
    • Use a view to join the old and new data if the users continue to need to access the data
    • Modify the front end code to use the view rather than the base table

     

  • If the data does not need to reside in the same database:
    • Move (insert and delete) the archive data to separate table(s) in another database on the server or to a separate database on another server
    • Have users request access to the data for specific queries or change the application to use a linked server to access the archived data

     

  • If the data is just not needed:
    • Back up the data, verify the backup is restorable, note the date of the last backup with the archived data and just delete the data
    • If the data is needed due to some unforeseen reason by the users, restore the noted backups to access the data

 

  • Automation options
    • Based on the application find out if the vendor has already implemented an archiving feature that just needed to be enabled.
    • If an automated option is not available, build a process with DTS or SSIS to move the data from the source system to the archive system. 
    • If significant amounts of data are changed, be sure to perform SQL Server maintenance as a portion of the archive process.

Next Steps



Last Update: 11/28/2006


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, November 28, 2012 - 11:09:47 AM - Lelala Read The Tip

Thanks for that interesting post;

i'm wondering, why does MSSQL do not support "archive tables" as we can have it in MySQL, for example? Would be very cool from Microsoft, if they would include something like this!

Regards,

Martin

Lelala


Wednesday, November 28, 2012 - 1:16:18 PM - Jeremy Kadlec Read The Tip

Lelala,

Thank you for the post.  I am not familiar with the functionality in MySQL.  Can you please explain it?

Thank you,
Jeremy Kadlec


Thursday, November 29, 2012 - 11:12:48 AM - Lelala Read The Tip

Hi Jeremy,

basicly its a special way of storing the data for long term use in a very special table, but you can't change it anymore (maybe they compress it or similar). The idea is very nice, because for things like statistics this implementation is ideal:

I've looked for their original dokumentation post, please:

http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html

I'm familiar with the idea  of "backup-tables" in MSSQL, but we use standard MSSQL-tables to achieve what we want - the MySQL thing seems a little bit more performant and uses less space than what we can achieve with MSSQL (and no, i do never use server-side full-DB compressions, because this is the end, if you have a disk crash or similar)

Regards

 


Thursday, November 29, 2012 - 4:23:44 PM - Jeremy Kadlec Read The Tip

Lelala,

Thank you for the additional information.

Thank you,
Jeremy Kadlec


Friday, February 01, 2013 - 7:45:54 AM - Nandkumar Read The Tip

Thanks for the grat information really it helped


Thursday, September 19, 2013 - 11:04:10 AM - ASIF GHANCHI Read The Tip

Please check below link to configure auto archive and purge in sql server table.

http://www.sqlscientist.com/2012/09/auto-maintain-archival-process.html



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.