Installing SSRS 2012 in SharePoint Mode on SP2010

This is a 2 Series Post on SSRS 2012 for SharePoint 2010.  Note: SP2013 steps are the same.

Part 1 - (This Post) Installing SSRS 2012 in SharePoint Mode on SharePoint 2010

Part 2 - Importing an SSRS report and displaying it on a new environment

Part 3 Update - Installing SSRS 2012 SP1 onto a SP2013 Farm

 

Overview:  Install SSRS 2012 onto an existing SP 2010 farm.  The image below shows a 4 server SP2010 farm.  2 of the web server roles (WFE) have the "SQL Server Reporting Services Service" running.  The 2 images below show the setup we are aiming for. 

Goal: SSRS working on a SP2010 farm using SQL 2012 / SSRS 2012

Another way of displaying what I am trying to achieve is document below:

There are essentially 2 parts:
1) Install SSRS on the database server (this does not need to be the same as the SharePoint database server/s) that will support SSRS in SharePoint mode mode.  This relies on SQL Server 2012 Enterprise edition (I used SQL Server 2012 SP1). 
2) Install minimal SQL Server 2012 with the 2 features mention: "Reporting Services - SharePoint" and "Reporting Services Add-in for SharePoint Products" on each WFE that will run the SSRS role.

The image below shows the SQL Server 2012 installation required for the two roles:
1) For the SSRS server click/select all features as shown below:
2) For the SQL/SSRS role on the WFE's install the 2 features marked in red (ignore the checks on the other features). 

If you have an existing SQL Server instance you can verify which features are installed as shown below:

 

You can do this on the SQL Server and the WFE's to check the correct versions and features are being used.
Tip: Ensure the SQL Server Server 2012 versions are identical on the datbase server and the WFE/App servers.

These are the 2 SQL Server 2012 features to needed for SSRS (SharePoint Mode) on the SharePoint App/WFE servers that will run the SSRS Service (Features if you automate the SQL installs -FEATURES=RS_SHP,RS_SHPWFE):

RS_SHP	Reporting Services – SharePoint (Installed on the SharePoint WFE where the Reporting Service Application will\ be run).
RS_SHPWFE	Reporting Services Add-in for SharePoint Products (Installed on the SharePoint WFE where the Reporting Service Application will\ be run).

Provision the SSRS Service on the Farm using PowerShell:
Install-SPRSService

Install-SPRSServiceProxy

Get-SPServiceInstance -all | where {$_.TypeName -like "SQL Server Reporting*"}| Start-SPServiceInstance

Validate the installation:

• CA > Manage Servers in the farm > Verify the Service "SQL Server Reporting Services Service"is on the WFE/App server.
• CA > Manage Services on Server > Select the Server where the SSRS-add-in has been installed and > Verify the "SQL Server Reporting Services Service" service is "Started".
• Using the Discovery Reports discussed above verify that the SQL Server with SSRS (SP mode is the same SQL version as the web/app servers in the farm (all my servers are version 11.1.3000.0 - SQL 2012 SP1). 

More Info:

http://www.madronasg.com/blog/how-configure-sql-server-reporting-services-2012-use-sharepoint-2010

Validate SQL Features: http://msdn.microsoft.com/en-us/library/bb510455.aspx
https://www.nothingbutsharepoint.com/sites/itpro/Pages/SQL-2012-Reporting-Services-Gotchas-with-SharePoint-2010.aspx

Errors to Lookout for:

Problem: Install SSRS on my SP2010 farm - I get the following error:
Exception calling "Provision" with "0" argument(s): "Installation Error: Could not find SOFTWARE\Microsoft\Microsoft SQL Server\110 registry key.

Initial Hypothesis:  Originally I thought I could overcome the issue by updating the folder layout and registry on each WFE that would run the reporting role.  I could not get this to work.

Resolution:  Don't use the rsSharePoint.msi file (even the SQL 2012 SP1 version).  You need to install the 2 features (1)Reporting Services – SharePoint and 2)Reporting Services Add-in for SharePoint Products.) during/using a SQL installation.

This is a 2 Series Post on SSRS 2012 for SharePoint 2010

Part 1 - (This Post) Installing SSRS 2012 in SharePoint Mode on SharePoint 2010

Part 2 - Importing an SSRS report and displaying it on a new environment



Boundries and Thresholds for SharePoint 2013

SP2013 recommends a max of 10 million per index partition, the same as SP2010.
Streched farms are supported in SP2013.  Stefan Gobner


More Info:
SP2013 from MS SP bountries and thresholds
SP2013 Search
SQL Server IOPS Calc
SP2010 Notes of Thresholds
Comparison of SP2010 vs SP2013
Comparison of SP2010 vs SP2013
Comparison of Foundation, Standard and Enterprise editions of SP2013
 

SharePoint Host Apps - User Permissions

Problem:  How do I give users or groups permissions in a SP Host App?  This post is not looking at giving the App Web permissions against the SharePoint Site.  It merely looks at giving users rights to work within the app.

Initial Hypothesis:
SharePoint has a structure Object hierarchy that developers can secure users against namely: SPSite (Site Collection), SPWeb (Sitess/Webs), SPList (Lists/Document Libraries) and SPItem (Rows).  Based on the hierarchy I initially assumed SP Apps would maintain their own security.  Well this would get rather confusing as you would need to maintain the host web and the app web user permissions.

There is no UI in the App web to be setting security but on the UI you can get to the lists permission page.  In the SP Hosted App, navigate to the list, in my case the url is: http://dev-d8f436fea0378f.apps.dev.local/SPHostedApp/Lists/Comment/AllItems.aspx this takes me to the "Comments" list.  I click the "List" tab on the ribbon.  Click the "Shared With" button and Choose "Advanced".

Here we can stop inheriting the security permissions for the list.

I thought I could set the App webs permissions by selecting the "Web" link.  You receive an error.

Resolution:  You need to stop inheriting permissions on the Host App (parent) and then the App web inherits theses permissions.

How if works:

 

Why I like VS/TFS 2012 no I mean VS/TFS 2013

Visual Studio (VS) 2013 and Team Foundation Server (TFS) 2013 is being released in the next few weeks - that was quick.  I'm just getting comfortable with VS 2012 and the announcement comes that VS 2013 is being released later this month.

http://blogs.msdn.com/b/bharry/archive/2013/06/03/visual-studio-2013.aspx

So, this is going to be a blog on VS 2012 with TFS, my number 1 favourite new feature in VS 2012 is local workspaces.  Pre VS2012 all workspaces were Server based workspaces.  "Local" workspaces differ in that when you go offline you have version control on your local machine.  You can rollback/compare to an old version.  So for me and my laptop on client sites I find it a big improvement.  Very simply this is how GitHub works (from my extremely limited knowledge of GitHub).  You have local version control and this is synced up with the central source control repository.

Another feature worth looking at will be Release Management.  I am working with TeamCity to build my Infrastructure (PowerCli for VMware and Powershell to build SQL Server.  Autoinstaller runs the SP part).

Team Rooms are already on TFSPreview, seems like a nice idea - not sure how the takeup will be from devs.

Retrieving lookup list columns using REST SharePoint 2013 API

Problem:  I have 2 related SharePoint lists: 1) Question and a Child (linked) list 2)Comment.  I need to perform an JS call using jQuery to retrieve a specific comment item and I need to also know the Title field from it's related parent list (Question).  The 2 lists are shown below.  The post Creating related lists for SP Hosted Apps using VS2012 show you how to do this.

