Musings on Reporting Services and Notification Services

SQL 2008 R2 RTM! Time to look at some new Execution Log Reports

Lukasz Pawlowski -- MS — Thu, 22 Apr 2010 19:17:23 GMT

Wow. We just RTM’ed SQL Server 2008 R2. Check out the official web site SQL Server 2008 R2 Website.

Now that the official release is on its way out the door, I thought I’d share some reports that show off some of the new capabilities. In SQL 2008 R2 there is a new ExecutionLog3 view in the Report Server database. For those familiar with SSRS, the ExecutionLog table, and then ExecutionLog and ExecutionLog2 views were used in previous versions to show which reports ran on the report server, who ran them, and to provide a lot of information about the execution. With SQL Server 2008 R2 the ExecutionLog content is expanded again. The newest version of the log is ExecutionLog3. You will find it by connecting directly to the report server database, looking under views and then you will see it. The execution log views are the only parts of the report server database which are supported across versions.

The reports shown in this Blog post depend on the new ExecutionLog3, they won’t work with SQL 2008 or SQL 2005 or even SQL 2000. The Report Executions by Hour report depends on new capabilities added to report processing in SQL 2008 R2 so it won’t run on SQL 2008. In principle you can recreate the Report Executions by Hour Trend report in SQL 2008, but you’d have to rebuild it since the RDL version I’m using here is the SQL 2008 R2 version.

What are some of the new features these reports show:

1) ExecutionLog3 – contains an expanded set of information and easier querying. Check out the Additional Info column if you want to really get into some detailed information.

2) Report Variables – ability to define variables at the report, data set or group level which allow you a more efficient report design experience – how many times have you copied the same expression around. Now, you can build it once (for certain uses) and reuse the output value in your data regions.

3) Lookup – ability to lookup data across data sets in a single data region. Probably this is the feature so many report authors needed. I have used it in the Report Execution by Hour report to compare the execution time of a report against its historical average. This feature alone should entice many report authors to consider SQL Server 2008 R2 Reporting Services.

What you need to do to run these reports:

1) Create a Shared Data source on your report server that connects to the Report Server database’s ExecutionLog3 table

2) publish the report and configure them to use the data source

3) run them :)

Report Executions By Hour

RDL File: ReportExecutionsByHour.rdl

Larger Image: ReportExecutionsByHour.PNG

Warning: The Report Executions By Hour report takes a while to run since it first, it runs quite a bit of logic and second because the download of all those pretty pictures. I find that running the report in Report Builder 3.0 preview is faster than running it in the Browser… one solution is to run it cached. But frankly, making it a subscriptions might be the best approach to viewing it repeatedly.

Report Executions By Hour Trend

RDL File: ReportExecutionsTrendByHour.rdl

Larger Image: ReportExecutionTrendByHour.PNG

Hopefully you’ll find using these reports helps you run your report servers efficiently. Of course there is a compulsory note about performance of these reports. I think you’ll find that they will take a long time to run as you increate the data volume. I would suggest extracting the data from the ExecutionLog3 view and loading it into a data warehouse.

Take care and good luck,

-Lukasz

Charts in a Tablix cell don’t work for me… but I’ve see these nice demos where they do…

Lukasz Pawlowski -- MS — Mon, 21 Dec 2009 16:34:30 GMT

Problem:

You may have learned about sparklines or you want to add a chart to your Tablix. You either copy/paste your working(!!!) chart into the tablix and then you preview and you get:

"The chart 'Chart1' has a detail member with inner members. Detail members can only contain static inner members."

Solution:

In a Tablix you cannot put dynamic member in a Detail row. You have to put it into a Group header or Group Footer row.

Steps:

Insert a table into your report.
Set the DataSetName for the Tablix (either in the Properties pane or by dragging a field into the tablix).
In Row Groups, right click the (Details1) group. Select Add Group –> Parent Group…
In the Tablix group dialog, Select your Group by: clause.
Check the Add group header checkbox.
Press OK.

Now you should see something that looks like:

Notice the two rows next to big parenthesis on the left. These are the rows in your group. The top row (where I has the highlighted cell) is the group header row. You can put a chart in any group header row. The bottom row (where the three link icon is) is the details row. If you put your chart in this row, you will get the aforementioned error.

Take care and good luck,

-Lukasz

High Availability – Frequently Asked Questions about Failover Clustering and Reporting Services

Lukasz Pawlowski -- MS — Wed, 28 Oct 2009 23:39:07 GMT

In SSRS high availability is achieved through a Scale-out deployment that is placed behind a Network Load Balancer (NLB). The NLB is then responsible for routing requests to Report Server nodes that are responding to requests.

