Guest Post: Providing the foundation for a High Availability BI Infrastructure

  • Article

imageChris 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 blog is part two of a series of blogs about implementing a SQL Server 2012 highly available BI environment. Part one can be found here

Three worlds to consider when embarking on this journey is:

  • Server/Virtualised server hardware/software
  • Network Load Balancing
  • DNS

The ingredients

To setup a SQL Server 2012 highly available BI environment you should establish the hardware and software that is required to support the architecture. As stated in the first blog, you may have to liaise with another team in order to resource these servers. Understanding the requirements will help you have meaningful conversations with the right people. The software includes:

So what is the end game? In this example we are assuming that your organisation has a requirement to use PowerView and/or PowerPivot for SharePoint. This software and hardware is collectively used together to create the following environment.

clip_image002

Network Load Balancer Layer

The Network Load Balancer (NLB) layer is there to accept client requests for a SQL Server 2012 Business Intelligence feature such as PowerView or PowerPivot for SharePoint that is hosted within a SharePoint 2010 farm. These requests are then directed to the first available web front end server to service the request. You should first establish with the network infrastructure team if a NLB already exists. Making use of an existing NLB will speed up the deployment of this infrastructure and can be managed by the infrastructure team. Should you require a NLB, Windows 2008 R2 Server or a hardware based solution can be used to fulfil this requirement. It is important to ensure that the NLB contains dual network cards that provides resilience should one card fail.

Conversations with the infrastructure team should also establish the IP address of the NLB. This is important as this address will be used to map to the hostname address that is decided for the SharePoint Farm which is discussed later in this article.

A second aspect that should be explored is the capability of the NLB to “probe” the web front end servers. “Probing” is a feature of NLB that is used to check the availability of servers that the NLB is directing requests to. The NLB will check to see if a server is available before forwarding requests to it. Should “probing” be employed on the NLB, make sure that the NLB does not just check for the availability of the Windows server. There could be a situation where Windows is working fine, but the SharePoint software installed on the web front end server has stopped. Check with the infrastructure team to see if the NLB can probe for the existence on running services such as SharePoint Foundation Service or other services so that there is a more realistic test of availability.

Web Front End Layer

The SharePoint Web Front End (WFE) is the layer between the applications that are stored in the SharePoint farm and the client requests to the applications. The WFE will direct requests for SharePoint services to the appropriate application server. A minimum of two WFE servers are required to facilitate a High Availability BI solution and the recommended hardware requirements for the WFE layer are:

  • CPU: 64 bit Quad core CPU
  • RAM: 4GB for developer or evaluation use or 8 GB for production use
  • Hard Disk: A minimum 80GB

Ensure sufficient space for the base installation and additional space for diagnostics such as logging, debugging and creating memory dumps.

The following software should be installed on each of the Web Front End servers

  • Windows 2008 R2 with .Net 3.5
  • SharePoint 2010 prerequisite files
  • SharePoint 2012 SP1 Enterprise
  • Reporting Services Add in

Additional WFE servers can be added to support higher concurrent connections to the SharePoint Farm or to provide dedicated servers for specific services such as Reporting Services or SharePoint Search Service.

Although WFE servers can be setup on dedicated hardware, they are typically setup within Hyper V. The key point when virtualising the servers is to ensure there is enough resources to host the virtual machines that are to be used within the virtualised environment. The virtualised environment can also have its own availability features to provide further resilience. Discuss these options with the team responsible for managing virtualised environments.

Application Layer

The application layer hosts the applications that are consumed by users of the SharePoint farm. From a SQL Server perspective this will include SQL Server Reporting Services, PowerPivot for SharePoint and Powerview. For the purpose of this article, a minimum of two application servers will be required to provide high availability. This is achieved by duplicating services on each server. It should be noted that you can add additional application servers for non-high availability reasons. An example could include offloading an application to a dedicated server that requires more power than other services. So you should consider which services require high availability and which services that do not.

The recommended hardware requirements for the application layer are the same as the WFE:

  • CPU: 64 bit Quad core CPU
  • RAM: 4GB for developer or evaluation use or 8 GB for production use
  • Hard Disk: A minimum 80GB

