SharePoint 2013 BI Farm Setup Guide: Section II. Plan Your SharePoint 2013 BI Farm

This is section II of the MBP SharePoint 2013 BI Farm Setup Guide. 

This section describes how to plan your SharePoint 2013 farm. It provides detailed tables and diagrams illustrating the MBP farm as an example of a mid-size, production corporate intranet. Your farm will have different details, so you should create your own versions of these tables and diagrams and use them as reference documentation for your farm. 

Below are links to all the sections of this Guide.

I.     Overview II.    Plan Your SharePoint 2013 Enterprise Farm III.   Build a Virtual Network in Microsoft Azure IV.   Build the SQL Server for SharePoint Databases V.    Build the SharePoint Server VMs

VI.    Configure OUs and Group Policies VII.   Pre-Installation Requirements VIII.  Install SharePoint Prerequisites IX.    Install SharePoint 2013 with SP1 but Quit Before Running the Configuration Wizard X.   Configure the SharePoint Farm

XI.    Create the Baseline Web Applications and Site Collections XII.   Configure Search and MySites XIII.  Configure Office Web Apps XIV.  Configure Business Intelligence XV.   Turn Windows Firewalls Back On for All Servers XVI.  Download PowerShell Scripts

As mentioned in Section I, MBP stands for "Martin's Best Practices."  I use MBP as the acronym for the enterprise, the domain and also the name of the farm. MBP does not correspond to any actual company or client. You may use this guide as a template by globally replacing "MBP" with the acronym or name of your company or client.

A. Technology Choices

This guide uses the following technology choices to build the MBP SharePoint 2013 farm:

• Windows Server 2012 R2
• SQL Server 2012 with SP2
• SharePoint 2013 with SP1

You may make slightly different technology choices and still use most of the steps and scripts in this guide. Nearly all steps of this procedure work the same using Windows Server 2008 R2, and the scripts have been successfully used in those environments. However, screen shots show the Windows Server 2012 R2 user interface. Most steps and scripts also work fine with SQL Server 2008 R2.

B. Infrastructure Choices - Microsoft Azure IaaS

Section III of this guide explains how to provision your virtual hardware using Microsoft Azure Infrastructure as a Service. This choice is particularly economical if you have a Microsoft Visual Studio Premium MSDN subscription. With this MSDN subscription, you get $100 per month credit towards any Azure service. This can make a multi-farm SharePoint infrastructure very affordable particularly for individuals or small teams who can shut down all the virtual servers when not in use, such as at night and on the weekends. You may also provision hardware in your own data center, in which case you may skip (or skim) section III of this guide.

C. Logical Architecture

This section provides a reference architecture for an Intranet farm. Your business goals will drive the logical architecture for your farm. Our goal for the logical architecture of the MBP farm is to support all the core SharePoint features plus the Business Intelligence features listed in section I – A – 2.

The following diagram illustrates the logical architecture of the MBP farm:

The Logical Architecture of the MBP farm shown above helps identify the following:

o What SharePoint web applications (IIS sites) the farm will have

•  MBP will have four web applications: Home, BI, MySite, and Central Admin

o What SharePoint Site Collections we will have

• The Home Web App will have four Site Collections: Home, Search, Departments, and Document Center

o What URLs we plan to use for each SharePoint Site Collection

Feel free to download the Visio file that contains the Logical Architecture diagram, High-Level Physical Architecture diagram, and Detailed Physical Architecture diagrams from the "Diagrams" folder in my One Drive. You may save a lot of time by starting with my diagrams and search for all instances of the acronym MBP and replace them with your company or client's name or acronym in order to document your own farm with this style of diagram.

Document the Logical Architecture as one of the first steps in planning your SharePoint farm. This is a best practice. The Logical Architecture will help you communicate within your organization what the SharePoint farm will have. Use it to validate what the SharePoint farm is intended to do. Documenting the Logical Architecture will simplify your next steps when building the farm and configuring SharePoint. You can always add more web applications and site collections later.

