Switch-D365ActiveDatabase issues

[kalejjanis]

Hello again Created a nice PowerShell script that basically restores a Tier2 DB on a Tier1 Devbox. All seemed to be fine, but after the DB was restored there were a couple of errors. I managed to fix them manually, but.. the whole idea is to automate everything so I came back here for some guidance. I'll be transparent and share the script so that you know the steps and order:

Install-PackageProvider nuget -Scope CurrentUser -Force -Confirm:$false Install-Module -Name d365fo.tools -AllowClobber -Scope CurrentUser -Force -Confirm:$false Invoke-D365InstallSqlPackage -Path "C:\sqlpackage" Import-D365Bacpac -ImportModeTier1 -BacpacFile "J:\MSSQL_BACKUP\AxDBprepped.bacpac" -NewDatabaseName "ImportedDatabase" Switch-D365ActiveDatabase -SourceDatabaseName "ImportedDatabase" Invoke-D365DBSync Enable-D365User

I noticed that issues started with switching the database (Switch-D365ActiveDatabase).

It appears that the AxDB is set to Single User mode by Switch-D365ActiveDatabase and that causes issues further down the line. No only it doesn't finish properly, it makes the next 2 commands also fail (Invoke-D365DBSync, Enable-D365User)

I manually resolved this by running: ALTER DATABASE AxDB SET MULTI_USER WITH ROLLBACK IMMEDIATE GO

And then DB sync worked fine, and I was able to enable all users (manually via VS and running "update userinfo set enable =1"). Is there a parameter or any other ways I could switch the databases without the AxDB ending up in Single User mode? Thanks

P.s. since you see the whole script I use, maybe you see something that I'm missing to make the script even better? Thanks

[FH-Inway]

Switch-D365ActiveDatabase runs internally switch-database-tier1.sql. This should normally enable the MULTI_USER mode again after the switch. In your case, there seems to be a deadlock situation happening.

Are you stopping the D365FO services before running the script? You can use the Stop-D365Environment cmdlet to do that. See also the guide on How To Import Bacpac Into Tier1. Running services might utilize the database during the switch, which could cause the deadlock.

Otherwise try running the cmdlets of the script with the -Verbose and (if available) -ShowOriginalProgress switches. If there is an issue during the script run, you can also output the $error variable afterwards to get more information about errors that happened. There are also some troubleshooting tips in the Troubleshoot guide.

You might also want to take a look at the guide on Exception handling if you want to stop the script from executing further cmdlets after an error has happened.

[fhoo]

If it is not the running services, maybe it is again an issue with the pooling we fixed 4 months ago with issue #644 for the Import-D365Bacpac command, and we need to add the NoPooling switch to the Switch-D365ActiveDatabase command as well?

[Splaxi]

@fhoo

Good call!

[kalejjanis]

Hello I tested Stop-D365Environment before importing the DB which was followed by Switch-D365ActiveDatabase afterwards and the switch was indeed successful. Thanks for the tips!