High Availability Documentation Reference: http://msdn.microsoft.com/en-us/library/bb522745.aspx

However, many customers are interested in using Windows Failover Clustering with SSRS, since this is how they deploy their SQL Servers. This article discusses Windows Failover Clustering in regards to SSRS.

Windows offers built-in Failover Clustering capability. SSRS does not natively support Windows Failover Clustering. The use of Failover Clustering with SSRS service is possible using standard capabilities of Windows Failover Clustering; however it may not be desirable as explained below.

Standard features of Windows Failover Clustering include a Generic Service and a Generic Script cluster resource. Each of these resources can be used in conjunction with SSRS to achieve failover during a server down situation. The Generic Service cluster resources can be used to determine whether the service is running or not and to failover to another cluster node when the service stops running for whatever reason. This option has the limitation that it will not detect service hangs. The Generic Script cluster resource allows the IT administrator to monitor any desired attribute of the service (performance counters, response time, service started/stopped, etc.) to determine when to failover to another cluster node. Using a Generic Script resource allows you to overcome the limitations of the Generic Service resource, at additional implementation cost.

Generic Script Resource: http://msdn.microsoft.com/en-us/library/aa373089(VS.85).aspx

Generic Script Resource Example: http://msdn.microsoft.com/en-us/library/aa372844(VS.85).aspx

When configuring a failover group, a Network resource (IP Address, NetBIOS name) is usually added to ensure that client machine configurations to not need to change in case of failover. During an actual failover, the network resource is moved first to the new active cluster node, and then any other resources are moved based on the failover group dependency tree. This means that during the failover there is some time during which users will receive errors when accessing SSRS since the network names will resolve to a computer where the SSRS service is in the process of starting. This is expected given the design of failover clustering.

Though a failover clustered SSRS deployment is achievable, it may not be desirable. There are a number of factors to consider:

a. Impact on other applications that share the SQL Server: One common idea is to put SSRS in the same cluster group as SQL Server. If SQL Server is hosting multiple application databases, other than just the SSRS databases, a failure in SSRS may cause a significant failover impact to the entire environment. This consideration should be carefully evaluated before deploying SSRS in the same cluster group as SQL Server. We generally recommend that SSRS should be isolated from a high availability viewpoint from the SQL Server to minimize impact.

b. SSRS failing over independently of SQL Server: Another idea is to put SSRS in a different cluster group as SQL Server. When using this configuration after a failover different cluster nodes could be running SSRS and SQL Server. If you use this configuration, ensure you have sufficient licenses to cover each cluster node with a running component of SQL Server (i.e. each RS server & each SQL Server).

c. Scalability of SSRS: in a failover cluster, only one SSRS server is handling requests. In the NLB scenario, multiple nodes are handling requests, which will result in increased capacity.

Some additional considerations:

Passive SSRS server: Customers sometimes want to know what a ‘Passive’ SSRS server looks like, or how to tell that SSRS is ‘Passive’. This has to do with the failover clustering licensing terms that come with the SQL Server box. For SSRS, since we don’t implement a cluster resource ourselves, the way to make SSRS ‘Passive’ is to stop the SSRS service. Reason for this that if SSRS is running, it is going to do work on behalf of the overall deployment so it will be ‘Active’. So if you’re looking to get the licensing benefit of failover clustering for HA with SSRS, then you need to ensure that the SSRS service is stopped on all your passive cluster nodes.

Scale-out availability in Enterprise Edition: Customers sometimes discover that they need to purchase Enterprise Edition to have a scale-out deployment of SSRS. This is important to know if you’re trying to build a failover cluster with SSRS. SQL Server offers failover clustering support with Standard Edition. This will mean that if you’re using Standard edition SSRS, you will need to handle the backup & restore of the symmetric key in the report server database in order to achieve a failover. This can be difficult since the symmetric key needs to be protected (it encrypts users names & passwords stored in the report server database). So you don’t want just anyone being able to get a copy of it and knowing the password used to encrypt the symmetric key itself. Due to this, I would say that if you want to try to do a failover cluster deployment with SSRS, use Enterprise Edition.

To summarize, failover clustering with SSRS is possible, though there are a number of considerations and manual steps involved on your part. SSRS is designed to achieve High Availability through a Scale-Out architecture. As our documentation and best practices point out, that is your best option for achieving High Availability with Reporting Services.

Take care and good luck,

-Lukasz

Building a robust setup experience with Reporting Services

Lukasz Pawlowski -- MS — Thu, 02 Jul 2009 18:39:01 GMT

