DSC_SqlAlwaysOnService: RestartTimeout not being set

jordanbreen28 commented 2 years ago

Problem description

When running the DSC_SqlAlwaysOnService resource and the Set-TargetResource function is ran, powershell returns that the resource executed and updated successfully.

An example of running this is:

Invoke-DscResource -Name SqlAlwaysOnService -Method Set -ModuleName C:/Users/vagrant/code/import/sqlserverdsc/spec/fixtures/modules/sqlserverdsc/lib/puppet_x/sqlserverdsc/dsc_resources/SqlServerDsc/SqlServerDsc.psd1 -Property @{
>>   RestartTimeout = 60
>>   InstanceName = 'MSSQLSERVER'
>>   Ensure = 'Present'
>> }

However, when I run a Get-TargetResource, the following is returned:

ConfigurationName    :
DependsOn            :
ModuleName           : C:/Users/vagrant/code/import/sqlserverdsc/spec/fixtures/modules/sqlserverdsc/lib/puppet_x/sqlserverds
                       c/dsc_resources/SqlServerDsc/SqlServerDsc.psd1
ModuleVersion        : 15.2.0
PsDscRunAsCredential :
ResourceId           :
SourceInfo           :
Ensure               : Present
InstanceName         : MSSQLSERVER
IsHadrEnabled        :
RestartTimeout       : 120
ServerName           : WIN-NCIV9KQ9FHV
PSComputerName       : localhost

Upon inspection of the code in DSC_SqlAlwaysOnService.psm1, you can see that the RestartTimeout value is hardcoded in the Get, Set and Test functions.

        [Parameter()]
        [System.UInt32]
        $RestartTimeout = 120

For the Get-TargetResource, the RestartTimeout being hardcoded as 120 would maybe explain why 120 is always returned, however when I remove this hardcoded value, 0 is returned. It seems as though the inputted value for RestartTimeout also isn't being set even with the hardcoded 120 in:

 Restart-SqlService -ServerName $ServerName -InstanceName $InstanceName -Timeout $RestartTimeout

So my guess would be that this is maybe the issue, and as the value of RestartTimeout won't be set, the default value of 120 in the Get function is returned.


### DSC configuration

