Tag Archives: service pack

Simple Database Inventory Manager 2.1

sdim_ver_2-1_trunc_275x400

A while back, I took on a fairly big project; build an database inventory manager that did the following:

  • Dynamically gathered information on SQL Instances/Databases
  • Dynamically gathered information on the OS underneath
  • Compiled and organized this data in a single repository
  • Provided a client GUI front-end for ease of use.
  • Was built on free and already available tools.

When I initially finished Inventory Manger 1.0 I wrote a 5-part series that took the user through the steps of how I built what I did and how everything worked. This was a good way for me to iron out details and also provide some documentation along the way. As the months have progressed I added updates to the code and altered the posts as necessary.

Then everything went silent as I moved on to other things, but I was constantly going back and adding new features and options. During this interim, I was not able to get the code updated on GitHub, and thus it fell behind. Also, it had morphed into its own beast, moving out of “project” and more into a standard “software” mode. To signify its new direction, its name is now Simple Database Inventory Manager™.

As such, I have added the new 2.1 version to its own GitHub project (previously it existed in the SQL code section there with other unrelated snippets) and will update it accordingly. You can just download the new files and overwrite the old ones to get the new version. Spiffy, right?

db_datapull_frontend_v2-1
Look! 200% more buttons!™

I will keep the old 1.x version in the original repository so it doesn’t break links for the walk-through articles.

All that said, what fabulous prizes are in the new version? Glad you asked!

CMS Integration

Now you can point to your Central Management Server and the Simple Database Inventory Manager™ will pull a list of servers and instances from there. It will then go through all of them recursively, and pull whatever data you want back.

In DB_DataPull.ps1, you can switch this on or off with the -UseCMS switch. If on, then you will need to specify the CMS Server with the -CMSServer ‘SOMESERVER’ parameter.

If you use the -UseCMS option, this will delete all data from the repository tables and repopulate them based on what you have in the CMS. This is backwardly compatible with the old system in that if you don’t use the option (off by default) then it will continue to use the Server and Instance List you provided manually.

Fresh, New Buttons

‘Services’ has been folded into the Inventory heading and Other Info is no more, replaced by the Reporting section (below).

Composite buttons have been created to give better side-by-side information from the GUI. Server\Instance and Instance\DB buttons have been added to do this. I think their names are pretty self-explanatory. Click on them. See what happens.

Reporting

Kind of. A bunch of stock reports were added to the DB_DataPull_FrontEnd.ps1 under the Reporting heading. These rely on Views in the Reporting Schema that build customizable information you want returned.

I’m going to leave this schema (Reporting) pretty much alone for now, so users can create their own views and then use those as datasets if you want for SSRS. You can use SDIM™ as the basis for reports that refresh as often as you want to run DB_DataPull.ps1

Here’s a quick list of the out-of-the-box reports given in the GUI:

Servers Grouped by OS and Service Pack – A count of all Server OS names and versions.

Instances Grouped By SQL Version – A count of all Instances at a specific server version (ignores Service Pack)

Instances Grouped by SQL Version, Edition – A count of all instances by SQL Version and Edition (ignores Service Pack)

Instances Grouped By SQL Version, Edition and SP – A count of Instances based on SQL Server Version, Edition and Service Pack

Bug Fixes/Minor Enhancements

  • Fixed an incorrectly spelled column.
  • Fixed incorrect calculation of Server number.
  • Added extended properties to all tables.
  • Added header at the top of all Views and Procedures

And All the Rest

This (Simple Database Inventory Manager™) is of course provided free of charge, use-at-your-own-risk. There is no warranty either expressed or implied. If SDIM™ burns down your data center, uninstalls all your favorite toolbars and ruins your best pair of dress socks, I’m not at fault. Remember to back up your databases!

And if you’ve skipped over everything just to get to the link, here it is: SDIMS v2.1

-CJ Julius

Creating A Simple Database Inventory Manager with Powershell – Introduction

Which databases names have the letter “B”?

And then we shall rule the world!
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 frontend for your Database Inventory
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:

Preferred:

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 4 posts (5 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.

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

Updates:
2016-11-27
Addendum I: Simple Database Inventory Manager 2.1
2017-05-20
Addendum II: Simple Database Inventory Manager 2.3

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

–CJ Julius