SQL Server Reference Guide

Most DBAs are looking for a way to locate the installations of SQL Server in their organization. They need to find and document all of these servers, including the versions, the editions, the amount of CPU’s on the system, the amount of RAM and so on.

In the past, I’ve used three methods to do this:

1. Build my own
2. Use Microsoft’s MOM (Now System Center Operations Manager, or SCOM)
3. Use a third-party product like Spotlight on SQL Server from Quest

All of these work, and in fact I’ve switched in and out between them. And sometimes I’ve even used a couple of them in tandem, because one solution might not have all of the information I need.

Each of these solutions offers advantages and disadvantages. Building my own system provided the most flexibility, and not counting my development time, the least cost. But it also is very proprietary, and unless I spent time documenting it well was not continued beyond my tenure as the company DBA.

Using SCOM (what Microsoft Operations Manager became) is a great solution, especially when you can “stack” the database in with other components such as network equipment and Windows Servers to see an entire line of business application. But it is not as comprehensive in its monitoring and control of the SQL Server system, especially when you factor in the new features in SQL Server 2008 like Policy Based Management and the Management Data Warehouse. I have no doubt that the System Center product will eventually “know” about these features, but as of this writing, it does not. And of course there is a cost to buy the product, and the time spent learning and implementing it, in addition to the hardware needed to run it.

I have used (and written about) other products that monitor SQL Server, such as Spotlight on SQL Server, from Quest. These are also great solutions, but they also do not know about the new features in SQL Server 2008, they have a relatively high cost, and they require hardware to run and time to learn.

So which route do I take now? Well, there are considerations, of course. Perhaps my firm already has System Center for other purposes, or perhaps the DBA team is already familiar with another third party product. In those cases, the decision is pretty simple — go with what you have, or go with what you know, and supplement with the tools and features in the product.

But if you’re like me, you might not have any budget right now, and only an anemic machine in the corner to run your system. And... I have a few more requirements than just performance data. I want a system that does it all, from one location.

So what I’m going to do is use the data I collected in that MAPS tool evaluation I wrote about some time ago. You can read that entire article here, but the upshot is that the Microsoft Assessment and Planning Solution Accelerator (MAPS) is a free tool from Microsoft that will run across a series of IP addresses, lists of servers, or even an Active Directory OU to find systems that have SQL Server installed — from version 2000 and higher. It then creates these awesome reports that show all that data, from the version and edition installed all the way down to the CPU and memory installed on the Instance.

So I thought I would try and leverage that data. I’ll run the MAPS tool on my network, and find the Instances of SQL Server I have installed. I would like to store all that data in a single location, so I decided to do some research to find out where all that data comes from, and where it ends up.

The Investigation

Before I begin, it’s important to note that I’m going after data that was generated by a specific version of a tool that is not a Microsoft SQL Server product. The point is that if I develop a procedure or process based on that tool, it might break someday if that tool ever changes. And of course it will change.

So the most important thing to know about the process I’m describing is the version of the MAPS tool. The version I used to inventory my SQL Server systems is: 3.2.2315.0. That’s important because of some decisions I’ll make along the way.

The main decision I have is where to get my data from. In the article I wrote on this tool earlier, you’ll see that the tool produces two reports when it is run for a SQL Server inventory, one in Microsoft Word and the other in Microsoft Excel.

The first approach I thought about what extracting the data from the Excel spreadsheet. I considered the Word document; but it only shows things like the number of versions and some other high-level data. Great for reporting what we have installed to the boss, but not as useful for the kind of detailed data I am after.

As I reviewed the Excel spreadsheet the tool makes, I found that it contains a lot of useful information, including:

• SQL Server Component Name
• Number of instances
• Computer Name
• SQL Server Instance Name
• SQL Server Product Name
• SQL Server Version Number
• SQL Server Service Pack
• SQL Server Edition
• Clustered?
• Language
• Machine Type
• SQL Server Sub-Directory
• SQL Service State
• SQL Service Start Mode
• Current Operating System
• Operating System Service Pack Level
• Operating System Architecture Type
• Number of Processors
• Number of Cores
• Number of Logical Processors CPU
• System Memory (MB)
• Logical Disk Drive Name
• Logical Disk Size (GB)
• Logical Disk Free Space (GB)

That’s quite a lot of information about a system, and I have a lot of choices about how to get the data. I can store the Excel files after each evaluation on my “Central Management Server” and then run a query from SQL Server to attach directly to the spreadsheet, I can import the data from the spreadsheet using SQL Server Integration Services, or I can export the data to a flat-text file in Excel and then import the data with BCP. I can even use PowerShell to open the spreadsheet, open SQL Server, and transfer the data that way.

