question

J-I-M-B-O avatar image
0 Votes"
J-I-M-B-O asked J-I-M-B-O commented

SQLPACKAGE.EXE - Filegroup Placement Not Ignored for Indexes

The deployment profile is set to ignore filegroups placement, but the generated change script is still creating indexes on specific filegroups. Using a dacpac created from an existing database as a source and a different database as a target, the change script generated has create index statements on filegroups that do not exist on the target database. As a workaround, the filegroups can be created on the target databases via a different process, but I would have assumed that this should not be necessary as it was already specified in the deployment profile to ignore filegroup placement.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered J-I-M-B-O commented

Ignore filegroup placement means do not MOVE indexes created on a different filegroup than in the code. If your code specified a file group, it will be created on that file group.

Specifies whether differences in the placement of objects in FILEGROUPs should be ignored or updated when you publish to a database.

https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-script?view=sql-server-ver15

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@TomPhillips-1744 are you saying that the IgnoreFilegroupPlacement property only pertains to existing objects in both the source and target databases? For CREATE statements, it will always create them on the filegroup specified in the source? When I read the description, it sounded like filegroup placement was completely ignored if that option was set.

0 Votes 0 ·

@TomPhillips-1744 Confirmed. If the index exists, then the filegroup placement is ignored. Looks like the IgnoreFilegroupPlacement is strictly used only when the object exists in both the source and target.

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered J-I-M-B-O edited

Hi @J-I-M-B-O,

Welcome to Microsoft Q&A!

Clustered or nonclustered index did you create? If there is a clustered index on a table, the data and the clustered index always reside in the same filegroup. Please refer to this thread and this blog to get more information.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I don't think the indexes being clustered or non-clusters should matter. The options to ignore placement on filegroups was selected. For the sake of your question, the indexes in question are non-clustered indexes that are placed in a different filegroup on the source database. Those filegroups do not exist in the target database. When the change script is created using the dacpac created from the source database to the target database, the CREATE INDEX statement includes the filegroup placement regardless if the ignore filegroup placement option was selected.

0 Votes 0 ·

Hi @J-I-M-B-O, sorry for the late reply. I made a test for this issue. Are you mean when you enable 'IgnoreFilegroupPlacement', the indexs still creating on specific filegroups? And then I checked the Description as below, I think this option will not ignore the index. Please get more information from SqlPackage Script parameters and properties

Specifies whether differences in the placement of objects in FILEGROUPs should be ignored or updated when you publish to a database.

99383-1.png

If I have misunderstanding, please feel free let me know.


0 Votes 0 ·
1.png (31.2 KiB)

Yes, I didn't explicitly state the property IgnoreFilegroupPlacement in my original post, but I referenced that it was set several times. While it states that the differences in the placement of objects in filegroups should be ignored or updated, it does not seem to ignore them in CREATE INDEX statements.

From reading @TomPhillips-1744 comment, I think that property only pertains to existing objects. If so, I may need to come up with a different strategy.

0 Votes 0 ·