This post is intended for ISVs or Product Development organizations who are planning to integrate Reporting Services into their product. Over the years I have seen a number of products do this and some get it right and some encounter issues. The part of the problem I’d like to focus on is recommendations for building your product’s setup/installation program when you know you have a dependency on Reporting Services.

When building your setup program, you most likely want your installation to be easy for your users (painless) and result in a successful, working installation for the customer. This is the right focus to have for the customer, and is achievable. However, there is risk involved in putting too many eggs in your setup basket. What follows is a broad strokes discussion of some often overlooked risks, followed by some specific recommendations at the end.

The key problem when relying on another product’s installation, especially in complex deployment environments, is that the other product may require some additional configuration settings/customization in those environments. As an ISV/Product Development org, you have limited resources. You mitigate risk by testing in various common deployment scenarios, but there will always be a few machine configurations which cause your installation to fail, or result in a non-working product.

Reporting Services has a reasonably complex deployment story. Our installation tries to make it easy, and it has gotten easier in the latest release. Since we are a web service, we regularly encounter problems with authentication due to SPN configuration, SSL certificates mis-configuration, network/firewall policies, etc. We do a lot of work to try to avoid these problems and handle them, but sometimes it just doesn’t work out how we wanted it to. A key problem for us is that patching our setup program with the current infrastructure is really hard (we essentially have to re-release the product). Let’s just say that’s an extremely costly activity :). However, patching our code after it is installed is much easier – we have a regular Cumulative Update model and an On Demand Hotfix model which allows customers facing serious issues to get patches very quickly. This cost ecosystem applies generally to ISVs who ship packaged product. Even for those who offer downloaded products, the cost of re-verifying all the deployment scenarios in setup is very costly and will be undesirable. The key lesson is find ways not to have to change setup.

So if you are an ISV building a setup program which will configure or install Reporting Services, you carry additional risk that SSRS configuration will not work which could result in your application setup rolling back. If there is one sin in building your setup experience it is rolling back an installation when it is not absolutely necessary. Remember the customer can always use the uninstall feature to remove the product if it doesn’t work. The reason you want setup to complete, even if there are failures along the way is that once the bits are installed on disk, they can easily be investigated by support personnel and it is easy to patch the installation. If you’ve ever seen the pain of a developer trying to debug a setup program or repro bugs which cause setup roll back you will be keenly aware of how much time and effort goes into setup debugging. Frankly, much of the developer’s/tester’s time is wasted waiting for setup to complete or clicking “next” in the setup UI. So there are operational benefits to your org/development cycle as well.

So what should you do? The pattern that works best for products that use Reporting Services is as follows:

1) Build a setup program that completes regardless of whether the Reporting Services configuration succeeds.

2) Build a configuration utility which can be run manually after your setup completes to configure Reporting Services.

By doing #1, you build an easy setup experience for the majority of your customers. They will just install the product, the configuration of Reporting Services will succeed and everyone will be happy.

By doing #2, you ensure that in the case of failure to configure Reporting Services, your customers have a back up plan. This lets you debug the scenario and provide fixes to workaround any issues you may encounter. It is also very useful in more complex scenarios like backup/restore, server migration, and multi-server provisioning where your customers may not actually want to run setup each time they need to provision Reporting Services to work with your product. This side benefit is worth its weight in gold from an IT cost/time perspective and is often overlooked. Ideally, you will make this utility a command line utility which will enable IT to automate their installation procedures and save themselves no operational costs.

Take care and good luck,

-Lukasz

InternalCatalogException, ASSERT, or NullReferenceException when using a Custom Security Extension

Lukasz Pawlowski -- MS — Fri, 08 May 2009 23:50:01 GMT

We have seen multiple customers with issues when customizing the Security Extension Sample (2005, 2008) that is available on http://www.codeplex.com/. This is the sample that demonstrates how to create a Forms Authentication solution with SQL Server Reporting Services.

There are two scenarios which are the primary sources of the problems:

Scenario 1: “Anonymous” access

Many custom security extension authors want to allow ‘anonymous’ access – where users do not need to login, or to allow all users some basic level of permission without needing to login explicitly. In order to do this, they change the sample code’s implementation of AuthenticationExtension.GetUserInfo to return a NULL for out IIdentity userIdentity.

However, this approach breaks the contract SSRS has with the Security Extension. SSRS in all versions (2000, 2005, and 2008) require that Custom Security Extensions specify a user name for all requests and the IIdentity returned by GetUserInfo must not be NULL.

Scenario 2: Unexpected error in Authentication Extension

The implementation of AuthenticationExtension.GetUserInfo could throw an unexpected error.

