Creating A Simple Database Inventory Manager with Powershell – Introduction

Which databases names have the letter “B”?

And then we shall rule the world!*

All DBAs should keep track of their Servers/ Instances/ etc not only for their own edification, but for Management and security reasons as well. If you’re not, then you need to, as it comes in incredibly handy even if it isn’t a requirement of the job.

Most of the time, this information is compiled into a spreadsheet of some kind or possibly in a word processing document somewhere. Keeping this data up-to-date and accurate is a pain, especially when you have to break it out into multiple tabs and/or over multiple documents.

You could get a full-blown inventory manager that collects and compiles all the data and organizes it for you. But there’s a definite cost to that solution and not one that all companies will find useful (Read: “It’s not in the budget this quarter”).

What if you can’t get someone to shell out the money for a product like that? Then you have to either keep with the spreadsheets (yuck) or you need to find another solution with the tools you have.

What’s the Catch?

A simple front-end for your Database Inventory

So, this is my attempt to resolve this issue using two tools that any MSSQL DBA should have: Powershell and SQL Server. I will point to other software products or versions both paid and free below, but the core code should run using things you should already have. That said, here’s my software list:

Required:

• Powershell 3.x
• Powershell ISE
• SQL Server 2008 Express

Preferred:

• Powershell 4.x
• PowerGUI 3.8.x
• SQL Server 2012 or better
• Visual Studio 2013

Also I will be making a few assumptions:

1. Your infrastructure security is set up using Active Directory.
2. Setting up a new instance is already done and you can connect to it.
3. Your personal login or the login you are using to execute the code is able to query the relevant system tables and server info on each of the target systems.
4. You know your current Server\Instance setup.
5. There is no fifth thing.

What You Get

By the time this series is finished you’ll have a simple GUI front-end (shown above) for current data with all of your servers, instances and all the information you could want about them.

We can pull back and organize any data that SQL Server or Windows Server can spit out including, but not limited to:

• OS Version, Service Pack.
• SQL Services running, their statuses and logon information.
• SQL Server Instance names, versions, and editions.
• SQL Server Database names and sizes.
• Ability to dynamically re-pull any of this information if needed.

I will walk you through my solution piece by piece over 5 posts (6 including this intro) that will consist of the following parts. This list will be updated with links to the different sections as they are released. Keep in mind that this is a “How I did it”. The readme included with the code should be used if you intend to put this in your environment.

Part I: Building the Repository Database and Tables
Part II: Creating the Repository Stored Procedures
Part III: Coding the Data-Pulling Powershell
Part IV: Putting together the GUI Front-end
Part V: The Future

See you soon in Part I: Building the Repository Database and Tables

–Charlton Julius

*world-ruling not guaranteed or endorsed