Creating a Simple Database Inventory Manager with Powershell – Part II: Stored Procedures

Published by

cjjulius

on

24/10/2023

Now that we’ve got the tables built and populated them with data (you did that right?) we can define how our Powershell script is going to access that data. If you don’t know what database or tables I’m talking about, check out the Introduction and Part I.

You can send the dynamic SQL to your database via Powershell but you really want all interactions to go through stored procedures. Why? Lots of reasons, but here’s 2:

Standardization – Every time you call a stored procedure the code will be exactly the same every time. If you change a hard-coded query in one part of your script, but not another, you can get inconsistent results.
Performance – Unless you’re accessing thousands of instances with this script then you may not care too much about performance, but SQL Server has a hard time building query plans off of dynamic SQL; it does so wonderfully when the code is in a compiled stored procedure.

So, that’s all well and good, but what do we need for our Powershell project to do? I’ve created the procedures that will allow you to do the basic tasks that you might be interested in. The sky is the limit if you want to do more.

The Procedure List

prGetConnectionInformation

[prGetConnectionInformation] – Setup script for 2012

Parameters:

Not a gosh-darn thing.

Returns:

Server\Instance string as ‘Connection’
ServerList.Id as ‘Server ID’
InstanceList.Id as ‘Instance ID’

Spits out a connection-string friendly version of all the servers and instances in the database.

prGetDatabasesAndSize

[prGetDatabasesAndSize] – Setup script for 2012

Parameters:

Not a gosh-darn thing.

Returns:

InstanceList.InstanceName
DatabaseList.DatabaseName
Size of the Database in Gigabytes as ‘SizeinGB’

Returns the database and the instance it belongs to along with the sizes of said DBs in Gigabytes for all databases in the database (Yo dawg, heard you like databases).

prGetInstances

[prGetInstances] – Setup script for 2012

Parameters:

Not a gosh-darn thing.

Returns:

InstanceList.InstanceName
InstanceList.MSSQLVersion
InstanceList.MSSQLEdition
InstanceList.MSSQLVersionLong

Basically returns everything in the dbo.InstanceList table that someone might be interested in.

prGetInventory

[prGetInventory] – Setup script for 2012

Parameters:

Not a gosh-darn thing.

Returns:

ServerList.ServerName
ServerList.IPAddress
ServerList.OSName
ServerList.OSServicePack
InstanceList.InstanceName
InstanceList.MSSQLVersion
InstanceList.MSSQLEdition
InstanceList.MSSQLVersionLong
Size of the Database in Gigabytes as ‘SizeinGB’

Leaves for a while and then comes back with everything in your Inventory. Does not bring back anything from dbo.ServiceList for visual reasons (we’ll see this later in the Powershell).

prGetServerNames

[prGetServerNames] – Setup script for 2012

Parameters:

Not a gosh-darn thing.

Returns:

ServerList.ServerName

Just brings back all the servers. Nothing else. Stop asking.

prGetServers

[prGetServers] – Setup script for 2012

Parameters:

Not a gosh-darn thing.

Returns:

ServerList.ServerName
ServerList.OSName
ServerList.OSServicePack

Returns all the information at the Server Level excluding IP Address.

prGetServerServices

[prGetServerServices] – Setup script for 2012

Parameters:

Not a gosh-darn thing.

Returns:

ServerList.ServerName
ServiceList.ServiceName
ServiceList.ServiceDisplayName
ServiceList.ServiceStartMode
ServiceList.ServiceStartName as ‘Service_Logon’

Spits out everything you and your friends ever wanted to know about Services in the dbo.ServiceList table and the name of the Server they are on.

prInsertDatabaseList

[prInsertDatabaseList] – Setup script for 2012

Parameters:

DatabaseName VarChar(MAX) (surprise! parameters!)
InstanceListId BigInt
Size Float

Returns:

Error Code if applicable

Inserts new Database entries as provided by the Powershell script.

prInsertServiceList

[prInsertServiceList] – Setup script for 2012

Parameters:

ServerName VarChar(MAX)
ServiceDisplayName VarChar(MAX)
ServiceName VarChar(MAX)
ServiceState VarChar(MAX)
ServiceStartMode VarChar(MAX)
ServiceStartName VarChar(MAX)

Returns:

Error Code if applicable

Wow. Look at all the VarChar(MAX)’s. These could probably be turned into other datatypes that are smaller, especially ServiceStartMode. In the interest of brevity I just made these as versatile as I could. This may change in the future if I get a chance to tighten this up.

Oh, and this stored procedure inserts new services into the dbo.ServiceList table by finding the ServerName in dbo.ServerList via text search.

prUpdateInstanceList

[prUpdateInstanceList] – Setup script for 2012

Parameters:

MSSQLVersionLong VarChar(MAX)
MSSQLVersion VarChar(MAX)
MSSQLEdition VarChar(MAX)
MSSQLServicePack VarChar(20)
InstanceId BigInt

Returns:

Error Code if applicable

Updates dbo.InstanceList with values passed from the script. If things have changed, this will overwrite the old data in the column.

prUpdateServerList

[prUpdateServerList] – Setup script for 2012

Parameters:

IPAddress VarChar(20)
OSName VarChar(100)
OSServicePack VarChar(100)
ServerName VarChar(MAX)

Returns:

Error Code if applicable

Adds Server information to dbo.ServerList by matching on the ServerName. This too will overwrite old data with new values passed to it.

Utility.prInsertNewServerAndInstance

[prInsertNewServerAndInstance] – Setup script for 2012

Parameters:

ServerName VarChar(MAX)
InstanceName VarChar(MAX)

Returns:

Error Code if applicable

Utility that Creates New Server and Instance if they don’t exist already.

I Was Told There’d Be PowerShell

And that’s the end of our Database setup. We now have a DB somewhere with tables and procedures, ready to be populated with our Server\Instance.Database information.

With the frame now in place, seats and tires (nice choice of body color maybe), we are prepared to drop the engine in. We’ll get started on that in our next section.

–Charlton Julius