Tag: Integrated Mode

SSRS 2016 – Integrated or Native Mode – Which one should you use?

Published March 28, 2016

The answer to the SQL Server Reporting Services Integrated vs. Native mode question used to be very simple. Once upon a time, if you had a SharePoint environment, you would want to deploy SharePoint Integrated mode, and if you didn’t, you would pick Native. Integrated mode would leverage your pre-existing security model in SharePoint, it would allow reports to look like documents in SharePoint making them more user friendly, and you would be able to use the advanced features of the SSRS web parts in SharePoint. Non-SharePoint users were able to do what they needed around security and report storage with Native mode. Everyone was happy.

SQL Server 2012 changed that a little bit. Power View reports were first introduced in SQL Server 2012 as a part of SSRS. These reports leveraged the tabular (PowerPivot) data models available in SSAS 2012 and provided some very user friendly tools for self service analytical reporting. However, one of the catches introduced was that Power View reports were only available in SharePoint Integrated mode. Suddenly, the choice of mode became feature based. This suited those with SharePoint environments just fine, but those without SharePoint would now need to stand up a SharePoint farm just to gain access to Power View. This is a daunting prospect, especially for those unfamiliar with SharePoint. This requirement, coupled with the minimal investment into new features for core SSRS in SQL Server 2012 had the effect of making the Native mode users feel abandoned. After all, we know what typically happens when Microsoft stops investing in a product. The balance was heavily tilted in the favour of Integrated mode.

The new normal

This situation remained exactly the same in SQL Server 2014, but has changed dramatically with SQL Server 2016. SSRS in SQL Server 2016 contains significant advancements, chief among them are a new HTML5 rendering engine, a new report portal, mobile reports, and (soon) Power BI Desktop rendering. This is fantastic news, but it also changes the game significantly with respect to the Integrated/Native mode decision. With SSRS 2016, most of the new investments are in Native mode only – the balance has shifted. The table below shows an (incomplete) list of new features, and their supported modes.

Feature	Integrated Mode	Native Mode
HTML 5 based rendering engine	X	X
New chart types	X	X
PDF based printing (no ActiveX)	X	X
PowerPoint rendering and export	X	X
New UI for Report Builder	X	X
Customizable parameters pane		X
New web portal		X
Mobile reports		X
KPIs		X
Pint to Power BI		X
Render Power BI reports*		X

* Coming soon

You can see above that the balance has shifted very heavily in favour of Native mode. The folks using Native mode are very happy about this move – they are no longer having SharePoint forced on them in order to access new features. However, now it’s the SharePoint folks turn to feel abandoned, but they really don’t need to. SSRS Integrated mode is still getting a significant enhancement in 2016, it’s just not as significant as the improvement to Native Mode. Integrated mode is also still required for rendering Power View reports. Last fall’s Reporting Roadmap reconfirmed Microsoft’s commitment to SharePoint as a platform -“We will continue to support embedding of BI content into SharePoint”. SharePoint has a bright future as an report destination. The only question is how that will be brought about.

It may well be that the features had to go into Native mode first in order to meet the shipping schedules, and that they’ll be brought along eventually. I suspect however that this is not the case. I think that this is either the last, or penultimate version of SSRS to contain Integrated mode. If the same level of embedding into SharePoint could be provided by Native mode, and the user experience improved (as it has been in the new report portal) then there is very little real need for Integrated mode at all.

Building shared service applications in SharePoint is a non-trivial task, and those resources could likely be better spent on features for SSRS. A new embedding model could support both SharePoint on-premises (as it currently does) and SharePoint Online (as it currently doesn’t). The same mechanism could be used to embed Power BI reports. We’ve already seen glimpses of this hybrid interoperability in the SSRS and Excel pin visual to Power BI capability. I suspect that over time we’ll see SSRS Native mode and its reporting portal also assume the role currently played by PerformancePoint Services as well. For all of these reasons, I think that SSRS Native mode is the only future for SSRS.

But that’s the future. What about the present?

When I first learned of these developments, I suspected that I would be recommending Native mode for anyone moving forward. However, as I discuss in an earlier article, the SSRS web parts for Native mode are deprecated, and missing key pieces of functionality, parameters being first among them. They are really little more than iframes, and they certainly can’t replace the Integrated mode web parts. If you’re going to use reporting in SharePoint in any meaningful way, or you are looking to upgrade an existing SharePoint farm with SSRS integration to 2016, you’re going to need Integrated mode. That means no mobile reports, report manager, or Power BI integration.

So why choose?

There is nothing stopping you (apart from possibly licensing) from running both modes. Using Integrated mode, you can take advantage of the new rendering engine, etc, and a separate Native mode server can be used for Report Manager, mobile reports, and Power BI integration. Over time, more reports can be brought over to Native mode and the embedding story improves. Once they are all brought over in “the future”, the Integrated mode service can be simply removed. This provides for a smooth, gradual migration. In fact, you can set up an SSRS 2016 Native mode server along side an existing SharePoint 2013 farm with SSRS 2014 or earlier Integrated mode to get started. Your SharePoint reports won’t have any of the new features, but your Native mode certainly will.

