SQL Server Reference Guide

In a previous tutorial, I explained the importance of change management. For each system that you are responsible for, it is vital that you understand the configuration of each component so that you can optimize, troubleshoot and protect the system. In part one of this series, I explained how you can find and display data about your system. I explained which data is important to capture, and how you can find it. In many other articles on this site, I've shown you the functions, procedures and statements you can use to gather this kind of data.

To wrap up this series, you'll need to have a place to put the data so that you can compare it over time. That allows you to create those "delta" reports that show performance changes, growth, and system configuration and so on.

You have a few choices for this storage. The first is a physical medium such as paper. The second is an electronic form such as Word documents or Excel spreadsheets. You can also store this information in a database. Let's take a look at the advantages and disadvantages of all three.

Physical Storage

It might surprise you to find that I consider a physical medium such as paper an acceptable location for your server's historical data, but paper has some distinct advantages.

Paper is easily read and understood by anyone. If you record your findings of static and dynamic information on paper, you can give it to someone to read even if they don't have a technical background. There are many such requirements, one of which I'll cover in a moment. The person or group that receives the information doesn't have to use the same software or computer that you do to read it. In fact, they don't need a computer at all.

Paper can be transported easily. You can place the documentation of your server's state in a folder or binder and take it with you anywhere. You can send a copy to a remote location through the mails, and the information you're likely to carry won't take up a lot of space.

Paper is persistent. Even 100 years from now the paper with your system's information will still be readable. Try that with magnetic media!

Paper might actually be required by law. As I mentioned a moment ago, there are times when you might have to maintain a paper copy of your records. Some laws for incorporation as a business, for example, ask you to list your assets, and sometimes they require a great deal of detail.

So don't count paper out of your plans.

Electronic Formats

Even when I do store records as paper, I create the records using electronic means like a computer. You might want to skip that step of printing and just save the results in a Word file or an Excel spreadsheet.

This is a find approach, as long as everyone who will consume the data has the same version of the software you used to create it, or at least can transform the file into their version or software.

Whenever I store configuration data like this I use a persistent format such as ASCII, SGML or PDF. And I include these files in my security plans and backup strategies.

Database Storage

Of course, I like the idea of creating, tracking and storing the data about my database servers in a database. I'll show you some of the techniques I use to do this.

I normally store all of the configuration information for all my servers in a single database. I preface the information I capture with the name of the server and the date that I captured it. That way I can reconstruct information for a certain date, plot growth trends, perform date-change impact studies and so on. Storing all the servers in a single database also makes it easier to compare servers.

For instance, I can capture the balance of memory and performance on two servers that are running similar workloads. Why is server A using its memory so much better than server B? Knowing that one server is acting differently than another allows me to do analysis to find out why.

So let me show you a few strategies I use for saving state information in a database. I won't explain an entire methodology here for a couple of reasons. First, your needs will be different than mine. Also, it's better that you understand how I store the data rather than just implementing something blindly.

As I mentioned, I create a single database to store my server information. You might find that it requires too much bandwidth to transfer all of the data from your servers around the world to a single location. Although I've had that issue in the past, I run a stored procedure using SQL Server Integration Services to store the data in a file location and then transfer that data on a rolling schedule to the main server here in the U.S. Once again, your needs might be different, so feel free to develop your own method.

If you want to try this out on your test system, you can create a simple database with this statement:

/* Set up the Database and the Tables */
CREATE DATABASE ServerAuditDB
GO
USE ServerAuditDB
GO

Now that you are using that database, you can set up some tables to hold the data. For the table design, I follow the same process that I always use to design a database. I create English statements that describe the requirements and just break out the nouns and verbs into tables and relationships. You can read my tutorial here on database design if you want to learn more about that.

I'll "normalize" this design to contain the server names in one place so that I don't record the server name more than once. I'll set up a Primary Key, record the name of the server, and its location. You might want to store more than that in this table, such as the DBA responsible, the purchase date and hardware information and so on, but I keep those in other tables.

In this simple example, I create a table to hold that information and enter one server in it. You can try this out as you follow along:

