SQL Server Reference Guide

I’m continuing a series on building a system that you can use to monitor and track your SQL Server systems, and in fact, any kind of system you like (The first article in this series is here ). At the bottom of this article I reference a CodePlex (Microsoft’s open-source software site) where I’m creating a solution called the SQL Central Management System, or SQLCMS. If you’re interested in participating, just post a notice there that you want to join in the solution. We’ll design it together. I’m working through that project in this series of articles.

In the solution I’m building, there are three basic “components”:

1. Storage
2. Execution
3. Reporting

This tutorial covers the storage aspect of the project, and in specific the tables (what I’ll call “Base Tables”) that I’ve created in my sample project. And that brings up a note I’ll post in every one of the articles in this series:

OK, with that out of the way, we can get started.

Recall that I mentioned some requirements for the SQLCMS solution, and at least three of them are important for the storage aspect of the system. The SQLCMS needs to use standard “components,” be extensible, and be based on components that can be switched in or out. At the very base level of the project, we need a place to store the results of the survey of the system, some performance and growth metrics, and anything else we want to record and report on. We could use XML files, text files, Microsoft Access Databases, Microsoft Excel Spreadsheets, or any number of other products to store the data – all would store data.

But remember that I am building my solution on several new features in SQL Server 2008 that are specifically designed to assist with multiple-server management, so it only makes sense to store the results of my system polling in a SQL Server 2008 database. It meets all the requirements of the system, and it makes querying the data across all of the features seamless. It also scales well, is well supported, and of course as DBA’s and administrators, we know the features we can use to leverage the data.

But that isn’t the end of the story. Even after deciding to use a SQL Server 2008 database as the storage for the solution, there are at least three ways to do that.

Use Current Databases and Tables from Other Sources

As I mentioned in the previous article, there are software packages out there that already perform much of the work you need done in a management and/or monitoring solution. Perhaps you’re reading this tutorial because you want to see how you can “fold in” the tools, processes and features in SQL Server 2008 to one of those solutions to shore up what they may not do, or at least do well.

If so, then they may already have a database, perhaps even on SQL Server 2008, where the discovery and other data has been stored. You could, and possibly should, leverage that data. But before you do…

Not every software license allows you to access the databases underneath them. The structure might change, and the vendor probably hasn’t explained to you how they insert, update or delete the data they track. And even if it were technically possible to find out where the data they have is stored, or how it is updated, it might not be legal to do so. You’ll need to read and understand their license terms, and perhaps even speak with their sales staff to make sure you have written permission to access the data without using their software.

If that isn’t possible, then you might consider at least exporting the data they’ve collected so that you don’t have to perform the discovery or collection methods yourself. Even there, you need to check that license to make sure that you’re allowed to do that. You don’t want to get into trouble with a vendor because you leveraged their data without permission.

Another possibility is to create a view in your own database that references the base tables in the application’s database. That way you only store the data once, and you have the control to make the views with the column names the way you want, with the security the way you want. You could also use a remote query, linked server or other methods to leave the data in the other platform but show it in the database where you’re tracking other information.

Create Completely New Tables in a Separate Database

Let’s assume you’ve either exported the data you want (legally), or you are going to collect it yourself and go it alone. If that’s the case, you might want to investigate creating your own database in SQL Server 2008 yourself.

If you do, I recommend that you follow the steps in the next option. It’s the solution I chose, and its advantage is that you can use the same techniques whether you’re using a completely new database or one that already exists.

In any case, make sure you treat this database like you would any production database. Remember, this data is production for you. And if you begin to depend on it, which I’m sure you will, it needs to be safe, secure, and performant, just like any other database. It will need a maintenance plan, security groups and so on.

While we’re on that subject, this system has to be on a licensed copy of SQL Server 2008. You can certainly experiment with an an evaluation edition or the Developer edition, but you'll need at least Standard edition to legally use it in production. You'll only need as many client licenses as DBA's, of course, so it isn't an expensive proposition.

Personally I'm using Enterprise edition, with just a few CALs (Client Licenses) rather than a CPU license. That keeps the cost down, but gives me compression, which becomes really important when I start storing a lot of performance data. More on that later.

Adding Tables and Objects to a Current Database

