Import-D365Bacpac rebuild index mode

[TrudAX]

Hi. Currently Import-D365Bacpac procedure using SQLPackage util with default parameters. So at the end of procedure, all indexes are rebuilt using an Online switch: ALTER INDEX [I_14937RECID] ON [dbo].[INVENTTRANS] REBUILD WITH ( ONLINE = ON , DATA_COMPRESSION = PAGE ) In the latest SQLPackage version Microsoft added a switch to improve this behaviour: https://docs.microsoft.com/en-us/sql/tools/sqlpackage/troubleshooting-import-export-sqlpackage?view=sql-server-ver15#import-action-tips

it is possible to add the following switch by default? online rebuild is quite slow compared to offline "/p:RebuildIndexesOfflineForDataPhase=True "

[FH-Inway]

Nice find, thanks. I see no issue adding that, according to https://stackoverflow.com/questions/6309614/what-is-the-difference-between-offline-and-online-index-rebuild-in-sql-server the only drawback of the offline index rebuild is that it causes table locking, which we don't care about during a bacpac import. The first SqlPackage version that supports that property was 18.7 (released on March 10, 2021). I don't think we need to support older versions.

We could also think about adding an optional parameter to Import-D365Bacpac that would allow specifying additional properties. It would work similar to the Properties parameter of Invoke-SqlPackage.

[Splaxi]

@FH-Inway

It would sense to allow for additional parameter flags to be passed into the cmdlet and for there into the inner cmdlet.

I believe we should handle the sane defaults, specify which are part of our implementation and let people run with their own special needs from there.

[TrudAX]

@Splaxi it is up to you how to maintain this, but I suggest keeping it simple. Import-D365Bacpac purpose is to restore a backup for D365FO databases. I suggest to set up default parameters that are most optimal for this one particular task. If someone has particular needs, they can run SQLPackage directly without this tooling. If you add a non-default parameter it just makes things more complex with no value for most users, people often don't read the documentation. But maybe as future improvements, it will be nice to have feature. Thanks

[TrudAX]

@Splaxi did several more tests for small and medium datafiles the most optimal parameter set is the following: /p:DisableIndexesForDataPhase=FALSE - it will not delete indexes before loading data. For small databases, it gives some performance advantages /p:RebuildIndexesOfflineForDataPhase=True - use offline rebuild

So it will be great to have some option for these 2 parameters

full command:

cd C:\temp\

$fileExe = "C:\Temp\d365fo.tools\SqlPackage\SqlPackage.exe"

& $fileExe /a:import /sf:$filePathpac /tsn:localhost /tdn:$fileDB /p:CommandTimeout=1200 /p:RebuildIndexesOfflineForDataPhase=True /MaxParallelism:32 /p:DisableIndexesForDataPhase=FALSE

[Splaxi]

Should any one of the mentioned parameters be defaults?

[TrudAX]

I suggest the following - /p:RebuildIndexesOfflineForDataPhase=True - should be a default, I don't think you need even a parameter for this

/p:DisableIndexesForDataPhase=FALSE - it is up to you, maybe set a parameter for this. In all cases that I tested this gives some perf boost. but maybe on a large dataset, it may be slower

[FH-Inway]

I created #760 to essentially expose the Properties parameter of Invoke-SqlPackage in Import-D365Bacpac.

This would enable bacpac imports like this:

[System.Collections.ArrayList] $PropertiesList = New-Object -TypeName "System.Collections.ArrayList"
$PropertiesList.Add("RebuildIndexesOfflineForDataPhase=true")
$PropertiesList.Add("DisableIndexesForDataPhase=false")

$Params = @{
  BacpacFile = "C:\Temp\d365fo.tools\AxDB.bacpac"
  ImportModeTier1 = $true
  NewDatabaseName = "GOLDEN"
  Properties = $PropertiesList.ToArray()
}

Import-D365Bacpac @Params

This enables testing of bacpac imports with user provided properties for SQLPackage.exe. Next steps would be to expose the properties for New-D365Bacpac and Import-D365Dacpac as well.

[Splaxi]

0.7.2 is on its way. Take it for a spin!