SqlSetup: Support version upgrade

[codykonior]

I wanted to use SqlSetup to do a version upgrade. This isn't currently supported.

A basic version of it can easily added to MSFT_SqlSetup.psm1:

• Replace anywhere labelled 'Install' with 'Install','Upgrade'

I tested and this upgrades a basic SQL 2012 install to SQL 2017. This doesn't need to support everything, but supporting something is better than nothing right?

Issues

• There is one non-terminating error. The cause for this is that there's two lines for $isDQCInstalled = (Get-ItemProperty... and one of them is missing -ErrorAction:SilentlyContinue.

• But after you run this, you also need to re-run SqlWindowsFirewall to add an exception for the updated .exe path. What it ends up doing is creating a second rule with the same name ("SQL Server Database Engine instance MSSQLSERVER"), then failing during testing because it only expects one rule with that name.

I think this is a bug and it should update the existing rule. The unique factor is the instance name, so if that gets overwritten, it's the correct behaviour.

• Separately, for SqlAGDatabase/Get was returning an error for AGs which have a single database. This is because it would attempt to return the DatabaseName property as a string instead of as a string array.

• Also .\Assert-TestEnvironment.ps1 would give an error when Set-StrictMode -Version Latest because it checked $dependencyMissing when it may not have been defined.

[codykonior]

I'm satisfied the PR works. Here's my functional test suite against my lab.

$systemAdministrator = New-Object System.Management.Automation.PSCredential("sa", ('System2019!' | ConvertTo-SecureString -AsPlainText -Force))
$localAdministrator = New-Object System.Management.Automation.PSCredential("LAB\LocalAdministrator", ('Local2019!' | ConvertTo-SecureString -AsPlainText -Force))
$sqlEngineService = New-Object System.Management.Automation.PSCredential("LAB\SQLEngineService", ('Engine2019!' | ConvertTo-SecureString -AsPlainText -Force))

# Pre-PR Upgrade was not accepted, and this would show an error on DQC

Invoke-DscResource -ModuleName SqlServerDsc -Name SqlSetup -Method Set -Property @{
    InstanceName = 'MSSQLSERVER'
    Action = 'Upgrade'
    SourcePath = '\\CHDC01\Resources\SQLServer2017'
    Features = 'SQLENGINE,REPLICATION,FULLTEXT'
    SecurityMode = 'Sql'
    SAPwd = $systemAdministrator
    SQLSvcAccount = $sqlEngineService
    SQLSysAdminAccounts = @($localAdministrator.UserName)
    UpdateEnabled = 'False'
    PsDscRunAsCredential = $localAdministrator
} -Verbose

# Pre-PR the database engine firewall rule will add a new one instead of updating the existing one

Invoke-DscResource -ModuleName SqlServerDsc -Name SqlWindowsFirewall -Method Set -Property @{
    InstanceName = "MSSQLSERVER"
    SourcePath = "\\CHDC01\Resources\SQLServer2017"
    Features = "SQLENGINE,REPLICATION,FULLTEXT"
    Ensure = 'Present'
} -Verbose

# Pre-PR this will fail because multiple firewall rules are returned unexpectedly

Invoke-DscResource -ModuleName SqlServerDsc -Name SqlWindowsFirewall -Method Test -Property @{
    InstanceName = "MSSQLSERVER"
    SourcePath = "\\CHDC01\Resources\SQLServer2017"
    Features = "SQLENGINE,REPLICATION,FULLTEXT"
    Ensure = 'Present'
} -Verbose

# Pre-PR this would fail for AGs which have a single database because DatabaseName wasn't an array

Invoke-DscResource -ModuleName SqlServerDsc -Name SqlAGDatabase -Method Get -Property @{
    PsDscRunAsCredential = $localAdministrator;
    ServerName = "SEC1N1";
    InstanceName = "MSSQLSERVER";
    BackupPath = "\\CHDC01\Temp";
    DatabaseName =     @("DummyAG1")
    AvailabilityGroupName = "AG1";
    Ensure = "Present";
} -Verbose