SQL Server Reference Guide

This is the final article in a series I’ve created 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 final component of the project, reporting and displaying all of the information that you’ve been tracking.

For the reporting of the data I’ve collected, I have several options. Because this is a manual-type system rather than I canned application, I have the ability to just query the data directly from SQL Server Management Studio. If I want to know some “point” of data, meaning a single item like current drive space on a particular system, I can just write a query and get that information right on the screen. I might even use that data in code to make a decision on an action I want to take.

If the data I’m looking for is a bit more complex, and I have to spend a moment or two thinking about the query, I’ll create a stored procedure for the query. I like stored procedures over a view because a stored procedure can accept parameters and perform other work in addition to returning data. I’ll use very descriptive names for the stored procedures and include a standard comment block just below the AS qualifier in the CREATE PROCEDURE statement to make sure everyone knows what its purpose is.

But sometimes I need to visualize the data in a different way. The status of multiple systems is one place a graphical representation helps, along with trending data like drive space usage or query performance. In these cases, I would like to use the data to create graphs, charts and links to other detailed sets of information.

Reporting Services

There are quite a few ways to create graphical information from a database, from writing a full application to creating web pages, but I found one tool that works well for my needs: Reporting Services. Once again, this is a tool that I get for free with SQL Server, it runs on the server itself, it is well documented, and has a good security model. The reports it creates can be printed, exported and viewed from a web browser anywhere I can hit the server from.

I’m not an expert in Reporting Services, so I read the installation instructions and installed and configured the system using the default settings right on my SQLCMS server. The Reporting Services system gathers and processes the data from “data sources,” which can be almost any kind of data resource, from text files to Excel worksheets, and range from SQL Server to Oracle databases. With this power in hand, I can easily make a single report that gathers data from multiple locations and display it in a single, unified view.

Once you have the Reporting Services feature installed and configured, you’ve basically got a repository on the server (it’s actually all stored in a couple of databases) that you can drop reports into. You can store the reports and even the data source definitions right on the server, and then you can view those reports by simply referring to the name of the web location of the reports.

By the way, if you’re using the Enterprise Policy Management framework (EPM) as part of your SQLCMS like I am, then you’re probably already using Reporting Services, since it’s part of that solution.

Report Builder 2.0

OK — I’ll assume that you’ve set up Reporting Services on your system by taking the defaults, and now you’re ready to create a report. You have several options for doing that.

Reporting Services reports are actually just XML files, using specific tags called RDL (for Report Definition Language). In fact, other reporting systems also use this format. You can just type some RDL, upload that to the Reporting Systems server you just set up, and you’re ready to go. Of course, most of us don’t read XML natively, and it’s kind of hard to visualize, so a graphical tool to make the reports is quite useful.

The first option you have is to use the Business Information Development Studio (BIDS) that I’ve described in other tutorials on this site. It’s a full design environment, very powerful, and allows you to do amazing things with those reports.

I’m not a full-time report designer, so I tend not to use this tool. I have lots of work to do other than to learn another tool, so I look for something simpler. And in SQL Server 2008, I’ve found it.

Early on in Reporting Services, Microsoft released a web-based tool that allowed general users to create reports from more "base" reports that you created in BIDS - called a "Report Model". This tool was interesting, but for my purposes here that would have been two steps. Microsoft has now re-engineered this tool, called Report Builder 2.0, to be more powerful, create reports from scratch, and yet it is still easy to use and is web-based.

I’ll show you step-by step how to create a simple report that using Report Builder 2.0 that contains a header, a data grid, a link to another location, and some graphics. You can use all of those tools to make a system of reports that form your “dashboard” like I’ve done in my project up on CodePlex.

First, you need to download and install Report Builder 2.0. You only need this to create reports; anyone can look at them just using a web browser. You can find the tool at this link: http://www.microsoft.com/downloads/details.aspx?FamilyID=dbddc9b6-6e19-4d4b-9309-13f62901b0d5&displaylang=en

Just install that per the instructions and you’ll get a new Windows Start menu item with that name. From there, just click the icon to load the tool.

Figure 1

You can click in the top area to add a title, which of course is fairly obvious:

Figure 2

I’ll move on to something a little more interesting — adding some data. You can add data to a chart, or into a grid. The process is the same either way, it’s just that you’ll get different options based on what you want to do.

In my case, recall that I’m using the MAPS tool’s database to store some of my data, so I’ll use a query I explained earlier to show some of the data here. Here’s the query I used:

