PowerShell, SharePoint, SQL Server, Technical

SSRS, SharePoint 2013, and SQL 2012 Standard Edition

When building out a new farm using SQL 2012 Standard and SharePoint 2010, a multi-server farm deployment with a dedicated SSRS server was possible. In SharePoint 2013 with SQL 2012 SP1, not so much possible, but with additional “gotchas”. To be clear, here’s the architecture:

image

SP-WFE1 and SP-WFE2 are just front ends, so really the three boxes we care about are SP-APP1 (running central admin), SP-SSRS (where we want SSRS to run), and our SQL box.

Following these guidelines from TechNet and this blog article pointing out some gotchas, we can extract the gist of what the steps are:

  1. Install SQL Server 2012 SP1 on SQL box
  2. Install “Reporting Services – SharePoint” and “Reporting Services Add-in for SharePoint Products” on SP-APP1 and SP-SSRS
  3. Install “Reporting Services Add-in for SharePoint Products” on SP-APP1 (don’t install the “Reporting Services – SharePoint” component)
  4. Install “Reporting Services – SharePoint” and “Reporting Services Add-in for SharePoint Products” on  SP-SSRS
  5. Run  Install-SPRSService and Install-SPRSServiceProxy on SP-APP1 and SP-SSRS
  6. Start the “SQL Server Reporting Services” service on SP-SSRS
  7. Create a new SQL Server Reporting Services service application

The result is a new service app, but it will throw an error when you try to access it’s settings through Central Administration. Viewing the ULS error, it will tell you that it received a 503 error when calling the reporting web service on the app server. Well, that makes sense because we didn’t start the service on the app server, we want it to run on our SSRS server. If you look in IIS on the app server, the reporting site doesn’t exist. If you look in IIS on your SSRS box, it will exist. The URL will be something like http://sp-ssrs:32843/{GUID}/ReportingWebService.svc, with the GUID getting generated when you start the SQL Server Reporting Service.

So now we’re in quite a quandary. If you try to start the service on your app server to create the reporting web service, you’ll get an error stating that the version of SQL doesn’t support a scale-out farm. It seems like SharePoint is looking at servers in the farm with Reporting Services installed on it, regardless of whether the service is running on that server, when checking licensing. Well, we can’t uninstall Reporting Services from our app server since we would then lose the ability to create a service app, so we’re screwed.

Taking a look at the feature comparison, Standard definitely does not support a scale-out deployment. However, unless I’m misinterpreting the definition of farm scale-out, that’s not what this architecture is. Since we are trying to run Reporting Services on a single instance in the farm, this should be covered by Standard license.

Bummer. So, for now, we can’t do the above architecture using Standard. Maybe I’m wrong and I’m missing a configuration step somewhere. If so, please chime in.

—————————————————————————-

By the way, there are two versions of SQL Server 2012 with SP1 installs out there. One says it’s SP1, but doesn’t really install SP1. At some point, this was resolved so the latest package does install SP1 as it should. After you install SQL, you should see a version of 11.1.3000.0 or higher. If you don’t, you still need to install SP1 over your install. This goes for SQL Server as well as the SQL components you install on your SharePoint servers. The latest installs can be found here, with either a slipstreamed ISO or a SP1 executable.

If you miss this and get a new SSRS Service App created, it will throw an error. ULS logs will show a 500 error, and when navigating to the reporting service URL you will see that it can’t read the configuration file. Diving into the 15 hive, you will notice that there is no “Reporting” folder under “15WebServices”, which is where the IIS site is looking for it’s configuration settings. There will be one in the 14 hive. After installing SP1 on the SharePoint servers you will see this folder and know you’re good to go.

<UPDATE>I was able to get the above architecture configured correctly by making the changes noted in the post. Turns out the trick was to not install the Reporting Services – SharePoint component on the APP box. Thanks go to my co-worker Mark for helping me with that one.

One thought on “SSRS, SharePoint 2013, and SQL 2012 Standard Edition

  1. I followed your steps for a farm with 2 servers: 1 SP 2013 all roles, and 1 ssrs. After I provisioned the SSRS Service Application I would get a 503 error when navigating to the settings for the service app. To fix this I had to use the following commands on the SP server:
    Install-SPRSService -uninstall
    Install-SPRSServiceProxy -uninstall
    Even though the service is not started on the WFE/App/sql server it had to (in my case) be removed to function. This is a bizarre undocumented fix.

Leave a Reply

Your email address will not be published. Required fields are marked *