search

d365collaborative / d365fo.tools

Tools used for Dynamics 365 Finance and Operations
MIT License
250 stars 102 forks source link

bacpac import now don't work #708

Closed FH-Inway closed 1 year ago

FH-Inway commented 1 year ago

Discussed in https://github.com/d365collaborative/d365fo.tools/discussions/707

Originally posted by **leocgf** January 12, 2023 I"m using latest d365fo.tools (as of Jan 9, 2023) and latest SQLpackage.exe version 16.1.6374.0. I've done over 50 of these imports over past 3 years, so am familiar with procedure and always have success. I"ve used different databases going back 3 months. D365FO version is 10.0.30.. I've even built 10.0.26 servers and pulled databases back from 4 months ago that i've imported before and i still get this error. I've manually decompressed bacpac files with no errors. _Error is: PS C:\windows\system32> Import-D365Bacpac -BacpacFile "J:\bacpac\CG_GOLD2.bacpac" -ImportModeTier1 -NewDatabaseName GOLD [02:07:15][Invoke-Process] Exit code from SqlPackage.exe indicated an error happened. Will output both standard stream and error stream. [02:07:15][Invoke-Process] Standard output was: \r\n Importing to database 'GOLD' on server 'xxxxxxxxxxxxxx'. Time elapsed 0:01:05.56 [02:07:15][Invoke-Process] Error output was: \r\n *** Error importing database:Could not load package from 'J:\bacpac\CG_GOLD2.bacpac'. File contains corrupted data. WARNING: [02:07:15][Invoke-Process] Stopping because of Exit Code. | Stopping because an Exit Code from SqlPackage.exe wasn't 0 (zero) like expected. [02:07:15][Invoke-SqlPackage] The SqlPackage.exe exited with an error. WARNING: [02:07:15][Invoke-SqlPackage] Stopping because of errors._
FH-Inway commented 1 year ago

@leocgf Thanks for reporting, I took the liberty of converting your discussion to an issue.

You write that you manually decompressed the bacpac file with no errors. Does that mean that you used the SQLpackage.exe directly to import the bacpac file as described in Import a database?

If not, could you try that and let us know the result?

kalejjanis commented 1 year ago

Hello. I also noticed this issue with the latest d365fo.tools and SQLpackage version. Doesn't matter if I use the -ImportOnly parameter or not. Also Invoke-D365InstallSqlPackage was run before this command. image

The interesting thing is that if I run via CMD SqlPackage.exe /a:import /sf:"J:\MSSQL_BACKUP\AxDBFOClean31012023.bacpac" /tsn:localhost /tdn:ImportedDatabase /p:CommandTimeout=1200 /TargetTrustServerCertificate:True image

then the DB gets imported without any issues. image

Could it be that due to a recent change where /TargetTrustServerCertificate:True needs to be added to the command, then this also needs to be incorporated into d365fo.tools Import-D365Bacpac ?

FH-Inway commented 1 year ago

@kalejjanis Thanks for the additional input. Regarding TargetTrustServerCertificate see #692. If you are using version 0.6.77 or higher of d365fo.tools, Import-D365Bacpac already sets that to True.

It is interesting that you do not get an issue when running SqlPackage.exe directly, thanks for providing the command you use for that.

Could you run the Import-D365Bacpac again with the -Verbose and -ShowOriginalProgress switches? This would provide more information on what is going wrong. Could you also provide the full output? The screenshots unfortunately cuts off where it gets interesting :)

In addition, if you run Import-D365Bacpac with the -OutputCommandOnly switch, it will tell you how it would call SqlPackage.exe with all parameters. If you could post that and compare it to your own SqlPackage.exe command, that may help as well.

kalejjanis commented 1 year ago

Thanks for your suggestions. Did as you said and here are the results: image In the first command (-OutputCommandOnly) I see that its using /TargetUser and password and I guess that causes the conflict and the error further down in red. Is there away to "remove" or disable those switches from Import-D365Bacpac?

Or are there better ways?

Thanks

FH-Inway commented 1 year ago

@kalejjanis Thanks for running the commands and providing the detailed output. As you noticed the issue is caused because Import-D365Bacpac calls SQLPackage.exe with the /TargetUser and /TargetPasswort parameters. Since this is a different issue than the one discussed here, I created issue #717 where we can continue the discussion regarding your problem.

FH-Inway commented 1 year ago

@leocgf Since @kalejjanis 's issue turned out to be a different issue, could you provide additional details on your issue (see my request https://github.com/d365collaborative/d365fo.tools/issues/708#issuecomment-1380773962)?

