Building a SQL Server Inventory – Part 2

Last time I went through my script to gather server information for my inventory. Today I’m going to go through my script to collect SQL Server instance data. So, I think I made it clear last time that I’m not a Powershell guru, and there are probably countless ways in which this script can be improved, yada yada. ‘Nuff said.

Credits

Like the server info script, this script is based heavily on Allen White’s inventory script on Simple-Talk. If you haven’t already seen it, I highly recommend checking out the entire article. I’ve made some changes to Allen’s base script, to suit my own needs. Whereas Allen writes his output to a csv file, I wanted to load directly into my inventory database. Allen also reads in his list of servers to inventory from a file, I keep mine in a master table, also in my inventory database.

Tools you’ll need

In order to load your inventory data directly into SQL Server, you’ll need to download a couple of PowerShell functions.

Permissions required

You’ll need a SQL Server login on each instance you plan to inventory. The permissions for that login will depend on what data you’re collecting. You can use the principle of least permissions and only grant the minimum required, which is what I do. In fact I use certificate-signed procedures to gather any info that requires sysadmin-level rights. This method requires creating procedures on all target servers, which was a larger footprint than I would have preferred, but I felt it was a better option from a security standpoint. Or you can simply use a sysadmin login and be done with it. Potayto potahto.

The moment you’ve all been waiting for – The script

Parameters

My script accepts 4 parameters:

Inventory database instance

Inventory login

Inventory password (I use the same login to collect my info and store it)

Environment (Which environment do I want to inventory?)

A word of explanation about the Environment parameter. As I mentioned earlier, I store my master instance list in a table inside my inventory database. That table holds all instances across all of my environments, Production, Development, etc., and the environment for each instance is also stored in this master table. When I run this script I use this parameter to specify which environment I want to inventory.

Clean out tables pre-run

This script performs a full load every time, so my first step is simply to clear out any existing data.

$connection = new-object system.data.sqlclient.sqlconnection( `
"Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()
##### Clean out all tables pre-run. Move Jobs to Jobs_Compare, that way we can see what jobs were dropped #####
$query = "select instance_name_short into #cmm from Servers where Environment in ('$ssi_env')
DELETE FROM [SQL].Login_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Database_Users where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Database_Files where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Database_Roles where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Server_Roles where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Credentials where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Linked_Server_Logins where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Linked_Servers where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Job_Schedules where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Schedule_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Job_Info_Compare where instance_name in (select instance_name_short from #cmm);
INSERT INTO [SQL].Job_Info_Compare SELECT * FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Job_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Job_Proxies where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Proxies where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Backup_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Database_Info where instance_name in (select instance_name_short from #cmm);
DELETE FROM [SQL].Instance_Info where instance_name in (select instance_name_short from #cmm);
"
$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()

Get list of servers to inventory

The next step in the process is to read the master table to get the list of servers to inventory. Then we’ll step through them and for each one we’ll check and make sure it’s online and if it is, we’ll execute the get-sqlinfo function.

Get-sqlinfo Function

The last part of the script is to actually gather the information.

First up on the list: Instance info. As you’ll soon see, most of my script uses the same basic format, over and over. The instance info section is the exception. Here I’m making use of the Server SMO object class to get additional instance-level information, like the version, edition, collation, whether it’s clustered, and database/log paths. I could have used T-SQL to get all of this info, like the rest of the script, but this way seemed easier and it let me add another tool to the arsenal. I then combine this information with some additional fields I’ve obtained using T-SQL. To load the data into our Inventory database, we use the same Write-DataTable and Out-Datatable functions we used in the server inventory script.

For login information, I’m simply defining a connection string and a query string that gets me the login data I want. I create a SQLDataAdapter object ($da), use it to fill a DataTable object ($dt), and load it up into my Inventory table.

For the remainder of the tables, I use the same basic format. The $ssi_table variable specifies my target table, and the $query variable holds the T-SQL query to execute. The rest is all the same, over and over. Could I have simplified it more by creating a function to handle the loading process and simply passed in the $ssi_table and $query? Absolutely. In fact, I’ll leave that for you to do. Call it homework. (And this is why I will probably *not* be the next Ola.) Here’s an example.

One last thing

The last thing this script is execute a stored procedure in my inventory database called usp_StoreDBGrowth. All this procedure does is archive the newly inserted datafile information into a separate table for historical growth analysis.

The complete script

So here’s the complete script. Give it a whirl and let me know what you think. If you come up with some improvements, I’d love to hear them, too.

Colleen,
Could you give me an idea of how to set this up and configure it? I have all the code, the db and tables created…just trying to get the powershell script to work. I would love to play around with this idea and see if it would be useful for me. I tried running the “complete script” passing in my own parameters but failed. Any help would be nice.

Hi Ian – So you’re using windows authentication and all of them are coming back as being online but only one is actually running? Can you post the validation portion of the code here? I’m assuming you altered it for the windows auth.

Many thanks for your quick response, my script is an extermely cut down type compared to your own, but as i am learning powershell on the go think may pf made a silly mistake. All my script wants to do is use an instance list, i want to attempt to connect and if online carry on, if not online flag up (we have 100+ instances) – my problem is everything is coming up as being online (even a fake entry in my instance list)so data loads in from valid instances but attempts the fake entry also..

Good news for you: you weren’t doing anything wrong. Bad news for me: I was. My use of the isOpen function to test for the existence of the instance wasn’t working as I expected. I don’t know why this never came up before, I know I’ve run this against servers that were offline. Interesting. Anyway, I’ve come up with a fix, which is basically checking for a process id, rather than using isopen. Give this a whirl and let me know if it works for you. If everything looks good I’ll be sure to update the original post and the version in my toolbox. Thanks for the heads up!

Colleen, you are a star, thanks it works now as you expected! This is a real milestone for what i want to use this process for and really appreciate your help..

for my own info (and what it looks like when i have ran it) when the script finishes its run it closes the connection to each sql instance itself? – is there a need for any $connection.close anywhere or that is taken care of at the end of run of the function to the online instance?

Last but not least! for instances online i am running the function to obtain the info from the instances, as i aim to run this powershell script from one central server via an agent job would it be easy to write the information for those not online back into my $ssi_instance db?

Hello Colleen, having trouble locating the stored procedures required for the SQL inventory scripts.
sp_get_database_roles, usp_StoreDBGrowth and sp_get_database_users. would you happen to have those handy?

Is there something missing within the inner query around line 405? The where statement reads “AND backupset_inner.backup_start_date” then a bunch of white space before picking backup up again with “AND backupset_inner.is_copy_only = 0 )”.