As SharePoint becomes more of a centerpiece in the Microsoft BI landscape, a major consideration that must be examined is the hardware and software required to operate a properly performing and secure environment.
Historically, a BI environment would require a large number of big iron servers with massive amounts of expensive storage behind it. With the new features of SQL Server 2012 and the xVelocity engine, the landscape has changed in a dramatic way.
In this chapter, we discuss the way in which you will need to lay out your environment to support the efforts in this book and a BI scenario in general. As a part of this effort, we will provide guidance around creating the environment in which to develop the solutions in this book, as well as recommended practices for development and production environments.
The topics covered are:
Server requirements
Physical versus virtual
Overall constants
SharePoint and SQL Server specifics
Single server versus multi-server environments
Logical requirements
Server role determination
Recommended technical specifications
Note that we are not going to be making recommendations around best practices, but instead will suggest recommended practices. The reason for this distinction is that Microsoft does change its best practices periodically and we are making recommendations based upon our years of experience doing work with BI scenarios.
When examining physical servers versus virtualized servers, general constants exist no matter the operating system or applications. Of course, you will also need to consider variables regardless of the hosting mechanism (hardware or virtual) when talking about SharePoint and SQL Server. The following virtualization specs refer to Hyper-V in Windows Server 2008 R2 & VMWare vSphere 4.x.
Let’s take a look at the constants first.
Benefits
Limitations are only those specified by the manufacturer
If the server supports 24 cores and 256GB of RAM, you can address all 24 cores and 256GB of RAM
Zero additional overhead computing penalties
Drawbacks
One server per machine
Direct networking/no native centralized management console
No native complete snapshot capability
No native high availability/disaster recovery capability
Benefits
Native complete system snapshot capability
Native high availability/disaster recovery capability
Licensing may cost extra, but capability is native to the tools
One physical server can host multiple virtual machines
Drawbacks
Minimum 8% compute overhead penalty for the physical host’s hypervisor
Limitations on number of cores that can be assigned to each VM
Shared networking
In the years since virtualization was finally supported as a platform for hosting SharePoint and specifically with the release of SharePoint 2010, the concept of virtualization has been more widely embraced and is now a standard for how SharePoint farms are architected. The debate around SQL Server still rages on in many circles, but it is widely acknowledged that in most cases the benefits outweigh the drawbacks when it comes to virtualization.
The concern around virtualization of SQL Servers is that when configured in an out-of-the-box manner, you have a VHD or VMDK file that serves as your logical disk container. This means that you are working within a single file on a file system for all reads and writes, which can cause contention in high activity instances, such as SQL Server.
The benefits that come from this manner of operation are that you are able to snapshot an entire farm at a specific point in time. Virtualization is the only option that allows you to restore a SharePoint farm and remain in a supported state. Doing a backup using snapshotting prior to the deployment of operating system patches, SharePoint or SQL updates, or new code that could impact the environment is a recommended practice.
When dealing with high read-or-write scenarios, such as with SQL Analysis Services OLAP cubes or highly transactional SQL databases, it is most desirable to be backed up against high-speed disk. This can be accomplished with a virtualized environment, but requires configuration of the SAN fabric, which is beyond that which we will be discussing here, as it is not a requirement for the solutions described in this book.
The reason that high-speed disks are not a requirement for these solutions lies in the nature of the new BI Semantic Model (BISM) in SQL Server 2012. SQL Server Analysis Services Tabular Mode (Tabular BISM) leverages the xVelocity for BI engine, which is a memory resident model of hosting cubes. Rather than requiring a high-speed disk, tabular BISM model cubes require large amounts of RAM. In addition, the architecture of SQL Reporting Services Integrated Mode makes it so that report definitions are hosted in the SharePoint content database rather than in an external database that would require another database engine on the App Tier.
In a development environment such as the one we’ve built for the purposes of this book, the decision about how to perform your installation depends on a number of factors, the first of which is: Do you have multiple physical hosts or virtual machine infrastructures available to you? If you do not, then the decision is fairly straightforward. For this reason and others, we have opted to utilize CloudShare to host our development environment, which has enabled us to use a multiple virtual machine server implementation.
In situations where resources are constrained and implementing a multiple server environment is not an option, it is important to still install the full version of SQL Server and install SharePoint as a farm (see Figure 32-1). Installing SharePoint in standalone mode is not recommended and will severely limit the ability to perform business intelligence operations.
The reason for using multi-server rather than a single-server implementation is to better model our scenario after a production environment. It is highly unlikely that someone would be doing a single-server implementation for Active Directory, SQL Server, SharePoint, and Visual Studio in a production situation. Further, the ramifications of installing Visual Studio in a production environment can have drastic side effects if utilizing SharePoint 2010’s Sandboxed Solutions capabilities.
Over time we have found that unless you model your development environment after your production environment, you can run into untested scenarios where code may not work across tiers as expected. It is not necessary to scale your development environment to exactly match your production environment; instead, simply mirror the functional tiers. While it is not necessary to “scale” a development environment to the same level as a production environment, the datasets used for testing should be similar in nature so as to ensure valid load and performance testing for business intelligence solutions that are implemented.
In our design (Figure 32-2), we have:
To build out a properly performing multi-server VM environment will require a large amount of RAM, high core count, and a significant amount of disk space. Keeping in mind that hardware allocation of resources is desired, you will most likely need to be hosting the environment and not using local resources. Table 32-1 shows the core, RAM, and disk count that we have allocated for the effort in CloudShare.
Table 32-1. Multi-server requirements
Server | Cores (CPUs) | RAM in GB | Disk in GB |
|---|---|---|---|
Active Directory | 1 | 1 | 80 |
Developer VM | 2 | 2 | 80 |
SharePoint Web Server | 2 | 3 | 80 |
SharePoint Application Server | 2 | 6 | 80 |
SQL Server | 2 | 4 | 100 |
It should be noted that while they work for our examples, these machines are undersized for production use and that Microsoft’s “Hardware and software requirements for SharePoint Server 2010” should be consulted before sizing this scenario for production.
We built out a functional, lower performing environment on CloudShare that you can work with to deploy out the functional components discussed in this book. That environment is shown in Table 32-2.
Table 32-2. Lower performing environment
Server | Cores (CPUs) | RAM in GB | Disk in GB |
|---|---|---|---|
Active Directory | 1 | 1 | 35 |
SharePoint Web Server | 2 | 3 | 80 |
SharePoint Application Server | 2 | 4 | 80 |
SQL Server | 1 | 2 | 35 |
In this environment, Visual Studio LightSwitch is installed on the App Server.
To build out a reasonably performing single server instance of this will require the specs in Table 32-3.
The reason that the disk count in the single-server model is lower is that there is no need for repetition of the operating system from each server. It is recommended that the disks that are backing SQL be placed on their own spindles to allow for higher input/output operations per second (IOPS). The high RAM count is required to run SQL Server, the SharePoint Web Apps and Service Applications, Tabular BISM, and the operating system concurrently. Running Visual Studio will impact performance, but is possible under these conditions and in a development scenario.
In this chapter, we discussed the way in which you will need to lay out your environment to support the efforts in this book and a BI scenario in general. As a part of this effort, we provided guidance around creating the environment in which to develop the solutions in this book, as well as recommended practices for development and production environments.
We covered server requirements including physical versus virtual, overall constants, as well as SharePoint and SQL Server specifics. Finally, we discussed single-server versus multi-server environments including logical requirements, server role determination, and recommended technical specifications.