Category: SQL Server

Upgrading SQL Server Reporting Services to 2012 In SharePoint Integrated Mode

Published March 18, 2012

That title could actually be longer….

SQL Server 2012 brings with it a number of key Business Intelligence features that apply directly to SharePoint environments. One of the major improvements is the way that Reporting Services installs. Prior to version 2012, when running in SharePoint integrated mode, it installed along side of SharePoint, and connected through an add-in. With 2012, it is now a full SharePoint Service application, with all of the associated benefits that brings.

It is simple enough to set this up on a new farm, but what about organizations that are already using SSRS in integrated mode? Since I was unable to find any prescriptive guidance on the upgrade process, I ran through it on a test farm, and below are my findings. This describes the process of upgrading from SSRS 2008 R2 to the RTM version of SSRS 2012.

SQL Server 2012 has some relatively strict operating system requirements. First and foremost, you need to be running at least Windows 7 or Windows Server 2008 R2 Service Pack 1, or Windows Vista or Windows Server 2008 Service Pack 2. If not, you’ll get the following message immediately.

In addition, depending on what you’re upgrading, it’s pretty fussy about your source environment as well. For example, if you’re upgrading management tools or BIDS, and you already have Visual Studio 2010 installed, it will need to be at least at the Service Pack 1 level. Your source SQL Server also has specific service pack requirements. The complete supported upgrade matrix can be found here. Unfortunately, if these requirements are not met, the installation will fail much further along in the process, and you’ll need to repeat several steps after correcting.

Once the SQL Server Installation Center launches, you’ll want to pick the Installation tab, and then the Upgrade option.

After a few steps, you’ll encounter one of the new screens pertaining to Reporting Services.

Previously, the installer was totally unaware of Integrated Mode Reporting Services. You would use SSRS configuration to set it up, but now the upgrade wizard, as well as the full product installer, is fully aware of Integrated Mode.

When performing the upgrade, the installer will go ahead and create the SharePoint service application for you. This is different than when you perform a fresh install – in that case you manually create the service application after installation. However, in order to do so, it needs to create an application pool for the service application, and you will be prompted for the credentials of that pool.

After several more standard screens, the upgrade rules will be run. This is where you will find out if you are missing a prerequisite, or it is not at the required patch level. However, if all is good, all of the rules should show as Passed, with the exception of “Direct Browsing to Report Server”, which will show a warning.

Previously, if you knew the correct URL, you could navigate directly to Reporting Services and the reports stored within SharePoint through a very rudimentary interface. This warning is simply alerting you to the fact that this is no longer an option with 2012.

The remainder of the installation is straightforward. When done (and if successful), you can navigate to the Service Applications section of Central Administration. There, you should see the new SSRS application.

I’m not a big fan of the name that the default upgrade uses for the application, but that’s simple enough to change. The important thing to note is that all of your subscriptions, snapshots, etc, will have migrated over. The upgrade upgrades the two Reporting Services databases (ReportServer, and ReportServerTempDB by default), and adds a new one, ReportServer_Alerting, which are all used by the service application.

In order to enable data alerts and subscriptions, a number of security modifications need to be made to the SQL Server. In addition, the SQL Server Agent must be running to use these features. Editing the Service Application shows a screen that has a link to Provision Subscriptions an alerts. Clicking through it reveals the following screen:

The View Status section simply gives you an indication of whether or not the Agent is running on the server, but clicking the “Download Script” button will give you a SQL script that will set up the required roles and permissions on your SQL Server. This script must be run on the SQL Server that holds the Reporting Services databases. In order to run it, simply open up SQL Server Management Studio, connect to the server, and click the New Query button. Once the query window opens, paste the query in, and run it (the Execute button).

Finally, enter the credentials for SharePoint to connect to your SQL Server Agent, and click OK. Once done, you’ll be in a position to use the new features available in Reporting Services, and all of your existing investments in reports should continue to operate as before.

For a major architectural change, this is actually a pretty smooth transition.

