Invoke-D365InstallSqlPackage installs old version of SQLPackage

[TrudAX]

When you run Invoke-D365InstallSqlPackage it creates a folder C:\Temp\d365fo.tools\SqlPackage, but the SqlPackage.exe inside this folder is from the mid2022. The current version of SQLPackage is newer. It leads to some error during DB restore "The Element or Annotation class SqlStatistic does not contain the Property class AutoDrop."

Expected behaviour: can you install the latest version of SQL package when you run Invoke-D365InstallSqlPackage (or at least update the version it downloads by default)

Current workaround: 1.Download the Windows version from this folder https://learn.microsoft.com/en-us/sql/tools/sqlpackage/release-notes-sqlpackage?view=sql-server-ver16

2.Copy all files from C:\Program Files\Microsoft SQL Server\160\DAC\bin to C:\Temp\d365fo.tools\SqlPackage

[FH-Inway]

Thanks for reporting. SqlPackage.exe is a gift that keeps on giving unfortunately. If you are up for a bit of reading, take a look at https://github.com/d365collaborative/d365fo.tools/issues/708#issuecomment-1528791325.

Long story short, we updated the SqlPackage.exe version that Invoke-D365InstallSqlPackage installs recently to version 19.1 (which as you wrote is from the middle of last year). This was the last version that was built with .Net Core. According to Microsoft support, a .Net Core version is required for D365FO .bacpacs because newer versions have a regression issue.

Since this version does not work for you, we are in a bit of a pickle, as there does not seem to be the one SqlPackage.exe version that works for everyone. Since we are screwed either way, we probably should use the latest SqlPackage.exe version and tell people that run into issues with that that they should use an older version. Note that the cmdlet has a -Url parameter that lets you specify the download link for the version you want to use.

https://github.com/d365collaborative/d365fo.tools/blob/d97b79f76efd44c3b318b1fc533441807ab2a258/d365fo.tools/functions/invoke-d365installsqlpackage.ps1#L20-L26

[TrudAX]

oh.. it seems nothing is simple. I suggest the following - keep it as now(you may change the version when Microsoft updates SQL to 2022), but as a workaround - can you add a custom parameter to Import-D365Bacpac /ModelFilePath:J:\MSSQL_BACKUP\model.xml

