Partitioning - Part 2

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Partitioning - Part 2

Partitioning - Part 2

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Tuesday, September 04, 2007 11:56 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/awarren/3242.asp

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #396217

Christian Buettner-167247

Posted Monday, October 08, 2007 1:21 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:06 AM
Points: 2,525, Visits: 3,618

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

Post #407878

Andy Warren

Posted Monday, October 08, 2007 6:29 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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.

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #407942

jacob sebastian

Posted Tuesday, October 09, 2007 9:23 AM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

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.

.

Post #408544

Mike.Holzwarth

Posted Friday, January 11, 2008 9:25 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 1:21 PM
Points: 25, Visits: 552

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.

Post #441854

« Prev Topic | Next Topic »

Permissions