As I post this, it’s quite early in the life of 2012, so I would be quite interested to hear of any other experiences or gotchas. If there’s something that I should add to this post, please post a comment, I’d love to hear about it.

18 Comments

How To Upgrade SQL Server 2012 RC0 to Release (RTW or RTM)

Published March 14, 2012

If you’ve been working with the RC0 version of SQL Server 2012 (formerly code named “Denali”), you are likely aware that the final release is now available as an evaluation download here, or from MSDN with in the Standard, Business Intelligence, and Enterprise editions.

If you want to a straight upgrade from the evaluation edition, it’s a relatively simple process. From the installation screen, select the Upgrade from a previous version of SQL Server option (it doesn’t list RC0, but it is the same process).

Select all of the appropriate options, and your server will be upgrade. However, if you want to move from the RC0 to one of the licensed editions, you could run into trouble. In my testing, what has happened is that parts of the installation will upgrade properly,, but others will fail. This can be seen from the instances screen when upgrade is run for a second time.

Unfortunately, the “left behind” instance can not be upgraded further, and now that this has been run, the Edition upgrade function will fail to work. In one case, I needed to do a complete SQL uninstall and then a reinstall. I’ve had more fun. The good news is that the reinstall did work.

The way to avoid this is to do the Edition upgrade first. If you’re unfamiliar with this, you can find it on the Maintenance tab of the SQL Server installation Center

Once you complete the edition upgrade, go ahead and perform the standard upgrade as you would with the evaluation version.

Using SharePoint Filters with Reporting Services Parameters for Personalized Reports

Published February 10, 2012

Using Reporting Services integrated with SharePoint has some tremendous advantages for report re-use. In many cases, a single report can be designed, and then placed on contextual pages for customized display by setting the report parameters through the Reporting Services web part. Filter web parts can also be used to drive the Reporting Services parameters, and finally, users can set the parameters directly if allowed.

However, what happens when we want users to be able to set some of the parameters, but not all of them? This can be achieved through a combination of filters and web part settings.

Consider the following report:

Our requirement is to place a Reporting Services web part on a page that will display the report for the currently logged in user. In addition, the user should be able to select the time period to display, but the default for the time period should be the current year. Finally, users should not be able to use this report to view the submissions of other employees.

We can accomplish this firstly by adding three parameters to the above report – Start Date, End Date, and Employee. We will then restrict the results returned to the report to those meeting our parameters. Our main dataset query is as follows:

SELECT
ExpenseTotal,Employee,ExpenseType,SubmittedDate
FROM
Expenses
WHERE SubmittedDate BETWEEN @StartDate AND @EndDate

AND Employee= @Employee

Ideally, we could use SharePoint filters to set the value of all three parameters (details on this below…), and then completely hide the Reporting Services parameter pane from the users. Unfortunately, the date filter in SharePoint doesn’t quite meet our requirement.

We can explicitly set the default value, or make it a function of the current date, but what we can’t do is to set it to the first day of the current year, last day of the current year, etc. True, on Jan 1 we could go to the page and edit the parameters of the filter, but we’re looking for something a little more elegant than that.

We can get these sorts of values through SQL Queries, so what we’re going to do is to set the default values of the parameters with a Reporting Services Dataset. The dataset query is:

SELECT 
  DATENAME(Year,GETDATE())+'0101' as StartDate, 
  DATENAME(Year,GETDATE())+'1231' as EndDate

This basically retrieves the current year and then appends Jan 01, and Dec 31 respectively to return the values in YYYMMDD format. These values are then set as the defaults for the start and end date parameters respectively:

We will be setting the value for employee with a filter, so we don’t need to set its available or default values.

Once this is done, and the report is saved, we’re ready to add the report to a SharePoint page. To do this, simply navigate to the destination page and edit it, then insert a Reporting Services Web Part on the page. Open the web part tool pane, select the report, and set all of the View/Display options that you want to use. You will need to make sure that the prompt are is displayed, as you will need it for your users to change the date ranges. However, you can start it collapsed.

Finally, you’ll want to load up the report parameters section

