SqlSetup: Cannot find newly mounted drive

[bozho]

Details of the scenario you tried and the problem that is occurring

We install SQL Server on our bare metal machines by downloading an ISO file, mounting it and running SQL setup. A simplified configuration looks something like this:

$sqlServerIsoPath = "C:\Users\Administrator\Downloads\$sqlServerIsoFileName"
xRemoteFile SqlServerIsoFile {
    Uri                  = "https://www.example.com/$sqlServerIsoFileName"
    DestinationPath      = $sqlServerIsoPath
    MatchSource          = $false
    PsDscRunAsCredential = $adminCredentials
}

MountImage MountSqlServerIsoFile {
    ImagePath   = $sqlServerIsoPath
    DriveLetter = 'D'
    Ensure      = "Present"
    DependsOn   = "[xRemoteFile]SqlServerIsoFile"
}

WaitForVolume WaitForSqlServerSetupVolume {
    DriveLetter      = 'D'
    RetryIntervalSec = 5
    RetryCount       = 12
}

# A DSC script resource that will compare major SQL Server version from the configuration document against
# setup.exe major version. Used to protect us from mismatching SQL Server versions, so we want this to fail
# early, to stop SQL Server installation.
xScript CheckSqlSetupVersion {
    SetScript = { ... }
    TestScript = { ... }
    GetScript = { ... }
    DependsOn = "[WaitForVolume]WaitForSqlServerSetupVolume"
}

SqlSetup SqlSetup {
    InstanceName          = "MSSQLSERVER"
    SourcePath            = "D:\"
    Action                = "Install"
    Features              = $sqlFeatures
    InstanceID            = "MSSQLSERVER"
    UpdateEnabled         = "True"
    UpdateSource          = "MU"
    SQMReporting          = "False"
    ErrorReporting        = "False"
    InstallSharedDir      = "C:\Program Files\Microsoft SQL Server"
    InstallSharedWOWDir   = "C:\Program Files (x86)\Microsoft SQL Server"
    InstanceDir           = "C:\Program Files\Microsoft SQL Server"
    SQLCollation          = "Latin1_General_CI_AS"
    SQLSysAdminAccounts   = "BUILTIN\Administrators"
    SecurityMode          = "SQL"
    SAPwd                 = $saCredentials
    RSInstallMode         = "FilesOnlyMode"
    ASCollation           = "Latin1_General_CI_AS"
    ASSysAdminAccounts    = "BUILTIN\Administrators"
    ASServerMode          = "MULTIDIMENSIONAL"
    SqlSvcStartupType     = "Automatic"
    AgtSvcStartupType     = "Manual"
    AsSvcStartupType      = "Automatic"
    RsSvcStartupType      = "Automatic"
    BrowserSvcStartupType = "Disabled"
    PsDscRunAsCredential  = $adminCredentials
    DependsOn             = "[xScript]CheckSqlSetupVersion"
}

WaitForVolume resource successfully waits for the D volume to appear, xScript resource depends on it and checks that D:\setup.exe matches expected major SQL version (it would fail if it couldn't find the path/file), and SqlSetup resource depends on the xScript resource.

Still, when SqlSetup's Get-TargetResource is called from Test-TargetResource, the Join-Path call will fail with Join-Path : Cannot find drive. A drive with the name 'D' does not exist.

Verbose logs showing the problem

N/A

Suggested solution to the issue

Executing Get-PSDrive seems to refresh the PS drive provider in the PowerShell session used by the SqlSetup resource and Join-Path does not complain about the missing drive.

The DSC configuration that is used to reproduce the issue (as detailed as possible)

See above.

SQL Server edition and version the target node is running

Any SQL server version supported by SqlSetup

SQL Server PowerShell modules present on the target node

Name      Version    Path                                                                                     
----      -------    ----                                                                                     
SqlServer 21.1.18068 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18068\SqlServer.psd1           
SQLPS     14.0       C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

The operating system the target node is running

Windows Server 2016

Version and build of PowerShell the target node is running

Name                           Value                                                                                   
----                           -----                                                                                   
PSVersion                      5.1.14393.2969                                                                          
PSEdition                      Desktop                                                                                 
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                 
BuildVersion                   10.0.14393.2969                                                                         
CLRVersion                     4.0.30319.42000                                                                         
WSManStackVersion              3.0                                                                                     
PSRemotingProtocolVersion      2.3                                                                                     
SerializationVersion           1.1.0.1                                                                                 

Version of the DSC module that was used

Name         Version  Path                                                                              
----         -------  ----                                                                              
SqlServerDsc 13.0.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\13.0.0.0\SqlServerDsc.psd1

Happy to submit a patch.

[johlju]

So the WaitForVolume does the same as the the suggested solution.

https://github.com/dsccommunity/StorageDsc/blob/d66d56864433466b2eee02cf2282da7224e8db67/DSCResources/MSFT_WaitForVolume/MSFT_WaitForVolume.psm1#L122-L126

Does this mean that every resource that are using paths, where the drive letter could be a mounted disk, must do this to refresh the drive list. There are no way to refresh the session that the LCM runs. 🤔

[bozho]

I don't quite understand it myself... SqlSetup transitively depends on WaitForVolume in the example above. If they were running in the same LCM session, Get-PSDrive call by WaitForVolume would refresh it for SqlSetup, too.

Admittedly, I do not know how DSC spawns and uses PS sessions...

[johlju]

From @gaelcolas in the Slack #DSC channel

because the LCM process is spinning up a child thread for each resource, each resources will operate in a context that is inherited from the LCM process. If the resource registers an Environment variable, it will be seen in the current thread, but when that resource is finished executing, this thread is disposed.
The next resource will be in a process that is also child from the LCM, not from the previous thread, so it won't know about.

Drives will then probably have the same since you are seeing it.