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.
This is one of those somewhat simple functions that I end up using quite a bit, similarly to the Test-ADUser one from my last post. I find it particularly useful for parameter validation in my other SQL-related functions. It is nice to be able to offload the connectivity tests right into the parameter declaration in the function without having to clutter up the main function.
I have been slowly moving my collection of scripts into more modular functions to make my code more re-usable, and so far it seems to be working out well. It is much easier to update one function in a module, then to try and track down every location I used that functionality and update it as well.
I have recently been working on automating our new hire process with PowerShell (at least the Exchange/AD aspects). I found myself needing a simple function that would return true or false based on whether a specified username is in use or not. I needed this for validating username parameters in my other functions, as well for my function that would generate a valid username from a first, middle, and last name that wasn’t already in use.
Most of the SQL instances at my current company were set up before me, and don’t really follow any standards for assigning the maximum server memory configuration for SQL. Since a couple of my instances were starting to starve the OS, I figured I would write a small PowerShell function to calculate what the max should be set to for a server running nothing but SQL on it. There are lots of good resources out there about how to calculate this, but I decided to use the recommended formula found here by Jonathan Kehayias. I also took some inspiration from the logic Chrissy LeMaire applies in her SMO recipe here, although I ended up modifying it quite a bit.