The route cause is backpac may contain statistics with AutoDrop property(the may appear even using LCS export), it seems this is something random. the only choice in this case - manually delete these records(as SQL 2019 doesn't support such statistics)

[FH-Inway]

Not quite sure I understand the request. Do you want a new parameter for Import-D365Bacpac that lets you specify a model file that replaces the one in the bacpac file? If yes, such a parameter already exists:

https://github.com/d365collaborative/d365fo.tools/blob/d97b79f76efd44c3b318b1fc533441807ab2a258/d365fo.tools/functions/import-d365bacpac.ps1#L67-L70

Or do you want a parameter that automatically removes the AutoDrop properties from the model file?

[FH-Inway]

Seems more people are having this issue: https://www.yammer.com/dynamicsaxfeedbackprograms/threads/2382104258371584?message_id=2382104258371584

[TrudAX]

ok. so I manage to find a workaround Description - if seems Microsoft changed something when you export data from LCS, the resulting backpac contains AutoDrop property. I tried all possible options, they can't process such backpacs. You either get an error in the beginning "class SqlStatistic does not contain the Property class AutoDrop" or if you use a new version of SqlPackage it will fail later, as SQL2019 doesn't have this property for statistics(it is available only for SQL2022)

So the proper solution will be - to update SQL to SQL2022 on Dev VMs and change Invoke-D365InstallSqlPackage to install a new(current) version of SqlPackage But this is time-consuming operation

The current workaround that I found: Extract model.xml from backpac and remove AutoDrop property tag. Then specify this file as a parameter to restore

another option from Yammer - do not use LCS for export the data, use old version of SQL package

But I still suggest changing Invoke-D365InstallSqlPackage to install a new(current) version of SqlPackage, then add this issue as a workaround

[Splaxi]

Never mind - I didn't read carefully...

[FH-Inway]

@TrudAX

Extract model.xml from backpac and remove AutoDrop property tag. Then specify this file as a parameter to restore

Did you do this manually or did you create a script? In case of a script, would you be able and willing to share it? If manually, would you be able to provide your model.xml file? The bacpacs I can access so far don't have the AutoDrop property.

another option from Yammer - do not use LCS for export the data, use old version of SQL package

Do you have a link to the Yammer conversation where this is discussed? Would this work for cloud environments? As far as I know, T2+ environments can only be accessed via LCS.

[TrudAX]

@FH-Inway , I just replaced references in model.xml manually, I hope this is a temporary MS problem.

The discussion can be found by this error "The Element or Annotation class SqlStatistic does not contain the Property class AutoDrop." https://www.yammer.com/dynamicsaxfeedbackprograms/threads/2100280684724224 In Tier2 you can get access to SQL (you need to enable it from LCS)

[Splaxi]

Pseudo code:

$path = "C:\Temp\model.xml"
$pathUpdated = "C:\Temp\model_updated.xml"

Export-D365BacpacModelFile -Path "C:\Temp\backup.bacpac" -OutputPath $path

Get-Content -Path $path | ForEach-Object {$_.Replace('<Property Name="AutoDrop" Value="True" />','')} | Add-Content -Path $pathUpdated

Import-D365Bacpac -ImportModeTier1 -BacpacFile "C:\Temp\backup.bacpac" -ModelFile $pathUpdated 

It took 4-5 minutes to extract the model file, loop through it and persist the updated one.

I didn't test the code end-2-end - but it should work...

[FH-Inway]

@TrudAX

The discussion can be found by this error "The Element or Annotation class SqlStatistic does not contain the Property class AutoDrop." https://www.yammer.com/dynamicsaxfeedbackprograms/threads/2100280684724224 In Tier2 you can get access to SQL (you need to enable it from LCS)

Very interesting, so this problem already started in January. Thanks for the link, it is a clever idea. Unfortunately, it can't be automated, as the JIT access request is a manual step in LCS. From the link, it seems the first version of SqlPackage.exe that adds support for the Auto Drop is 16.1.6374.0, which was released on November 9th, 2022.

@Splaxi Thanks for the script, I agree, it looks like it should do the trick.

[FH-Inway]

@TrudAX : @batetech did a test of @Splaxi 's script and made some performance improvements. With his permission, I created a gist of the script: https://gist.github.com/FH-Inway/f485c720b43b72bffaca5fb6c094707e

[Splaxi]

I can confirm that the above gist script works, had a customer that was in the same spot with a bacpac file. So EPIC work @batetech !

[Simon-Syd]

hello, thank you very much for sharing this valuable lines of codes :)

I just edited the script, because since few month we are not using the "Import-D365Bacpac" anymore, instead we using the sqlpackage.exe to add parameters :

Local-FixBacPacModelFile -sourceFile $modelFilePath -destinationFile $modelFileUpdatedPath

**C:\temp\d365fo.tools\SqlPackage\SqlPackage.exe /Action:import /TargetServerName:$(DatabaseServer) /TargetDatabaseName:$(TempDatabaseName) /SourceFile:"$(TempPathBac)" /Properties:CommandTimeout=0 /TargetUser:$(SqlUser) /TargetPassword:$(SqlPwd) /MaxParallelism:12 /p:RebuildIndexesOfflineForDataPhase=True /ModelFilePath:$modelFileUpdatedPath**

# Where Local-FixBacPacModelFile is the function defined above using StreamReader with buffering instead on Get-Content piped directly to Add-Content, which on a dev VM (B8ms, HHDs) made a big difference.

maybe it can help somebody to earn precious minutes of import ? (14,5 hours to import 15 GB bacpac file with this command)

[Simon-Syd]

hello @FH-Inway, I had to edit your script because it did not fit in my devops taskgroup the $get-date for example was recognize as a variable so I removed it But maybe your are using your script with "file path" instead of "inline" in azure devops ?

[FH-Inway]

