Desired State Configuration(DSC) for SQL Server ***Update 2


Following up from my last post I have published the Failover Cluster Instance composite configurations and updated the previous configurations.  Initially lets talk about the updates to the which were pushed.

Single Instance, Primary AlwaysOn,  and Secondary AlwaysOn  all had additional optional parameters added enabling all configurable items to be leveraged.  Each of these are defaulted so no breaking changes to the previous examples but additional flexibility added.

Lets move on to Failover Cluster Instance as this has been one I have gotten several questions on how to leverage.  Utilizing the SQLCompositeResources module there are three new resources which will be utilized to build a Failover Cluster Instance FailOverClusterDisk, FailOverClusterInstanceFirstNode, and FailOverClusterInstanceAdditionalNode.  To show how they can be leveraged we will walk through the example provided for FailOverClusterInstanceFirstNode and FailOverClusterInstanceAdditionalNode.

Failover Cluster Instance First Node

First resource we will cover this time is FailOverClusterInstanceFirstNode_Example.ps1. As in the previous post Credential Management and LCM Config are identical so I will not cover these again.

Metadata ($CofigData)

Here we need to provide a few more data elements than we did previously.  Unlike our previous builds we have to provide an installation path for our SQL Server database and logs files as these are required to reside on a shared disk.  Since at least one shared disk is required we need to provide the disk layout and how each drive should be labeled.  To simplify we do this by providing a key of DiskConfiguration and passing an array of hashtable values.  Each hashtable will provide the parameters FailOverClusterDisk requires. Finally we need to provide a name for our FCI and the IPaddress.

SQL Server Configuration

The main section is the SQL Configuration and its a simple call.  One call to  FailOverClusterInstanceFirstNode will ensure the following:

  1. Ensure Failover Cluster feature is installed on the machine
  2. Ensures disks partitions are created, formatted and added to the cluster.  This is accomplished by calling FailOverClusterDisk
  3. Ensure .Net framework is present
  4. Installs Failover Cluster Instance of SQL Server
  5. Configures SQL FCI

To complete the example Resources are moved and the configuration is generated and deployed.

Failover Cluster Instance Additional Node

Next we will look at FailOverClusterInstanceAdditionalNode_Example.ps1 

Metadata ($CofigData)

Since we are adding to an existing cluster, we have a much smaller set required.  All required values are listed in FailOverClusterInstanceAdditionalNode_Example.ps1 .

SQL Server Configuration

The main section for SQL Configuration is again simple, a singe call to FailOverClusterInstanceAdditionalNode.  Calling FailOverClusterInstanceAdditionalNode will do the following:

  1. Ensure Failover Cluster feature is installed on the machine
  2. Wait for the cluster created from FailOverClusterInstanceFirstNode to be found
  3. Join the cluster
  4. Ensure .Net framework is present
  5. Add node to SQL FCI

To complete the example Resources are moved and the configuration is generated and deployed.

With the addition of these composite resources I hope building out new SQL configurations will be simpler and have less redundancy.  Please let me know if you have issues or recommendations for improvement.

 

 

 

Comments (5)

  1. Steve says:

    Very useful, thank you. Don’t forget the xStorage resource is required also.

    1. Troy Ault says:

      Thank you I will update the documentation.

  2. Steve says:

    Hi Troy, I really appreciate your resource, especially the FCI, but I’m having some problems running ‘FailOverClusterInstanceFirstNode_example.ps1’. I will continue to dive into this, but if you had time to point me in the right direction that would be great. Hopefully it is ok to post all this on your blog.
    Here’s the Powershell errors I am seeing:


    VERBOSE: [SERVER01]: [[xClusterDisk]Data::[FailOverClusterDisk]SetupDisks::[FailOverClusterInstanceFirstNode]Firs
    tNode] Set the disk label for the disk 1 to ‘Data’.
    VERBOSE: [SERVER01]: [[xClusterDisk]Data::[FailOverClusterDisk]SetupDisks::[FailOverClusterInstanceFirstNode]Firs
    tNode] Perform operation ‘Query CimInstances’ with following parameters, ”queryExpression’ = SELECT * FROM MSCluster_Disk WHERE Number = 1,’queryD
    ialect’ = WQL,’namespaceName’ = Root\MSCluster’.
    VERBOSE: [SERVER01]: [[xClusterDisk]Data::[FailOverClusterDisk]SetupDisks::[FailOverClusterInstanceFirstNode]Firs
    tNode] Operation ‘Query CimInstances’ complete.
    VERBOSE: [SERVER01]: [[xClusterDisk]Data::[FailOverClusterDisk]SetupDisks::[FailOverClusterInstanceFirstNode]Firs
    tNode] Connecting to cluster on local computer SERVER01.
    The property ‘Name’ cannot be found on this object. Verify that the property exists and can be set.
    + CategoryInfo : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : PropertyNotFound
    + PSComputerName : Server01

    You cannot call a method on a null-valued expression.
    + CategoryInfo : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : InvokeMethodOnNull
    + PSComputerName : Server01

    VERBOSE: [SERVER01]: LCM: [ End Set ] [[xClusterDisk]Data::[FailOverClusterDisk]SetupDisks::[FailOverClusterInstanceFirstNode]Firs
    tNode] in 0.7960 seconds.
    The PowerShell DSC resource ‘[xClusterDisk]Data::[FailOverClusterDisk]SetupDisks::[FailOverClusterInstanceFirstNode]FirstNode’ with SourceInfo
    ‘C:\Program
    Files\WindowsPowerShell\Modules\SQLCompositeResources\DSCResources\FailOverClusterDisk\FailOverClusterDisk.schema.psm1::13::9::xClusterDisk’
    threw one or more non-terminating errors while running the Set-TargetResource functionality. These errors are logged to the ETW channel called
    Microsoft-Windows-DSC/Operational. Refer to this channel for more details.
    + CategoryInfo : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : NonTerminatingErrorFromProvider
    + PSComputerName : Server01

    1. Steve says:

      This had failed for me because my VM’s VMWare SCSI drive did not have VPD ID type 3, and was not a problem with any of the DSC resources here. (Get-Disk | FT FriendlyName, UniqueIdFormat -AutoSize)
      Resource:
      https://social.technet.microsoft.com/Forums/ie/en-US/cf865701-674d-45b1-bdd0-e982a17114f9/cluster-validation-test-says-physical-disk-does-not-have-the-inquiry-data-scsi-page-83h-vpd?forum=winserverClustering

      1. Troy Ault says:

        Steve sorry for the delay do you have everything working now or do you still have a road block?

Skip to main content