Getting through a SharePoint 2013 BI Farm install is a complex and daunting process for even the very best Microsoft BI talent in the world. It is not easy…period. Each and every time I go through a SharePoint BI installation process, I promise myself that it will be the last one for a very, very, very long time.
Last year at this same time I was setting up SQL Server PASS Summit key note SharePoint BI demo machine installations and also the Microsoft BI booth demo machines. I think I set up SharePoint 2013 BI machines about 50 times in the past two years with all the various SQL Server 2008R2, 2012 CTPs, Office 2013 and SharePoint 2013 releases. For the most part, I usually set up “All-in-One” machines for demos – meaning databases reside on the same SharePoint machine to avoid Kerberos Delegation configuration that is required for double-hops. “All-in-One” machines take about a day or two max to set up and are not too rough. However in the real world, a distributed SharePoint BI Farm is typically what would need to be installed… and that my friends is a another story, level of complexity and a bit larger project.
If you are considering a new SharePoint 2013 BI Farm installation, first read Kay Unkroth’s incredible white paper to understand SharePoint BI Security. It is a variation of the previous, infamous Carl Rabeler’s paper on a related topic and several SQL CAT articles on Microsoft BI with Kerberos Delegation. Then watch Rob Kerr’s video series – a must see!!! Finally start reviewing the TechNet docs SharePoint 2013/SQL Server 2012 SP1 BI Installation Overview and Process, Supported Combinations of SharePoint and Reporting Services Components, (TIP! For SharePoint 2013, you can ONLY use the SQL Server 2012 SP1 version of the Reporting Services add-in for SharePoint), take notes on your specific environment and collaborate planning this SharePoint 2013 BI Farm installation with both your IT and database operations folks.
With each SQL Server, Office or SharePoint release, the SharePoint BI install process changes and/or new steps are added. There are also a myriad of little BI bugs that sneak in usually with neglected PerformancePoint. SharePoint CUs also tend to break BI features – beware of that and be extra diligent when choosing to apply a SharePoint CU to your BI Farm. In SharePoint 2007 days, BI was a very hacky install. In SharePoint 2010 days with the introduction of Power Pivot, it was still hacky, web.config file tweaks were common, you had to run and rerun installs, install SSRS msi’s, ADOMD drivers and on and on…it got more complex. In 2013, a few parts of the Power Pivot and SSRS BI installs have improved but the growing list of little things to install for BI now makes this the longest installation process of all SharePoint and SQL Server releases thus far. Allocate at least a week or two for up to a four machine SharePoint BI farm build. I have heard of some SharePoint 2013 BI Farm installations taking much, much longer. It really depends on your environment.
In preparing for this past SharePoint 2013 BI Farm installation with the latest SQL Server 2012 SP1 release and SQL Server CU4+ for DAXMD Power View, I noticed that there were no instructions for a multi-server farm installation on TechNet – only single machine installs. When I reached out to my former peers at Microsoft they informed me that there had been some staffing/budget reductions and they weren’t able to do the level of documentation that they used to do for BI. Since this group and my BI peers over there have always been kind to me, I promised that I would share the SharePoint 2013 BI Farm steps in a blog. They know it is tricky and we have all been on calls together with client broken installs before…if you get it wrong, there might not be an easy repair. So don’t take it lightly.
Minimum Steps for Setting Up a SharePoint 2013 BI Farm
with SQL Server 2012 SP1 CU4+
Note that this is NOT the official blessed install process – one does not seem to exist. However, treat these steps as a running start outline to get you prepared. Also keep in mind that each step listed here has related TechNet articles that have many other little steps within the specific higher level listed step below.
TIP! Create detailed documents as you go through each step with your chosen token passwords, account configurations and other options to help you troubleshoot or upgrade later on.
1. DO YOUR HOMEWORK! Minimally read:
– Kay Unkroth’s SharePoint BI Security White Paper
– Supported Combinations of SharePoint and Reporting Services Components
– SharePoint 2013/SQL Server 2012 SP1 BI Installation Overview and Process
2. Plan SharePoint 2013 BI Farm Server Topology and Roles
3. Plan Service Accounts for SharePoint, various services and data sources
4. Take care of Install Pre-requisites
– Check that the hardware or VMs are capable of hosting SharePoint 2013 (the specs have increased) and the various SQL Server 2012 SP1 BI features you want installed
– Log software license installation keys to use, note SharePoint Server 2013 and SQL Server Enterprise Edition is required for BI features
– Make sure Kerberos is supported within the organization
– Installer accounts must be a Local Administrator to run installs
– SharePoint Farm machines must be joined to a Domain/Active Directory
– Must have one or more domain user accounts to provision the BI services. Domain accounts are required by the managed accounts feature in SharePoint. The Database Engine can be provisioned using a virtual account, but all other services should run as a domain user. Domain user accounts are needed for the following BI related services: Reporting Services, Analysis Services, Excel Services, Secure Store Services and Power Pivot System Service.
– SharePoint Farm machines should have an Internet connection for various installation steps, you can turn that off later
– Choose the URLs that you will use to hit the SharePoint site and have those configured in DNS
Set Up the Base SharePoint Farm
5. Install and Configure Base SharePoint
6. Create the SharePoint 2013 Farm App Servers and WFEs
7. Register the SharePoint 2013 Service Accounts
8. Configure the SharePoint 2013 Search Service
9. Creating the SharePoint 2013 Site Collection
TIP! Use BI Center as Root Site Collection for the best BI experience.
10. Add additional Base SharePoint Services, Apps or Add-Ins
Set Up BI on the Base SharePoint Farm
11. Configure and Start Secure Store Services
12. Configure and Start Excel Services
– Set up Unattended Service Account: this is what Excel uses to connect to data sources to auto-refresh data, the account needs database permissions too for published data sources.
– Optionally Enable EffectiveUserName and follow configuration steps
– Set up at least one Trusted Data Source Library
– TIP! If you run into errors, it is often related to Loopback in Registry settings
13. Configure and Start Claims to Windows Token Service
14. Configure PerformancePoint Services in SharePoint 2013
– Manually install SQLSERVER2008_ASADOMD10.msi for PerformancePoint to connect to SSAS cubes on all servers in the SharePoint Farm and do an IISRESET
15. Install and Configure Power Pivot Install for SharePoint 2013 and the Power Pivot Add-In
– Note that this has changed a bit with SQL Server 2012 SP1 but no one really talked much about it. There is a new stand-alone SSAS server option for higher scale. To learn about that, check out this article.
– Don’t forget to change Maximum File Upload Size
16. Install and Configure SSRS Integrated Mode for SharePoint 2013
– You need to download and Install SSRS Add-In 2012 SP1 and ADOMD10.msi to all WFEs in the farm and issue another IISRESET
– Optional set up SSRS Alerts and Subscriptions in SharePoint and SQL Server
– Add SSRS Content Types to SharePoint document libraries
18. Configure Kerberos Constrained Delegation
TIP! This is usually the step that bites you. READ THE DOCS OR YOU WILL FAIL. Watch Rob’s videos. Register entries in Active Directory for SharePoint, various services and data sources. Read the latest docs – it has changed. You also have to make sure the appropriate service accounts have permissions on the data sources for use within SharePoint. You will have to do this for all the data sources used with SharePoint that exist on a machine not in the SharePoint Farm, basically all real world data sources.
19. Test, test, test and test again
20. Brand the SharePoint BI Farm, master pages, page layouts and theme
21. Celebrate that the SharePoint BI Farm install pain is finally over
22. Get back to enjoying the fun data and BI work!!!
I hope this helps other folks out there that have been looking for TechNet documentation that simply does not exist. Oh and if you need a consultant to come in to do a SharePoint 2013 BI Farm install for you, I’d be happy to refer you to someone else, anyone else but me. : )
UPDATE: So I did hear from a former Microsoft peer today that there are instructions for a setting up a SharePoint 2013 BI Farm – if you deploy it IaaS on the Azure Cloud! Here is an 80+ page white paper that now only exists for the SharePoint 2013 BI Farm in the Azure Cloud IaaS option: Deploy SQL Server Business Intelligence in Windows Azure Virtual Machines.