You should not that all of the parameters need a value for the report to render – hence the “Enter A User” value, which will return no records. At this point, we can save the web part and test the form by entering an employee name in the Employee parameter field.

The next step is to add the Current User Filter Web Part to the page (in the Filters section of the web parts list), and configure it. It’s a context filter, and it won’t display to the end user, so you can put it anywhere on the page that you wish.

You can send either the user’s Active Directory account name, or the value of any of the User Profile fields (as an aside, this is one reason that the user profile is really important…). In our case, the back end data is in a “First Name Last Name” format, and this corresponds with the Name property of the User Profile, and is therefore what we’ll use.

Once configured, we save the web part, and then we need to connect it to the report. We do this by accessing the Web Part Edit Menu, hovering over Connections, and selecting “Send Filter Values To”

We then select the report, and the parameter to set:

Once connected, the report should be using the name of the currently logged in user, in our case, Olaf Franz.

In the above screen I have opened the parameters pane (I have it closed by default) to show that users can change the dates, but by navigating to this single page, a user will see their own entries for the current year. However the important thing to note here is that although “Employee” is a parameter, it no longer appears in the parameters pane. This is due to the fact that its value is being set by a SharePoint filter, and it has the added benefit of allowing us to prevent users from seeing other users’ reports.

This is a perfect example of where the personalization capabilities of SharePoint can be combined with the analytic power of Reporting Services for a result that’s truly greater than the sum of the parts.

2 Comments

Upgrading and Moving Integrated Mode Reporting Services With SharePoint

Published December 19, 2011

There is a plethora of instructions out there on upgrading from SharePoint 2007 to SharePoint 2010, but relatively little on doing the upgrade where Reporting Services has been set up in SharePoint integrated mode. Given that there are a few gotchas that you can run into when doing this, I decided to put together this step-by-step, complete with the gotchas.

The most common scenario that will be encountered, given the vintages of the products will be an RS upgrade from SQL Server 2005 to 2008 R2. In addition, the in place upgrade is relatively painless (in the short term….) so I’ll be walking through a DB attach upgrade, which is just as applicable to RS as it is to SharePoint. Finally as I’ve written about previously, in a small farm, it’s likely a better idea to add the Reporting Services bits to a SharePoint front end server, than to add the SQL server to the SharePoint farm, and that will also be a part of our scenario.

The first question to answer is “why bother”? One of the advantages to using RS in SharePoint Integrated mode is that unlike Native mode, the reports, data connections and models are stored directly in SharePoint. It is therefore possible to just create a new RS database, and move forward. However, since subscriptions, schedules, and cache profiles are still in the database, it’s likely worth it to do the upgrade.

Step 1 – Back Up The Asymmetric Key

Reporting Services itself uses 2 SQL databases. One of the databases is for temporary operations, but the other database stores a number of important, and sensitive items for this reason, all sensitive items in the database are encrypted with a key. If we want to get access to these items, we need the key. To do so, we need to back it up from the source server before we move ahead.

Run the Reporting Services Configuration Manager on the source RS server, and select “Encryption Keys”. Click the Backup button, select (and remember) a password, and then save the key to the file system.

Once saved, copy the key file to the destination RS server (likely your SharePoint 2010 front end server).

Step 2 – Back Up the Reporting Services Databases

Run SQL Server Management Studio on the Server where the Reporting Services databases are located. Run full backups of the two RS databases. When complete, copy the backups to the destination SQL server (likely the server that will host the SharePoint 2010 databases).

Step 3 – Restore the Reporting Services Databases

Using SSMS, restore the two databases to the host SQL server. Once restored, it’s likely a good idea to set the recovery model to Simple, and the Compatibility level to SQL Server 2008. These steps aren’t required, but are recommended, unless you have a reason for not doing so.

Step 4 – Run the RS Configuration

If Reporting Services hasn’t yet been installed on the SharePoint server, do so, otherwise, proceed to configuration by running the Reporting Services Configuration Wizard on the destination RS Server. Configure the basic steps, and then when it comes to Database configuration, select the option to choose an existing database.

