This will be the first part of a long series defining my custom SQL Server inventory system. I started my inventory as an exercise to learn my new environment after starting a new job, but it has grown to be very useful for reporting, using as a source for PowerShell to target all my instances, and for basic monitoring. There are many components to it, so I figured breaking them down piece by piece would be easier to digest. In this part, I will go through the script I use to pull information about the instance.
In my ever expanding mission to document and inventory my SQL environment, I wanted a way to get human-readable schedule information about my jobs. While initially this seemed like it would be a fairly trivial exercise, it turned out to be a little more involved. Its possible there is an easier way to do this, and if you know of one let me know. I will go through the interesting sections as I’ve done in the past, with a link to the full script at the end.
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.
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.
Perhaps the most fundamental DBA task is to configure proper SQL maintenance on all of your instances. There are many ways to approach this, including SSIS-based GUI-driven maintenance plans, custom homebrewed T-SQL scripts, PowerShell scheduled jobs, or third-party applications. However, my (and many other’s) preferred solution is to use Ola Hallengren’s fantastic maintenance scripts (ola.hallengren.com). This solution is recommended by many people for good reason. It provides a good amount of flexibility, compatibility, as well as a solid amount of logging. You can use this solution right out of the box and it will work great, but I like to tweak it a bit to better fit my needs and be a little easier to apply.
One of the first things I check when I get to the office in the morning is any SQL agent jobs that failed overnight. Our monitoring system will also pick these up, but I like to have a condensed report with the failed production jobs, job step(s) that failed, and error message so that I can quickly diagnose what went wrong. We already had a script in place which mostly worked, based on this, but I wanted to modify it a bit to return some more useful information.
I was recently tasked with coming up with a stored procedure that would accept an Active Directory username, and return basically an organizational tree about that user. This includes peers (other employees with the same manager), direct manager, and the rest of the management tree up to the CEO-level.