/* One table for the servers */
CREATE TABLE Servers 
(
ServerKey int PRIMARY KEY
, ServerName varchar(55)
)
GO

/* Enter a server */
INSERT INTO Servers
VALUES 
(1
, ’BuckSoft1’)
GO

So now you have a single server entered into your Servers table. Of course, in a real-world implementation you might use another key or an identity field to create the key values automatically, but this is a simple example.

Next, you need tables to store the static and dynamic information. Once again, I write down my requirements and break those into standard tables, so I won't cover that process here. For this simple example I'll create a table to hold the SQL Server server configuration information. I need the key reference to the Servers table, a field for the date that I enter the information, and the configuration option name and current value of that option. You can run this example to create that:

/* Set up a table to hold the data of the main values and the server key, 
as well as a date that we gathered the data */
CREATE TABLE ServerConfiguration
(ServerKey Int
, DateRecorded DateTime
, ServerPropertyName varchar (100)
, ServerPropertyValue varchar (100)
)
GO

Now on to the important bits: the part where you fill this table. As I mentioned earlier, I use a set of stored procedures to get the data I want for the tables. The stored procedures run either based on a trigger, a schedule, or a manual run. It all depends on the data schedule I need.

You can run several statements to fill the table. You can just use an INSERT statement and select the values you want for the row of data. For instance, you can go after each value one at a time using a function like this one:

/* Here’s one way to get the data for this information, one element at a time: */
SELECT CONVERT(char(20), SERVERPROPERTY(’servername’));
GO

You could select that data into the table along with the key for the server name and the date you collected it.

But I normally use stored procedures or functions to get more data, as I've described in other tutorials. In that case I want to select specific data out of the results of the stored procedure. That isn't easily done inside T-SQL, although in a program it isn't difficult at all.

Here's what I do to solve that problem. I select all of the data from the stored procedure into a temporary table, then back out of the temporary table along with the server key and the date. It might make more sense to see this in an example:

/* You can get the all the data for the configuration of that server 
 and put it into a temp table for the moment - we don’t need all of these values 
 but they need a place to go */
CREATE TABLE #SourceTable
(ValueName varchar(100), Minimum int, Maximum int , ConfiguredValue int, RunningValue int)
INSERT #SourceTable EXEC sp_configure

/* Now you can select out what you want and pair it with the server
 key and date, or any other data and insert that into the final table: */
INSERT INTO ServerConfiguration
SELECT ’1’
, GETDATE()
, ValueName
, RunningValue
FROM #SourceTable
GO

/* Clean up */
DROP Table #SourceTable
GO
And the final step is to see all of that data together:
/* Join the data to see the results */
SELECT a.ServerName
, b.DateRecorded
, ServerPropertyName
, ServerPropertyValue
FROM Servers a
INNER JOIN ServerConfiguration b 
  ON a.ServerKey = b.ServerKey

GO

I use this process for both "static" and "dynamic" data. The only differences are the frequency of collection and the layout of the destination table.

I don't keep all of the detail data forever from the "dynamic" information I mentioned in my last tutorial. For one thing, it would make the data set far too large over time. For another, after a while it just isn't that interesting any more. What I do is "roll up" the data to an average amount of memory used, or a percentage change and so on. I do this about every two months for most counters. For those that change more slowly, I save the data longer. I use separate tables for these rollups, so that I can continue to track the detail on a rolling window of two months.

As a final step, I usually create a Reporting Services report to save out the data in a de-normalized format. I then save that data in ASCII format and send it off with my backup tapes. It's just another step to ensure that I can access the data quickly and easily.

InformIT Articles and Sample Chapters

In his book excerpt, Eric Brown covers some of the catalog views you can find for SQL Server 2005.

Books and eBooks

In the book called SQL Server 2005 Management and Administration by Ross Mistry, Chris Amaris, Alec Minty, and Rand Morimoto, you can find even more information on these counters and values.

Online Resources

The comprehensive list of functions, catalog and dynamic management views are from Microsoft.