Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm creating a sliding window loading scenario and the partition functions will be changing their boundaries through time.

I have created some partition functions in my SQL Server Data Tools (SSDT) database project with some initial boundaries hardcoded.

However, as time goes on and the partition functions' boundaries change, future SSDT database publishes will revert the boundaries to the original ones.

Is there some way to handle this scenario gracefully, possibly by disabling the publishing of the partition functions?

I've tried changing the Build Action property of the partition functions in SSDT, from the default Build, to None, but then the project fails to build due to a missing reference in the depending objects.

share|improve this question
2  
I always said that diff based deployment is fundamentally broken. Migrations is such a vastly superior deployment metaphor! –  Remus Rusanu Jun 4 '12 at 17:56
add comment

2 Answers

up vote 7 down vote accepted

I was able to find a solution to my problem — hope this helps someone else.

To avoid each database publish recreating the partition function, you can check the Ignore partition schemes option in the Advanced Publish Settings dialog (Advanced... button in the Database Publish dialog).

From the description of the option (emphasis mine):

Specifies whether differences in partition schemes and functions should be ignored or updated when you publish to a database.

However, if you have defined a partitioned object (table or index) with page or row compression, although the Ignore partition schemes option no longer recreates the partition function, the partitioned object will get recreated anyway.

This happens because the partitioned object gets scripted with the compression defined per partition, and since the object has a different number of partitions than it was originally defined, SSDT recreates the object on publish. E.g. (formatted):

WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS (1)
     , DATA_COMPRESSION = PAGE ON PARTITIONS (2)
     , ...

In order for this not to happen, you can also check the Ignore table options option, in the same Advanced Publish Settings dialog — just keep in mind that you will be ignoring other options, such as ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS (reference of table options).

Advanced Publish Settings dialog

share|improve this answer
    
+1 thanks for posting this –  Remus Rusanu Jun 5 '12 at 11:45
    
Google just led me in here. Awesome info, thank you for sharing. –  jamiet Apr 5 '13 at 7:39
add comment

Ouch, that's not a good situation. As far as I know SSDT doesn't support this. The best workaround I can think of is to use a Pre-Deployment script to store the boundaries in a table and then alter the partition function using those values in the Post-Deployment script.

share|improve this answer
    
Ignore my reply here, judging my Gonsalu's reply above I was clearly wrong. –  jamiet Apr 5 '13 at 7:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.