/* 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

That will form the data I want to show in a grid. In the center part of the screen I clicked “Table or Matrix,” which brought up this screen:

Figure 3

I have already created two data sources (a definition of where I get my data for this report) up on my server, and my installation remembers that. In your case, this will probably be blank. I’ll click on the “New” button and you’ll see what I see here:

Figure 4

In this panel you name the data source — I’ll use “test” for mine, but you’ll want something more descriptive for yours. From here I need to tell Report Builder where the data is, so I click the “Build...” button there on the right-hand part of the screen to bring up this panel:

Figure 5

In this panel I’ve typed in the name of the server and Instance of my SQLCMS that has the MAPS database on it, and I selected that database as well. Then I just click “OK” to return to the first panel.

Figure 6

By the way, next time I can just enter that text directly if I want. From here, I click “OK” and then I’m back to the first panel in the wizard, and I’ll click the “Next” button there to bring up this panel:

Figure 7

This panel lets me build the query graphically, which can be quite useful, but since I know the query I want already I just click the “Edit as Text” button there on the left to bring up this panel:

Figure 8

I’ve just pasted my text from the query I created earlier, and then I click the “Next” button to bring up this panel:

Figure 9

This is where your creativity comes in. You can arrange things in columns or rows, and even have summary data as well. I’m just going to drag a few values in the bottom, but you can experiment on your own.

From here I click “Next” to bring up this panel:

Figure 10

Since I haven’t used any numerical values, I don’t have any subtotals to arrange. I’ll just click “Next” to continue.

Figure 11

I can choose a style here, but I’ll just take the defaults for this sample. You can always change this now or even later if you wish.

From here I’m dropped back into the main design page:

Figure 12

I’m interested in seeing how this report really looks with data in it, perhaps to check out how wide I want to make the columns and so on. I click the “Run” button up in the top left to admire my handiwork:

Figure 13

I like it. I’ll click the “Design” button in the left top area and return to add a graphic. I click the “Insert” tab at the top of the design area and then click the “Image” button:

Figure 14

That changes my icon to a cross-hair, and I draw a little box on the screen where I want the image to appear. After I do that, the system brings up this dialog box:

Figure 15

There is a lot going on here, so I’ll explain each choice I make. First, I’ll just leave the name at the default, although in production you want to give this image a real name. Second, I need to choose where the image is stored. It can be an external link, like a hyperlink on the web, inside a database, or you can have the Report Builder grab the file from your hard drive and store it right in the report, which is the option I’ll choose here. To do that, I click the “Import...” button to bring up the selection box, and then click “Open” once I’ve found my graphic.

Returning to the main panel, I want to do one more thing — I want to be able to click on that graphic and open a link to something else. In this case it will be just a link out to a web page, but you can do much more – something you should experiment with. I use this method to open yet another report to create my “dashboard” for my entire system. Here’s how I do that:

On this panel I click the tab on the left called “Action.” That brings up this panel:

Figure 16

You have several choices here. If I were creating a dashboard, I would open another report that is stored on the server. If I were designing that other report, I would always create a link to come back to this “base” report. I think you have the idea. I’ll leave this test as a URL, and click “OK” to finalize the project.

Figure 17

And there it is, ready to save and run. I click the small disc icon in the very top bar to save the RDL file down to my hard drive:

Figure 18

Now I just have to import that file up onto my Reporting Services Server. You can probably just change the BWOODY1 (the name of the server) and SQL2K8 (the name of the SQL Server Instance) parts in this URL to hit your server as well: http://bwoody1/Reports_SQL2K8. Of course, if you used something other than the defaults, you can use the Report Server Configuration Manager in the SQL Server 2008 Start Menu item in Windows to find your location to upload reports.

Figure 19

I already have a folder here created for my SQLCMS reports, so I’ll create a Test folder now. I click on the button in the top bar called “New Folder” to bring up this selection:

Figure 20

I type in a name and description and click the “OK” button. I’m returned to the main page, where I click my new “Test” link. I’ll now click the “Upload File” button there to bring up this selection panel:

Figure 21

I type in (or browse) the name of the file, check the box to replace the file (not really necessary) and click the “OK” button to bring the file in. Once I’m returned to the screen where the report name is listed, I click on it, and there on the screen is my masterpiece:

Figure 22

And there you have it. Of course, this is only a simple example, and there’s a whole lot more you can do. Each field in the report has properties you can set, and you could even design your report to show the names of your servers and when you click on them you can drill in to even more detail. Just read the documentation in the “help” files and you’ll see the power that you have.

Finalizing the SQLCMS Project

As I’ve explained in the articles that precede this one, I’m using as many of the features in SQL Server 2008 as I can to monitor and manage my other SQL Server Instances in my organization. I like using the tools I have already for a few reasons — one is cost, and yet another is I like the amount of control and customization that I can do when I’m writing and configuring all the parts. You might not like to do that much work — it might be simpler for you just to purchase one of the many monitoring tools from another vendor and just install it to watch your systems. I chose the methods I’ve described here because I not only monitor my systems, but I also can manage them from there as well.

If you’re creating a system like this for yourself, you can certainly change any part of it that you want. In fact, I think that’s the beauty of this solution — it’s just made up of several parts, and you can substitute in any part that you want to perform that piece of work. In fact, you can add in other functions such as monitoring and managing Windows, Microsoft Exchange, even UNIX systems if you like. It’s all up to you.

As you do that, I would offer one tip that I’ve found really helps out: document everything. In fact, I try and keep one web page in the “dashboard” that explains exactly what I’ve done. I reference this series of articles directly from the project page on CodePlex to show what I was thinking and why I did things the way they turned out. This helps not only myself, but also anyone that has to follow in my shoes later. I’ve inherited some really cruddy systems from other DBAs in the past, and I certainly don’t want to be that guy.

As you make changes and enhancements to this system, feel free to join the project at SQLCMS and explain what you did and why. I might even adapt my system to be more like yours!

InformIT Articles and Sample Chapters

I introduce Report Services in this earlier update to the SQL Server Reference Guide.

Books and eBooks

You can read a lot more about creating reports in Microsoft SQL Server 2008 Reporting Services Unleashed.

Online Resources

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