```powershell
See above.

SQL Server edition and version

SQL Server Developer 2019

SQL Server PowerShell modules

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

Operating system

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

PowerShell version

5.1.17763.2931

SqlServerDsc version

SqlServerDsc
Module Version: 15.2.0-0-1

johlju commented 2 years ago

The value of 120 is just a default value if RestartTimeout parameter is not passed. If a value is passed the default value is not used.

Can you run Set and Get and also add the parameter -Verbose to see if we can get more information of whats happening?

jordanbreen28 commented 2 years ago

Thanks for getting back and clearing up the confusion regarding the RestartTimeout parameter @johlju, I've run the commands with the -Verbose flag, i'll add them here below.

Set throws an error:

VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = ResourceSet,'className' =
MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer WIN-NCIV9KQ9FHV with user sid
S-1-5-21-3271800354-1623257648-989399822-1000.
VERBOSE: [WIN-NCIV9KQ9FHV]: LCM:  [ Start  Set      ]  [[SqlAlwaysOnService]DirectResourceAccess]
VERBOSE: [WIN-NCIV9KQ9FHV]:                            [[SqlAlwaysOnService]DirectResourceAccess] Information:
PowerShell module SqlServer not found, trying to use older SQLPS module. (SQLCOMMON0024)
VERBOSE: [WIN-NCIV9KQ9FHV]:                            [[SqlAlwaysOnService]DirectResourceAccess] Importing PowerShell
module 'SQLPS' with version '15.0' from path 'C:\Program Files (x86)\Microsoft SQL
Server\150\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1'. (SQLCOMMON0025)
VERBOSE: [WIN-NCIV9KQ9FHV]:                            [[SqlAlwaysOnService]DirectResourceAccess] Enabling Always On
Availability Groups for the instance 'WIN-NCIV9KQ9FHV\MSSQLSERVER'.
VERBOSE: [WIN-NCIV9KQ9FHV]:                            [[SqlAlwaysOnService]DirectResourceAccess] Always On
Availability Groups has been enabled on the instance 'WIN-NCIV9KQ9FHV\MSSQLSERVER'. Restarting the service.
VERBOSE: [WIN-NCIV9KQ9FHV]:                            [[SqlAlwaysOnService]DirectResourceAccess] Found PowerShell
module SQLPS already imported in the session. (SQLCOMMON0026)
VERBOSE: [WIN-NCIV9KQ9FHV]:                            [[SqlAlwaysOnService]DirectResourceAccess] Connecting as current
 user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)
VERBOSE: [WIN-NCIV9KQ9FHV]: LCM:  [ End    Set      ]  [[SqlAlwaysOnService]DirectResourceAccess]  in 17.2530 seconds.
An internal error occurred.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : HRESULT 0x8007054f
    + PSComputerName        : localhost

VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 22.706 seconds

Running get with -verbose flag:

VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = ResourceGet,'className' =
MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer WIN-NCIV9KQ9FHV with user sid
S-1-5-21-3271800354-1623257648-989399822-1000.
VERBOSE: [WIN-NCIV9KQ9FHV]:                            [[SqlAlwaysOnService]DirectResourceAccess] Information:
PowerShell module SqlServer not found, trying to use older SQLPS module. (SQLCOMMON0024)
VERBOSE: [WIN-NCIV9KQ9FHV]:                            [[SqlAlwaysOnService]DirectResourceAccess] Importing PowerShell
module 'SQLPS' with version '15.0' from path 'C:\Program Files (x86)\Microsoft SQL
Server\150\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1'. (SQLCOMMON0025)
VERBOSE: [WIN-NCIV9KQ9FHV]:                            [[SqlAlwaysOnService]DirectResourceAccess] Connecting as current
 user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)
VERBOSE: [WIN-NCIV9KQ9FHV]:                            [[SqlAlwaysOnService]DirectResourceAccess] Connected to SQL
instance 'WIN-NCIV9KQ9FHV'. (SQLCOMMON0018)
VERBOSE: [WIN-NCIV9KQ9FHV]:                            [[SqlAlwaysOnService]DirectResourceAccess] Always On
Availability Groups is enabled on the instance 'WIN-NCIV9KQ9FHV\MSSQLSERVER'.
VERBOSE: [WIN-NCIV9KQ9FHV]: LCM:  [ End    Get      ]  [[SqlAlwaysOnService]DirectResourceAccess]  in 0.8820 seconds.
VERBOSE: [WIN-NCIV9KQ9FHV]: LCM:  [ End    Set      ]    in  1.2400 seconds.
VERBOSE: Operation 'Invoke CimMethod' complete.

ConfigurationName    :
DependsOn            :
ModuleName           : C:/Users/vagrant/code/import/sqlserverdsc/spec/fixtures/modules/sqlserverdsc/lib/puppet_x/sqlser
                       verdsc/dsc_resources/SqlServerDsc/SqlServerDsc.psd1
ModuleVersion        : 15.2.0
PsDscRunAsCredential :
ResourceId           :
SourceInfo           :
Ensure               : Present
InstanceName         : MSSQLSERVER
IsHadrEnabled        :
RestartTimeout       : 120
ServerName           : WIN-NCIV9KQ9FHV
PSComputerName       : localhost

VERBOSE: Time taken for configuration job to complete is 1.511 seconds

johlju commented 2 years ago

How is method Get invoked?

jordanbreen28 commented 2 years ago

How is method Get invoked?

Invoke-DscResource -Name SqlAlwaysOnService -Method Get -ModuleName C:/Users/vagrant/code/import /sqlserverdsc/spec/fixtures/modules/sqlserverdsc/lib/puppet_x/sqlserverdsc/dsc_resources/SqlServerDsc/SqlServerDsc.psd1 -Property @{ InstanceName = 'MSSQLSERVER' Ensure = 'Present' } -Verbose

johlju commented 2 years ago

So if you add RestartTimeout in this call then it should change from the RestartTimeout default value.

Invoke-DscResource -Name SqlAlwaysOnService -Method Get -ModuleName C:/Users/vagrant/code/import
/sqlserverdsc/spec/fixtures/modules/sqlserverdsc/lib/puppet_x/sqlserverdsc/dsc_resources/SqlServerDsc/SqlServerDsc.psd1
-Property @{
   InstanceName = 'MSSQLSERVER'
   Ensure = 'Present'
   RestartTimeout = 200
 } -Verbose

Does that concludes your question?

jordanbreen28 commented 2 years ago

So if you add RestartTimeout in this call then it should change from the RestartTimeout default value.

Invoke-DscResource -Name SqlAlwaysOnService -Method Get -ModuleName C:/Users/vagrant/code/import
/sqlserverdsc/spec/fixtures/modules/sqlserverdsc/lib/puppet_x/sqlserverdsc/dsc_resources/SqlServerDsc/SqlServerDsc.psd1
-Property @{
   InstanceName = 'MSSQLSERVER'
   Ensure = 'Present'
   RestartTimeout = 200
 } -Verbose

Does that concludes your question?

@johlju If no value is passed in the Get method, I was under the impression that the most previously set value should be returned and not a default value of 120?

johlju commented 2 years ago

A resource does not keep the state of the "thing" it sets, it must always be returned by Get method. The RestartTimeout is a parameter to control the behavior of the resource. In this case the parameter is used to pass to the internal function Restart-SqlService, but it is never actually used to change the state of the "thing". That means that there is no way for the Get method to get the value used in the configuration unless it is passed into the Get method. There ar no way to differentiate between a parameter that is used to change the state and a parameter that is used to control the behavior of the resource (we can only change the parameter description to be descriptive).

jordanbreen28 commented 2 years ago

@johlju thanks for clearing that up. Happy to close this issue.

dsccommunity / SqlServerDsc