Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
124 lines (87 sloc) 6.66 KB

InventoryManager

Simple Database Inventory Manager (SDIM)

What is SDIM?

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.

This is an attempt to do this and make it portable from one system to another.

Requirements

Repository Server - SQL Server 2012 or better. PowerShell 3 or better installed.
Clients - Powershell 4 or better.
CMS Server (Optional) - SQL Server 2012 or better. PowerShell 3 or better installed.
Active Directory Environment

Note: SDIM will be able to access 2005 - 2016 SQL Server Instances, however, it needs to be installed on 2012 or better. You might be able to get it to work on 2008R2, but that is not a supported setup.

How do I install?

Is not a long process, but it does require certain things in a certain order. This will guide you through setting these up. The current version of this guide is for SDIM 2.3

Step 1: Set up your Repository Server

The repository server is where you are going to stored the data after you have pulled it from your instances. It can be the same server as your CMS or something else entirely.

On your Repository Server, run the following script:

DBAdmin.sql

Step 2: Build schemas and tables

On your Repository Server in the DBAdmin database (or whatever you chose to name the DB) run the following scripts in this order:

Utility.Schema.sql
Reports.Schema.sql

dbo.ServerList.Table.sql
dbo.InstanceList.Table.sql
dbo.DatabaseList.Table.sql
dbo.ServiceList.Table.sql
dbo.JobList.Table.sql

Step 3: Create Views

On your Repository Server in the DBAdmin database (or whatever you chose to name the DB) run the following scripts in this order:

Reports.vwGetInstancesGroup_SQLVer.View.sql
Reports.vwGetServers.View.sql
Reports.vwGetServers_Instances_SQLVersion_Instance_FullList.View.sql

Step 4: Create Stored Prcedures

On your Repository Server in the DBAdmin database (or whatever you chose to name the DB) run the following scripts in this order:

dbo.prGetConnectionInformation.StoredProcedure.sql
dbo.prGetDatabasesAndSize.StoredProcedure.sql
dbo.prGetInstances.StoredProcedure.sql
dbo.prGetInstancesAndDatabases.StoredProcedure.sql
dbo.prGetInstancesGroup_SQLVer.StoredProcedure.sql
dbo.prGetInstancesGroup_SQLVer_SQLEd.StoredProcedure.sql
dbo.prGetInstancesGroup_SQLVer_SQLEd_SQLSP.StoredProcedure.sql
dbo.prGetInventory.StoredProcedure.sql
dbo.prGetJobs.StoredProcedure.sql
dbo.prGetJobsExt.StoredProcedure.sql
dbo.prGetServerNames.StoredProcedure.sql
dbo.prGetServers.StoredProcedure.sql
dbo.prGetServersAndInstances.StoredProcedure.sql
dbo.prGetServerServices.StoredProcedure.sql
dbo.prGetServersGroup_OS_SP.StoredProcedure.sql
dbo.prInsertDatabaseList.StoredProcedure.sql
dbo.prInsertJobList.StoredProcedure.sql
dbo.prInsertNewServerAndInstanceCMS.StoredProcedure.sql
dbo.prInsertServiceList.StoredProcedure.sql
dbo.prUpdateInstanceList.StoredProcedure.sql
dbo.prUpdateServerList.StoredProcedure.sql
Utility.prInsertNewServerAndInstance.StoredProcedure.sql

Step 5: Set up the DataPull

Put DB_DataPull.ps1 on your repository server, somewhere easily accessible. You'll want to pass the parameters into this to pull your data. I would recommend setting up a windows task to run this by passing in the necessary parameters. You could also create a batch file that calls it and add parameters in there, your call.

There are defaults for all of these already in the script, the only one you HAVE to change is the CMS Server location. If you're not providing a CMS server just ignore this parameter (you will need to provide a list of servers and instances manually using Utility.prInsertNewServerAndInstance).

DB_DataPull.ps1 Parameters
-RepositoryInstance "SomeInstance"
The location of the repository instance relative to the server running the script. Assumed to be the same server "(local)" as this is probably the best practice
-RepositoryDB "SomeDB"
The location of the Repository DB relative to the instance. If you set it up like above, then that should be DBAdmin.
-CMSServer "SomeServer"
The location of the CMS Server. You can pass in an instance name as well "SOMESERVER\SOMEINSTANCE"
-LogDir "C:\SomeDir"
Where to place the log. Log file names will be generated automatically.
-UseCMS
Toggle on if you're using a CMS Server. If not, it will pull from the ServerList\InstanceList and not truncate the tables.
-Verbose
Gives you lots of feedback.
-Debug
Even more feedback.

Step 6: Set up the Clients

The clients can run these through powershell, or you can create a batch file that passes the parameters in and then create shortcuts and whatnot to the batch (I like to pass PowerShell the -WindowStyle Hidden option). It's fairly simple.

DB_DataPull_FrontEnd.ps1 Parameters
-RepositoryInstance
The location of the repository instance relative to the server running the script. Assumed to be the same server "(local)", but probably won't be.
-RepositoryDB
The location of the Repository DB relative to the instance. If you set it up like above, then that should be DBAdmin.

Further Information

There's quite a bit of documentation available, and decently organized if I say so myself. You can get that here: http://wp.me/p5ee2M-2I

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!

As always, feel free to contact me if you have comments, suggestions or questions.