leocgf commented 1 year ago

@FH-Inway in regards to issue I originally posted in #707 - I was able to finally hear from microsoft that there is a known bug with SQLPACKAGE.exe and D365FO bacpac backups. below is their statement.. ( I followed their workaround and it resolved the issue of "database is corrupt" on attempted import"

Hello ,

I am M==== A======, support engineer from Microsoft Dynamics 365 Finance and Operations team and will assist you on this case.

Issue: Importing bacpac file into Tier 1 environment says " File contains corrupted data"

Scope: We consider the incident to be resolved once we provide you the solution/information for your above issue or if the issue is found to be out-of-scope. or if the issue is found to be 3rd party related. or if the issue is found to be due to a known problem or is by design or is not supported

This issue is due to the .Net Core version of Sqlpackage.exe we use in our Dynamics-hosted export engine has a regression against .Net Standard Sqlpackage.exe

The workaround is to download .NET CORE platform of Sqlpackage.exe [https://docs.microsoft.com/en-us/sql/tools/sqlpackage-download?view=sql-server-ver15]. This is a .zip file that can be extracted to C:\Temp\Sqlpackage-dotnetcore. Now when you import the database, instead of using the Sqlpackage.exe under C:\Program Files (x86) you can use the Sqlpackage.exe in C:\Temp\Sqlpackage-dotnetcore.

Please let me know if it helped to move forward.

Thanks, M=======

Splaxi commented 1 year ago

Thanks for getting back.

But which one did you actually download?

image
FH-Inway commented 1 year ago

@leocgf Thanks for the information. I will close this issue, but feel free to add additional information or thoughts.

@Splaxi Since the message from Microsoft support mentions a .zip file, I assume it is the first entry in your screenshot, which is this link: https://go.microsoft.com/fwlink/?linkid=2224909

Splaxi commented 1 year ago

Should we update the default download path for SqlPackage.exe then - as we know this is the current one we can trust?

https://github.com/d365collaborative/d365fo.tools/blob/d538ccf96841d4ce32c80fd6ae0a4c904318f44d/d365fo.tools/functions/invoke-d365installsqlpackage.ps1#L59-L74

FH-Inway commented 1 year ago

@Splaxi My thoughts as well. I will take a look this weekend.

FH-Inway commented 1 year ago

Ah yes, the joy of SqlPackage.exe versioning. I've been there before ☚ī¸

Ok, so here is what I think is happening:

TLDR

Can't blame you if you skipped over that wall of text with version detective work. In summary, the newest versions of SqlPackage built with .NET 6 apparently cannot be used to import D365FO bacpac files. You need a version built with .NET Core for that.

For Invoke-D365InstallSqlPackage, we have 3 options:

  1. keep the version 18.4.1 that Invoke-D365InstallSqlPackage currently uses (dated 13th December 2019)
  2. switch to version 19.0 (dated 25th January 2022), which was the version Download and install SqlPackage recommended until the link to it was removed. This is the page that Import a database links to
  3. switch to version 19.1 (dated 24th May 2022), which was the last version built on .NET Core

I propose we stay on version 18.4.1, since we don't have any known issues with that version. Updating to 19.0 or 19.1 is probably also fine. If we update to a newer version, we should also remove the link scraping logic.

Splaxi commented 1 year ago

I vote 19.1 - we want to be on latest and greatest.

Regarding the scraping, could we keep it for a little while. At some point we will switch to the .net 6.0 release and to avoid version updates just because they release new versions has been helpful in the past ...

Splaxi commented 1 year ago

Forgot - awesome detective work đŸ”ĨđŸ’¯

FH-Inway commented 1 year ago

Thanks 😃

I vote 19.1 - we want to be on latest and greatest.

Ok, will do a bit of testing with that version tomorrow.

Regarding the scraping, could we keep it for a little while. At some point we will switch to the .net 6.0 release and to avoid version updates just because they release new versions has been helpful in the past ...

Ok, will leave it as is. Currently, I don't think it does anything because it can't find the link, so no harm in leaving it. When we/Microsoft switches to .NET 6.0, we will have to modify the scraping logic so it finds the .NET 6.0 link.

FH-Inway commented 1 year ago

Here are my test results:

The error for the latest version is

*** An unexpected failure occurred: The type initializer for 'Microsoft.Data.Tools.Schema.Sql.Common.SqlClient.CachedServerAndDatabaseInfo' threw an exception..

So at least for the bacpac files I'm working with, the information that .NET 6 versions of SqlPackage do not work is not correct. For me, only the latest .NET 6 version does not work.

Not quite sure what to make of that and especially, why 16.1.6374.0 works for me, but not for @leocgf . One reason I can think of is that Microsoft keeps changing the version of SqlPackage they use for creating the bacpacs as well. So the bacpacs from @leocgf were created with a different SqlPackage version than mine, which is why for me 16.1.6374.0 works. But that is just guessing.

Anyway, I created #730 to update the default SqlPackage version that Invoke-D365InstallSqlPackage uses to 19.1

During testing/analysis, I came across some links which may be of interest if this topic comes up again:

Splaxi commented 1 year ago

Like always - AMAZING WORK!

I'll release the change during the week.

FH-Inway commented 1 year ago

I used the new version and the changed SqlPackage version today in the real world, worked flawlessly đŸ’¯

MGNosov commented 8 months ago

Dea all, Hope you're all doing great. Few weeks ago we had issue with importing DB to OneBox. Long story short, DB was exported from SandBox, and on import step failed with error The Element of Annotation class SqlStatistic does not contain the Property class Auto. I checked the same .bacpac on different OneBoxes, the result was same. SQL version: 15.0.4345.5. OS Windows Server 2019. To overcome this issue I used version of SQLpackage 162.1.172 from MS instead SQLpackage from D365FO.tools which is 16.0.6161.0. Could you please tell, are you planning to add updated version of SQLpackge in D365FOtools? Thank you very much in advance. PS Would appreciate any other replies regarding this comment. Thank you.

Splaxi commented 8 months ago

As you can imagine - you're the first one to report this issue.

We're always behind Microsoft, when they introduce major changes to some of the more "hidden" things - like bacpacs.

From what I can understand, the bacpac process from the Tier2 environment - introduces a new element in the XML structure, which is part of the bacpac file. The latest SQLPackage was able to handle this. Correct?

Maybe the 16.2.1.172 release is stable enough, for us to default to that going forward. Could you provide the direct link for it? Then we have a discussion based on that.

The next time I would recommend that you create your own Issue or Discussion, and simply reference other. That way we get a better starting point, instead of reviving something that is close to 12 months old...

FH-Inway commented 8 months ago

@MGNosov Thanks for reporting.

Here is the direct link for version 16.2.1.172 of SqlPackage.exe, taken from Release notes for SqlPackage: https://go.microsoft.com/fwlink/?linkid=2257374

To be fair, our documentation for Invoke-D365InstallSqlPackage refers to this issue, so it seems natural to add a comment here. We should add a last comment that tells people to open a new issue if they have an issue not discussed in this one.

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

Since the Microsoft documentation still says to use the .NET Core version of SqlPackage, maybe we should introduce a switch -Latest to Invoke-D365InstallSqlPackage which then uses the evergreen link https://aka.ms/sqlpackage-windows to download and install the latest version?

Splaxi commented 8 months ago

@FH-Inway

I'm up for the what makes most sense. Back in the days, I wasn't able to get the evergreen link working - so I was parsing the html page, to obtain the latest download link for the desired version, based on the documentation.

Introducing the -Latest, would allow people to have the current cmdlet working, and not introducing breaking changes. But if we know that the latest version (or the specific version 16.2.1.172) - is the new working base, then we should just update the cmdlet to download that version directly.

I would have expected more people posting issues, if the Element of Annotation class SqlStatistic does not contain the Property class Auto. issue had been going on for weeks. So either we are totally cutting edge at the moment, or simply that people are using older bacpac files, and will first hit the issue the next time they export a new bacpac file...

FH-Inway commented 8 months ago

But if we know that the latest version (or the specific version 16.2.1.172) - is the new working base, then we should just update the cmdlet to download that version directly.

Not sure how we could know that, since Microsoft is not really forthcoming with information in this regard. We could also switch it around, make 16.2.1.172 the new default version and provide a -MicrosoftSupportRecommendedVersion switch that installs 16.0.6161.0 :smile:

In the end, we will always struggle with the issue that there is no one version of SqlPackage.exe that works for everyone.

MGNosov commented 8 months ago

Good day @Splaxi and @FH-Inway , Many thanks for your reply and explanations, but could you please tell, are you going to implement newer version of SQLPackage to Invoke-D365InstallSqlPackage, or may be add switch -Latest to allow selection of SQLPackage? Thank you very much in advance.

FH-Inway commented 8 months ago

I think adding the -Latest switch makes sense.

@MGNosov Would you be available to work with us on this? E.g. providing a pull request with the necessary changes or testing the new switch?

MGNosov commented 8 months ago

Hello @FH-Inway , I would love to help with this!