SQLSetup: Upgrade not being performed from SQL2014 to SQL2017

Carv01 commented 4 years ago

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

I have found a problem when upgrading from SQL2014 to SQL2017, where the set element of the SQLSetup action appears to be skipped. To further test this, I have switched to a test VM using the sample config from MS docs to deploy a vanilla SQL2014 instance.

This works and SQL2014 with SP3 installs perfectly. I then switch to perform the upgrade but it is skipped.

I know that Action = Install or Upgrade should be acceptable, I just hardcoded it for testing. This gets skipped however and the upgrade never occurs.

Verbose logs showing the problem

PS C:\Users\Administrator> Start-DscConfiguration -Path C:\DSC -Wait -Force -Verbose VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = SendConfigurationApply,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'. VERBOSE: An LCM method call arrived from computer WIN-IG6003QK5HN with user sid S-1-5-21-1769353984-2845518682-2742595298-500. VERBOSE: [WIN-IG6003QK5HN]: LCM: [ Start Set ] VERBOSE: [WIN-IG6003QK5HN]: LCM: [ Start Resource ] [[WindowsFeature]NetFramework45] VERBOSE: [WIN-IG6003QK5HN]: LCM: [ Start Test ] [[WindowsFeature]NetFramework45] VERBOSE: [WIN-IG6003QK5HN]: [[WindowsFeature]NetFramework45] The operation 'Get-WindowsFeature' started: NET-Framework-45-Core VERBOSE: [WIN-IG6003QK5HN]: [[WindowsFeature]NetFramework45] The operation 'Get-WindowsFeature' succeeded: NET-Framework-45-Core VERBOSE: [WIN-IG6003QK5HN]: LCM: [ End Test ] [[WindowsFeature]NetFramework45] in 0.7500 seconds. VERBOSE: [WIN-IG6003QK5HN]: LCM: [ Skip Set ] [[WindowsFeature]NetFramework45] VERBOSE: [WIN-IG6003QK5HN]: LCM: [ End Resource ] [[WindowsFeature]NetFramework45] VERBOSE: [WIN-IG6003QK5HN]: LCM: [ Start Resource ] [[SqlSetup]InstallDefaultInstance] VERBOSE: [WIN-IG6003QK5HN]: LCM: [ Start Test ] [[SqlSetup]InstallDefaultInstance] VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Using path 'C:\Repos\SQLServer2017\setup.exe'. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Detecting Database Engine feature. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Database Engine feature detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Perform operation 'Query CimInstances' with following parameters, ''queryExpression' = SELECT FROM Win32_Service WHERE Name = 'MSSQLSERVER','queryDialect' = WQL,'namespaceName' = root\cimv2'. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Operation 'Query CimInstances' complete. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Perform operation 'Query CimInstances' with following parameters, ''queryExpression' = SELECT FROM Win32_Service WHERE Name = 'SQLSERVERAGENT','queryDialect' = WQL,'namespaceName' = root\cimv2'. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Operation 'Query CimInstances' complete. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Detecting replication feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\ConfigurationState). VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Replication feature not detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Detecting Data Services Client feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\DQ*). VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Data Quality Services feature not detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Detecting Data Quality Client feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\ConfigurationState). VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Data Quality Client feature not detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Information: PowerShell module SqlServer not found, trying to use older SQLPS module. (SQLCOMMON0024) VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Importing PowerShell module 'SQLPS' with version '1.0' from path 'C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1'. (SQLCOMMON0025) VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054) VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Connected to SQL instance 'WIN-IG6003QK5HN'. (SQLCOMMON0018) VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Clustered instance not detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Detecting Full-text feature. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Full-text feature not detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Detecting Reporting Services feature. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Reporting Services feature not detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Detecting Analysis Services feature. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Analysis Services feature not detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Detecting Integration Services feature. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Integration Services feature not detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Detecting Documentation Components feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\ConfigurationState). VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Documentation Components feature not detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Detecting Client Connectivity Tools feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\Tools\Setup\Client_Components_Full). VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Client Connectivity Tools feature not detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Detecting Client Connectivity Backwards Compatibility Tools feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\Tools\Setup\Client_Components_Full). VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Client Connectivity Backwards Compatibility Tools feature not detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Detecting Client Tools SDK feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\Tools\Setup\Client_Components_Full). VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Client Tools SDK feature not detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Detecting Master Data Services (MDS) feature (HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\ConfigurationState). VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Master Data Services (MDS) feature not detected. VERBOSE: [WIN-IG6003QK5HN]: [[SqlSetup]InstallDefaultInstance] Found features already installed: SQLENGINE VERBOSE: [WIN-IG6003QK5HN]: LCM: [ End Test ] [[SqlSetup]InstallDefaultInstance] in 2.4220 seconds. VERBOSE: [WIN-IG6003QK5HN]: LCM: [ Skip Set ] [[SqlSetup]InstallDefaultInstance] VERBOSE: [WIN-IG6003QK5HN]: LCM: [ End Resource ] [[SqlSetup]InstallDefaultInstance] VERBOSE: [WIN-IG6003QK5HN]: LCM: [ End Set ] VERBOSE: [WIN-IG6003QK5HN]: LCM: [ End Set ] in 6.5620 seconds. VERBOSE: Operation 'Invoke CimMethod' complete. VERBOSE: Time taken for configuration job to complete is 6.635 seconds

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