The solution I settled on is to use a database that already exists in SQL Server 2008 — sort of. I’ve explained the Management Data Warehouse feature in another tutorial, and I mentioned there that when you run the Wizard to use the feature for the first time, it creates a new database. I named mine “MDW.” Although this is a Microsoft “owned” database, they have publicly stated that they do not mind if you create other objects in it. There are three schemas that Microsoft creates within the database: core, snapshots, and optionally custom_snapshots. We’ve been told that as long as we stay away from those three schemas, we can create objects in that database.

Why use this database? My choice was guided by a few factors. Number one, the MDW database stores performance and other useful data. Also, this is the database that is used by the Enterprise Policy Management Framework, something I’ll cover in future articles on this topic.

So if you are following along with this example, make sure you read that article on the MDW system and run the Wizard to create the database.

Even if you don’t use the MDW database and decide to create your own, you can follow along with the rest of this tutorial.

The SQLCMS Schemas

Since I’m using the MDW database, I need to create some new schemas to hold my database objects. Even if you’re using your own database, it’s a best practice to ensure that you create your objects in schemas as well, so this works for you as well. If you’re new to schemas, it’s actually a pretty simple concept. They are simply “containers” for other objects, so that you can group them together.

A tip that I’ll show and use throughout this process is to self-document my objects. In other words, I’ll make sure that even if you don’t have this document, you could figure out (probably) what I’m doing with each object. This is a tip that I use on just about everything, and it involves “Extended Properties.” You can get to the Extended Properties by right-clicking an object in SQL Server Management Studio, and looking at the last tab for that object. You can then add an extended property, which involves a “tag” or name of the description, and then the data you want to add to the object. From then on, you can query the Extended Properties of the object. I use that feature to add documentation to my objects, so that I know what they are and are used for.

You can also do this with a stored procure, called sys.sp_addextendedproperty. You’ll see this in the code in a moment.

First, which schemas do we need? Well, in my solution, I wanted to make sure I captured the pertinent data, but leave it open to have more information if I wanted to extend it later. Since I’m tracking data about SQL Server Instances, I’ll certainly need a schema for that. Here’s the code I used to create that schema, once I set the database context to the MDW database:

-- Change MDW to the database of your choice
USE MDW;
GO 
CREATE SCHEMA [sqlinstances] ;
GO
Now to annotate it to explain what it does:
EXEC sys.sp_addextendedproperty @name=N'MS_Description'
, @value=N'Stores Information specific to SQL Server Instances.' 
, @level0type=N'SCHEMA'
,@level0name=N'sqlinstances';
GO

Now, whenever anyone wants to see the annotations I’ve put on the objects, they can use the system function called fn_listexendedproperty to see them. Here’s the query that shows schema information, once again making sure I’m in the MDW database first:

-- New schemas and their tables
SELECT  *
FROM    fn_listextendedproperty(NULL
, 'schema'
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT
, DEFAULT) ;
GO

I’ll show those again when we’re done with the process.

Since SQL Server runs on a Windows Server, I decided to create a schema for that as well. True, I could store that information in the SQL Server schema, but by separating them this way I can allow system administrators to leverage this system as well, or even Microsoft Exchange, System Center, SharePoint or any other kind of platform to leverage the server data as well. Here’s the code for that — a little more verbose, a little more proper to create and annotate the schema for the servers:

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'servers')
EXEC sys.sp_executesql N'CREATE SCHEMA [servers] AUTHORIZATION [dbo]'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'servers', NULL,NULL, NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores data about the Windows or other Servers hosting a platform such 
as SQL Server.' , @level0type=N'SCHEMA',@level0name=N'servers'
GO 

Now on to the tables. I’ll start with the server tables this time. This is the part where your solution may differ from mine — I’ve decided on some base columns that I plan to create, and as long as I know I have these columns, I can add to them later if I need to.

My first table covers some basic information about the server. I toyed with leveraging the data in the MAPS tool, because it is really useful. If you would like to do that, just create views here that reference that data. You can find more about that in this tutorial. More about using the data it stores is here.