Note that you may require additional resources dependent upon the services you run on the application servers. As an example, the Visio Graphics Service in SharePoint may require additional memory should this be extensively used.

The following software should be installed on each of the application servers

  • Windows 2008 R2 with .Net 3.5
  • SharePoint 2010 prerequisite files
  • SharePoint 2012 SP1 Enterprise
  • Reporting Services
  • PowerPivot for SharePoint

Like the Web Front End layer, the application layer can too be virtualised and should be resourced appropriately to ensure the application servers do not suffer from resource contention

SharePoint Database Backend Layer

The SharePoint Database backend layer is used to host the SharePoint configuration databases from the SharePoint Farm. At the time of writing it is not clear if these databases can be stored or supported using SQL Server 2012 Always On availability groups. As a result the SQL Server Instance on which the SharePoint databases are installed can use an Active Passive cluster to ensure high availability. Keep an eye out for developments in this area.

Also ensure that the firewall ports are opened up to enable inbound communication from the SharePoint Service. As applications are installed and enabled within the SharePoint farm, SharePoint will create databases within the SharePoint backend layer.

Source Databases

While not strictly within the scope of the high availability BI infrastructure, you should make a note of all the Database Engine, Multi-Dimensional and Tabular SQL Server instances and the associated service accounts that Reporting Services, PowerPivot and PowerView will use as data sources for the creation of reports. This will become important should you enable Kerberos authentication and delegation within the SharePoint farm, discussed in part three of this series.

For a complete high availability solution, you should also ensure that the identified data sources are also configures in a highly available solution. Otherwise the data sources could be the source of a loss of service that reflects badly for the BI environment

What’s in a name?

To use features such as SQL Server 2012 Power View, there is a requirement to setup a SharePoint 2010 SP1 Farm. If your organisation does not have one already, give serious consideration to the url name that the users will type in a web browser to access the SharePoint environment. This is a decision that you will want to involve the business in as well. The name should reflect the purpose of the environment. As many of my engagements involve setting up a BI platform, customers will choose a name such as CompanyBI, an example being coeobi. Ultimately, this means that the users will type in https://coeobi in order to access to SharePoint 2010 platform.

From a technical perspective, the name must be registered in Domain Name Services (DNS) so that communication can happen seamlessly within your infrastructure. DNS is a name resolution service. Its primary purpose is to resolve a hostname to an IP address, similar to searching for the telephone number for an individual within a telephone directory. There are many other features that can be discussed about DNS. There is however a key point that is very important to the setup of your SharePoint name in DNS.

Ultimately the name you have chosen for your SharePoint Farm, in our example coeobi, must be mapped to the IP address of the Network Load Balancer. When discussing the creation of the DNS record, stress to the infrastructure team that the DNS record registered is either an A Resource Record that maps the name to the IP address if using IP version 4 or an AAA Resource Record that maps the name to the IP address if using IP version 6. Experience has shown that an assumption is made to create CNAME Record in DNS instead. This causes problems within the SharePoint farm in terms of authenticating users as the Kerberos protocol does not work well with CNAME records.

So in summary the following points should be established to get the foundation of the high availability BI farm up and running

  • Establish the IP address of the Network Load Balancer (NLB)
  • Establish if the NLB will be probing for the availability of the WFE servers and at what level.
  • Decide on a name for the SharePoint farm and ensure an A or AAA resource record is used in DNS to map the name to the NLB IP address.
  • Decide which levels of your BI infrastructure are to be virtualised and plan for the appropriate hardware resources to be made available.
  • Ensure at least two web front end servers for availability at the web front end layer
  • Ensure at least two application servers for availability at the application layert.
  • Place the SharePoint 2010 configuration databases on an Active Passive cluster for availability.
  • Ensure the ports are opened on the SharePoint 2010 database backend for so that databases can be added as services are introduced to the farm.
  • Ensure high availability across all levels of the infrastructure, including the data sources

In part three we will look at the impact of Active Directory and Kerberos on a high availabilty architecture