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.
–Charlton Julius
CJ Julius 
You must be logged in to post a comment.