@Simon-Syd Thanks for sharing your experience with the script. So far, I have only been running it directly and not in a Devops pipeline. I have yet to encounter the issue the script aims to address, so unfortunately I can't share my own experiences. If you can, please provide your own version of the script. I'm sure people that have the issue would appreciate it.

[Simon-Syd]

of course ! my edit : line 9 & 10 : we already get a variable called "TempPathBac" so I replace your "bacpacFileNameAndPath" line 12 & 13 : we already write this path with a "BacpacLocalPath" variable so I replaced it line 50 : the 2 $($streamEncoding.BodyName) $($streamEncoding.CodePage) were seen as varialbe, so I just remove them ......I'm not a PS expert, but as it's a simple "write-verbose" I guessed it was fine ? line 67 & 71 : idem for $(Get-Date -Format 'u') : I removed this and just keep the "flush buffer" & "flush complete" line 95 : instead of using the "Import-D365Bacpac" I used the root sqlpackage.exe with parameters : /ModelFilePath:$modelFileUpdatedPath

# This script can be used to remove AutoDrop properties from the model file of a

# SQL Server 2022 (or equivalient Azure SQL) bacpac backup.
# This enables restoring the bacpac on a SQL server 2019.
# See also https://github.com/d365collaborative/d365fo.tools/issues/747
# Original script by @batetech in https://www.yammer.com/dynamicsaxfeedbackprograms/#/Threads/show?threadId=2382104258371584
# Minor changes by @FH-Inway
# Gist of script: https://gist.github.com/FH-Inway/f485c720b43b72bffaca5fb6c094707e

# Will be created by script. Existing files will be overwritten.
$modelFilePath = "$(BacpacLocalPath)\BacpacModel.xml" 
$modelFileUpdatedPath = "$(BacpacLocalPath)\UpdatedBacpacModel.xml"

function Local-FixBacPacModelFile
{
    param(
        [string]$sourceFile, 

        [string]$destinationFile,

        [int]$flushCnt = 500000
    )

    if($sourceFile.Equals($destinationFile, [System.StringComparison]::CurrentCultureIgnoreCase))
    {
        throw "Source and destination files must not be the same."
        return;
    }

    $searchForString = '<Property Name="AutoDrop" Value="True" />';
    $replaceWithString = '';

    #using performance suggestions from here: https://learn.microsoft.com/en-us/powershell/scripting/dev-cross-plat/performance/script-authoring-considerations
    # * use List<String> instead of PS Array @()
    # * use StreamReader instead of Get-Content
    $buffer = [System.Collections.Generic.List[string]]::new($flushCnt) #much faster than PS array using +=
    $buffCnt = 0;

    #delete dest file if it already exists.
    if(Test-Path -LiteralPath $destinationFile)
    {
        Remove-Item -LiteralPath $destinationFile -Force;
    }

    try
    {
        $stream = [System.IO.StreamReader]::new($sourceFile)
        $streamEncoding = $stream.CurrentEncoding;
        Write-Verbose "StreamReader.CurrentEncoding: "

        while ($stream.Peek() -ge 0)
        {
            $line = $stream.ReadLine()
            if(-not [string]::IsNullOrEmpty($line))
            {
                $buffer.Add($line.Replace($searchForString,$replaceWithString));
            }
            else
            {
                $buffer.Add($line);
            }

            $buffCnt++;
            if($buffCnt -ge $flushCnt)
            {
                Write-Verbose "Flush buffer"
                $buffer | Add-Content -LiteralPath $destinationFile -Encoding UTF8
                $buffer = [System.Collections.Generic.List[string]]::new($flushCnt);
                $buffCnt = 0;
                Write-Verbose "Flush complete"
            }
        }
    }
    finally
    {
        $stream.Dispose()
        Write-Verbose 'Stream disposed'
    }

    #flush anything still remaining in the buffer
    if($buffCnt -gt 0)
    {
        $buffer | Add-Content -LiteralPath $destinationFile -Encoding UTF8
        $buffer = $null;
        $buffCnt = 0;
    }

}

Export-D365BacpacModelFile -Path $(TempPathBac) -OutputPath $modelFilePath -Force

