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 project (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 one aspect of the execution component of the project, and in specific gathering the server information. It’s a continuation of the previous tutorial (just before this one) that used the MAPS product as a data source.

The MAPS option I covered in the previous tutorial serves as a “model” for the other methods that I’ll show in this tutorial. All of them involve three steps:

• Identifying the source data you want to capture
• Identifying the location the data will end up in – the destination
• Copying, moving or just referencing the source data from the source in the destination

Using the MAPS tutorial as a guide, let’s examine a few other options for finding SQL Server Instances and recording them in your base tables.

Gathering Server Data using PowerShell

There are other methods you can use to poll a system to see if it has SQL Server installed, rather than using the MAPS tool. One of those tools is PowerShell, which I've described in this series of articles.

PowerShell can use quite a few methods to document the hardware and environment on the server — the easiest of which is Windows Management Instrumentation, or WMI. You can read more about what that is here. The WMI interface shows an absolute litany of information that you can use to track for your system. You can also use it to query the Windows services installed on a server. By doing that, you can show if SQL Server is installed — even if the SQL Server Instance isn’t running.

You can use WMI in lots of places, including in C# or other code. PowerShell makes it pretty simple to use as well. One approach is to query all of the same information from PowerShell as the base tables in your SQLCMS tracking system from one location. To do that, you need to be an administrator on the systems you’re tracking. You can also have the PowerShell script run on the individual servers, and then collect the data into text files, which you can read and store in SQL Server using SQL Server Integration Services or other import methods. I’ll leave that decision to you — the part that you won’t find in script-land is the query for SQL Serve Services. This part of the WMI query will do the trick:

# Find SQL Server Instances    
$SQLServices = gwmi -query "select * from win32_service where Name LIKE 'MSSQL%' and Description LIKE '%transaction%'"   
forEach ($SQLService in $SQLServices) {write-host  $SQLService.Name}

Using the processes I show in my other PowerShell articles, you could pipe that to a file or even open a database connection directly and feed that data in.

This is just a portion, however. I recommend that you collect the server name and other data in the order that you need to insert it into your tracking tables. The advantage with PowerShell is that you can get more than just SQL Server data — you can use this system to manage much more. That’s the point of the SQL Server Central Management System (SQLCMS) — you can tailor it to do whatever you need, using standard functions found in the operating system and SQL Server 2008.

Gathering Server Data using Quest Discovery Wizard for SQL Server

There’s another product that is currently free that you can use to discover your SQL Servers, and even do some security testing while you’re at it. It’s called the Quest Discovery Wizard for SQL Server, and it’s located here. It has a very nice graphical interface, and has the advantage of being able to run multiple times.

To use that product with your SQLCMS, I don’t recommend using their base tables — they haven’t licensed it that way. But what they have done is to create an export routine within the product. You have the option of XML or Comma-Separated Values (CSV), and PDF, although PDF isn’t useful for importing data.

You can follow the same steps as I showed in the MAPS and PowerShell examples. You decompose the source (in this case the CSV file or XML document you exported) and find out where the data is, and then use the same techniques to find where that data goes. From there, it’s just importing the data from the source to the destination. You can do that with any of the methods I’ve described here on this site, or just use SQL Server Integration Services (SSIS) to read that source into the “right” places in the destination tables — in my case, that’s the MDW database.

Gathering Server Data using Other Databases

I’ll mention this method for completeness, but the process is the same as in the other methods. If you’re already collecting server or other data in a system that you bought or designed yourself. Examine the source data, map it to the destination and then decide to transfer it over to your base tables or just reference it with a view, a linked server, or the other methods I’ve detailed in various articles on this site.

Again, we’re staying with the standard tools we have available — nothing new to buy, nothing new to learn. It’s what we already have and know.

Creating a Registered Server

So now you’ve located your SQL Server Instances, and you’ve transferred or referenced the data about them in a single location. But what else can we do other than reporting on the systems we have?

We can do a lot. I’ll now walk through adding the server names into the Central Management Server (not to be confused with the SQLCMS system we’re designing) feature of SQL Server 2008. This is a new SQL Server registration group that allows you to share the server registrations between DBA’s.

The way this works looks like this: you stand up a SQL Server 2008 server somewhere (like the SQLCMS system) and connect to it from a workstation running SQL Server 2008 SQL Server Management Studio (SSMS). In the Registered Servers panel, you have a new node called Central Management Servers. You right-click that node and then register the name and connection information of that main SQL Server 2008 — in this case, the SQLCMS. From there, you just treat that registration like a group in regular Registered Servers. Right-click it and register servers and they will show up just beneath it, acting just like regular registered servers.

The trick is that from then on, other DBA’s can register the SQLCMS server on their copies of SSMS, and all of the servers you registered are there automatically for them. This creates a central location for your server registrations — hence the name.

The interesting thing is that you can right-click the SQLCMS server registration and do two very powerful things: you can run a query on all of the registered servers below it, and you can run Policies from Policy Based Management on those servers as well. I’ll come to that later, but for now it would be useful to take the discovery of the SQL Server Instances found in the discovery methods earlier, and turn that into registered servers on the CMS in SQL Server 2008, specifically on the SQLCMS system we’re building.

It turns out that the Central Management Servers are registered in the msdb system database, in two tables. One holds the groups (which I’m not using here) and the other is the one that holds the servers, with a key between them.

Let’s take a look at a few queries that will pull this information out, since I’ll use this data later for — well, lots of things. Then I’ll show you the process you can use to read the destination data you made from your server discoveries and place those servers in the CMS feature I’m explaining now.

On the SQLCMS system, you can run the following query to see the groups registered on the server:

USE msdb ;
GO

/* Server Groups and Types */
SELECT *
FROM msdb.dbo.sysmanagement_shared_server_groups_internal ;
GO

Note carefully the server type (0 is for the database Engine, the main thing we’re concerned about here) and the server group ID. You’ll need the next higher number to programmatically register the group if you want to create a new one. Note that in my examples, I’m not using groups.

And you can run this query to see the servers registered on the SQLCMS (again, this is the msdb database on the SQLCMS server):

/* Servers that are in the CMS group */
SELECT * FROM msdb.dbo.sysmanagement_shared_registered_servers_internal ;
GO

From the return you get here, make sure you pay attention to the server ID that is returned. You’ll need the next highest number to insert a new one.

This query puts them all together, so that you’ll have this information later:

/* Put it all together */
SELECT DISTINCT g.name AS 'GroupName'
  , s.server_name AS 'ServerName'
FROM msdb.dbo.sysmanagement_shared_server_groups_internal g 
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal s
  ON g.server_group_id = s.server_group_id
ORDER BY s.server_name ;
GO

I’ll make a lot of use of this information in the future, so make sure you keep these queries in mind. To put a server group into the CMS feature (optional, I’m not doing that in my system) while not using the graphical interface in Management Studio, you can use this stored procedure:

/* Adding a new server group and server programatically */
EXEC msdb.dbo.sp_sysmanagement_add_shared_server_group @parent_id = 1 -- must be the next unique number
, @name = N'TestSharedServerGroup'
, @description = N'Test Entry - put anything you like here...'
, @server_type = 0
, @server_group_id = 2 -- must be the next unique number ;
GO
To place a new server into a group, use this stored procedure:
EXEC msdb.dbo.sp_sysmanagement_add_shared_registered_server @server_group_id = 1 -- link to server group you want 
, @name = N'TestSharedServerName'
, @server_name = N'TestSharedServerActual'
, @description = N'Test Server  Description Entry - use whatever you like...'
, @server_type = 0
, @server_id = 1 -- must be next unique number;
GO

So now let’s put all that together. You’ve discovered your servers, and placed their names in base tables. Now it is a matter of extracting the server names using one of the queries I showed you earlier, and feeding that data into the stored procedure with the next highest number. You can do that with a loop, a cursor, or many other methods. I’ll show you an example of that process in a future tutorial.

And there you have it. We’ve discovered the SQL Server Instances in the organization, and copied or referenced that data in a central location. Then we take those servers and registered them in the SQL Server 2008 CMS feature so that we can work with them.

In the next installment, I’ll explore how to load more data into the MDW database — particularly the file and space information.

InformIT Articles and Sample Chapters

In the book I mention below, Kirk Haselden covers SQL Server Integration Services. This chapter, What Is Integration Services and Why Do I Need It?, will get you started with it.

Books and eBooks

SQL Server Integration Services (SSIS) could be used in place of almost all of the code you see here. Kirk Haselden has written a book that can help you understand it called Microsoft SQL Server 2008 Integration Services Unleashed.

Online Resources

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