SQL 2012 RTMed last month (March 2012) with much fanfare. For us SharePoint types, SQL is a big deal, as SQL is the backbone of SharePoint. SQL 2012 has a few new or updated features that are of interest to SharePoint aficionados, but this blog post is specifically about SQL Server Reporting Services (SSRS). SSRS got a major overhaul in SQL 2012. It is finally a SharePoint service application. Anyone that has installed SSRS previous to SQL 2012 can appreciate how big of a deal that is. SSRS 2012 is better than SSRS 2008 R2 in a whole lot more ways, but as an IT Pro, the installation and management improvements take the cake.
But what if you already have SharePoint 2010 in place, and it’s running on a SQL 2008 R2 database backend? Do you get to enjoy all the gloriousness that is SSRS 2012 without upgrading your entire SQL instance to 2012? Of course you can, or else this would be a very, very short blog post. This blog post will walk you through installing the SQL 2012 version of SSRS even though all your databases are a SQL instance that is not 2012.
First, I must take a minute to thank Steve Caravajal. This blog post is all his idea. I overheard him telling someone else about this and I stole his idea. Shhhhh! Don’t tell him. He’s difficult enough to get along with without him getting a big head. Enough about Steve, let’s get back to SSRS.
The TLDR version is this; run the SQL 2012 installer on one of your SharePoint boxes. Only install the SSRS components. Then create an SSRS service app.
Here’s the longwinded version. First you need to acquire the SQL 2012 installation. You need the Standard, BI or Enterprise editions of SQL 2012 to use SSRS. Be careful with the Developer Edition, or evaluation edition. I had some issues with “Edition Mismatch” errors when I used them. It also wouldn’t hurt if you had a SharePoint 2010 farm handy, too. On your SharePoint server run the SQL 2012 installation. Instead of installing the database engine and all the other fun stuff, just install the Reporting Services bits and the SQL Server Management Studio, like this:
That’ll install all the fresh and funky new SSRS bits you want. After the installation is complete start up Central Admin and browse to the Manage Service Applications page. Click New and see what we’ve got there:
Go ahead, click it. It won’t bite. It will walk you through creating a shiny new SSRS service application. Whoopee! Let’s look at the screens:
Give the service app an appropriate name. Put your database server’s name in the server database blocks. This is the important part. That database server does NOT need to be SQL 2012. This TechNet article explains the database backed for SSRS 2012 can be any version of SQL from 2005 to 2012. You’ll also want to remove the evil GUID off of the default Database name.
Then choose the web apps you’ll be using SSRS with. You’ll see this screen:
And then this one:
Hurray! At this point you have SSRS installed, but you still need to configure it. I haven’t done much of that yet, so I’m not going to cover it in this blog post.
I do want to mention that not only do you get a UI for creating and configuring SSRS, you also get a batch of PowerShell cmdlets as well:
Here you can see running Get-SPRSServiceApplication returns back the service application I just created. You can configure SSRS with Central Admin, or if you want to show off, you could finish it up in PowerShell. Go ahead. I dare you.
Here’s another good link to help you get SSRS 2012 running.
I hope this blog post helps you start digging into SSRS 2012 sooner, rather than later.
Edited to add the Standard license