Following standard design processes, I’ll only store the data here that is recorded once for each server — to a point. Instead of documenting each one of the columns in this tutorial, open a connection to a SQL Server and try the following queries in a test database to check out the “self documenting” idea:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS(SELECT  *
              FROM    sys.objects
              WHERE   object_id = OBJECT_ID(N'[servers].[system]')
                      AND TYPE IN(N'U'))
   BEGIN
         CREATE TABLE [servers].[system]
              (
               [systemKey] [INT] IDENTITY(1,1) NOT NULL
               , [DomainName] [VARCHAR](150) NOT NULL
               , [ServerName] [VARCHAR](150) NOT NULL
               , [NetworkAddress] [VARCHAR](50) NULL
               , [Architecture] [VARCHAR](50) NULL
               , [ProcessorType] [VARCHAR](50) NULL
               , [NumberOfProcessors] [NUMERIC](18,0) NULL
               , [TotalMemoryInstalledMB] [NUMERIC](18,0) NULL
               , [OperatingSystem] [VARCHAR](100) NULL
               , [OperatingServicePack] [VARCHAR](50) NULL
               , [IsClustered] [BIT] NULL
               , [Owner] [VARCHAR](150) NULL
               , [Notes] [VARCHAR](255) NULL
               , [AssetNumber] [VARCHAR](150) NULL
               , [LastUpdated] [DATETIME] NULL
               , CONSTRAINT [PK_system] PRIMARY KEY CLUSTERED([systemKey] ASC) WITH(PAD_INDEX = OFF
               , STATISTICS_NORECOMPUTE = OFF
               , IGNORE_DUP_KEY = OFF
               , ALLOW_ROW_LOCKS = ON
               , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
              )
         ON  [PRIMARY]
   END
GO

SET ANSI_PADDING OFF
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'systemKey'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Primary Key for this Table', 
@level0type = N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN', @level2name = N'systemKey'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'DomainName'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Name of the domain the computer belongs to. Use the ServerName if 
no domain. Makes this row unique.', @level0type = N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', 
@level2type = N'COLUMN', @level2name = N'DomainName'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'ServerName'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'HostName of the computer.', @level0type = N'SCHEMA', 
@level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN', @level2name = N'ServerName'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'NetworkAddress'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Network address of the server.', @level0type = N'SCHEMA', 
@level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN', @level2name = N'NetworkAddress'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'Architecture'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Bit-depth of the motherboard (32, 64, etc).', @level0type = 
N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN', @level2name = N'Architecture'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'ProcessorType'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Vendor and chip name of the central processor(s).', @level0type = 
N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN', @level2name = 
N'ProcessorType'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'NumberOfProcessors'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Number of physical Processors installed.', @level0type = 
N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN', @level2name = 
N'NumberOfProcessors'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'TotalMemoryInstalledMB'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Total amount (in megabytes) of physical memory installed in the 
server.', @level0type =  N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN', 
@level2name = N'TotalMemoryInstalledMB'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'OperatingSystem'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Vendor and SKU name of the operating system installed on the 
server.', @level0type = N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN', 
@level2name = N'OperatingSystem'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'OperatingServicePack'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Service Pack Description of the installed operating system.', 
@level0type = N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN', 
@level2name = N'OperatingServicePack'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'IsClustered'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Specifies whether the operating system is clustered (1) or not (0).', 
@level0type = N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN', 
@level2name = N'IsClustered'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'Owner'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Responsible party for this system. Optional. ', @level0type = N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN', @level2name = N'Owner'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'Notes'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Short set of notes regarding this hardware. Optional.', @level0type = 
N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN', @level2name = N'Notes'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'AssetNumber'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Organization''s asset numbering system identification number. 
Optional.', @level0type = N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN',
@level2name = N'AssetNumber'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'system', N'COLUMN', N'LastUpdated'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'The last time this system''s data was updated. Default is insert time.', 
@level0type = N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system', @level2type = N'COLUMN', 
@level2name = N'LastUpdated'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'Description', N'SCHEMA', N'servers', N'TABLE', N'system', NULL, NULL))
   EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Systems to track. Could be physical or virtual, but must be a discrete 
computing server.', @level0type = N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'system'
GO

IF NOT EXISTS(SELECT  *
              FROM    dbo.sysobjects
              WHERE   id = OBJECT_ID(N'[DF_Table_1_Clustered]')
                      AND TYPE = 'D')
   BEGIN
         ALTER TABLE [servers].[system]
         ADD CONSTRAINT [DF_Table_1_Clustered] DEFAULT((0)) FOR [IsClustered]
   END

GO

