Powershell – Extract Versions for SQL Features


Over the last few months I’ve been directed to ensure that all of our SQL Servers in the Development and QA segments have been installed with the proper licensing, namely Developer Edition.    One of the challenges I’ve ran across is how do I verify the SSRS and SSAS features are on the right versions. For SQL Server I can simply perform a multi-server query from our CMS and easily retrieve this information using the various SERVERPROPERTY options, but I’m not sure how to do this for the ancillary services.

Since I have limited experience with those two features of SQL Server I decided to go with Powershell, since it can simply do anything you ask of it.

In this example, I am going to use Powershell to extract these values directly from the registry of the host computer.

Running this against a list of servers defined in the top line will result in this output:

Now the beauty of Powershell is that I could source my list from a list in a text file:

Or directly from SQL:

Or from Active Directory (I know there is an inline way to filter this down, but I’m not sure the syntax right off.

In our case, scanning the Active Directory allows us to check the entire AD Server group to ensure we are not missing any rogue SSRS or SSAS servers that haven’t been added to our CMS lists which could throw us into a licensing surprise when Microsoft walks in to do their audit.

Hopefully this will help you.