Local-FixBacPacModelFile -sourceFile $modelFilePath -destinationFile $modelFileUpdatedPath

C:\temp\d365fo.tools\SqlPackage\SqlPackage.exe /Action:import /TargetServerName:$(DatabaseServer) /TargetDatabaseName:$(TempDatabaseName) /SourceFile:"$(TempPathBac)" /Properties:CommandTimeout=0 /TargetUser:$(SqlUser) /TargetPassword:$(SqlPwd) /MaxParallelism:12 /p:RebuildIndexesOfflineForDataPhase=True /ModelFilePath:$modelFileUpdatedPath

# Where Local-FixBacPacModelFile is the function defined above using StreamReader with buffering instead on Get-Content piped directly to Add-Content, which on a dev VM (B8ms, HHDs) made a big difference.

[TrudAX]

It seems MS fixed bug with a SQL package. Is it possible to change the Invoke-D365InstallSqlPackage default behaviour to install the latest version?

Workaround that I just tested, and it worked fine with PROD database(so no backpack fixes needed)

Invoke-D365InstallSqlPackage -url "https://go.microsoft.com/fwlink/?linkid=2261576"

[FH-Inway]

@TrudAX Is there some statement from Microsoft that they made a fix? Or is it based on the test with the latest version?

We had situations in the past where using the latest version worked for some people, but for others, it did not. This is why I am hesitant to make this the default behavior. What could be done is to add a -Latest switch to the cmdlet which would install the latest version. However, we still have to figure out how the latest version can be determined. According to @Splaxi , the aka.ms link to the latest version cannot be used to download it. Maybe we could get it directly from GitHub?

See also #708

[TrudAX]

@FH-Inway - the current situation is the following:

Invoke-D365InstallSqlPackage - will not work in any case if DB is restored from PROD(It may work for Tier2 restore). I know at least 3 projects where people complained about this and the recommendation was not to use your tooling.

Invoke-D365InstallSqlPackage -url "https://go.microsoft.com/fwlink/?linkid=2261576" works at least on 2 different instances where I use it

MS recommends always using the latest SQL package

My view: can you for now just change the default behaviour in order people who downloaded your tools don't get an error when trying to run the restore. So I suggest that Invoke-D365InstallSqlPackage works like Invoke-D365InstallSqlPackage -url "https://go.microsoft.com/fwlink/?linkid=2261576" . that is it, nothing more

For the latest switch: I am not sure why the -Latest switch is needed, you can just take the link from a webpage I don't even sure they will change it, so probably hardcode it will be fine. Maybe it will be changed when new .NET 9 will be released. Anyway this -latest is probably outside the current scope of this bug

[FH-Inway]

@TrudAX Thanks for the detailed overview. I did a review of this issue and #708 as well as Microsoft documentation and some Yammer threads to refresh my memory.

This is a bit of a longer post. I divided it into 3 parts:

• questions and comments on your overview
• summary of the current state
• thoughts on next steps

As this impacts some of the more widely used cmdlets of d365fo.tools, I would also encourage others to share their thoughts on this.

Questions and comments

• You mention that Invoke-D365InstallSqlPackage does not work in any case if DB is restored from PROD. I was not aware that a direct .bacpac export from PROD is possible. To my knowledge, it always goes through a restore on a T2+ environment first. Is that an on premise / local database (LDB) process?
• You also mention that Microsoft recommends to always use the latest SQL package (I assume the latest version of SQLPackage.exe is meant here). Is this guidance provided by Microsoft support? The documentation on Import a database still recommends using sqlpackage .NET Core for Windows. Though I suspect that documentation is outdated in that regard, because the link refers to an anchor that does not exist anymore since April 2022.

Current state

Currently, Invoke-D365InstallSqlPackage first tries to scrape the download link from https://learn.microsoft.com/en-us/sql/tools/sqlpackage-download. That scraping stopped working in September 2022, when the text "Windows .NET Core" was removed from that page.

