September 4, 2007 at 11:56 am
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/awarren/3242.asp
October 8, 2007 at 1:21 am
Hello Andy,
thanks for the article. But I think this only moves the problem around.
Archiving in my eyes means to move the data out of SQL Server.
Therefore I usually take the following approach:
I have a DTS Package that exports data from a table using BCP and deletes the data after export.
It takes the table name, retention period in months and date column as argument.
When exported, you zip the data (most cases this shrinks the size to 1% of the original size).
But I truly agree that archiving is an important topic. In "dark" past, I usually did not think about archiving until the server was too full. But thank god these times are over now. Every application now requires a space estimation (initial size and growth per month) and more important - an archiving strategy before you deploy the application. This archiving strategy is usually the mentioned DTS Package which can be called for any table that has a suitable date column.
One implication of the DTS Package is that it requires the original table structure to be able to recover the data. So if you add or remove one column from the table, a BCP export will not import successfully back into the table if it does not also contain this column. So this means you also need proper source control to make sure you can not only restore the data but also the structure to a point in time.
Best Regards,
Chris Büttner
October 8, 2007 at 6:29 am
You're right that archiving isn't technically partitioning, but it's often thought of at the same time and I think people often apply one technique instead of the other. Next article will be focused on pure partitioning.
October 9, 2007 at 9:23 am
Very interesting stuff, and I am glad that I found it right at the moment when we were thinking about partitioning a couple of our databases. Waiting for the next installment!
I would like to add that, while working with SQL Server 2005, we can avoid using a #TEMP table and use the OUTPUT directive to delete records from the main table, and insert the deleted records to an audit/archive table.
.
January 11, 2008 at 9:25 am
Just wondering about how partitoning works with replication. In my environment we have lots of reference data that we store on one server and use one way transactional replication to others. I was looking at partitioning the tables to break out the older data that is acccessed less frequently, but don't know if this is possible, or reccomended.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply