search

microsoft / DacFx

DacFx, SqlPackage, and other SQL development libraries enable declarative database development and database portability across SQL versions and environments. Share feedback here on dacpacs, bacpacs, and SQL projects.
https://aka.ms/sqlpackage-ref
MIT License
314 stars 19 forks source link

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

Closed kramer1007 closed 7 months ago

kramer1007 commented 3 years ago

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 commented 2 years ago

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

kramer1007 commented 1 year ago

image

tony-donley commented 1 year ago

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 commented 1 year ago

image

kramer1007 commented 1 year ago

image

tony-donley commented 10 months ago

@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 commented 9 months ago

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

image

asrichesson commented 7 months ago

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 commented 7 months ago

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

dzsquared commented 7 months ago

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 commented 7 months ago

[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 GitHubhttps://github.com/microsoft/DacFx/issues/27#issuecomment-1967448529 or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2XYWHZ2P4GCUXICYV437XTYVYXT5BFKMF2HI4TJMJ2XIZLTSSBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDUOJ2WLJDOMFWWLLTXMF2GG2C7MFRXI2LWNF2HTAVFOZQWY5LFUVUXG43VMWSG4YLNMWVXI2DSMVQWIX3UPFYGLAVFOZQWY5LFVIZDCOBVGMYDOMZUHCSG4YLNMWUWQYLTL5WGCYTFNSWHG5LCNJSWG5C7OR4XAZNMJFZXG5LFINXW23LFNZ2KM5DPOBUWG44TQKSHI6LQMWVHEZLQN5ZWS5DPOJ42K5TBNR2WLKJSG43TMMBZHEZTDAVEOR4XAZNFNFZXG5LFUV3GC3DVMWUTSNZTGA4DMOJRG2BKI5DZOBS2K3DBMJSWZJLWMFWHKZNKGIYTQNJTGA3TGNBYU52HE2LHM5SXFJTDOJSWC5DF. You are receiving this email because you were assigned.

Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

maciejw commented 1 month ago

are you considering adding other options such as mentioned DROP_EXISTING in other comments? lack of this feature makes dacpacs kind of useless when you have SQL Enterprise... and you have to write custom contributors or change scripts by hand...

dzsquared commented 1 month ago

@maciejw - there are quite a few future improvements for DacFx under consideration, so I'd highly recommend that any specific options you're most interested in get filed as new issues

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)

for example, #483 was added

maciejw commented 3 weeks ago

@maciejw - there are quite a few future improvements for DacFx under consideration, so I'd highly recommend that any specific options you're most interested in get filed as new issues

here you go https://github.com/microsoft/DacFx/issues/488