SqlServer module not found

Question

SqlServer module not found

Closed this issue 5 months ago · 17 comments

Tested with pre-release: BcContainerHelper is version 6.0.16

Create a multi tenant container based on artifact: https://bcartifacts.azureedge.net/sandbox/24.0.16410.18040/nl
After creating the multtenant container:

Replace App.DB with bacpac from our full build
Replace Tenant DB with default DB from our master sample set

Sync / upgrade environment all went well
Bacpacs created without problems

For development purposes we transform the DB into Single tenant again
Move all apps to DevEndpoint
When invoking the Backup-Navdatabases receive following error:
Backup-NavContainerDatabases -containerName "$ContainerName" -bakFolder "$BackupFolder"
-Verbose

Backing up default to c:\s-Extensions\database\database.bak
The module 'SqlServer' could not be loaded. For more information, run 'Import-Module SqlServer'.

Exception Script Stack Trace:
at Backup-SqlDatabase, C:\run\pscoreoverrides.ps1: line 2
at Backup, : line 20
at , : line 65

PowerShell Call Stack:
at Invoke-ScriptInBcContainer, C:\Users\buildadmin2\Documents\WindowsPowerShell\Modules\bccontainerhelper\6.0.16\ContainerHandling\Invoke-ScriptInNavContainer.ps1: line 71
at Backup-BcContainerDatabases, C:\Users\buildadmin2\Documents\WindowsPowerShell\Modules\bccontainerhelper\6.0.16\Bacpac\Backup-NavContainerDatabases.ps1: line 52
at , C:\agent2_work\64\s\DevOps.Builds\ERP AL\Invoke-Master-CreateDevelopmentDB.ps1: line 103
at , : line 1
at , C:\agent2_work_tasks\AzurePowerShell_72a1931b-effb-4d2e-8fd8-f8472a07cb62\4.238.7\azurepowershell.ps1: line 261
at , C:\agent2_work_tasks\AzurePowerShell_72a1931b-effb-4d2e-8fd8-f8472a07cb62\4.238.7\azurepowershell.ps1: line 257
at , : line 1
at , : line 22
at , : line 18
at , : line 1

Currently trying with workaround :
New-cdsaAzureDevOpsSection -Message "Workaround BC24 - Install SqlServer module"
Invoke-ScriptInNavContainer -containerName $ContainerName -usePwsh:$false -scriptblock {
Install-Module PowerShellGet -AllowClobber -Force -verbose -Repository PSGallery
Install-Module -Name SqlServer -RequiredVersion 21.1.18256 -Force -AllowClobber
}

Answer 1 · 2024-04-27T15:10:58.000Z

Full log?
(which generic image is used)

Answer 2 · 2024-04-27T15:13:05.000Z

and...
If you use docker exec -it containername pwsh and type Import-Module SqlServer (without you adding your own) what does it say?
I mean - it doesn't say it isn't installed - it says it cannot be loaded.

Answer 3 · 2024-04-27T16:05:21.000Z

I've added just the import step infront of the backup-navcontainer. I'll check and let you know.

Answer 4 · 2024-04-27T21:25:13.000Z

So I changed code to this...
Invoke-ScriptInNavContainer -containerName $ContainerName -usePwsh:$false -scriptblock {
Import-Module SqlServer
#Install-PackageProvider -Name NuGet -MinimumVersion '2.8.5.201' -Force
#Install-Module -Name SqlServer -RequiredVersion 21.1.18256 -Force -AllowClobber
}
Backup-NavContainerDatabases -containerName "$ContainerName" -bakFolder "$BackupFolder"
-Verbose

But failed like this...
The specified module 'SqlServer' was not loaded because no valid module file was found in any module directory.

Exception Script Stack Trace:
at Invoke-ScriptInBcContainer, C:\Users\buildadmin2\Documents\WindowsPowerShell\Modules\bccontainerhelper\6.0.16\ContainerHandling\Invoke-ScriptInNavContainer.ps1: line 61
at , C:\agent2_work\64\s\DevOps.Builds\ERP AL\Invoke-Master-CreateDevelopmentDB.ps1: line 103
at , : line 1
at , C:\agent2_work_tasks\AzurePowerShell_72a1931b-effb-4d2e-8fd8-f8472a07cb62\4.238.7\azurepowershell.ps1: line 261
at , C:\agent2_work_tasks\AzurePowerShell_72a1931b-effb-4d2e-8fd8-f8472a07cb62\4.238.7\azurepowershell.ps1: line 257
at , : line 1
at , : line 22
at , : line 18
at , : line 1