Since then, the fallback of a hardcoded URL is used. Since version 0.6.79 of d365fo.tools, that URL is https://go.microsoft.com/fwlink/?linkid=2196334, which matches version 19.1 (16.0.6161.0) of SQLPackage, which is the last version that was built on .NET Core, dated May 24th 2022.

This behavior can be overruled by specifying the -Url parameter with a different URL. -SkipExtractFromPage can also be used to deactivate the web scraping logic. But since that does not work anyway, it is rarely used.

https://github.com/d365collaborative/d365fo.tools/blob/7be75231b65abf46d05fc684521da46c4b5e91e5/d365fo.tools/functions/invoke-d365installsqlpackage.ps1#L70-L85

Next steps

• As you mentioned, an easy change to do is update the URL to https://go.microsoft.com/fwlink/?linkid=2261576, which is version 162.2.111, the first .NET 8 version, dated February 27th 2024. I will try to find some time and test that version. So far, all my projects are still working ok with the 19.1 version. Until this change is done, the -Url parameter can be used to install a different version if the default version used does not work.
• We should also decide if we want to repair the web scraping logic. As we have seen, both always using the latest version and always using a fixed version will not work for everyone. My gut feeling is to not enable it by default, but give the user the option to use it, aka -Latest switch. Ideally, we get the evergreen link https://aka.ms/sqlpackage-windows working for that so we do not have to rely on the web scraping anymore.

[TrudAX]

Thanks I think just using https://aka.ms/sqlpackage-windows as default will resolve this. Or you can't read this link? I don't get an idea of the -Latest; from my view, the default should be the latest, but you can specify the URL if needed(that already exists). for your questions: 1 - I mean when DB from Tier2 come as a previous PROD restore. 2 - Yes, probably .NET core is Windows .NET 8. Anyway it works as expected

[FH-Inway]

The idea behind -Latest is that in the past, the newest version of SQLPackage was not always the right version to use. So people ran into issues with that. The reason is probably the time delay between the Microsoft team of SQLPackage releasing a new version and the Microsoft teams for D365FO adopting the new version*. That is why I'm advocating for making a specific version the default (and update it if need be as we are currently doing) and introducing -Latest for people that "know what they are doing".

*

It seems to me the D365FO side is actually much more complex, with different versions of SQLPackage being used. Only Microsoft would know how many, but my guess would be that it may even differ between environments of the same LCS project.

Regarding the questions: 1) Interesting, I never heard or experienced that you get different results for a T2+ export based on where the data is originally from. But I would assume that this is a temporay situation and eventually, all T2+ environments would be based on data that originally came from PROD. Nevertheless, good to keep in mind for troubleshooting. 2) Ah yes, the beautiful world of names for .NET. I also heard that .NET core is sometimes used as name for all cross platform versions of .NET, not just versions up to 3.1. But that does not seem to match with the official naming policy for .NET as e.g. in .NET and .NET Core Support Policy. There, they make a clear distinction based on version between .NET core and .NET. In any case, I'm pretty sure that for D365FO, they mean .NET core as in version 3.1. See the response from Microsoft support in this comment: https://github.com/d365collaborative/d365fo.tools/issues/708#issuecomment-1523572246

In other news, I tried Invoke-D365InstallSqlPackage -Url "https://aka.ms/sqlpackage-windows" and it works! Which means the web scraping logic can be replaced with this evergreen link. @Splaxi

[TrudAX]

For 1 - actually, if you are an implementation partner, most of the time, your data will not be from PROD; only after GoLive will you get it. I think the reason for this difference is that DAMS is working on PROD only.

I think if you replace current Invoke-D365InstallSqlPackage with Invoke-D365InstallSqlPackage -Url "https://aka.ms/sqlpackage-windows" it will solve the problem

[FH-Inway]

I started implementing the changes: #817

[FH-Inway]

@TrudAX 0.7.10 is out, give it a try.

[batetech]

I wish there was a "continue on error" option in SQL package import, where you could be informed that not everything imported successfully but it would continue on and you could review the logs for errors. That would solve 99% of these issues with compatibility between Azure SQL and local SQL server. I know that's nothing you all can control, but just a thought.