In the news, Microsoft updates the “Script Center”, In this week’s feature, we’ll continue the introduction to SQL Server Features and talk about Reporting Services. The web link is on scaling out with SQL Server 2005, and this week’s tip is on Database Tuning Advisor maintenance.
News this week:
Microsoft has updated the “Script Center” for SQL Server 2005 with new content. You can find great scripts for most versions of SQL Server at http://tinyurl.com/2sb8jj.
In a previous Podcast I started an overview of the major features you can find in SQL Server 2005. I’ll cover each feature in more depth in future Podcasts, but we need a place to start the discussion for those features, so we’ll cover the basics first. This week we’ll take a look at Reporting Services.
In most organizations, the data architecture includes a place to manipulate and store data, a way to report on data, and a system to analyze data. As DBA’s we normally deal with the storing and manipulating part, and in the last Podcast I talked about Analysis Services, which allows you to analyze data. This week I’m covering Reporting Services, which, as the name implies, provides a means to display and output data.
Reporting Services is one of the more mature technologies in SQL Server 2005, and is also available as a separate, free download for SQL Server 2000. In addition to the database or data source that holds the data for a report, Reporting Services includes a few other components: the Report Server, a Report Server Database; a Web interface to show the reports, and the Report Manager which is a .NET-based web interface to manage reports. There’s also a “full” client that installs on your system and lets you design the reports.
I’ll come back to the server components in a moment. Starting with the report itself, there are two basic types: A formatted, defined report which is filled out using a data source, and a report definition, which contains elements that end users can select from to build their own reports. In both cases the report is a text file using an XML-like structure called the report Definition Language, or RDL. This is an industry standard definition, so you can use tools other than those from Microsoft to build reports that a Reporting Services server can run.
The reports include formatting, labels, pictures, and other non-data elements. The data can be pulled not only from SQL Server but from Oracle, Access, text files and more. Once the report is rendered on the screen, you can export it to PDF, Web Archive, TIFF, HTML, Excel, CSV, and XML formats.
Your users don’t even have to run the report. You can automate reports with schedules, send them automatically through e-mails, or even call for them using an API.
Moving back to the server, there is a database the system uses to store the RDL files for each report. This database needs to have the same maintenance, security and backups as any user database. Once the files are created, you can import or publish the RDL files using the Report Manager, which stores them in the Reporting Database.
With the files stored, the Reporting Services engine, which is once again a set of files and registry entries combined with a Windows Service, processes the reports either on the schedule defined for the report or when the processing is called for by the user or from an API call. The Reporting Processor extracts the data from the data source and sends it along with the report definition to the Rendering Extension, another part of the Reporting Services engine. This part of the process combines the report layout with the data from the data source, similar to the Microsoft Word “mail merge” feature. Once combined, the user sees the report either on the screen or from a file location.
There’s a lot more here – and I’ll cover much more about Reporting Services in other Podcasts.
The web link for this week is on “Scale Out” Internals, troubleshooting and best practices for SQL Server 2005. You can find the link at http://tinyurl.com/4wapgp.
Tip of the Week:
The SQL Server Database Tuning Advisor, or DBTA, stores tuning session data and other information in the msdb database. That means you should always include the msdb database in your maintenance plans and backups to ensure you don’t lose this valuable tuning data.