Delayed durability in Import-D365Bacpac

[TrudAX]

Delayed durability greatly improves the speed of restoring a bacpac ( In my tests from 400 to 650 requests per second).
Can you enable it by default? So after creating a database in Import-D365Bacpac procedure, automatically execute the following command

USE [master]
GO
ALTER DATABASE [RestoredName] SET DELAYED_DURABILITY = FORCED WITH NO_WAIT
GO

[FH-Inway]

Thanks for the suggestion. Did you try to run Import-D365Bacpac with an existing, but empty database with DELAYED_DURABILITY?

Currently, SqlPackage is used by the internal cmdlet Invoke-SqlPackage to both create the database and import the data into it.
According to SqlPackage Import parameters and properties, the import can also be done with an existing, but empty database.

If this is to be added to the d365fo.tools, we should consider that apparently, the increased speed comes with the risk of data loss: When to use delayed transaction durability
An idea would be to not enable this by default, but add a switch parameter to enable it. Would that still work for you?
If it has to be on by default, I guess a new cmdlet Import-D365BacpacWithDelayedDurability would have to be created.

As for the actual change:
Import-D365Bacpac would need to first create the new database, set DELAYED_DURABILITY on it, then run the import and then switch DELAYED_DURABILITY off again.

[TrudAX]

I am restoring the database from Tier2 by creating a new database(I don't have an empty database)
I don't see any risk of data lost, if the server crash is happening during the restore you in any case lost your new database(either with delayed durability or without it) and need to run the restore again

I think your proposal is great, I suggest this will be a default mode:

Import-D365Bacpac would need to first create the new database, set DELAYED_DURABILITY on it, then run the import and then switch DELAYED_DURABILITY off again.

[Splaxi]

@TrudAX

Can you share the raw commands that you have been testing with? My current understanding is that we could manipulate a model/manifest file prior the import and have the expected output.

Then we only need a set delayed on / off cmdlet. Could prove useful for stress testing and data migration down the road.

There are some caveats with the delayed durability functionality, but it could be solved with the on / off cmdlet.

[Splaxi]

This would be the perfect time to simplify the import cmdlet, as Tier2 support isn't needed anymore.

[TrudAX]

@Splaxi - I did the following test

1. Run the restore command
   Import-D365Bacpac -BacpacFile "J:\LCS\UAT2022_05_13.bacpac" -ImportModeTier1 -NewDatabaseName AAA
2. Then monitor some time in SQL Activity monitor
3. When the system started creating objects I run the following command in SQL
   USE [master] GO
   ALTER DATABASE AAA SET DELAYED_DURABILITY = FORCED WITH NO_WAIT GO
   4.I saw the following changes in the restore speed
   I don't think we need a new on / off cmdlet, as the database AAA doesn't exists before the restore
   

[HC-Pin]

Hi

I use DBAtools to create the database before importing (with meads i can speed up import by hours by setting flinched database size to start with)

Set-DbatoolsConfig -Name Import.EncryptionMessageCheck -Value $false -PassThru | Register-DbatoolsConfig
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register
Set-DbatoolsConfig -FullName sql.connection.encrypt -Value $false -Register

Write-Host "Create new AxDB and Import BACPAC file to the SQL database" $NewDB -ForegroundColor Yellow
New-DbaDatabase -SqlInstance localhost -Name $NewDB -DataFilePath $AxDataFilePath -LogFilePath $AXLogFilePath -Recoverymodel $axRecoverymodel -Owner $axOwner -PrimaryFilesize $AXPrimaryFilesize -PrimaryFileGrowth $AXPrimaryFileGrowth -LogSize $AXLogSize -LogGrowth $AXLogGrowth | Out-Null
Start-Sleep -Seconds 10

Write-Host "Importing bacpac" -ForegroundColor Yellow
Import-D365Bacpac -ImportModeTier1 -BacpacFile $bacpaclocandname -NewDatabaseName $NewDB -ModelFile $modelFileUpdatedPath -ShowOriginalProgress -Verbose

[Splaxi]

@HC-Pin

Nice - do you in any way calculate or extract the database size from somewhere? Or are you just "guessing"?

[HC-Pin]

i start with
$AXPrimaryFilesize = 4096 # data file initial size
$AXPrimaryFileGrowth = 1024 # data file autrogrowth amount
$AXLogSize = 1024 # data file initial size
$AXLogGrowth = 512

And i check the database size in the admin power platform , and change accordingly to the size there :-) always try an start with the database in the corrects size, thats maks the restore faster by hours