PowerShell Call Stack:
at Invoke-ScriptInBcContainer, C:\Users\buildadmin2\Documents\WindowsPowerShell\Modules\bccontainerhelper\6.0.16\ContainerHandling\Invoke-ScriptInNavContainer.ps1: line 71
at , C:\agent2_work\64\s\DevOps.Builds\ERP AL\Invoke-Master-CreateDevelopmentDB.ps1: line 103
at , : line 1
at , C:\agent2_work_tasks\AzurePowerShell_72a1931b-effb-4d2e-8fd8-f8472a07cb62\4.238.7\azurepowershell.ps1: line 261
at , C:\agent2_work_tasks\AzurePowerShell_72a1931b-effb-4d2e-8fd8-f8472a07cb62\4.238.7\azurepowershell.ps1: line 257
at , : line 1
at , : line 22
at , : line 18
at , : line 1

Container Free Physical Memory: 46.8Gb

Answer 5 · 2024-04-28T02:58:03.000Z

When you use -usepwsh:$false - SqlServer module is NOT available and pscoreoverrides should never be included.
When running PS5 - SQLPS is included as always.

Are you including pscoreoverrides overrides yourself?

Answer 6 · 2024-04-28T03:00:02.000Z

code looks like this:

Answer 7 · 2024-04-28T09:26:45.000Z

I guess found the issue. Part of the upgrade process I'm performing this step:

        Write-Verbose "Restore app database from bacpac $BacpacPath"
        $StringBlock = @'
        param ($BacpacFile,
        $DatabaseName
        )
        Restore-BacpacWithRetry -bacpac $BacpacFile `
        -databasename $DatabaseName `
        -maxattempts 1
'@
        $ScriptBlock = [Scriptblock]::Create($StringBlock)
        Invoke-ScriptInNAVContainer -containerName $ContainerName -Scriptblock $ScriptBlock -ArgumentList $BacpacFile, $ContainerConfig.DatabaseName -usePwsh:$false

As you can see it has -usePwsh:$false, without this change it will fail with an error.
Could this be the root cause.
The statement Backup-NavDatabase is in a different devops step. Meaning a diffrent session.

Answer 8 · 2024-04-28T09:31:53.000Z

Error when not using option UsePwsh

VERBOSE: Restore app database from bacpac
Restoring Database from C:\ProgramData\BcContainerHelper\Extensions\backup\app.bacpac as CRONUS
Exception calling ".ctor" with "1" argument(s): "The type initializer for 'Microsoft.SqlServer.Dac.DacServices' threw an exception."

Exception Script Stack Trace:
at Restore-BacpacWithRetry, C:\run\HelperFunctions.ps1: line 94

PowerShell Call Stack:
at Invoke-ScriptInBcContainer, C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\6.0.16\ContainerHandling\Invoke-ScriptInNavContainer.ps1: line 71
at Update-cdsaMultiTenantContainer, : line 96
at , : line 6

Container Free Physical Memory: 19.2Gb

Services in container demo:
Exception calling ".ctor" with "1" argument(s): "The type initializer for 'Microsoft.SqlServer.Dac.DacServices' threw an exception."
At C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\6.0.16\ContainerHandling\Invoke-ScriptInNavContainer.ps1:112 char:13

