Respect existing partitions and not create new

Question

We have an existing database and a DACPAC-based deployment procedure to send it schema updates at deploy time. The database has an existing partition function and existing partition schemes, and the application using the database will create new partition schemes at runtime.. Currently we see SSDT dropping the existing partitions and rebuilding the table. Column data is not lost but the application-created partition schemes are lost.

What can we do to stop SSDT from dropping existing partition schemes when we deploy the DACPAC to the existing database instance?

EDIT: We have already tried both "Ignore partition schemes" (suggested by https://dba.stackexchange.com/a/18826/23463) and "Ignore partition functions" (same advanced settings area). This did not change the outcome.

EDIT 2: All these options have been tried. Here's the message from my dbas:

This is about our app where Partitions are created at runtime. Anytime we apply master dacpac to DB it recreated partition functions and schemes dropping any partitions created at runtime. We tried: several options like below., based on docs they are supposed to work but not working

/p:ExcludeObjectType=PartitionSchemes
/p:ExcludeObjectType=PartitionFunctions 
/p:IgnoreObjectPlacementOnPartitionScheme=true 
/p:IgnorePartitionSchemes=true 
/p:IgnoreTablePartitionOptions=true 
/p:DoNotDropObjectType=PartitionSchemes  
/p:DoNotDropObjectType=PartitionFunctions

If you could provide a worked example with code or concrete details it might help to orient people to your question, which is currently somewhat hard to follow. — Michael B. Currie, Commented Oct 28, 2024 at 14:52
@Ross there are two more options "Ignore table placement on file groups/partitions" or something like that - try disabling them too. — IVNSTN, Commented Oct 28, 2024 at 19:02
They have been tried already. Question updated with additional info from DBAs. — Ross Presser, Commented Oct 30, 2024 at 15:10
Add sqlpackage version to your question and check if there is an update available. We do have partiitoned tables in many DBs and all of those projects are deployed via sqlpackage automatically. The partition issue was resolved long time ago. I'll take a look at specific number of sqlpackage arguments but I don't think there is something more for partitions. — IVNSTN, Commented Oct 31, 2024 at 15:24
@RossPresser /p:IgnoreIndexOptions=true /p:IgnoreTableOptions=true — IVNSTN, Commented Oct 31, 2024 at 15:30

IVNSTN · Accepted Answer · 2024-11-26 07:11:57Z

1

Additional magic is needed to leave partitions alone:

/p:IgnoreIndexOptions=true /p:IgnoreTableOptions=true

answered Nov 26, 2024 at 7:11

IVNSTN

9,3445 gold badges24 silver badges41 bronze badges

Add a comment |

Collectives™ on Stack Overflow

Respect existing partitions and not create new

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related