Inventorying your SQL Servers with Powershell and SMO: Part 1

Part 1: Collecting and Storing Instance Level Information with PowerShell and SQL Server SMO

In the first part of this series we’re going to look at collecting information at the instance level using Powershell and the SQL Server SMO.

I’m going to obtain the information via PowerShell by interacting with the SMO, and push that data in SQL Server to store it.

I have a database called smoInventory which is is going to house the table relating to my SQL Server Inventory.

Step 1: Create a List of Instances in smoInventory

We need to create a table in this database called inst_list, which is going to hold a list of SQL Server instance names. These are the servers we’re going to collect data from in PowerShell.

CREATE TABLE dbo.inst_list(
 Name varchar(500) NULL
)

Once populated with Server/Instance references, our inst_list table should look a little like this. I’ll discuss how we use this in more detail later.

instances
inst_list table

Step 2: Exploring the SQL Server SMO

Before we do any real work, it’s worth looking at the SMO properties in more detail.

Open up the PowerShell ISE by going to Start > Run and typing Powershell ISE.

First, We’ll bring in the SQL Server SMO as a new object:

$smo = new-object ('Microsoft.SqlServer.Management.Smo.Server')

Then we’ll pipe the $smo variable into get-member to see what it contains:

$smo | get-member

A long list of members contained in the SMO will be returned:

smo-get-member

Notice that the list contains three different MemberType’s. Events, Methods and Properties. Properties are what we’re going to be looking at today, as those are the bits of information we want in relation to our instances, so lets filter the other stuff out by piping it into Where-Object:

$smo | Get-Member | Where-Object {$_.MemberType -eq 'Property'}

That’s a much smaller list, but it’s still not right. Contained within the list now we have some Properties which are other collections, so they are a level down from the current level. Think of it as subfolder. The Property Databases is a Collection. The Collection is like a sub-folder which contains other Properties. Later on this series we’ll explore these Collections (or sub-folders!) but right now we don’t need them so we’ll filter them out.

$smo | Get-Member | Where-Object {($_.MemberType -eq 'Property') -and ($_.Definition -notlike '*collection*')}

So now we have a list of properties relating to a Database Instance, pieces of information, if you like, that we can use to begin building our SQL Server Inventory.

Step 3: Getting the Information

In order to get the information for every SQL Server instance in our inst_list table, we need to do a few things:

  1. Populate a variable with an array of our instance names
  2. Iterate through that list
  3. Create and Set a variable for each of the properties we want to obtain

In PowerShell ISE, we’ll start with the following:

$instances = @(Invoke-Sqlcmd -serverinstance sqlbuffetserver\instance1 -database smoInventory -query "select name from inst_list") | select-object -expand name

At this point we can read the variable to see if it correctly contains an array of instance names from inst_list.

For each instance in the instances variable we need to use SMO to generate a list of variables relating to the properties we want to obtain.

For the purpose of this demo, I’m going to use the following instance-level properties:

  • Name. The full instance name expressed as \
  • Status. The status of the instance.
  • ComputerPhysicalNetBIOS. The NetBIOS name of the instance expressed as \.
  • NetName. The general network name of the instance expressed as \.
  • InstanceName. The SQL Server instance name.
  • Collation. The instance-level collation.
  • IsSingleUser. Whether the instance is currently in SingleUser mode or not.
  • LoginMode. The authentication mode of the instance.
  • HadrManagerStatus. Used to determine if any Availability Groups are running on the instance.
  • DefaultFile, DefaultLog and ErrorLogPath. Simply the directories set as the instance defaults.
  • VersionString. String based version number of the SQL Server build.
  • Edition. The edition of the instance, as in Enterprise, Standard, Express etc.
  • Product. The product name.
  • ProductLevel. The service pack level of the instance.
  • Count of Databases on the instance using the Databases collection.

You can use less than this, but I think this is a pretty good overview of an instance for an inventory. You can also, if you wish, include many more by exploring the SMO properties.

In a foreach block, we need to set a variable to each of the properties we want to obtain:

