Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partitioning - Part 2 Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2007 11:56 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 12:51 PM
Points: 6,808, Visits: 1,938
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/awarren/3242.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #396217
Posted Monday, October 8, 2007 1:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Posted Monday, October 8, 2007 6:29 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 12:51 PM
Points: 6,808, Visits: 1,938
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
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #407942
Posted Tuesday, October 9, 2007 9:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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
Posted Friday, January 11, 2008 9:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 18, 2014 11:00 AM
Points: 25, Visits: 735
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 »

Add to briefcase

Permissions Expand / Collapse