Invoke-D365DBSync issues

[kalejjanis]

Hello Hopefully one of the last issues I'll be facing with restoring a DB on a Tier 1 environment.

When executing Invoke-D365DBSync -Verbose I soon after get the following error:

Note, Standard Cloud-Hosted F&O version 10.0.31, Latest sqlpackage and D365FOTools. Completely fresh VM. Everything else worked fine apart from this command. Manual DB sync via Visual Studio works without issues.

Any ideas?

Thanks

[FH-Inway]

@kalejjanis Seems you have quite the knack for finding these issues ;) As always, appreciate your reporting so that we all can benefit.

As in the other issues, run the cmdlet with -ShowOriginalProgress and -OutputCommandOnly switches. -Debug switch could also be interesting.

When you get the command that is used to call SyncEngine.exe, try running the command yourself directly. Play around with it to see if you can get a version of the command that runs successfully.

Also: Did previous runs of Invoke-D365DbSync (e.g. on different vm or older versions) work?

[kalejjanis]

Hello Again, reminder that I run the commands below before every execute: Install-PackageProvider nuget -Scope CurrentUser -Force -Confirm:$false Install-Module -Name d365fo.tools -AllowClobber -Scope CurrentUser -Force -Confirm:$false Invoke-D365InstallSqlPackage -Path "C:\sqlpackage"

Here is the command that is supposed to be executed:

Here are the results of it:

Here is a command, that actually works (sync results are above of the command that I run)

This is really strange as I believe it should work out of the box.

[Splaxi]

We faced the same issue with a customer 1-2 weeks ago. They had 2 different Tier1 (CHE), both running the same application / build.

One failed just like yours and they other didn't. We solved it by the developer to open VS and run it from there, as we were in a rush.

They "funny" thing is that your second command does work - the only difference is how it is invoked.

We are abstracting things behind some .NET objects/constructs, that gives us the power to report on them. One solution could be to add a new parameter (-DirectInvoke), which would effective be calling the exe file directly.

I suspect that the SyncEngine has some new checks / logics, which could explain that it isn't able to produce a valid SQL Connection string - when we run it using the descriped approach.

Come to think off it - I see that we are in fact NOT calling the SyncEngine with the same parameters, as you are doing in your second script:

We are using explicit username & password, look at the output from the "OutputCommandOnly" - while in your example, you build the connection string using the Integrated Security=True option. So right now we are comparing apples and oranges, at least in terms of the core SQL Connection string.

On top of that, your second example also uses 2 different parameters / switches we don't: -FallBackToNative=False -RaiseDataEntityViewSyncNotification

So - could you try the following on the local Tier1 where things are failing:

• Located the ps1 file named invoke-d365dbsync.ps1, in the module folder
• Change line 144 from: https://github.com/d365collaborative/d365fo.tools/blob/d739b376ad43cc4f49e2eb572298e611a05e1cf5/d365fo.tools/functions/invoke-d365dbsync.ps1#L144
• To:

  "-connect=`"server=$DatabaseServer;Database=$DatabaseName; Integrated Security=True;`"" 

Let us know if that gets you any closer - the next step is to play around the params object, to add your parameters/switches and see if that is what moves us closer:

https://github.com/d365collaborative/d365fo.tools/blob/d739b376ad43cc4f49e2eb572298e611a05e1cf5/d365fo.tools/functions/invoke-d365dbsync.ps1#L141-L145

I would add it on line 143: "-fallbacktonative=False", "-raiseDataEntityViewSyncNotification",

[FH-Inway]

@kalejjanis Just a quick note in advance, in your screenshots, the password is still visible in the yellow debug part.

Regarding the rest, I agree with @Splaxi that since your successful call of SyncEngine.exe uses different parameters, it is hard to pinpoint the reason it is failing with Invoke-D365DbSync.

In addition to what @Splaxi suggests, could you try running the SyncEngine.exe command that Invoke-D365DBSync with the -OutputCommandOnly switch displays directly? Just to verify that the issue is with the command itself and not how d365fo.tools calls it.

If the command called this way results in the same issue, try changing the command parameter by parameter until you end up with your version of the command that works. Somewhere along the way the command should start working, at which point we have a better idea which of the parameters causes the issue.

[kalejjanis]

Hello. Thanks for the suggestions. At first glance changing some of the parameters as suggested by Splaxi didn't help. But I didn't went through all of them. But I'd say its was something else that was causing the issues. Yesterday I performed all the standard command in a real-case scenario and everything went fine, even the DB sync. So I'd say it was just bad luck.

The good thing is that I basically now have the perfect PowerShell script to restore a Staging (Tier2) environments DB on developer Tier 1 environments without any manual work. Well the only manual thing is that the script prompts me for the SAS link of the DB in LCS which I provide and then everything from there on is automated till the very end.

I'm really grateful for these d365fo.tools and the support.