Creating a Simple Database Inventory Manager with Powershell – Part III: Data Pull

Powershell time; no really.
DB_inven_Mgmt_PS1_sm_III
I come bearing scripts.

Now it’s time to get to get this thing moving. We’re going to go out to each of our server\instances and pull back the information for our tables, updating them with the stored procedures from the last section.

We’re going to be looking at this script [DB-DataPull.ps1]. It’s about as simple as I could get it for our needs. There’s not a lot of frills, but it’s a good cop and it. gets. results.

If you think you missed something you can go back to Part II: Stored Procedures or check out the Introduction.

Get this Jalopy on the Road

The only thing you need to do is specify where the repository is. If the repository is on your local machine in the DBAdmin database then you need to change nothing.

$RepositoryInstance = '(local)'
$RepositoryDB = 'DBAdmin'

After that you’re done. Seriously. The rest of this post is going to be about the nuts and bolts of the script and what does what and why. If you’re looking to just get it fired up then you’re done. Be gone with you.

What’s in the box?

The first few functions (Get-Type and Out-DataTable) are required to turn multi-line WMI-Object output into DataTables so we can insert them into the Repository. These have been cleaned up and/or modified to fit our needs but are based on the code in the two links I provided.

The Invoke-SQL function is a pared-down version of a pretty popular script for sending dynamic SQL directly to a SQL server. There’s not much to be said about this one other than it opens a connection, sends the command and returns the results as a datatable.

Time to get into the meat of the process. First up, let’s grab all the Instance information using the stored procedure we built in the last post.

$ConnectionString = Invoke-SQL -datasource $RepositoryInstance -database $RepositoryDB -sqlCommand "
EXEC dbo.prGetConnectionInformation;
"

Using a foreach loop to cycle through the rows and thus connecting to each instance to pull the information. We’ll remove the ‘\\MSSQLSERVER’ part since that will actually break our connection, even though it’s the name of the instance (For more information on why this is, see every other Microsoft product ever created).

foreach ($Row in $ConnectionString.Rows)
{
Try
{
$SubConnection = $($Row[0]) -replace '\\MSSQLSERVER',''
$InstanceID = $($Row[2])
Write-Debug $InstanceID
Write-Debug $SubConnection
$Version = Invoke-SQL -datasource $SubConnection -database master -sqlCommand "
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition'), @@VERSION
"
}
...

And then with another loop we use dbo.UpdateInstanceList to push all that into our database.

Invoke-SQL -datasource $RepositoryInstance -database $RepositoryDB -sqlCommand "
EXEC dbo.prUpdateInstanceList
@MSSQLVersionLong = '$MSSQLVersionLong'
,@MSSQLVersion = '$MSSQLVersion'
,@MSSQLEdition = '$MSSQLEdition'
,@MSSQLServicePack = '$MSSQLServicePack'
,@InstanceId = $InstanceID
"

That’s it for the Instance information, let’s get the database information. We use the same process to generate the connections as we did before, so I’m going to skip that. The only change you should note is the inclusion of the statement TRUNCATE TABLE dbo.DatabaseList since we are going to completely repopulate it. This way no matter if databases are added or removed, we’re starting each pull with a clean slate.

We get our data via a cte…

$DataPull = Invoke-SQL -datasource $SubConnection -database master -sqlCommand "
with fs
as
(
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
)
select
$InstanceID AS 'InstanceId',
name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) AS DataFileSizeMB
from sys.databases db
ORDER BY DataFileSizeMB
"

…and push it into the Repository via our stored procedure.

Invoke-SQL -datasource $RepositoryInstance -database $RepositoryDB -sqlCommand "
EXEC dbo.prInsertDatabaseList
@DatabaseName = '$DatabaseName'
,@InstanceListId = '$InstanceListId'
,@Size = $Size"

Lastly, we’ll get Service and Server information with the same rinse-and-repeat method, with one notable exception. If you try to return the results of a WMI-Object and parse it into a SQL table, then you’re going to have a bad time.

This is where our two functions from the beginning come in to play. Out-DataTable and its sidekick Get-Type return the results into the proper type for our foreach loop.

$ServerInfo = Get-WmiObject win32_Service -Computer $Row[0] |
where {$_.DisplayName -match "SQL Server"} |
select SystemName, DisplayName, Name, State, StartMode, StartName | Out-DataTable

Now, if you run EXEC dbo.prGetInventory on your Repository database, you should see all of the information you could ever want right there. Magic.

But Wait, There’s More!

Now we’ve got all the data in one place, which is nice and all, but what if we want to get this information quickly? Sure we can jump into SSMS and run the procedures that have the data we want. However, I propose we make a GUI front-end so we can win friends and get free drinks.

DB_DataPull_FrontEnd_GUI_2
Something like this?

We’ll do that in Part IV: The Voyage Home GUI Front-End.

–CJ Julius

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

Dynamic SQL? Them's fightin' words.
Dynamic SQL? Them’s fightin’ words.

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:

  1. 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.
  2. 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.

–CJ Julius

Creating a Simple Database Inventory Manager with Powershell – Part I: Building the Repository Database

I've got it. We'll put the databases in a database
I’ve got it! We’ll put the databases in a database.

