A year ago I wrote about installing MDS prerequisites using a PowerShell script but things have changed a little in SQL Server 2016 MDS and with ever more untested and ambiguous Microsoft documentation.

Some of the terminology does not exist as described on Windows Server 2012 R2. Here are what is stated vs the component I think it is referring to.

  • Internet Information Services = Web Server (IIS) = Web-Server
  • World Wide Web Services = Web Server = Web-WebServer
  • .NET Framework 4.5 Advanced Services = .NET Framework 4.5 Features = NET-Framework-45-Features

But it also mentions "Dynamic Content Compression is enabled by default". After some experimentation, it may have been enabled for use within MDS by default, but you still need to install the feature in order for it to function and it is not installed by any of the other roles or features or otherwise listed on the page.

The documentation also includes a "sample PowerShell script to add prerequisite server roles and features".

Install-WindowsFeature Web-Mgmt-Console, AS-NET-Framework, Web-Asp-Net, Web-Asp-Net45, Web-Default-Doc, Web-Dir-Browsing, Web-Http-Errors, Web-Static-Content, Web-Http-Logging, Web-Request-Monitor, Web-Stat-Compression, Web-Filtering, Web-Windows-Auth, NET-Framework-Core, WAS-Process-Model, WAS-NET-Environment, WAS-Config-APIs  
  
Install-WindowsFeature Web-App-Dev, NET-Framework-45-Features -IncludeAllSubFeature –Restart

Calling it a "sample" worried me. In what way is it a sample? Is it complete? Does that list even correlate to the list of features correctly? I intended to find out, and I started by running it on a clean Windows Server 2012 R2 + SQL Server 2016 (Database Engine + MDS).

Preparing a component list for comparison

To generate my own list of features based on the documentation I did some of the text processing in PowerShell.

  • Getting a list of possible components.

    Part of the confusion with MDS prerequisities is that it's split into two sections of Roles and Features and some of them have the same names. The default output of the Get-WindowsFeature cmdlet doesn't show you this column, and if you include it, this then breaks the indented formatting. So I rolled my own like this:

    Get-WindowsFeature | Select @{n="DisplayName"; e={(" " * (($_.Depth - 1) * 4)) + "[ ] " + $_.DisplayName}}, Name, FeatureType | Format-Table -AutoSize | Clip

  • After this I put it into the PowerShell ISE and started marking items from the Microsoft documentation with an [X].

  • Then I paste it back into a PowerShell variable and use RegEx to bring out the middle column, and then join it all together into a comma-separated list.

    $data = "..."
(($data -split [Environment]::NewLine | Where { $_ -like "*X] *" } | %{ $_.Trim() -match ".*?  (.*)  .*" | Out-Null; $Matches[1].Trim()}) -join ", " | Clip

  • This all results in a full list of what is required to install based on their documentation. The end result which I installed on a second clean server same as above:

    Install-WindowsFeature Web-Server, Web-WebServer, Web-Common-Http, Web-Default-Doc, Web-Dir-Browsing, Web-Http-Errors, Web-Static-Content, Web-Health, Web-Http-Logging, Web-Request-Monitor, Web-Performance, Web-Stat-Compression, Web-Dyn-Compression, Web-Security, Web-Filtering, Web-Windows-Auth, Web-App-Dev, Web-Net-Ext, Web-Net-Ext45, Web-Asp-Net, Web-Asp-Net45, Web-ISAPI-Ext, Web-ISAPI-Filter, Web-Mgmt-Tools, Web-Mgmt-Console, NET-Framework-Core, NET-Framework-45-Features, NET-Framework-45-ASPNET, NET-WCF-Services45, NET-WCF-HTTP-Activation45, NET-WCF-TCP-PortSharing45, WAS, WAS-Process-Model, WAS-NET-Environment, WAS-Config-APIs -Source D:\sources\sxs

  • The final step was to get the output of each node and compare the list of installed features.

    $a = Get-WindowsFeature | Clip # Node 1
$b = Get-WindowsFeature | Out-String # Node 2
Compare-Object ($a -split [Environment]::NewLine) ($b -split [Environment]::NewLine) | Format-Table -AutoSize

Conclusion

Microsoft's list of MDS prerequisites doesn't match their PowerShell installation script "sample".

  • The sample installs the Application Server component which is not listed in the requirements.
  • The sample installs the Application Server NET 4.5 component even though it's not in the requirements (it lists .NET Framework 4.5 Advanced Services, but a look through other versions of the OS would indicate that this is the plain .NET Framework 4.5 Features category; I suspect someone misread this as "Application Services").
  • The sample installs additional "Application Development" components that are not listed in the requirements.
  • The sample doesn't install the recommended Dynamic Content Compression component.

I feel a little dead inside spending hours investigating this, and frankly I think unnecessarily poor documentation is not cool and wastes all of our time.

Now that we know the discrepancy exists, pick whichever script is your poison, cross your fingers and hope for the best.