But I began to wonder about the fact that the MAPS tool installs SQL Server Express edition when it runs. Perhaps that’s where the data is stored for all those spreadsheets and Word documents.

And in fact it is. I opened the SQL Server Configuration Management tool on my MAPS system, and there was the name of the Instance — MAPS. So I connected to that database using SQL Server Management Studio (SSMS) and began to explore. I found the name of the database I installed when I set up the MAPS product, and began to explore the relationships in the tables there.

I normally always start with the views on a system when I want to look at the data it contains. I don’t normally use the stored procedures, since they are code and technically belong to the vendor, and many times they not only return data, but insert, delete or update data as well. I want to ensure that I’m in a “read only” mode.

The views I found in this database didn’t help me all that much. In fact, many of them returned no data at all. That makes sense, since the MAPS tool has the ability to evaluate everything from Microsoft Office to Vista and even Virtualization.

So that left me with the tables. What I did to evaluate them was to use the Database Diagram feature in SQL Server 2008 to pull in all of the tables into a diagram, and that showed me the links that the Primary and Foreign keys had. I then did a SELECT from each table with a TOP 10 limit to see which ones had data.

Armed with that knowledge, I began to pull out the data that I wanted to see.

The Tables

What I found in my discoveries was that there was one main table that seems to store all of the data the other primary tables link to. I also found that some of the columns contained pointers to other data using a numeric system, so I excluded that data for the time being. For instance, in the table that shows the installed software on the machine, there’s a column called install_state, which is numeric. I don’t know what those mean, so I did not include them.

But what I was left with was a rich selection of data — very useful for my monitoring solution, so that I can find out things like service pack numbers, CPU counts and so on. I can even find information about drive space, although it isn’t always easy to find out the unit the space is stored in.

The way I documented this information was in a grid, showing the table name, the information I found that it stored, and how it was linked to other tables. Here is that grid, and then in the section that follows I’ll show you a few of the queries I used to get the information I wanted as an example. I won’t show all columns, since some of them were empty, and others had those numeric or code references. If you’re interested in doing the same thing, you can install the MAPS tool and then perform an inventory on your own systems. Then you can explore more of these columns.

Sample Queries

Right now I’m leaving the data in the MAPS tables, and just using these queries when I need them. In the future, I’ll pull the data out and store it in my own tables, and then refresh the data in my “Central Management Server” from time to time for the data space, drive space and so on. In fact, I’ll probably just use the Management Data Warehouse feature for that data.

But in any case, these queries are examples of what I can get out of this database — very handy! There is much more here, of course, but this will demonstrate a little of what you can find out.

 /* SQL Server Instance Information */
SELECT DISTINCT
device_number
, [clustered]
, create_datetime
, dns_host_name
, instanceid
, iswow64
, [language]
, servicename
, skuname
, splevel
, CASE sqlservicetype
WHEN 1 THEN 'Engine'
 WHEN 2 THEN 'Agent'
WHEN 3 THEN 'FTS'
WHEN 4 THEN 'SSIS'
WHEN 5 THEN 'SSAS'
WHEN 6 THEN 'SSRS' 
WHEN 7 THEN 'SQLBrowser'
END 
, version
FROM sql_assessments
ORDER BY dns_host_name

/* Show SQL Servers by 64 or 32 bit, by OS installed 32 or 64 bit */
SELECT DISTINCT 
a.instanceid
, skuname
, b.os_architecture
FROM sql_assessments a
INNER JOIN devices b
ON a.device_number = b.device_number
ORDER BY instanceid

/* Show other software installed on a SQL Server */
SELECT DISTINCT
b.dns_host_name
,a.name
,a.vendor
,a.version
FROM products a
INNER JOIN sql_assessments b
ON a.device_number = b.device_number
ORDER BY a.name

InformIT Articles and Sample Chapters

Rich Schiesser has a great tutorial on inventorying your hardware in the (now defunct) InformIT IT Management Reference Guide. You can find the full installment still online with this direct link, however.

Books and eBooks

Rich Schieser also wrote IT Systems Management: Designing, Implementing, and Managing World-Class Infrastructures, from which you can find an excerpt on software inventories in the aforementioned InformIT IT Management Reference Guide.

Online Resources

The MAPS software is a free download from here: http://technet.microsoft.com/en-us/library/bb977556.aspx.