Archiving and Purging Data

15 pts.
Tags:
SQL Server administration
SQL Server archiving
We deal with a very large database that grows at an avrage of 50GB a month. The current database size is 700 GB. All the transaction tables are horizontally partitioned to seperate filegroups assigned to a individual data files holding each months data. What would be the best strategy to retain another archive server that holds data prior to 1year and retain the production server with the recent 1 year data?
ASKED: July 2, 2008  10:24 AM
UPDATED: November 1, 2011  9:57 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Your current technique seams sound. I would recommend sticking with the existing method, and simply moving the data from the current server to the archive server using DTS or SSIS on a daily basis. Then on the server with the current years worth of data delete all data which is over on year old.

Are you using SQL 2000 or SQL 2005? If you are using SQL 2005 are you using the native table partitioning or manual partitioning?

From R:
You might find this interesting

http://technet.microsoft.com/en-us/magazine/cc160974(TechNet.10).aspx

Refer to the below readily available products that u can use for archival and purge if you using Oracle Database

http://www.apmg.co.in/software_products.php?app_id=apm

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    65,490 pointsBadges:
    report
  • Roops
    i am using SQL 2005. i have created partitions using partition functions and partition scheme with corresponding data file to hold each months data. i have used a numeric partition key. All the transaction tables has this key in the table design. The backend procedures are also coded based on this partition key..
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following