Typically this happens when the cookie returned to the extension is not found or incorrectly processed. This can be a tricky area to get right and requires some iteration.

Solutions:

Key thing for Authentication extension authors is that the error messages are hard to diagnose. We’d recommend that in your extension you fail authentication on errors and use a try-catch block to log the error message to your own log file specific to your authentication extension so that you can diagnose the state of the system when the failure occurred.

In the implementation of AuthenticationExtension.GetUserInfo, ensure you are returning a non-null IIdentity object and that UserName has a value.

Diagnosis:

In RS 2008:

We changed the error message we return to:

rsAuthenticationExtensionError - The Authentication Extension threw an unexpected exception or returned a value that is not valid: <value>.

When the authentication extension returns a null identity <value> will be “identity==null”.

If the authentication extension returns an error message from the AuthenticationExtension.GetUserInfo method then <value> will be the name of the exception.

In RS 2005 and before:

The challenge to diagnose this issue is that the call stacks can vary depending on the call being made to the SSRS server. However, these typically will include an InternalCatalogException and in the call stack you’ll see ASSERT or NullReferenceException.

If AuthenticationExtension.GetUserInfo method throws an error, an InternalCatalogException or a NullReferenceException will be in the SSRS trace log file.

If UserName is NULL, you’re going to get an ASSERT on the server side and an error will be returned to the user.

Here are a couple of examples:

Example 1: ASSERT

w3wp!library!5!2/4/2009-14:31:47:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., un-named assertion fired for component library;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details.
w3wp!library!1!2/4/2009-14:32:10:: i INFO: Call to RenderFirst( '/VP Reports/Client/Generic/AdvFinChargesByAccount' )
w3wp!library!1!2/4/2009-14:32:11:: a ASSERT: Assertion failed! Call stack:

Example 2: NullReferenceException

w3wp!library!8!06/21/2007-14:52:09:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.ReportingServices.WebServer.WebServiceHelper.ConstructRSServiceObjectFromSecurityExtension()
at Microsoft.ReportingServices.WebServer.Global.ConstructRSServiceFromRequest(String item)
at Microsoft.ReportingServices.WebServer.Global.get_Service()
at Microsoft.ReportingServices.WebServer.Global.DispatchRequest(Boolean& transferedToViewerPage)
at Microsoft.ReportingServices.WebServer.Global.Application_AuthenticateRequest(Object sender, EventArgs e)

Take care and good luck,

-Lukasz

IIS Troubleshooting for SQL Server 2005 Reporting Services

Lukasz Pawlowski -- MS — Wed, 04 Feb 2009 20:42:43 GMT

Over the years, we have seen a number of IIS related issues that cause trouble for users who deploy SQL Server 2005 Reporting Services. We have tried to resolve these in the MSDN Forums on a case by case basis. Now we have compiled the common issues with IIS into a single KB Article along with explanation of why these occur and what can be done to resolve them. Kudos to James Wu on the SSRS team for slogging through the issues and compiling them into this document.

How to troubleshoot IIS configuration issues in SQL Server 2005 Reporting Services

Take care and good luck,

-Lukasz

Several blogs I would keep on my reading list...

Lukasz Pawlowski -- MS — Fri, 23 Jan 2009 20:04:05 GMT

Recently I have had little time to devote to blogging, but I did want to pass along a couple of references that I tend to provide to customers, partners, and Microsoft internal folks as they dig deep into SSRS.

Robert Bruckner has a great blog which offers many tips on SSRS, especially focusing on report processing. The topic I tell customers to reference most often is how to use the ExecutionLog2 View for analyzing and optimizing reports.

Dean Kalanquin has just started a new blog. He's a old time SQL guy who can craft any SQL Query you can imagine. He's started a blog and has created a post on how to diagnose and trouble shoot subscriptions which I think would be very helpful, especially to those who have read my previous posts on subscriptions management.

Take care and good luck,

-Lukasz

SQL PASS Community Summit 2008 Presentation Links

Lukasz Pawlowski -- MS — Fri, 21 Nov 2008 21:03:33 GMT

Denny Lee and I presented on Wednesday at the SQL PASS Community Summit 2008 on Building SSRS 2008 Large Scale Solutions.

The slide deck is available for conference attendees at from the conference site. However, the format is PDF and during the transformation from PowerPoint to PDF many of the links were lost.

I have compiled all the links from the presentation deck below for everyone's reference:

SQLCAT.com

http://blogs.msdn.com/sqlcat

http://blogs.msdn.com/mssqlisv

http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx

Planning for Scalability and Performance with Reporting Services

Upgrading Reporting Services (SQL Books Online)

Configuring a Report Server Scale-Out Deployment