IF NOT EXISTS(SELECT  *
              FROM    dbo.sysobjects
              WHERE   id = OBJECT_ID(N'[DF_system_LastUpdated]')
                      AND TYPE = 'D')
   BEGIN
         ALTER TABLE [servers].[system]
         ADD CONSTRAINT [DF_system_LastUpdated] DEFAULT(getdate()) FOR [LastUpdated]
   END

GO

Now, run this query to see what you just created:

SELECT  *
FROM    fn_listextendedproperty(NULL
, 'schema'
, 'servers'
, 'table'
, DEFAULT, NULL, NULL) ;
GO

-- Columns
SELECT 'servers.system' AS 'Table'
      , objname AS 'Column'
      , VALUE AS 'Description'
FROM   fn_listextendedproperty(NULL
, 'schema'
, 'servers'
, 'table'
, 'system'
, 'column'
, DEFAULT) ;
GO

Don’t agree with my choices or layout? Change it! That’s the point of this exercise – it’s your system, and you can change the schemas, layout or anything else in any way you like.

OK, now on to some other information in the server schema. Although the server itself is recorded once, there is other data that I am interested in, specifically the disk information. So I’ll create a “disk” table to hold that.

Here’s where the design becomes a bit more...interesting. Because I’ve chosen to use the MDW database and the Enterprise Policy Management Framework (which will also put some tables in the MDW database) as well as some tables in the msdb database for the Central Management Server (CMS) feature, I’ll have to link everything up with the server name. Normally you want to use a Primary Key to link things together, and normally that is a number or some other value that has nothing to do with the meaning of the data. But since several of the features I’m leveraging are using the server and Instance name as a link, I’ll have to do that as well if I want to remain consistent with them.

I’m OK with this. I’ll never have more than a few dozen servers to contend with, and the data is certainly re-discoverable — it’s not like the server will ever “lose” its name. It might change, but I can handle that as well.

It’s these type of decisions that really make the project yours. Feel free to architect yours differently. Here is the script (and the descriptions again) for the “disk” table:

/* Create the disk table */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS(SELECT  *
              FROM    sys.objects
              WHERE   object_id = OBJECT_ID(N'[servers].[disk]')
                      AND TYPE IN(N'U'))
   BEGIN
         CREATE TABLE [servers].[disk]
              (
               [diskKey] [INT] IDENTITY(1,1) NOT NULL
               , [ServerName] [VARBINARY](150) NOT NULL
               , [DriveReference] [VARCHAR](100) NULL
               , [TotalSizeMB] [NUMERIC](18,0) NULL
               , [CurrentlyUsedMB] [NUMERIC](18,0) NULL
               , [Notes] [VARCHAR](255) NULL
               , [LastUpdated] [DATETIME] NULL
               , CONSTRAINT [PK_disk] PRIMARY KEY CLUSTERED([diskKey] ASC) WITH(PAD_INDEX = OFF
               , STATISTICS_NORECOMPUTE = OFF
               , IGNORE_DUP_KEY = OFF
               , ALLOW_ROW_LOCKS = ON
               , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
              )
         ON  [PRIMARY]
   END
GO

SET ANSI_PADDING OFF
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'disk', N'COLUMN', N'diskKey'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Key for the disk table.', @level0type = N'SCHEMA', @level0name = 
N'servers', @level1type = N'TABLE', @level1name = N'disk', @level2type = N'COLUMN', @level2name = N'diskKey'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'disk', N'COLUMN', N'ServerName'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Physical or virtual server where this drive is installed. Will be 
denormalized.', @level0type = N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'disk', @level2type = N'COLUMN', 
@level2name = N'ServerName'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'disk', N'COLUMN', N'DriveReference'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Drive letter, mount point or share location that you want to track for 
this server.', @level0type = N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'disk', @level2type = N'COLUMN', 
@level2name = N'DriveReference'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'disk', N'COLUMN', N'TotalSizeMB'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Complete size of the drive, in megabytes.', @level0type = 
N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'disk', @level2type = N'COLUMN', @level2name =
N'TotalSizeMB'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'disk', N'COLUMN', N'CurrentlyUsedMB'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'The amount of space left as of this poll.', @level0type = N'SCHEMA', 
@level0name = N'servers', @level1type = N'TABLE', @level1name = N'disk', @level2type = N'COLUMN', @level2name = N'CurrentlyUsedMB'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'disk', N'COLUMN', N'Notes'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Short notes about this drive.', @level0type = N'SCHEMA', 
@level0name = N'servers', @level1type = N'TABLE', @level1name = N'disk', @level2type = N'COLUMN', @level2name = N'Notes'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'servers', N'TABLE', N'disk', N'COLUMN', N'LastUpdated'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'The last time this data was updated. Default is insert date.', 
@level0type = N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'disk', @level2type = N'COLUMN', @level2name 
= N'LastUpdated'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'Description', N'SCHEMA', N'servers', N'TABLE', N'disk', NULL, NULL))
   EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Stores logical disk information for a server. Links to servers.system 
