Posted by: Colin Smith
Audit, Baselining, MSSQL, MSSQL Administration, Trending
I have recently posted about my new job and how I am walking into the unknown. Like I have said in the past, I did get a list of Host Names that are running SQL Server. I have written a script to go out and look at each of those hosts and get me all instances. I have put together a list of about 150 SQL Servers that are running but I still no very little about. I have also posted about a Powershell script that I have written that goes out and gathers information about the SQL Server instances in question. I ran this and I was shocked. After glancing at the output I see things that just should not be. I see many DB’s that have logs larger than the Database. I see many that are running from the system drive, I see some that are far behind on patching, I even see one with only system databases on it. Like I said I have about 150 identified running instances and just over 1000 databases. You read that correctly over 1000. I just really do not even know where to start.
Because I have so many servers and so many databases, I thought that a simple Excel Spreadsheet is just not going to do it for me this time. I need more than just a simple report. I want to put all this data in a database so that I can slice and dice the data more easily, when I finally figure out how to prioritize what needs to be done. So here is what I have so far. I will use the same Powershell script to go get all the data that I am after and then I will have it insert the data into the database. This is just the beginning for this database as I have some bigger plans brewing in my head now. For now though, I just need something quick and basic to get the data into so I can prioritize and identify the largest problem areas.
I have two tables consisting of the following:
|Instance Table||Database Table|
|ID PK||I_ID FK to I.ID|
|Type (Test or Prod)|
I think that this will give me the ability to look at the basics and identify major problems. As I get things taken care of I will then start to add more to this Database so that I can do some more tunning, baselining, trending, and so forth.
Got any thoughts on what I should add to this initially. I am all ears.