SqlTraceFlag: Assembly not loaded

[joshshand]

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

Just noticed that SqlTraceFlag has finally been added as a resource so am just trying the most basic configuration I can think of to test it out.

Unfortunately it would seem that some SQL SMO modules are not loaded and therefore the calls for $sqlManagement = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' -ArgumentList $ServerName in DSC_SqlTraceFlag.psm1 fail.

Configuration is being run on local node as an Administrator.

Verbose logs showing the problem

Cannot find type [Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]: verify that the assembly containing this
type is loaded.
    + CategoryInfo          : InvalidType: (:) [], CimException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand
    + PSComputerName        : localhost

Suggested solution to the issue

Referring to Zuldans work, it appears there is an additional helper function Initialize-SQLSMOAssembly that is used to load said module(s): https://github.com/Zuldan/cSQLServerTraceFlag/blob/master/cSQLServerTraceFlagHelper.psm1#L131

Not sure if it will be helpful or not. 🤞

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

Configuration Example
{

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlTraceFlag 'Set_SqlTraceFlags'
        {
            InstanceName         = 'MSSQLSERVER'
            TraceFlags           = 3226
            RestartService      = $true
        }
    }
}

Example

SQL Server edition and version the target node is running

Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64)

SQL Server PowerShell modules present on the target node

Name  Version Path
----  ------- ----
SQLPS 1.0     C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

The operating system the target node is running

OsName               : Microsoft Windows Server 2019 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 1809
WindowsBuildLabEx    : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

Version and build of PowerShell the target node is running

Name                           Value
----                           -----
PSVersion                      5.1.17763.1490
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.17763.1490
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 15.0.1  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\15.0.1\SqlServerDsc.psd1

[johlju]

Ah, this is not catched in the integration tests because the are other resources that do load the SMO.

At the top of the Get-TargetResource and Set-TargetResource it should call the helper function Import-SQLPSModule, that should solve your issue.

[Fiander]

i have the same resource that loads SMO also always running so never saw this one.

[johlju]

@Fiander looking into adding a Script Analyzer rule to detect these in the future (tracking in issue #1683), there could be more resources with the same issues.

[johlju]

@joshshand thanks to @Fiander this has been merged, I will push a new release during the weekend.

[joshshand]

Thanks all, really appreciate the fast turnaround! @johlju & @Fiander