Automate partition creation

Hi,

Is there any example out there on how to automate the creation of time based partitions?
As an example, I would like to create 1 partition for each quarter and when a new quarter start have a new partition automatically added with the same attributes than the previous one.

I would also have the oldest 4 partitions automatically deleted as soon as the total number of partitions reaches 13.

Any thoughts or links?

Thanks,

Philippe

Hi Philippe,

The Project REAL Analysis Services Technical Drilldown discusses one implementation of such automation:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

>>

Project REAL: Analysis Services Technical Drilldown

By Dave Wickert, Microsoft Corporation

SQL Server Technical Article
Published: September 2005

Appendix A: Automating Partition Creation

The Project REAL design uses partitioning quite heavily. The production system has more than 220 extremely large partitions. The sample data uses over 125 partitions that are only tens of thousands of records per partition. The full production system has 180 to 200 million records per partition. With so many partitions, extensive typing was required to create each partition every time we generated a new schema.

So, as the saying goes, “When the going gets rough, a programmer writes a program.”

This appendix documents the BuildASPartition SQL Server 2005 Integration Services package that we created to automate the building of Analysis Services measure group partitions in SQL Server 2005 Analysis Services databases. This package synchronizes the relational partition scheme with the Analysis Services partition scheme. It loops through the relational database looking for a weekly fact table partition (by using a table naming convention). If a relational table is found, it looks to see if an Analysis Services measure group partition already exists (using the same naming convention). If not, it constructs and executes a XMLA script that creates it.

>>

|||

Philippe,

Add your comments here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=461211&SiteID=1

-Jamie