through ServerName.', @level0type = N'SCHEMA', @level0name = N'servers', @level1type = N'TABLE', @level1name = N'disk'
GO

IF NOT EXISTS(SELECT  *
              FROM    dbo.sysobjects
              WHERE   id = OBJECT_ID(N'[DF_disk_LastUpdated]')
                      AND TYPE = 'D')
   BEGIN
         ALTER TABLE [servers].[disk]
         ADD CONSTRAINT [DF_disk_LastUpdated] DEFAULT(getdate()) FOR [LastUpdated]
   END

GO
/* Show the documentation */
SELECT 'servers.disk' AS 'Table'
      , objname AS 'Column'
      , VALUE AS 'Description'
FROM   fn_listextendedproperty(NULL
, 'schema'
, 'servers'
, 'table'
, 'disk'
, 'column'
, DEFAULT) ;
GO

You can see the kind of data I intend to collect about the disks. Now on to the tables for the SQL Server Instances. I’ll show the code for two tables at one time, one for the Instance itself and the other for the databases on the Instance (remember, we’re still in the MDW database — that’s where this code should run):

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS(SELECT  *
              FROM    sys.objects
              WHERE   object_id = OBJECT_ID(N'[sqlinstances].[sqlserver]')
                      AND TYPE IN(N'U'))
   BEGIN
         CREATE TABLE [sqlinstances].[sqlserver]
              (
               [sqlserverKey] [INT] IDENTITY(1,1) NOT NULL
               , [ServerName] [VARCHAR](150) NOT NULL
               , [instance_name] [VARCHAR](150) NOT NULL
               , [InstanceMajorVersion] [VARCHAR](50) NULL
               , [InstanceMinorVersion] [VARCHAR](100) NULL
               , [Edition] [VARCHAR](50) NULL
               , [ProcessorsAssigned] [NUMERIC](18,0) NULL
               , [MaxMemoryAssigned] [NUMERIC](18,0) NULL
               , [ClusterNode] [VARCHAR](50) NULL
               , [InstanceStatusFlag] [BIT] NULL
               , [InstanceStatusDescription] [VARCHAR](255) NULL
               , [InstanceOwner] [VARCHAR](150) NULL
               , [InstanceNotes] [VARCHAR](255) NULL
               , [LastUpdated] [DATETIME] NULL
               , CONSTRAINT [PK_sqlserver] PRIMARY KEY CLUSTERED([sqlserverKey] ASC) WITH(PAD_INDEX = OFF
               , STATISTICS_NORECOMPUTE = OFF
               , IGNORE_DUP_KEY = OFF
               , ALLOW_ROW_LOCKS = ON
               , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
              )
         ON  [PRIMARY]
   END
GO

SET ANSI_PADDING OFF
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', N'sqlserverKey'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Primary key for the sqlserver table.', @level0type = N'SCHEMA', 
@level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'sqlserver', @level2type = N'COLUMN', @level2name = N'sqlserverKey'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', N'ServerName'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Name of the physical or virtual server where this instance is installed. 
Used as a link to other tables in the MDW database.', @level0type = N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', 
@level1name = N'sqlserver', @level2type = N'COLUMN', @level2name = N'ServerName'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', N'instance_name'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Instance name. Use the server name if this is the DEFAULT instance 
to link properly to other schemas in the MDW database.', @level0type = N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', 
@level1name = N'sqlserver', @level2type = N'COLUMN', @level2name = N'instance_name'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', 
N'InstanceMajorVersion'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Version of SQL Server installed on this instance.', @level0type = 
N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'sqlserver', @level2type = N'COLUMN', @level2name = 
N'InstanceMajorVersion'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', 
N'InstanceMinorVersion'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Service Pack, Hotfix, and / or Cumulative Update installed on this 
instance.', @level0type = N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'sqlserver', @level2type = 
N'COLUMN', @level2name = N'InstanceMinorVersion'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', N'Edition'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Edition of SQL Server installed.', @level0type = N'SCHEMA', 
@level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'sqlserver', @level2type = N'COLUMN', @level2name = N'Edition'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', 
N'ProcessorsAssigned'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Total number of processors assigned to this instance of SQL Server.', 
@level0type = N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'sqlserver', @level2type = N'COLUMN', 
@level2name = N'ProcessorsAssigned'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', 
N'MaxMemoryAssigned'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Maximum amount of RAM assigned to this instance.', @level0type = 
N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'sqlserver', @level2type = N'COLUMN', @level2name = 
N'MaxMemoryAssigned'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', N'ClusterNode'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'If part of a cluster, the name of this node.', @level0type = 
N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'sqlserver', @level2type = N'COLUMN', @level2name = 
N'ClusterNode'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', 
N'InstanceStatusFlag'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Shows whether this instance needs attention (1) or not (0).', 
@level0type = N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'sqlserver', @level2type = N'COLUMN', 
@level2name = N'InstanceStatusFlag'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', 
N'InstanceStatusDescription'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Description of why this system needs attention.', @level0type = 
N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'sqlserver', @level2type = N'COLUMN', @level2name = 
N'InstanceStatusDescription'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', N'InstanceOwner'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Individual or group responsible for this server. Can be used to show 
the on-call person and number.', @level0type = N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'sqlserver', 
@level2type = N'COLUMN', @level2name = N'InstanceOwner'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', N'InstanceNotes'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Short notes about this instance.', @level0type = N'SCHEMA', 
@level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'sqlserver', @level2type = N'COLUMN', @level2name = N'InstanceNotes'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', N'COLUMN', N'LastUpdated'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Last time this information was gathered. Default is the row insert 
date.', @level0type = N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'sqlserver', @level2type = 
N'COLUMN', @level2name = N'LastUpdated'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'sqlserver', NULL, NULL))
   EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'SQL Server Instance Information.', @level0type = N'SCHEMA', 
