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.
I had a question . Can the existing SharePoint 2010 farm be left running on SQL Server 2008, and you just put the new components (SSRS 2012) on a new farm application server pointing to the SQL Server 2012 server instance for storing the new reporting catalog databases. Thanks
@Tory – Absolutely yes. In fact I tested that out with the RC0 version just before release.
What about report deployment using Sql Data Tools (formerly known as BIDS) ? Due to architectural change, reporting services configuration management is no longer used… Correct me if I’m wrong, but now we no longer have a report server url for sharepoint integrated mode…? In my environment, I’m having this problem. I can no longer deploy using Sql Data Tools, it seems it relies on a report server url to do the deployment. It keeps asking for reporting services login credentials, log says “/ReportService2010.asmx does not exist”… Any clues?
John, you are a genius! I’ve been looking for this exact scenario for days!!! Thanks so much!
We have Reporting Services (integrated with Sharepoint)on a separate server from the Sharepoint farm. Right now both servers have SQL Server 2008R2 sp1, enterprise edition.
Would we have to upgrade the SQL Server to 2012 on the Sharepoint server first, then upgrade the SQL Server on the integrated Reporting Services server after?
Or can we just upgrade the SQL on the Reporting Services server?
Should all SQL services be upgraded, that is Database engine, Analysis, Integration.
Thanks very much.
I’m a little confused… You can’t have SSRS on a separate server from the farm if it’s running integrated, but to answer your question, the order doesn’t matter. Running 2012 under Reporting Services only and 2008R2 for all other SQL is a valid configuration.
Renato – Not sure where your problem lies – you should be able to deploy with Data Tools just as you did with BIDS previously. I certainly can here. Any other error codes/messages?
Thanks for your response. I guess I’m confused too. We do have Reporting Services on a separate server from the one that has the Sharepoint databases. SQL Server 2008R2 is installed on both servers, they are both part of the farm. Reporting Services on the separate server is integrated with Sharepoint on the other server.
Maybe I’m misunderstanding something.
My question still stands as to whether I need to upggrade THE SQL to 2012 on both servers.
Thanks again for any help.
@Denise – The answer to your question is no – you only need to upgrade the RS instance.
[…] few months ago I posted an article discussing how to upgrade integrated mode Reporting Services from 2008R2 to 2012. That article […]
Renato,
Did you ever get your BIDS report deployment issue resolved?
This link looks like a great resource for this problem, but I am not sure if you have come across this and tried it.
Let me know where you are at with your problem. I am about to install SSRS 2012, but only if I am confident I can deploy reports to it from BIDS 🙂
Renato,
Did you ever get your BIDS report deployment issue resolved?
This here looks like a great resource for this problem, but I am not sure if you have come across this and tried it.
Let me know where you are at with your problem. I am about to install SSRS 2012, but only if I am confident I can deploy reports to it from BIDS
We are upgrading from MOSS 2007 – SSRS 2005 to MOSS 2103 – SSRS 2012. Can we do a fresh install of SSRS 2012 and then some how link to old database? Or how should we go about it?
Hi,
First of all i would like to thank you for this nice article. Got to learn a lot.
I have watched one of your videos on “How to Setup Reporting Services and PowerPivot in Sharepoint 2010”. This is the url to that video http://www.youtube.com/watch?v=E5D8cSJDkf8
i was litllte confused on a step at 21:32 where you create that SQL Server reporting Service application. The database server name that you use in creating that service application is “SharepointSQL” . My question to you is :is this the same server, in this case you were installing the reporting server on the same app/web server or the actual sharepoint sql server where all the content database resides. Also where do the database for reporting services reside? Is it on the the app server as you have installed reporting service instance or just the sql server where all your content database are? There are two server name that you use one is “SharepointSQL” and the other is “SQLSRV2012RC0”. Thanks,
Al
Thanks Al
The SharePointSQL server in the video is an alias for the SQL server that is hosting the SharePoint databases. It actually doesn’t really matter where the databases reside, they just need to be on a server that’s available to the SharePoint server. I normally find it most convenient to put them on the same server as the rest of the SharePoint DBs.
Hello,
I have SSRS 2008 R2 with SharePoint 2010 integrated mode in my current environment. We are trying to upgrade it to SSRS 2012 with SharePoint 2013. I have already configured my SP 2013 environment – created a new service application etc. I just need to move the data sources, datasets and rdl files from 2008R2 to new farm with SSRS 2012. Is there a way I can attach the database and change it in my service application? Thanks a ton for your help.
I’m wondering if I need to install the latest SSRS add-in or will the existing add-in from 2008 R2 work.
HI John ,
Thank you for such a brilliant article.
I wondering if i have ssrs 2012 and want to upgrade to ssrs2014 but still on sharepoint 2010 farm.
What are my options?
I need to also keep downtime to a minimal and at the same time try my best not to request for more hardware.
According to some posts I’ve read,I cant do side by side because its not supported but they also go on to mention that they would be changing the farm as well and i don’t want o do that.
Im getting really confused.
Please help.
Kind Regards
D