Guest Post: Something So Strong – Completing the High Availability BI Infrastructure
Chris Testa-O'Neill is a Senior Consultant for Coeo Ltd, a leading provider of SQL Server Managed Support and Consulting in the UK and Europe. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, sole author of the MCTS SQL Server 2008 Microsoft E-Learning courses and technical reviewer for SQL Server 2012 BI Official Microsoft courses for Microsoft Learning . He is heavily involved with the SQL Server community as a speaker and an organiser of SQLBits , a Regional Mentor for SQLPASS and he runs his own user group in Manchester, UK. As well as being certified as a SQL Server MCDBA, MCTS and MCITP in all tracks
This is part four of four of implementing Highly Available Business Intelligence environment using SQL Server 2012 and SharePoint 2010 technologies including PowerPivot for SharePoint, PowerView and Reporting Services. Part one described the considerations required for implementing the environment. Part two focused on the network infrastructure preparation including an Active/Passive SQL Server cluster to host the SharePoint configuration databases that is required to support the environment and part three focused on the Kerberos requirements for the environment
Once these are in place, the focus can turn towards building the Highly Available environment itself. Here is a reminder of the architecture
Provisioning SharePoint 2010
The first step involved is to create a SharePoint Farm. This requires provisioning both the application layer and web front end layer servers with the SharePoint prerequisite installer that prepares the servers for the installation of SharePoint 2010. If the servers are connected to the internet, the files are installed automatically. However if the servers are not connected to the internet, then you can run the files manually or create an unattended script to manage the installation from a network share. It is important that this is run on all servers first.
Installing SharePoint
Once the prerequisites are installed, proceed with the installation of SharePoint 2010 Enterprise Edition on both the Application server and the Web Front End Servers. At the end of the installation ensure that you deselect the option to Run the SharePoint Product Configuration wizard Now. Once SharePoint 2010 Enterprise Edition is installed, then install service pack 1 on every server. If you are considering using Microsoft Office Web Applications (OWA), consider installing this now with OWA service pack 1 so that it is automatically configured when you configure the SharePoint Farm.
Configuring SharePoint
After the installation of the SharePoint source files. Go to the first application server and click on SharePoint Product Configuration wizard inStart, Programs, SharePoint 2010 folder. This will start the configuration of the first server; create the SharePoint farm and define the SQL Server Cluster as the location of the SharePoint configuration databases. You will also define the database access account (also known as the SharePoint Farm account) that acts the SharePoint Farm service account. A passphrase is also defined; do not lose this as it will be used to join the other servers to the SharePoint Farm.
Once the other servers are joined to the farm, go back to the first server to complete the configuration in SharePoint Central Administration. In this webpage you are presented with the option to configure SharePoint using a wizard or to configure SharePoint manually. It is recommended that you configure the additional SharePoint Services manually. You will also want to configure logging within the SharePoint farm to help troubleshoot any errors that occur in setting up this environment.
SharePoint Core Services
Before installing PowerPivot for SharePoint, Power View or Reporting services, there are a number of SharePoint core services that should be configured. These include
· SharePoint Search Services – Enables you to search for content stored in SharePoint
· Create a Site Collection and Site – This will be used to create the high level site that will be the platform that hosts the applications. Consider using the Business Intelligence template when creating the site collection and site
· Claims to Windows Token Services – a required service for Excel Services should Excel Services query remote data sources
· Excel Services – is a pre-requisite service that is required for PowerPivot for SharePoint to run.
· Secure Store – An optional service, but would be required if you wish to store credentials that will be used to connect to a remote data source
Additional information on configuring these services can be found in the PowerPoint presentation at the end of this article.
With the SharePoint core services configured, proceed with the installation of PowerPivot for SharePoint using the SQL Server installation media. Before installing you should perform the following steps on which PowerPivot for SharePoint is installed:
Add the excel and PowerPivot account to all local admins on the farm and add the following rights
o Act as client after authentication
o Act as part of the operating system
o Log on as service
Once done, install PowerPivot for SharePoint on the first application server. Once installed you can then Configure or repair PowerPivot for SharePoint which automates much of the setup. This will be found on the Start menu of the application server. All that is required is the service account of the PowerPivot account and the SharePoint farm Passphrase as it will be making changes to the farm. You can then use Central Administration to check that PowerPivot activation has been done. To activate any feature perform the following steps:
1. In Central Administration, Click Site Actions.
2. Click Site Settings.
3. Click Site Collection Features in the Site Collection Administration Group.
4. Find PowerPivot Integration Feature in the list.
5. Click Activate.
Once done, you are ready to test the PowerPivot functionality by uploading a PowerPivot workbook to the SharePoint site. When successful testing is completed, repeat the setup on the second application server to provide high availability by providing a second instance of PowerPivot for SharePoint.
Reporting Services Integration for SharePoint
This requires using the SQL Server 2012 media to install Reporting Services for SharePoint Integration on the Application servers. You then add the Reporting Services Add in on the Web Front end servers and the application servers. Then a Reporting Services Application can be created that will be used to determine the service account the Reporting Services will execute under by performing the following steps as found in Books Online:
1. In SharePoint Central Administration, in the Application Management group, click Manage Service Applications.
2. In the SharePoint ribbon, click the New button.
3. In the New menu, click SQL Server Reporting Services Service Application..
4. In the Create SQL Server Reporting Services Service Application page, enter a name for the application. If you are creating multiple Reporting Services service applications, a descriptive name or naming convention helps you organize your administration and management operations.
5. In Application Pool section, create a new application pool for the application (recommended). Using the same name for the new application pool as the service application, makes ongoing administration easier.
6. In the Database Server, you can use the current server or choose a different SQL Server.
7. In Database Name the default value is ReportingService_<guid>, which is a unique database name. If you type a new value, type a unique value.
8. In Database Authentication, the default is Windows Authentication. If you choose SQL Authentication,
9. In the Web Application Association section, select the Web Application to be provisioned for access by the current Reporting Services Service Application. You can associate one Reporting Services service application to one web application.
10. Click OK.
11. The process to create a service application could take several minutes to complete. When it is complete, you will see a confirmation message and a link to a Provision Subscriptions and Alerts page. Complete the provision step if you want to use the Reporting Services subscriptions and alerts features. For more information, see Provision Subscriptions and Alerts for SSRS Service Applications.
Once this is complete, within Central Administration ensure that the reporting services and PowerView Integration for the site is activated.
1. Click Site Actions.
2. Click Site Settings.
3. Click Site Collection Features in the Site Collection Administration Group.
4. Find Power View Integration Feature and Reporting Integration Feature in the list.
5. Click Activate.
You can then access Report Builder to create and test reports. If Windows authentication is required, it is important that this is tested to ensure that the Kerberos configuration is done correctly. You can use the following webpage to help troubleshoot Kerberos, although it is also recommended that you have read through the Configure Kerberos authentication for SharePoint 2010 Products whitepaper before embarking on this architecture.
Attached is a PowerPoint presentation that I present at numerous conferences. Additionally if you want to see how IT and business can collaborate more effectively with PowerPivot and PowerView on a SharePoint 2010 platform, the following 30 minute video explores how this can be achieved
