Problem with the switch-d365activedatabase command

[fwilkinson]

I have created a DevOps pipeline to automate the PROD to Tier 1 refresh routine (and a bit more). I am routinely finding that after I import the bacpac and then attempt to use the switch-d365activedatabase command, it leaves one of them in Single User Mode. Any thoughts? It seems to be switching the DB's but then leaves the new AxDB in the Single User Mode.

Here is the error I am getting and after that are the PS commands in my Inline DevOps Pipeline PowerShell script. Any thoughts?

ERROR: 2020-06-03T23:29:33.2859568Z End import 2020-06-03T23:29:33.2859900Z Start Switch DB 2020-06-03T23:29:47.2924070Z [18:29:47][Switch-D365ActiveDatabase] Something went wrong while switching out the AXDB database. | Exception calling "ExecuteNonQuery" with "0" argument(s): "Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 2020-06-03T23:29:47.2932035Z ALTER DATABASE statement failed. 2020-06-03T23:29:47.2939620Z Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. 2020-06-03T23:29:47.2947745Z Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. 2020-06-03T23:29:47.2955475Z The database name '_orig_20200603' has been set. 2020-06-03T23:29:47.2963356Z Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. 2020-06-03T23:29:47.2971026Z Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. 2020-06-03T23:29:47.2978574Z The database name '' has been set." 2020-06-03T23:29:47.3044009Z WARNING: [18:29:47][Switch-D365ActiveDatabase] Stopping because of errors. | Something went wrong while switching out 2020-06-03T23:29:47.3045130Z the AXDB database. 2020-06-03T23:29:47.3059306Z End Switch DB

Here are the d365fo.tools IMPORT and SWITCH commands I am running: Write-Host Start Copy Bacpac Invoke-D365AzCopyTransfer -SourceUri $fileLocation -DestinationUri $downloadPath Write-Host End Copy bacpac

Write-Host Start Import Import-D365Bacpac -ImportModeTier1 -BacpacFile $downloadPath -NewDatabaseName $newDBName Write-Host End import

Write-Host Start Switch DB Switch-D365ActiveDatabase -SourceDatabaseName "$newDBName" -DestinationSuffix "orig$currentDate" Write-Host End Switch DB

It is then failing when I attempt to back up the AxDB since it is then in Single User mode..

[fwilkinson]

BTW --- I'm using Azure Key Vault variables

[Splaxi]

Hi Ford!

I would expect you to do something like this:

Write-Host Start Copy Bacpac
Invoke-D365AzCopyTransfer -SourceUri $fileLocation -DestinationUri $downloadPath
Write-Host End Copy bacpac

Write-Host Start Import
Import-D365Bacpac -ImportModeTier1 -BacpacFile $downloadPath -NewDatabaseName $newDBName
Write-Host End import

Write-Host Start stopping the D365 Enviroment
Stop-D365Environment -All
Write-Host End stopping the D365 Enviroment

Write-Host Start Switch DB
Switch-D365ActiveDatabase -SourceDatabaseName "$newDBName" -DestinationSuffix "orig$currentDate"
Write-Host End Switch DB

Without knowing you entire script (I know you shared it), I would expect that you missed stopping the environment (so there is still an active session from the different D365FO services on the machine), which would explain that things are not working for you.

If I remember correctly, you are trying to end up with a bak file, instead of a bacpac file. If you don't run Invoke-D365DBSync between the switch and before running the backup, you actually don't need to switch out the database. Unless you want the real AXDB name on your backup. Just a little FYI.

[fwilkinson]

Ha.. I’ve always used the switch command by itself. Just assumed it stopped / started on its own 😊… I guess I have just been staring at this too long… I’ll add the start / stop command.

As for the DB Sync, it’s a toss up on whether or not I want to invoke that in the scripts. I usually restore the raw AxDB on a developers workstation and then instruct them to get the latest code and do a DB sync in case they don’t have the latest code on their box.

I guess the other option is to simply backup the imported bacpac with the date in the name, back it up to Azure and when it restores it, just leave it as the AxDB_date database name and let the developer switch it out and do the sync..

Ford Wilkinson Director of Cloud Services Phone: (970) 300-2428 | fwilkinson@soprissystems.commailto:fwilkinson@soprissystems.com

From: Mötz Jensen notifications@github.com Sent: Thursday, June 4, 2020 12:54 AM To: d365collaborative/d365fo.tools d365fo.tools@noreply.github.com Cc: Ford Wilkinson fwilkinson@soprissystems.com; Author author@noreply.github.com Subject: Re: [d365collaborative/d365fo.tools] Problem with the switch-d365activedatabase command (#431)

Hi Ford!

I would expect you to do something like this:

Write-Host Start Copy Bacpac

Invoke-D365AzCopyTransfer -SourceUri $fileLocation -DestinationUri $downloadPath

Write-Host End Copy bacpac

Write-Host Start Import

Import-D365Bacpac -ImportModeTier1 -BacpacFile $downloadPath -NewDatabaseName $newDBName

Write-Host End import

Write-Host Start stopping the D365 Enviroment

Stop-D365Environment -All

Write-Host End stopping the D365 Enviroment

Write-Host Start Switch DB

Switch-D365ActiveDatabase -SourceDatabaseName "$newDBName" -DestinationSuffix "orig$currentDate"

Write-Host End Switch DB

Without knowing you entire script (I know you shared it), I would expect that you missed stopping the environment (so there is still an active session from the different D365FO services on the machine), which would explain that things are not working for you.

If I remember correctly, you are trying to end up with a bak file, instead of a bacpac file. If you don't run Invoke-D365DBSync between the switch and before running the backup, you actually don't need to switch out the database. Unless you want the real AXDB name on your backup. Just a little FYI.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/d365collaborative/d365fo.tools/issues/431#issuecomment-638618491, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AKU4PD2VIYD5SRBQZJ3LEHDRU4ZILANCNFSM4NSFA5XQ. CAUTION: This email originated outside of Sopris. Do not click links or open attachments unless you recognize the sender and know the content is safe.

[Splaxi]

I'm going to close this, you can reopen it if you feel we need to revisit it later on...