foreach ($instance in $instances)
{

$svr = new-object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$name = $svr.Name
$status = $svr.status
$bios = $svr.ComputerNamePhysicalNetBIOS
$netname = $svr.NetName
$collation = $svr.Collation
$instname = $svr.InstanceName
$singuser = $svr.IsSingleUser
$loginmode = $svr.LoginMode
$ag = $svr.HadrManagerStatus
$defaultfile = $svr.DefaultFile
$defaultlog = $svr.DefaultLog
$errorlogpath = $svr.ErrorLogPath
$ver = $svr.VersionString
$edition = $svr.Edition
$prod = $svr.Product
$prodlev = $svr.ProductLevel
$dbcount = ($svr.Databases | Select Name).Count
}

Essentially, this translates as, for each instance listed in the instances variable array, obtain the following pieces of data from the SQL Server SMO.

If you run this as is, you won’t get anything returned. We need to add an extra part within the foreach block to get the data into our Inventory.

Step 4: Populate SQL Server Inventory Database with Instance Information

We have seventeen pieces of information coming from the SMO and we therefore have seventeen variables.

To get this into SQL Server (in my case into the smoInventory database) we need to create a table to store it. I’ll call it smoInstances, and it can be defined as follows:

CREATE TABLE dbo.smoInstances (
	FullInstanceName varchar(500) NULL
	,InstanceStatus varchar(50) NULL
	,NetBiosName varchar(500) NULL
	,NetName varchar(500) NULL
	,InstanceName varchar(500) NULL
	,Collation varchar(500) NULL
	,SingleUser varchar(5) NULL
	,AuthMode varchar(25) NULL
	,AlwaysOn varchar(25) NULL
	,DefaultFileLoc varchar(500) NULL
	,DefaultLogLoc varchar(500) NULL
	,ErrorLogLoc varchar(500) NULL
	,Version varchar(500) NULL
	,Edition varchar(500) NULL
	,Product varchar(500) NULL
	,ProductLevel varchar(500) NULL
	,Databases int NULL
	)

Once the table exists, we can go back to PowerShell and add an Invoke-Sqlcmd operation to the foreach block.

Invoke-Sqlcmd -serverinstance sqlbuffetserver\instance1 -database smoInventory -query "INSERT INTO smoInstances VALUES`
('$name','$status','$bios','$netname','$instname','$collation','$singuser','$loginmode',CASE WHEN '$ag' = 'running'`
THEN 'Running' ELSE 'Not Running' END,'$defaultfile','$defaultlog','$errorlogpath','$ver' ,'$edition','$prod','$prodlev'`
,CAST('$dbcount' AS int))"

So now we have an entire PowerShell script that looks like this:

$instances = @(Invoke-Sqlcmd -serverinstance sqlbuffetserver\instance1 -database smoInventory -query "select name from inst_list") | select-object -expand name

foreach ($instance in $instances)
{

$svr = new-object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$name = $svr.Name
$status = $svr.status
$bios = $svr.ComputerNamePhysicalNetBIOS
$netname = $svr.NetName
$collation = $svr.Collation
$instname = $svr.InstanceName
$singuser = $svr.IsSingleUser
$loginmode = $svr.LoginMode
$ag = $svr.HadrManagerStatus
$defaultfile = $svr.DefaultFile
$defaultlog = $svr.DefaultLog
$errorlogpath = $svr.ErrorLogPath
$ver = $svr.VersionString
$edition = $svr.Edition
$prod = $svr.Product
$prodlev = $svr.ProductLevel
$dbcount = ($svr.Databases | Select Name).Count

Invoke-Sqlcmd -serverinstance sqlbuffetserver\instance1 -database smoInventory -query "INSERT INTO smoInstances VALUES`
('$name','$status','$bios','$netname','$instname','$collation','$singuser','$loginmode',CASE WHEN '$ag' = 'running'`
THEN 'Running' ELSE 'Not Running' END,'$defaultfile','$defaultlog','$errorlogpath','$ver' ,'$edition','$prod','$prodlev'`
,CAST('$dbcount' AS int))"
}

Running that script now will (hopefully) populate our table, and it shouldn’t take long to do so. For reference, I have 41 instances in my inst_list table and the script takes 3 seconds to run.

Check the smoInstances table…

smoinstances

…just to confirm we’re on our way to building an awesome SQL Server Inventory!

In the next part we’ll look at exploring second-level collections in the SMO and also at scheduling these scripts.

Advertisements

One thought on “Inventorying your SQL Servers with Powershell and SMO: Part 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s