SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partitioning - Part 2


Partitioning - Part 2

Author
Message
Andy Warren
Andy Warren
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: Moderators
Points: 24443 Visits: 2746
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
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5177 Visits: 3889
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
Andy Warren
Andy Warren
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: Moderators
Points: 24443 Visits: 2746
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
jacob sebastian
jacob sebastian
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2558 Visits: 2523
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.

.
Mike.Holzwarth
Mike.Holzwarth
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 869
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search