We are clearly in a transitional stage when it comes to on-premises reporting technologies from Microsoft. There are significant, bold steps forward, but there is also a legacy of technology to support. The current lineup of technologies allows for both approaches for organizations to embrace at their own pace.

14 Comments

Migrate Reporting Services from Native Mode To SharePoint Integrated Mode

Published April 26, 2012

I have previously written about upgrading and moving Reporting Services to SSRS/2008 R2/SP2010, and also on upgrading to the new Service application in 2012. Both of these deal with moving prior versions of Reporting Services running in SharePoint mode to more recent versions, also running in integrated mode. What has been lacking from Microsoft until now was a mechanism to help move an organization from Reporting Services in Native mode to Reporting Services in integrated mode.

The solution to date has been to go back to the source projects in BIDS and redeploy them to the Integrated Mode server. This of course assumes that BIDS was used for report design (not Report Builder), and that the projects are available. You also lose all server side configurations (like subscriptions) with this approach.

On Friday, April 20 2012 Microsoft released Version 1.0 of the Reporting Services Migration Tool, which allows you to do just that. It’s a high level tool that brings all of the artifacts out of the Native mode instance, and at a later point in time, import them into the Integrated Mode instance. Ultimately, the stated aim of the tool is to allow a file system level backup of your Reporting Services Instances, be they Native or Integrated mode.

It can be run either by command line, or through a GUI. A snapshot of the GUI screen can be seen below.

The tool is definitely version 1, and has several limitations which I’ll outline below, but it does work. It does so by connecting to either the WMI provider, or the Reporting Services web services, then extracting all of the available content, and then building a PowerShell script which can be run to place the backed up content in a SharePoint document library that has been properly configured to support the Reporting Services content types.

Operation of the tool is relatively straightforward, and is adequately documented on the download page, so I won’t go through a step by step, but I do want to share a few observations.

Firstly, migration is from Native Mode to Integrated Mode only. The stated objective of this tool is to support both modes on either end of the migration path, but for now it’s a one way trip. For the moment, it does limit its ability to perform as a backup tool. However, if you examine your output folder, you’ll find all of your report files, connection files (etc), so if you’ve built your reports with BIDS, and lost the original source project, it’s a great way to get them out of the Reporting Services database.

I have also been unable to get the WMI provider to work at all. I’ve tested with both SSRS 2008R2 and SSRS 2012 Native mode sources, but the tool can’t seem to find the WMI instance. The tool still works in this configuration, but it will not back up passwords or history snapshots. I’ll update this post if/when a solution to this can be found.

UPDATE – Thanks to Tristan in this MSDN forum thread – The WMI provider is working. I have added the paragraph and image below.

The Instance Name field is mislabelled. It should be SERVERInstance for non default instances, or just SERVER for default instances. Unfortunately the nowhere in the help is the requirement for SERVERNAME mentioned. Essentially, you should treat this field the same as you would the Server field when connecting via Management Studio. The image above has been updated to show the correct value for Instance Name (In this case, although not necessary, I have included the name of the default instance).

As outlined on the download page, the tool does not back up Reporting Services security information, or role information – which makes sense when moving to a new security model. Also, linked reports aren’t supported in Integrated mode, so they’re not backed up at all.

For a complete list of constraints and instructions, visit the download page.

For it’s limitations, this tool is a very welcome addition to the toolkit. Migrating from Native Mode to SharePoint Integrated mode Reporting Services no longer needs to be painful.

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.

Deploying Reporting Services Reports to SharePoint using Business Intelligence Development (Visual) Studio

Published May 19, 2010

If you are using BIDS to develop reports for Reporting Services in SharePoint Integrated mode, you may find some of the deployment options somewhat confusing. Paths in Native mode must be relative, white in integrated mode, they must be absolute. To get to the deployment options, you right click on the project from the Solution Explorer window, and select Properties.

The highlighted areas are the ones that we need to be concerned with. The TargetServerURL property is the most important of the bunch, as you are essentially telling Visual Studio where to find the Reporting Services Web Service. The value that you select here should be the root of the site collection where the report is contained. Basically, because the SharePoint front end is now the report server this makes sense, and the property makes sense in native mode, but for integrated mode this property should be called TargetSiteCollectionURL.

The xxxFolder parameters all behave the same way, and they should contain the complete path to the container for each one (which easily could be the same value). The path should include everything including http, the site collection,the path to the site,the library, and if used, the SharePoint folder. In integrated mode, if you replace folder with Library, or even better, path, this will make more sense.

5 Comments