Getting SQL Server Configuration Values

This is one of those functions born out of not wanting to do a task more than once, which evolved into a more useful/reusable function when I added a little extra functionality.  I originally started writing it for the humble task of finding all instances that have xp_cmdshell enabled, but I evolved it to have the ability to find the configuration values for any and all of the configurations stored in sys.configurations as well as checking whether the values match the defaults.

Here is the function.  I will add it in its entirety to my SQL-Functions module, which you can find a link to at the bottom of this post.

Nothing of interest here, just normal function help contents.  On to parameters:

The parameters are -Instance, which can either be a single instance or an array of them, followed by -Config, which tab completes to all the current available configuration names as well as a wildcard for when all configurations are desired.  There is also a -NonDefault switch, which toggles a filter on the results only returning the configurations which are not set to their default value.

Next up, the begin{}:

A couple of cool things here.  Import-SQLPS is documented here, but it basically just imports the SQLPS module.  The script variable stores the actual T-SQL that I am using to pull the values.  All those inserts are putting the default values into a table variable to be used to determine whether the current setting is default or not.  It would be nice if the default value was just stored somewhere accessible, but alas this is how I worked around it.  I’m using this single line INSERT syntax to maintain compatibility with SQL 2005.  The SELECT statement returns the instance name, configuration name, configuration description, current value, and default value.

The rest of the function is pretty boring.  It just loops through each instance, runs the query, and appends the results to an overall results variable.  Here is the process{}:

For those playing along at home, you probably will want to change the else to have a method that works for your environment to pull all your instances, or get rid of it all together.  I like to code in my function which returns all my instances from my inventory to make things easier on me.  However, if you just have a text file or something with all your instances, that works just as well.  The only cool thing in this is the progress bars.  There is one for overall instance progress, and a sub-bar for the progress within the current instance.  Pretty neat.  Also does some bare-bones error handling.

That brings us to the end{}:

All this does is return the results, filtering out the ones that are set to the default value if the -NonDefault switch is specified.

Probably the easiest way to call this is something like this:  Get-SqlConfigValue -Instance (Get-Content C:\TEMP\instances.txt) -Config % | Export-CSV -NoTypeInformation C:\TEMP\configurations.csv .  This will get you a nice spreadsheet with all your instances configurations.  One thing to note about this is that I embedded the cmdlet instead of piping the results.  You could use the pipeline (something like Get-Content C:\TEMP\Instances.txt | Get-SqlConfigValue -Config % ) but that will break the overall progress bar due to the nature of how the pipeline works.  It’ll still work though, just isn’t as friendly.

Here is the link to my SQL-Functions module: GitHub

Leave a Reply

Your email address will not be published. Required fields are marked *