SQLSetup: Upgrade not being performed from SQL2014 to SQL2017

[Carv01]

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

Suggested solution to the issue

I thought upgrades were supported and looking at the SQLSetup element of the DSC code base it does look to be supported?

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]

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]

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?
#1368

[johlju]

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]

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]

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

SqlServerDsc/source/DSCResources/DSC_SqlSetup/DSC_SqlSetup.psm1

Line 987 in 3a449f5

$sqlVersion = Get-SqlMajorVersion -Path $pathToSetupExecutable

SqlServerDsc/source/Modules/SqlServerDsc.Common/SqlServerDsc.Common.psm1

Lines 750 to 760 in 883013c

	<#
	.SYNOPSIS
	Returns the major SQL version for the specific instance.
	.PARAMETER InstanceName
	String containing the name of the SQL instance to be configured. Default value is 'MSSQLSERVER'.
	.OUTPUTS
	System.UInt16. Returns the SQL Server major version number.
	#>
	function Get-SqlInstanceMajorVersion

[johlju]

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]

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.