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.

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.

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.

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

Next we will look at  FailOverClusterInstanceAdditionalNode_Example.ps1  

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

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.

• Anonymous
  October 26, 2017
  Very useful, thank you. Don't forget the xStorage resource is required also.
  • Anonymous
    October 27, 2017
    Thank you I will update the documentation.
• Anonymous
  October 30, 2017
  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]FirstNode] Set the disk label for the disk 1 to 'Data'.VERBOSE: [SERVER01]: [[xClusterDisk]Data::[FailOverClusterDisk]SetupDisks::[FailOverClusterInstanceFirstNode]FirstNode] Perform operation 'Query CimInstances' with following parameters, ''queryExpression' = SELECT * FROM MSCluster_Disk WHERE Number = 1,'queryDialect' = WQL,'namespaceName' = Root\MSCluster'.VERBOSE: [SERVER01]: [[xClusterDisk]Data::[FailOverClusterDisk]SetupDisks::[FailOverClusterInstanceFirstNode]FirstNode] Operation 'Query CimInstances' complete.VERBOSE: [SERVER01]: [[xClusterDisk]Data::[FailOverClusterDisk]SetupDisks::[FailOverClusterInstanceFirstNode]FirstNode] 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 : Server01You 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]FirstNode] 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 ...
  • Anonymous
    November 03, 2017
    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
    • Anonymous
      November 16, 2017
      Steve sorry for the delay do you have everything working now or do you still have a road block?