Select the server where you restored the files in step 3, and select the primary RS database (the one without the word “Temp” in it”).

Complete the configuration wizard.

Step 5 – Connect SharePoint to Reporting Services

From SharePoint Central Administration, select General Application Settings, and then Reporting Services Integration.

Complete the integration configuration, and then select OK

So far so good – now we’re ready for some gotchas. If you now click on the “Set Server Defaults” link in the Reporting Services section, you likely get a rather nasty looking error. You’ll also experience this error if you access ewither of the two RS URLs defined in the RS configuration wizard. The error is:

The report server installation is not initialized. (rsReportServerNotActivated)

This error happens when the server can’t access configuration information, and the most common cause of that is that it can’t decrypt the content. In our case, it can’t because we haven’t yet restored our key.

Step 6 – Restore the Asymmetric Key

On our new RS server, we need to run the RS Configuration Manager, Select Encryption Keys, and then click the Restore button. You will be prompted for the file that you created and copied in Step 1, and this is where remembering the password comes in very handy.

Once this is done, we can close the configuration manager and return to Central Administration. However, now when we try to access access any aspect of RS we get a new error:

The feature: “Scale-out deployment” is not supported in this edition of Reporting Services. (rsOperationNotSupported)

The reason for this error is that when we restored the key, it added an entry in the Keys table in the Reporting Services database, causing RS to think that we’re using multiple Reporting Services servers. This is what’s known as a scale-out deployment, and is only supported in the Enterprise version of Reporting Services. Obviously this isn’t a problem for anyone running Enterprise, but if not, it’s a showstopper.

The way to fix this is to remove the old server entry in the Keys table. Using SQL Server Management Studio, connect to the Reporting Services database, and open the dbo.Keys table. The old entry should be easy to spot as it will have the old server name. Simple delete the row.

Once the offending entry is deleted, RS should be good to go.

7. Fix up the content type names

I have posted about this already, but often, an upgrade will break the Content Type names for the Reporting Services content types. Just follow the steps in this post to clean them up.

8. Reconnect Reports with Data Sources And/Or Republish

In addition, moving connection files and reports around in SharePoint can cause them to be disconnected from each other, or for the connection files to be disabled. It’s a good idea to navigate to all of your reports to make sure that they are connected, or better yet, to republish from the source if you had previously used BIDS to publish reports.

Reporting Services Reports With Sparklines Running Very Slow

Published November 14, 2011

I recently encountered a fairly significant performance issue with SQL Server Reporting Services 2008 R2 and Reporting Services Azure. After having built out an electoral report that broke down election results poll by poll, I used what I think is a fantastic new feature in Reporting Services 2008 R2, sparklines.

A sparkline is essentially a mini-graph – a visual representation of a single row of data. In my case, it is the results, by party (as indicated by colour) for a given poll. It looks like this:

Once I got this report looking the way that I wanted it to, I deployed it to Azure Reporting Services. However, when I ran the report, it took an incredibly long time to load (3 minutes and 30 seconds). I immediately blamed Azure RS for this, as it’s still a preview edition. However, further testing revealed the same behaviour on an on-premise deployment. The puzzling thing was that it rendered very quickly using the preview in BIDS or in Report Builder.

Adding to the mystery is the fact that the report renders fairly quickly when called from the Reporting Service web service to create a PDF file, or even an HTML file. I managed to discover this when I decided to pre-create a number of these reports in PDF format to reducre the load time for users (more on how I did this in an upcoming post).

The oddest part is precisely where the performance problem shows up. When the report is run using a browser, the browser thread’s CPU utilization goes up to 100% of available resources (a 2 core machine CPU runs up to 50%). This is happening on the client side.

On a hunch, I tried removing the sparklines. Presto, the load time dropped to 20 seconds. So I don’t get to use my sparklines in the live report which is unfortunate, but at least I found the culprit. What I wish I knew was why this was happening, or if there’s something I’m simply doing wrong.

If anyone has any ideas, I’m all ears.

2 Comments