This first part is simply setting up the database and the tables underneath of them. I’ve tried to make this as painless as possible by providing scripts do do most of this for you. I’ll use this infrastructure piece to explain some of the data that we’ll be pulling back.

This is by no means an exhaustive list of all the information that can be retrieved, but it serves as a foundation to show how all data could be pulled back to these tables. The only limit is determining how you’re going to retrieve this data.

This part will require some legwork from you as entering the ServerName and InstanceName with corresponding Id fields will be necessary. You should only have to do this once*.

If you’re unclear about what all this is then, and think you missed something, check out the Introduction.

[DBAdmin_QA]

[DBAdmin_QA.sql] – Setup script for 2012 can be retrieved here.

This is the database that will act as the repository for all of the information we want to collect. You can call it whatever you want, as you can change this in the code later, but I don’t recommend it the first time around. Why make it more complicated than it needs to be?

Make sure you take a look at the settings and verify they fit to your environment. It is intentionally small and grows slowly as we most likely will not be making it larger by leaps and bounds.

You will want to also make sure that the user you will be making updates/deletes/etc as, has full access to this database. This is a ‘durh’, but I always have to say it.

[dbo].[ServerList]

[dbo.ServerList.sql] – Setup script for 2012 can be retrieved here.

This is our master list of all Servers that contain SQL instances we want to know about. Initially, you’ll want to insert all the Server Names (fully qualified if necessary for your environment) leaving the other fields blank. I don’t put things in bold unless they’re important.

An example of T-SQL code to insert these items:

INSERT INTO dbo.ServerList
( ServerName )
VALUES
('SOMESERVER'),
('SOMEOTHERSERVER')

Notable Columns:

Id – Identity Column for all Servers
ServerName – Manually entered Server Name
IPAddress – IP Address of Server
OSName – Name of the Operating System
OSServicePack – Number of the Service Pack

[dbo].[InstanceList]

[dbo.InstanceList.sql] – Setup script for 2012 can be retrieved here.

Contains all of the Instances and the proper ServerListId (Foriegn Key to dbo.ServerList.Id) as well as some related information. You’ll need to INSERT all of the Instances and match them up to the ServerList.Id column when you inserted the servers.

At some point in the future I may add to the code where it dynamically builds the InstanceList, but that has not been done. This is because there are some instances that ‘exist’ on servers are shut down or disabled in some way. This allows the Instance to be listed in your Full Inventory even if it can’t be accessed. Wouldn’t be much of an Inventory if we weren’t able to inventory it.

An example of T-SQL code to insert these items:

INSERT INTO dbo.InstanceList
(InstanceName,ServerListId )
VALUES
('NAMEDINSTANCE',1)
,('MSSQLSERVER',2)

If you’re a lazy bum and don’t like matching ServerList.Id’s to InstanceList.ServerListId:

INSERT INTO dbo.InstanceList
( InstanceName
,ServerListId
)
SELECT 'NAMEDINSTANCE',
sl.Id
FROM dbo.ServerList sl
WHERE sl.ServerName = 'SOMESERVER'

UPDATE: I’ve made this even easier with a new stored procedure Utility.prInsertNewServerAndInstance. If this is your first time seeing this then there was no update and this procedure has always been here. Look! Over there! Something distracting!

Notable Columns:

InstanceName – Manually entered name of the Instance
ServerListId  – Manually entered FK to dbo.ServerList.Id
MSSQLVersion – Version of MSSQL running this Instance
MSSQLVersionLong – The long-form version of previous column
MSSQLServicePack – Current Service Pack of the MSSQL engine
MSSQLEdition – Edition of the MSSQL engine
isProduction – Manual bit flag to designate a production instance

[dbo].[ServiceList]

[dbo.ServiceList.sql] – Setup script for 2012 can be retrieved here.

The dbo.ServiceList table contains information on MSSQL services running on the server. That does include the MSSQL Database Engine, but also other items such as Reporting Services or Full Text Services.

dbo.ServiceList is dynamically filled so there is no need to add any information to this table.

Notable Columns:

ServiceDisplayName – The Display Name of the Service
ServiceName – The system name for the service
ServiceState – State of the Service (ie “Running”)
ServiceStartMode – Start Mode of Service (ie “Auto”)
ServiceStartName – User the Service is running as

[dbo].[DatabaseList]

[dbo.DatabaseList.sql] – Setup script for 2012 can be retrieved here.

This table is the dynamically generated list of all the tables in the database. As you’ll see later in the Powershell, anything we can pull back from sys.databases (or anything we can join to it) can be put in this table or a related table.

Notable Columns:

DatabaseName – Name of the database (crazy, right?)
SizeInMB – Size of the database returned in Megabytes

dbo.DatabaseList is dynamically filled so there is no need to add any information to this table.

Database Ready

And that’s it for the tables and database. Keep in mind that this is a basic structure that can be the core of any setup you’d like. Any data you can capture via T-SQL or Powershell can be compiled and put into these tables or better yet, other related tables.

In the next post, I’ll talk about how we’re going to be allowing this information to be put into the database via stored procedures.

 

*Unless you forgot to make backups and accidentally wipe the tables. In this case, you can think about your mistake while you re-enter all the data again.

–CJ Julius