```
        throw $errorMessage
```
```
        ~~~~~~~~~~~~~~~~~~~
```
- CategoryInfo : OperationStopped: (Exception calli... an exception.":String) [], RuntimeException
- FullyQualifiedErrorId : Exception calling ".ctor" with "1" argument(s): "The type initializer for 'Microsoft.SqlServer.Dac.DacServices' threw an exception."

Answer 9 · 2024-04-28T09:46:24.000Z

Any idea from which repo this function is comming?
Restore-BacpacWithRetry it exists in the container.

Answer 10 · 2024-04-28T18:24:49.000Z

Restore-BacpacWithRetry is from HelperFunctions inside the generic image (nav-docker repo)
I can see this only works in PS5 - so yes, that code will need -usepwsh $false (no colon - it is not a switch)
You should also use -useSession $false - else it will remove your pwsh session and create a powershell session - which it next time around will do again with a pwsh session.

Answer 11 · 2024-04-29T07:14:48.000Z

I added following to the script when restoring the bacpac but it keeps failing at the backup-navcontainer later in the process.

Invoke-ScriptInNAVContainer -containerName $ContainerName -Scriptblock $ScriptBlock -ArgumentList $BacpacFile, $ContainerConfig.DatabaseName -usePwsh $false -useSession $false

I'll try to create a repo scenario for this.

Answer 12 · 2024-04-29T19:04:57.000Z

I have a repro on my laptop... Grapping it into script(s) for you and will send it in a presonal mail.
Instructions:

Restore script to c:\temp\testcase2 (Needed because of hardcoded refrences)
Open script: SimpleDemoContainer-Artifacts.ps1 in ISE and f5 to run
Open script: Invoke-cdsaUpdateMultiTenantContainer.ps1 in ISE f5 to run
Close ISE and re-open
Open script: DevEnvironment.ps1 in ISE f5 to run

It will crash at the last script when trying to make backup... Allthough backups works in the first script (SimpleDemoContainer-Artifacts.ps1 ).

Answer 13 · 2024-04-30T06:23:26.000Z

Replied with a workaround on how to modify your code on email

Answer 14 · 2024-04-30T08:09:51.000Z

I tested this, but now it fails on step:
Remove-CompanyInBcContainer -containerName $ContainerName -companyName $_

The tenant 'default' is not mounted or does not exist.

Exception Script Stack Trace:
at , : line 2

PowerShell Call Stack:
at Invoke-ScriptInBcContainer, C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\6.0.16\ContainerHandling\Invoke-ScriptInNavContainer.ps1: line 71
at Get-CompanyInBcContainer, C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\6.0.16\CompanyHandling\Get-CompanyInNavContainer.ps1: line 22
at , : line 2

Somehow the default tenant doesnt exist anymore after switching to Single tenant.
Also the cmdlet get-Navtenant inside the container is now empty.. This looks like a diff in the platform compared to BC23

I can workaround this I guess using invoke-script instead Remove-NavContainerCompany

Answer 15 · 2024-04-30T08:31:16.000Z

Found rootcause the script to convert to single tenant contained a statement:
Dismount-NAVTenant -ServerInstance 'BC' -Tenant 'default' -Force

Before exporting the App.DB and moving it into the tenant DB. Somehow the behavior is different now. In the past it got mounted by defaullt somehow...

I removed the dismounting And now it continues. As we are only using single tenant in DEV environments for this case can be closed. But this might be something from the server team to look into as its changed behavior.

Answer 16 · 2024-04-30T08:56:02.000Z

I tested this, but now it fails on step: Remove-CompanyInBcContainer -containerName $ContainerName -companyName $_

The tenant 'default' is not mounted or does not exist.

Exception Script Stack Trace: at , : line 2

PowerShell Call Stack: at Invoke-ScriptInBcContainer, C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\6.0.16\ContainerHandling\Invoke-ScriptInNavContainer.ps1: line 71 at Get-CompanyInBcContainer, C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\6.0.16\CompanyHandling\Get-CompanyInNavContainer.ps1: line 22 at , : line 2

Somehow the default tenant doesnt exist anymore after switching to Single tenant. Also the cmdlet get-Navtenant inside the container is now empty.. This looks like a diff in the platform compared to BC23

I can workaround this I guess using invoke-script instead Remove-NavContainerCompany

This is likely because your scriptblock had a switch parameter and then it also read that value from args[0] - that doesn't work - I changed that (as I also saw that problem).

Answer 17 · 2024-04-30T09:50:14.000Z

Removing the dismount in my script when transforming to single tenant also solved the problem. Tested on both 23.x and 24x and all is fine. Thx for support again!