Add support for Enterprise SQL Server features (i.e. Indexes ONLINE=ON)

[kramer1007]

When building a SQL Server Database project, if SQL files include enterprise features such as Online Index building, these statements are ignored when outputting the SQL code or applying to target databases.

How to reproduce: add an index to a database project that would get deployed. test it with "WITH (ONLINE = ON)" and without. You'll see that when looking at the output, you'll see that the "WITH (ONLINE = ON)" is ignored.

[kramer1007]

@dzsquared any update on this? i see it's on the backlog.

[kramer1007]

[tony-donley]

Persisted options are honored or ignored according to publish profile flags, but non-persisted options are always ignored. I would like to see SOME non-persisted options honored, such as ONLINE and SORT_IN_TEMPDB. DROP_EXISTING would need to be applied intelligently because it would fail if the index doesn't exist.

[kramer1007]

[kramer1007]

[tony-donley]

@dzsquared Thanks for the great information you provided at PASS last week. I appreciated the session.

Is there any interest in this issue? I don't think this is limited to just Enterprise features, it's other non-persisted options also, like SORT_IN_TEMPDB and MAXDOP, which when applied intelligently can reduce the outage window during a deployment.

[kramer1007]

Great points Tony! I would have to agree with those options as well.

[asrichesson]

It would be nice to have an option in the publish profile to create all indexes with ONLINE = ON regardless if the option is not set in the Create Index DDL statement. Right now, I am using this contributor to set ONLINE = ON for all create index statements.

[llali]

this feature is implemented in dacFx and available in the preview version 162.2.91-preview

[dzsquared]

Support for applying the non-persistent ONLINE index option has been added in SqlPackage 162.2, releasing this morning.

There will be a blog post shortly with more details, but there's 2 layers of control over the behavior:

1. A command line publish property (/p:PerformIndexOperationsOnline) can be set to true, which will apply to eligible index operations for that deployment.
2. ONLINE = ON or ONLINE = OFF statements on indexes in a SQL project will be incorporated in the compiled model (.dacpac) and when the dacpac is published those options override any command line options. The SQL projects functionality is available in the next Microsoft.Build.Sql SDK version (very soon). For use with the original SQL projects, it will roll to the different tools - Azure Data Studio will be the first (very soon) and SSDT will come with a future preview of Visual Studio 17.10.

I noticed that there was some discussion here about other non-persisted options - we would be happy to discuss any of those individually in new issues! (I ask for individually so we can be sure to determine and confirm the right behavior for each one)

[namangupta211]

[like] Naman Gupta reacted to your message:

…

________________________________ From: Drew Skwiers-Koballa ***@***.***> Sent: Tuesday, February 27, 2024 7:27:58 PM To: microsoft/DacFx ***@***.***> Cc: Assign ***@***.***> Subject: Re: [microsoft/DacFx] Add support for Enterprise SQL Server features (i.e. Indexes ONLINE=ON) (Issue #27) Support for applying the non-persistent ONLINE index option has been added in SqlPackage 162.2, releasing this morning. There will be a blog post shortly with more details, but there's 2 layers of control over the behavior: 1. A command line publish property (/p:PerformIndexOperationsOnline) can be set to true, which will apply to eligible index operations for that deployment. 2. ONLINE = ON or ONLINE = OFF statements on indexes in a SQL project will be incorporated in the compiled model (.dacpac) and when the dacpac is published those options override any command line options. The SQL projects functionality is available in the next Microsoft.Build.Sql SDK version (very soon). For use with the original SQL projects, it will roll to the different tools - Azure Data Studio will be the first (very soon) and SSDT will come with a future preview of Visual Studio 17.10. I noticed that there was some discussion here about other non-persisted options - we would be happy to discuss any of those individually in new issues! (I ask for individually so we can be sure to determine and confirm the right behavior for each one) — Reply to this email directly, view it on GitHub<#27 (comment)> or unsubscribe<https://github.com/notifications/unsubscribe-auth/A2XYWHZ2P4GCUXICYV437XTYVYXT5BFKMF2HI4TJMJ2XIZLTSSBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDUOJ2WLJDOMFWWLLTXMF2GG2C7MFRXI2LWNF2HTAVFOZQWY5LFUVUXG43VMWSG4YLNMWVXI2DSMVQWIX3UPFYGLAVFOZQWY5LFVIZDCOBVGMYDOMZUHCSG4YLNMWUWQYLTL5WGCYTFNSWHG5LCNJSWG5C7OR4XAZNMJFZXG5LFINXW23LFNZ2KM5DPOBUWG44TQKSHI6LQMWVHEZLQN5ZWS5DPOJ42K5TBNR2WLKJSG43TMMBZHEZTDAVEOR4XAZNFNFZXG5LFUV3GC3DVMWUTSNZTGA4DMOJRG2BKI5DZOBS2K3DBMJSWZJLWMFWHKZNKGIYTQNJTGA3TGNBYU52HE2LHM5SXFJTDOJSWC5DF>. You are receiving this email because you were assigned. Triage notifications on the go with GitHub Mobile for iOS<https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android<https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.