Sql<Setup Action>: New resource proposal

[Sudman1]

Resource proposal

To simplify the SqlSetup resource and address some existing issues (#1637 in particular), I propose that the SqlSetup resource be broken out into separate resources based on the setup Action being performed.

A SqlSetupBase class would hold the shared logic for setup tasks. But subclasses would be created to correlate with setup actions as listed below. The subclasses would also only allow users to set options relevant to that particular activity as set out in the Microsoft install docs here.

• Actions
  • Install
  • Uninstall
  • PrepareImage
  • CompleteImage
  • Upgrade
  • EditionUpgrade
  • Repair
  • RebuildDatabase
  • InstallFailoverCluster
  • PrepareFailoverCluster
  • CompleteFailoverCluster
  • AddNode
  • RemoveNode

Proposed properties

All properties would be refactored from the existing SqlSetup MOF-based resource with common properties consolidated up to the parent SqlSetupBase class.

Special considerations or limitations

The current functionality of SqlSetup uses active connections to get many settings. However, at least one action: PrepareFailoverCluster does not result in a functioning SQL service. This should be taken into account when writing the shared functionality of SqlSetupBase.

[johlju]

The current functionality of SqlSetup uses active connections to get many settings. However, at least one action: PrepareFailoverCluster does not result in a functioning SQL service. This should be taken into account when writing the shared functionality of SqlSetupBase.

We should make each resource build the necessary arguments for the command line and each resource should have all logic for determining that (but can call private or public functions where possible to avoid duplicate code). A parent class (SqlSetupBase) should only have common functions that executes the setup.exe (which preferably should be a public function Install-SqlServer).

[johlju]

@Sudman1 I created a command Install-SqlServerDsc (PR https://github.com/dsccommunity/SqlServerDsc/pull/1784) that extracted the install part from SqlSetup. Thinking that the proposed resources can just send (more or less) $PSBoundParameters to the command and it will handle the rest. Then we need commands to get current state like Get-SqlDscSqlEngineState, Get-SqlDscAnalysisServicesState, etc. to be able to return the correct state for Get() and evaluate Test(). 🤔

Would it work, or bad idea?

[johlju]

The following public commands now exist in this module:

• Install-SqlDscServer
• Uninstall-SqlDscServer
• Add-SqlDscNode
• Remove-SqlDscNode
• Repair-SqlDscServer
• Complete-SqlDscImage
• Complete-SqlDscFailoverCluster
• Initialize-SqlDscRebuildDatabase

Thy cover all the current available setup actions with all current setup arguments (except some that got new in GA of SQL Server 2022, tracked in issue #1798) . This public commands can be used to simplify class-based resources, since less logic need to be in each individual class-based resource.

I wish we could have split up Install-SqlDscServer but I could not find any allowed verbs that matched the setup actions, e.g. there are no verb for Upgrade.