For the MBP farm, this Logical Architecture identifies three Host Headers we will need so that URLs for our SharePoint sites are intuitive (e.g. http://bi.mbp.com) rather than named using the server name:

Host Headers

• home.mbp.com
• mysite.mbp.com 
• bi.mbp.com

D. Physical Architecture

1. High-Level Physical Architecture

Your objectives and constraints will drive the physical architecture for your farm. Our goal for the physical architecture of the MBP farm is to distribute the workload across multiple servers in a manner consistent with a medium-sized farm, with extra servers allocated to Search and Business Intelligence workloads. This starts with the three-tier farm model, as recommended for production deployments in the Microsoft Technet article, Install SharePoint 2013 across multiple servers for a three-tier farm. The MBP farm physical architecture goes beyond this fundamental architecture by adding one additional SharePoint application server specifically for search, and another SharePoint application server specifically for Business Intelligence. The resulting high-level physical architecture is illustrated in the following diagram. It is a best practice to create your own diagram illustrating the farm you plan to build. Jump-start your work to create your own diagram by downloading the free Visio 2013 copy of this diagram then change the details to match the farm you plan to build:

For economy and because MBP does not have many end users, when we get into the detailed steps to install and configure this farm, this guild drops the load balancer and one of the two front-end web servers that appear in the preceding diagram. 

2.     Size of Each Server

For a production farm, use Capacity Management and Sizing to estimate the number of servers, the work-load of each server, the amount of RAM, number of cores and amount of disk storage.

Normally an enterprise will require a set of SharePoint farms -- e.g. DEV, TEST, STAGE and PRODUCTION -- all following the same, consistent configuration. For the MBP farm hardware sizing, we use five SharePoint servers to model the deployment considerations of a PRODUCTION farm, but we keep the size of each server relatively small as you would for a DEV farm.  According to Hardware and software requirements for SharePoint 2013, the minimum hardware requirement for a SharePoint Web server or Application server in a pilot, user acceptance test or production deployment is 12 GB RAM, 4 processors and an 80 GB of hard disk space. We would choose 12 GB RAM and 4 cores for most machines in the MBP farm, but Windows Azure Management Portal only provides a limited selection of virtual machine sizes. For the MBP example, therefore, we will use the Windows Azure “A5” virtual machine size, which comes fairly close with 14 GB RAM and 2 cores. If your farm must support pilot or production deployment, you should increase this to “A5” which has 14 GB RAM and 8 cores.

In addition to the one front-end Web server, three SharePoint Application servers, and one SQL Database server, the MBP farm requires: 

·        A SQL Server Analysis Services Server to support BI PowerPivot

·        An Office Web Apps server to support the OWA experience.

·        An Active Directory/Doman Controller (AD/DC) to support the MBP domain

The AD/DC machine can use an Azure “small” size virtual machine (1.75 GB RAM and 1 core) while we’ll use another “A5” for the OWA.

Plan your farm physical architecture by filling in a table like the one below.

Server Role	Machines	Cores	RAM	system drive	data drive
Front-end Web Server (WFE)	2	2	14 GB	100 GB		100 GB
App Server 1: Central Admin	1	2	14 GB	100 GB		100 GB
App Server 2: Search	1	2	14 GB	100 GB		256 GB
App Server 3: Bus. Intelligence	1	2	14 GB	100 GB		100 GB
SQL Database Server for SP	1	2	14 GB	100 GB		512 GB
Office Web Apps Server	1	2	14 GB	100 GB		100 GB
Active Directory/Domain Controller	1	1	1.75 GB	100 GB		100 GB

Note in the physical architecture table above we allocate somewhat more space on the data drive for the Search Server because the Index will reside on that drive. Also, for the SQL Database server, we allocate a significant amount to the data drive where all the SharePoint content databases will reside.

3. Choose Domain Name and Machine Names

Now that we have the physical architecture plan, we may choose our domain name and machine names.

We can do this because we are building the MBP farm and domain from scratch. Normally in corporate environments you do not get to choose your domain name nor your machine names. Rather, you provide your hardware requirements (like the table above) to the data center staff, and the data center staff come back later and tell you what your server names are. Typically, data center staff assign hard-to-remember machine names like “ukspsppd501” that make perfect sense to them seem arcane to me. I don't try to argue about naming conventions, so I keep a table associating machines roles with machine names. Fortunately, for the MBP farm, we can take advantage of our Azure freedom and choose simple, intuitive names that capture the server role, such as MBP-CA for Central Admin.

Domain Name = MBP.COM

Along with our domain, we are going to require a domain administrator account. Using Azure we will specify this account when we provision our first server (the Domain Controller) so it’s important to plan that now.

Domain Administrator = mbpadmin

Document your machine names using the planning table below, including the names of the machines in your configuration and what administrator account you will normally use to login (more on accounts in the next section.)

Machine Names

Machine Name	Server Role	Administrator Login
MBP-DC	Active Directory / Domain Controller	MBP\mbpadmin
MBP-SQL	SQL Server 2012 SP1 BI Edition for all SharePoint Databases	MBP\sql_admin
MBP-CA	SharePoint 2013 Central Admin and Application Server	MBP\sp_admin
MBP-WFE1	SharePoint 2013 WFE 1	MBP\sp_admin
MBP-WFE2	SharePoint 2013 WFE 2	MBP\sp_admin
MBP-SEARCH	SharePoint 2013 Search Server	MBP\sp_admin
MBP-BI	SharePoint 2013 Business Intelligence Services Server	NPB\sp_admin
MBP-SSAS	SQL Server 2012 SP1 BI Edition for SQL Server Analysis Services	MBP\sql_admin
MBP-OWA	Office Web Applications Server	MBP\sp_admin

                  

4. Detailed Physical Architecture           

Create a detailed diagram of your farm physical architecture that includes:

• the number of servers
• the names and roles of each server
• the hardware size of each server
• the IP address of each server
• sites and services hosted on each server

We can plan the IP address of each server because we are building our own VMs in Windows Azure. This guide will describe in detail how to configure static internal IP addresses for each VM. Note that you must explicitly configure static IP addresses for your Azure Virtual Machines, otherwise, by default, Azure VMs receive dynamically-allocated IP addresses when they reboot. If you are using Azure, use this technique for the DNS server and for all your Azure VMs. Setting a static IP Address is necessary if you plan to shut down your VMs for economy when not using them. Otherwise if you have an enterprise Data Center, you will probably request virtual machines from your Data Center. Later, your Data Center will tell you the names and IP address of each machine and you should come back and update your detailed diagram with that information.

The detailed physical architecture diagram for our MBP farm is shown below. Download the free copy of the Visio diagram shown below to jump-start your work on your detailed physical architecture diagram. Use your detailed physical diagram to document all the choices you made when planning your SharePoint 2013 farm.

This physical architecture diagram for MBP shows eight servers, including MBP-SQL, the SQL Server for the SharePoint content databases. It also includes the MBP-DC, the Domain Controller for the mbp.com domain. Note that all the user-facing web applications including the Home site, BI Center, and MySite are hosted on MBP-WFE1 and MBP-WFE2. Maintain your detailed physical architecture diagram as you maintain your farm; you will refer back to this diagram many times throughout the process of configuring SharePoint.

E. Plan Service Accounts

SharePoint requires many accounts. Plan out the names of the Farm account, the Setup account, and all the service accounts now. It is a best practice to document in detail what accounts your farm(s) will use and for what purpose. This section contains a detailed table of all service accounts used in the MBP farm, including notes on usage and required memberships and permissions.

As we did with machine names, we choose simple, intuitive account names such as sp_farm for the farm account and sp_admin for the SharePoint Setup 

(install) account.

The following table specifies the Account name, the role, the purpose of each account, and what rights that account must be granted in the different systems prior to configuration of the SharePoint farm. You will refer back to this table many times throughout the process of configuring your SharePoint farm.

Account User Name	Role	Usage/Purpose/AKA	Local SharePoint Rights	SQL Rights Needed
mbpadmin	Domain Administrator	Administrator of the Active Directory / Domain Services server
sp_farm	Farm Account	A.K.A. the Database Access Account. Used for Windows Timer Service, Central Admin, and User Profile Service.	Before installing SP, must be a member of the local administrators group on all SharePoint Servers. Must be able to log on locally to all SP servers (member of remote desktop users group – default for local administrators); The Local admin rights may be removed after UPS provisioning.	SharePoint setup program automatically adds this account into the dbcreator fixed server-level role and into the securityadmin fixed server-level role. SP setup also makes this account a member of the db_owner role for all SharePoint databases.
sp_admin	Setup User Account	AKA SharePoint Admin; AKA Install Account. Used to perform bit-level changes on servers. Used to install SharePoint, run PSConfig and to install any service packs or other s/w on any SP server.	Must be a member of the local administrators group on all SharePoint servers. Does not need to be a local admin on SQL server. Do not use a “normal” user account. [For least privileges, disable this account after SP install is complete. Re-activate when need to install patches.]	The account must be able to log on to the computer running SQL server. You must add this account into the securityadmin server-level role and into the dbcreator server-level role in SQL Server before using it to install SharePoint (before you run setup).
sql_service	SQL Server Service Account	SQL Server Service  account, AKA the Account that starts the SQL Server instance service.	None	Needed when installing SQL server. Before you install SQL Server, if you need Kerberos, you must register a Service Principal Name (SPN) for the SQL Server Service for this domain account in AD. See http://msdn.microsoft.com/en-us/library/ms191153(v=SQL.100).aspx
sql_admin	SQL Server Administrator Account	SQL Administrator account	None	User with full admin rights in SQL server. Must be a member of the local Administrators group on the SQL server.
sp_webapp	SP Web Application App Pool Account	App pool account for all User-facing web applications.	None	Roles assigned automatically by SP Install
sp_svcapp	SP Service Application app pool Account	App pool account for all service applications that do not have a dedicated specific account	None	Roles assigned automatically by SP Install
sp_crawl	Content  (Crawl) Account	AKA the default content access account. Used in the configuration of the Search Service Application. Used to crawl content.	None	Roles assigned automatically by SP Install
sp_search	Search Account	This account will run the application pool for the Search Service Application.	None	None
sp_adsync	User Profile Synch Account	AKA the AD Synch Account. Used when configuring a synchronization connection with Active Directory.	Your Active Directory administrator must grant the Replicating Directory Changes permission on the domain to this account.
sp_ppvtsvcapp	PowerPivot  App pool Account	Account that will run the application pool for the PowerPivot for SharePoint Service Application	None	When configuring PowerPivot for SharePoint, you will grant this account access to all SharePoint content database. Grant access each time a new SharePoint content database is created.
ssas_pvtsvc	SQL Server Analysis Services (PowerPivot) service account	SQL Server Analysis Services account. AKA the account that starts the SQL Server Analysis Services (PowerPivot) service.	None	Needed when installing SQL Server Analysis Services.
ssis_service	SQL Server Integration service account		None	Needed when installing SQL Server Integration Services.
sp_unattended	Unattended service account	Unattended service account for Excel and Visio services, AKA the Unattended data refresh account	Grant this account access to the data sources.

In this step we have planned out all the service accounts. Later we will need to create them in Active Directory. Section III – C of this guide contains the detailed steps to create these accounts in AD.  Other sections have the steps to grant them the appropriate rights. In an enterprise deployment, you may need to put in a request to administrators who are responsible for Active Directory, asking them to create these service accounts for you. 

F. Plan Naming Conventions

Plan out the naming convention for your databases, web applications, host headers and the URLs your users will use to access the SharePoint sites.

A well-planned naming convention is worth the up-front investment because it simplifies maintenance over time and can be extended in a consistent way to better support multiple farms.

In step II – C above you selected the name of your domain: mbp.com in this case. Now plan out the rest of the names to be used for your web sites, databases, and host headers. You will refer back to this plan when you tailor the SetEnvironmentVariables.ps1 PowerShell script to set Windows environment variables such as MBP_HOME_WEB_APP_NAME to the names you have selected in this plan. The goal is for your PowerShell scripts to contain no hard-coded strings that are specific to only one farm.

The first step is to flesh out the plan for just the PRODUCTION SharePoint farm by filling in the table below. This table lists all the names of your web sites, databases, and host headers, and what Environment Variable will be set to the value of that name in one specific farm.

Name	Purpose	Environment Variable
SPDB	SQL Server Instance Name	n/a
sql_server_alias	SQL Server Alias set in cliconfg.exe	MBP_SQL_SERVER_ALIAS
sp_config	SharePoint configuration DB name	MBP_SP_CONFIG_DB
sp_content_ca	SharePoint Central Admin DB name	MBP_SP_CA_CONTENT_DB
Home	Name of Home SharePoint Web App	MBP_HOME_WEB_APP_NAME
http://home.mbp.com	URL of the Home SharePoint Web App	MBP_HOME_URL
sp_content_home	Content Database name for Home	MBP_HOME_DBNAME
home.mbp.com	Host Header for the Home SP Web App	MBP_HOME_HOST_HEADER
MySite	Name of the My Site Host Web App	MBP_MYSITE_WEB_APP_NAME
http://mysite.mbp.com	URL of the MySite Host Web Application	MBP_MYSITE_URL
sp_content_mysite	Content Database name for MySite	MBP_MYSITE_DBNAME
mysite.mbp.com	Host Header for the MySite Host	MBP_MYSITE_HOST_HEADER
sp_state_service	State Service App Database name	MBP_STATE_SVC_DB_NAME
sp_ssrs_service_	SSRS Service App Database name	MBP_SSRS_SVC_DB_NAME
sp_managed_metadata	Managed Metadata Database name	MBP_MANAGED_META_DB
sp_search_service	Search Service App Database name	MBP_SEARCH_SVC_DB
sp_userprofile_service	User Profile Service App Database name	MBP_USERPROFILE_SVC_DB
sp_secure_store_service	Secure Store Service App DB name	MBP_SECURE_STORE_SVC_DB
BI Center	Name of Bus. Intelligence Ctr web app	MBP_BI_WEB_APP_NAME
http://bi.mbp.com	URL of the Bus. Intelligence Center	MBP_BI_URL
sp_content_bi	Content Database name for BI Center	MBP_BI_DBNAME
bi.mbp.com	Host Header for the BI Center Web App	MBP_BI_HOST_HEADER
mbp-owa.mbp.com	the OWA Server Name	MBP_OWA_SERVER

In anticipation of building multiple farms, the next step describes how to extend all these names to handle multiple forms (e.g. DEV, TEST and STAGE).

G. Naming Conventions for Multiple Farms

Most enterprises that deploy SharePoint on-premises deploy more than one farm. Typically, they deploy a series of SharePoint farms to support a pipeline for solution development. Your enterprise may need three or four SharePoint farms to support a build-and-deploy process that takes customization from development to test, from test to staging and from staging to production.

Plan the account names, site names, URLs and host headers you will use for your DEV, TEST, STAGE and PRODUCTION farms now. If you are only going to build one farm, you can use only the PRODUCTION version of each name. The PRODUCTION names are the shortest versions of each name.  Fill in the table below with your plan:

 1.  URL and Site Names for Multiple Farms

DEV	TEST	STAGE	PRODUCTION
SPDB-DEV	SPDB-TEST	SPDB-STAGE	SPDB
sql_server_alias	sql_server_alias	sql_server_alias	sql_server_alias
sp_dev_config	sp_test_config	sp_stage_config	sp_config
sp_dev_content_ca	sp_test_content_ca	sp_ stage_content_ca	sp_content_ca
Home-DEV	Home-TEST	Home-STAGE	Home
http://home-dev.mbp.com	http://home-test.mbp.com	http://home-stage.mbp.com	http://home.mbp.com
sp_dev_content_home	sp_test_content_home	sp_stage_content_home	sp_content_home
home-dev.mbp.com	home-test.mbp.com	home-stage.mbp.com	home.mbp.com
MySite-DEV	MySite-TEST	MySite-STAGE	MySite
http://mysite-dev.mbp.com	http://mysite-test.mbp.com	http://mysite-stage.mbp.com	http://mysite.mbp.com
sp_dev_content_mysite	sp_test_content_mysite	sp_stage_content_mysite	sp_content_mysite
mysite-dev.mbp.com	mysite-test.mbp.com	mysite-stage.mbp.com	mysite.mbp.com
sp_dev_state_service	sp_test_state_service	sp_stage_state_service	sp_state_service
sp_dev_ssrs_service_	sp_test_ssrs_service_	sp_stage_ssrs_service_	sp_ssrs_service_
sp_dev_managed_metadata	sp_test_managed_metadata	sp_stage_managed_metadata	sp_managed_metadata
sp_dev_search_service	sp_test_search_service	sp_stage_search_service	sp_search_service
sp_dev_userprofile_service	sp_test_userprofile_service	sp_stage_userprofile_service	sp_userprofile_service
sp_dev_ecure_store_service	sp_test_secure_store_service	sp_stage_secure_store_service	sp_secure_store_service
BI Center-DEV	BI Center-TEST	BI Center-STAGE	BI Center
http://bi-dev.mbp.com	http://bi-test.mbp.com	http://bi-stage.mbp.com	http://bi.mbp.com
sp_dev_content_bi	sp_test_content_bi	sp_stage_content_bi	sp_content_bi
bi-dev.mbp.com	bi-test.mbp.com	bi-stage.mbp.com	bi.mbp.com
mbp-owa-dev.mbp.com	mbp-owa-test.mbp.com	mbp-owa-stage.mbp.com	mbp-owa.mbp.com

Your primary concern is to have unique URLs, site names and account names across farms. This is best practice for security. Furthermore, different sites on the same network must have different URLs. Note that this table lists unique SQL database names (e.g. sp_dev_config, sp_test_config) out of an abundance of caution. You do not really need different database names from farm to farm because each farm should use a unique SQL instance. However, just in case two farms do use the same SQL instance, you absolutely must have unique database names. This naming convention favors consistency.

2. Account Names for Multiple Farms

DEV	TEST	STAGE	PRODUCTION
sp_dev_farm	sp_test_farm	sp_stage_farm	sp_farm
sp_dev_admin	sp_test_admin	sp_stage_admin	sp_admin
sql_dev_service	sql_test_service	sql_stage_service	sql_service
sql_dev_admin	sql_test_admin	sql_stage_admin	sql_admin
sp_dev_webapp	sp_test_webapp	sp_stage_webapp	sp_webapp
sp_dev_svcapp	sp_test_svcapp	sp_stage_svcapp	sp_svcapp
sp_dev_crawl	sp_test_crawl	sp_stage_crawl	sp_crawl
sp_dev_search	sp_test_search	sp_stage_search	sp_search
sp_dev_adsync	sp_test_adsync	sp_stage_adsync	sp_adsync
sp_dev_ppvtsvcapp	sp_test_ppvtsvcapp	sp_stage_ppvtsvcapp	sp_ppvtsvcapp
ssas_dev_pvtsvc	ssas_test_pvtsvc	ssas_stage_pvtsvc	ssas_pvtsvc
ssis_dev_service	ssis_test_service	ssis_stage_service	ssis_service
sp_dev_unattended	sp_test_unattended	sp_stage_unattended	sp_unattended

Next Steps

This concludes Section II of the MBP SharePoint 2013 BI Farm Setup Guide. Section II described how to plan your SharePoint 2013 farm, including recommended ways to document your technology choices, logical architecture, physical architecture and naming conventions. 

The next section, section III Build a Virtual Network in Microsoft Azure, describes how to create a Virtual Network in Azure and a Domain Controller to manage the Service Accounts.