First Install, which works fine:

Configuration SQLInstall
{
     Import-DscResource -ModuleName SqlServerDsc

     node localhost
     {
          WindowsFeature 'NetFramework45'
          {
               Name   = 'NET-Framework-45-Core'
               Ensure = 'Present'
          }

          SqlSetup 'InstallDefaultInstance'
          {
               InstanceName        = 'MSSQLSERVER'
               Features            = 'SQLENGINE'
               SourcePath          = 'C:\Repos\SQLServer2014'
               SQLSysAdminAccounts = @('Administrators')
               DependsOn           = '[WindowsFeature]NetFramework45'
          }
     }
}

Upgrade to SQL2017, which does not get invoked:

Configuration SQLInstall
{
     Import-DscResource -ModuleName SqlServerDsc

     node localhost
     {
          WindowsFeature 'NetFramework45'
          {
               Name   = 'NET-Framework-45-Core'
               Ensure = 'Present'
          }

          SqlSetup 'InstallDefaultInstance'
          {
               Action              = 'Upgrade'
               InstanceName        = 'MSSQLSERVER'
               Features            = 'SQLENGINE'
               SourcePath          = 'C:\Repos\SQLServer2017'
               SQLSysAdminAccounts = @('Administrators')
               DependsOn           = '[WindowsFeature]NetFramework45'
          }
     }
}

SQL Server edition and version the target node is running

Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

Name Version Path

SQLASCMDLETS 1.0 C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLASCMDLETS\SQLASCMDL... SQLPS 1.0 C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

The operating system the target node is running

OsName : Microsoft Windows Server 2016 Standard OsOperatingSystemSKU : StandardServerEdition OsArchitecture : 64-bit WindowsBuildLabEx : 14393.1794.amd64fre.rs1_release.171008-1615 OsLanguage : en-US OsMuiLanguages : {en-US}

Version and build of PowerShell the target node is running

Name Value

PSVersion 5.1.14393.1884 PSEdition Desktop PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...} BuildVersion 10.0.14393.1884 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.5.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\13.5.0\SqlServerDsc.psd1

johlju commented 4 years ago

I can't see that there is logic in Test-TargetResource that evaluates the major version on the installation media and the major version currently installed. So when in see that all features are installed Test-TargetResource returns $true and that will make Set to be skipped.

Carv01 commented 4 years ago

Oh, embarrassing! I thought from reviewing this issue and subsequent PR that upgrading a version was a supported operation (example given is 2012 to 2107), is this incorrect? https://github.com/dsccommunity/SqlServerDsc/issues/1368

johlju commented 4 years ago

I too thought it was supported by that issue being closed, but when I looked at the code prior to commenting before I can't see that there is any logic that have ever made this work. It probably work if there are no previous instance, but that is the same as using the Install action. 🤔

Carv01 commented 4 years ago

Ok, well in that case could I request that this be reviewed as a feature request please? Performing an in-place major version upgrade would be very useful if it could be supported.

johlju commented 4 years ago

But this should be a relative easy fix by using these helper functions.

https://github.com/dsccommunity/SqlServerDsc/blob/3a449f5c5c36a29f713390265e7ea07b122efb49/source/DSCResources/DSC_SqlSetup/DSC_SqlSetup.psm1#L987

https://github.com/dsccommunity/SqlServerDsc/blob/883013cfdb1ef951d121324934c31feb5a008260/source/Modules/SqlServerDsc.Common/SqlServerDsc.Common.psm1#L750-L760

johlju commented 4 years ago

Ok, well in that case could I request that this be reviewed as a feature request please? Performing an in-place major version upgrade would be very useful if it could be supported.

Anyone in the community can run with this. I'm more than happy to review a PR.

Carv01 commented 4 years ago

if ($getTargetResourceParameters.Action -eq "Upgrade")
    {
        $InstallerSQLVersion = Get-SqlMajorVersion -Path $SourcePath\setup.exe
        $InstanceSQLVersion = Get-SQLInstanceMajorVersion -InstanceName $InstanceName
        if ($InstallerSQLVersion -gt $InstanceSQLVersion)
            {
                $result = $false
            }
    }

That fixes it when I tested locally. Invoked if the "Upgrade" string is passed in as the action and this code block has been added to the SqlSetup test-targetresource area.

dsccommunity / SqlServerDsc