@level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'sqlserver'
GO

USE <SourceDatabaseName, varchar(100), MDW>
GO

/* Now the databases */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS(SELECT  *
              FROM    sys.objects
              WHERE   object_id = OBJECT_ID(N'[sqlinstances].[database]')
                      AND TYPE IN(N'U'))
   BEGIN
         CREATE TABLE [sqlinstances].[database]
              (
               [databaseKey] [INT] IDENTITY(1,1) NOT NULL
               , [ServerName] [VARCHAR](150) NULL
               , [instance_name] [VARCHAR](150) NULL
               , [DatabaseName] [VARCHAR](100) NULL
               , [LastDatabaseBackup] [DATETIME] NULL
               , [LastLogBackup] [DATETIME] NULL
               , [CurrentDataSizeMB] [NUMERIC](18,0) NULL
               , [CurrentLogSizeMD] [NUMERIC](18,0) NULL
               , [RecoveryModel] [VARCHAR](50) NULL
               , [Notes] [VARCHAR](255) NULL
               , [Purpose] [VARCHAR](255) NULL
               , [LastUpdated] [DATETIME] NULL
               , CONSTRAINT [PK_database] PRIMARY KEY CLUSTERED([databaseKey] ASC) WITH(PAD_INDEX = OFF
               , STATISTICS_NORECOMPUTE = OFF
               , IGNORE_DUP_KEY = OFF
               , ALLOW_ROW_LOCKS = ON
               , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
              )
         ON  [PRIMARY]
   END
GO

SET ANSI_PADDING OFF
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'database', N'COLUMN', N'databaseKey'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Primary key for database table.', @level0type = N'SCHEMA', 
@level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'database', @level2type = N'COLUMN', @level2name = N'databaseKey'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'database', N'COLUMN', N'ServerName'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Partial link to other tables. Also use instance_name to make this 
unique.', @level0type = N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'database', @level2type = 
N'COLUMN', @level2name = N'ServerName'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'database', N'COLUMN', N'instance_name'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Partial link to server. Also use ServerName.', @level0type = 
N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'database', @level2type = N'COLUMN', @level2name = 
N'instance_name'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'database', N'COLUMN', N'DatabaseName'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Name of the database.', @level0type = N'SCHEMA', @level0name = 
N'sqlinstances', @level1type = N'TABLE', @level1name = N'database', @level2type = N'COLUMN', @level2name = N'DatabaseName'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'database', N'COLUMN', 
N'LastDatabaseBackup'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Date and time of the last backup of the database.', @level0type = 
N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'database', @level2type = N'COLUMN', @level2name = 
N'LastDatabaseBackup'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'database', N'COLUMN', N'LastLogBackup'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Date and time of the last log backup.', @level0type = N'SCHEMA', 
@level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'database', @level2type = N'COLUMN', @level2name = 
N'LastLogBackup'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'database', N'COLUMN', 
N'CurrentDataSizeMB'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Size of the database as of this poll.', @level0type = N'SCHEMA', 
@level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'database', @level2type = N'COLUMN', @level2name = 
N'CurrentDataSizeMB'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'database', N'COLUMN', 
N'CurrentLogSizeMD'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Current log size as of this poll.', @level0type = N'SCHEMA', 
@level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'database', @level2type = N'COLUMN', @level2name = 
N'CurrentLogSizeMD'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'database', N'COLUMN', N'RecoveryModel'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Recovery model for the database.', @level0type = N'SCHEMA', 
@level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'database', @level2type = N'COLUMN', @level2name = 
N'RecoveryModel'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'database', N'COLUMN', N'Notes'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Short notes about this database.', @level0type = N'SCHEMA', 
@level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'database', @level2type = N'COLUMN', @level2name = N'Notes'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'database', N'COLUMN', N'Purpose'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Short description of database purpose.', @level0type = N'SCHEMA', 
@level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'database', @level2type = N'COLUMN', @level2name = N'Purpose'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'database', N'COLUMN', N'LastUpdated'))
   EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'Last time this database was polled. Default is the insert date.', 
