Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SqlSetup: Support version upgrade #1368

codykonior opened this issue May 31, 2019 · 1 comment · Fixed by #1369


Copy link

@codykonior codykonior commented May 31, 2019

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?


  • 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.


This comment has been minimized.

Copy link
Contributor Author

@codykonior codykonior commented Jun 6, 2019

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'
    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"
    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"
    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
@johlju johlju closed this in #1369 Jun 6, 2019
johlju added a commit that referenced this issue Jun 6, 2019

- Changes to SqlServerDsc
  - Update Assert-TestEnvironment.ps1 to not error if strict mode is enabled
    and there are no missing dependencies (issue #1368).
- Changes to SqlSetup
  - Add an Action type of 'Upgrade'. This will ask setup to do a version
    upgrade where possible (issue #1368).
  - Fix an error when testing for DQS installation (issue #1368).
- Changes to SqlWindowsFirewall
  - Where a version upgrade has changed paths for a database engine, the
    existing firewall rule for that instance will be updated rather than
    another one created (issue #1368).
    Other firewall rules can be fixed to work in the same way later.
- Changes to SqlAGDatabase
  - Fix issue where calling Get would return an error because the database
    name list may have been returned as a string instead of as a string array
    (issue #1368).
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
None yet
2 participants
You can’t perform that action at this time.