Building and Deploying Large Scale SQL Server Reporting Services Environments Series

Using Visual Studio 2005 to Perform Load Testing on a SQL Server 2005 Reporting Services Report Server

Scaling Up RS 2008 vs. RS 2005: Lessoned Learned

Predeployment I/O Best Practices

Backing Up and Restore Databases in SQL Server

Optimizing Backup and Restore Performance in SQL Server

Backing Up and Restore Encryption Keys

Deploying a Scalable Shared Database

SQL Server Replication: Providing High Availability using Database Mirroring

Database Mirroring and Log Shipping

SQL Server Replication Features

Scale-Out Querying with Analysis Services

Scale-Out Querying with Analysis Services Using SAN Snapshots

Scaling out an Analysis Services Solution

Tune IIS

Tune Http.Sys:Windows 2003

Tune Http.Sys: Windows 2008

Take care and good luck,

-Lukasz

SQL Server 2005 Notification Services Components Package RC1 Availability

Lukasz Pawlowski -- MS — Wed, 10 Sep 2008 02:23:30 GMT

As part of this blog, I comment about Notification Services. I wanted to provide you the following information about the availability of a pre-release SQL Server 2005 Notification Services components package.

If you have questions about SQL Server 2005 Notification Services, visit the SQL Server Notification Services Forum on MSDN. You can help improve the final version by submitting bugs to the Connect Feedback Center.

Take care and good luck,

-Lukasz

----------------------

SQL Server 2005 Notification Services Components Package Availability

The SQL Server 2005 Notification Services component package made available in the February 2007 release of the Microsoft Feature Pack for SQL Server 2005 is being updated to include Notification Services components, such as Notification Services server and client components, but will not include Management Studio integration. These updated components are designed to work with SQL Server 2005 and SQL Server 2008 databases.

The SQL Server 2005 Notification Services components package update will be released in two phases:

Phase 1: RC1

The SQL Server 2005 Notification Services component package RC1 is available from the Microsoft Download Center.

This release is a pre-release version and is available only for testing. Product support is not available for this pre-release.

Phase 2: RTW

Release to Web (RTW) will be released as part of the Feature Pack update currently scheduled for release as part of SQL Server 2005 Service Pack 3 (SP3). SQL Server 2005 SP3 is targeted for calendar year 2008. The SQL Server 2005 Notification Services component package update will be subject to the SQL Server 2005 Product Support Lifecycle policies.

SQL Server 2005 SP3 Notification Services

Starting with SQL Server 2005 SP3, the updated Notification Services will support using either SQL Server 2005 or SQL Server 2008 to host Notification Services Instance and Application databases.

Changes introduced in the Updated Notification Services Components Package

We have not changed core functionality of Notification Services in the components package. We made limited interoperability fixes to enable Notification Services command line tools and service components to work correctly when using a SQL Server 2008 Database Engine to host the Notification Services Instance and Application databases.

No feature additions were made.

Phase 1: RC1 - Deploying SQL Server 2005 Notification Services Components Package

The RC1 package can be installed on the same computer as an existing SQL Server 2005 Notification Services deployment. The existing deployment will automatically use the new service components installed by the RC1 package. Any Notification Services instances running on the computer should be stopped prior to installing the RC1package.

Before installing the RC1 Package, you will need to install a SQL Server 2005 Cumulative Update to enable Notification Services to connect to a SQL Server 2008 database server.

For the purposes of testing the RC1 package, the following prerequisites are needed:

1) SQL Server 2005 Notification Services

2) SQL Server 2005 SP2

3) Cumulative update package 9 for SQL Server 2005 Service Pack 2, or later

If you do not have SQL Server 2005 installed and wish to test the RC1 Package, an option is to install the Evaluation Edition of SQL Server 2005 for the purposes of testing RC1. Please note that the Evaluation Edition does have a limited license term (please see the Evaluation Edition license terms for details).

The Future of Notification Services

SQL Server 2005 is the last planned release of Notification Services. Microsoft believes “alerting” scenarios are valuable to our customers and we are looking at ways to include them in future product offerings.

SQL 2008 RTM - Where is Report Builder 2.0?

Lukasz Pawlowski -- MS — Fri, 08 Aug 2008 03:16:00 GMT

We've just released SQL Server 2008 (it feels great by the way!) and there have already been many e-mails asking us where to find Report Builder 2.0. You can find an update on the release plan for Report Builder 2.0 on the main Reporting Services Blog:

http://blogs.msdn.com/sqlrsteamblog/archive/2008/08/07/report-builder-2-0-release-plan-update.aspx

Take care and good luck,

-Lukasz