@level0type = N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = N'database', @level2type = N'COLUMN', 
@level2name = N'LastUpdated'
GO

IF NOT EXISTS(SELECT  *
              FROM    ::fn_listextendedproperty(N'Description', N'SCHEMA', N'sqlinstances', N'TABLE', N'database', NULL, NULL))
   EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Database information for SQL Servers. Links to sqlinstances.sqlserver 
through ServerName and instance_name.', @level0type = N'SCHEMA', @level0name = N'sqlinstances', @level1type = N'TABLE', @level1name = 
N'database'
GO

IF NOT EXISTS(SELECT  *
              FROM    dbo.sysobjects
              WHERE   id = OBJECT_ID(N'[DF_database_LastUpdated]')
                      AND TYPE = 'D')
   BEGIN
         ALTER TABLE [sqlinstances].[database]
         ADD CONSTRAINT [DF_database_LastUpdated] DEFAULT(getdate()) FOR [LastUpdated]
   END

GO

Now you can see what I’ve created using these statements (staying in the MDW database):

SELECT 'sqlinstances.sqlserver' AS 'Table'
      , objname AS 'Column'
      , VALUE AS 'Description'
FROM   fn_listextendedproperty(NULL
, 'schema'
, 'sqlinstances'
, 'table'
, 'sqlserver'
, 'column'
, DEFAULT) ;
GO

SELECT 'sqlinstances.database' AS 'Table'
      , objname AS 'Column'
      , VALUE AS 'Description'
FROM   fn_listextendedproperty(NULL
, 'schema'
, 'sqlinstances'
, 'table'
, 'database'
, 'column'
, DEFAULT) ;
GO

In the next installment I’ll show you how I use a few queries to begin to tie the data together. I’ll then explain how to fill it in.

InformIT Articles and Sample Chapters

As I mentioned, you’ll need to ensure that you have maintenance on this database. You can read more about that in this previous Reference Guide entry

Books and eBooks

If some of these queries are new to you, or you need a refresher in the SQL language, definitely check out Sams Teach Yourself SQL in One Hour a Day, 5th Edition, by Ryan Stephens, Ron Plew, and Arie D. Jones.

Online Resources

The SQL Central Management System (SQLCMS) CodePlex project is located here.