Resolution:
Open you browser  and check the query works:
http://dev-d8f436fea03755.apps.dev.local/SPHostedApp/_api/web/lists/getByTitle('Comment')/items(1)?$select=*,Question/Title&$expand=Question/Title
(replace the url)

jQuery Ajax code I used to call the REST API is shown below:

The ajax call is made in the document.Ready() method call.

AccessDeniedException when deploying Rdl to SP2010 Claims based site

This Post by Adam Saxton saved me hours of time today.

http://blogs.msdn.com/b/psssql/archive/2012/07/12/sharepoint-adventures-access-denied-errors-when-using-rs-2012-with-a-claims-sharepoint-site.aspx

I setup SP2010 with SQL 2012, all appeared to be working until I create a rdl and tried to deploy it into SharePoint from BIDS in VS2010.  Got the error: The permissions granted to user are insufficient for performing this operation. reporting services + AccessDeniedException.

Resolution: This is a permissions issue on the SharePoint site, I opened the url http://<sitecollection>/_layouts/people.aspx?membershipGroupId=0

I had 2 identical accounts: demo\administrator & DEMO\Administrator.  I ran the select statement explained by Adam to check the user table on the content db where the site collection sites:
SELECT tp_login, tp_title, tp_delete from UserInfo

I deleted the NT account (not the claim account) using the SP UI (http://<sitecollection>/_layouts/people.aspx?membershipGroupId=0).  Re-ran the T-SQL query and the offending NT account (DEMO\Administrator) has been virtually deleted by SP.

I can now publish the report.

More Info on setting up SSRS for SharePoint:

• http://www.madronasg.com/blog/how-configure-sql-server-reporting-services-2012-use-sharepoint-2010
• http://thebaretta.blogspot.co.uk/2013/04/sharepoint-2010-sql-server-2012.html
• http://sperryh.wordpress.com/2012/09/06/lessons-learned-from-ssrs-2012-install-in-multi-farm-environment/

 

Creating related lists for SP Hosted Apps using VS2012

Problem:  SharePoint Hosted apps need to use their own site collection (app site collection) to hold list data.  In this post I look at setting up 2 lists that are related.  To clarify what I am trying to achieve I have created an Entity Relationship Diagram (ERD) below.

Initial Hypothesis:  Provisioning fields, content types, list definitions, list instances and seed data is fairly similar between SP2010 and SP2013 within an App.  The VS2012 tooling is good OOTB and I am using a few simple xml based tweaks to get them working as I like them.

Resolution:
Open VS2012 ensure you have the SharePoint developer addin and create the Question list as shown below.

 

Amend the list to show your fields:

Hit F5 to debug the App, edit the url to view the list you created.  My url after I edited it is shown below:
http://dev-d8f436fea036f6.apps.dev.local/KB-SPHostedApp/Lists/Question


Question list is working





Repeat the list setup process in VS for the "Comments" list.  We will start from the step where you add the related look to the Posts list.

Working Result: