Building SharePoint can be straight-forward, clicking everything as default will definitely get you a working farm. But clicking Next doesn’t necessarily follow Microsoft Best Practice. In fact, if you do not follow certain steps to build out SharePoint, you will have a system not worthy of a production environment. I have seen multiple bad builds of SharePoint and it has taken a considerable amount of time and effort to shift systems back to “Best Practice.”  At times, if it is early in the process I recommend just rebuilding the environment.  Most engineers that build SharePoint expect the install to be just like typical applications, only SharePoint is not an application.  Even the most experienced engineer falls into this bad habit of just clicking Next. Now there are multiple approaches to build SharePoint and there are checklists out there, but this post will focus on establishing a farm inclusive of a SQL build.

This document will try to be as generic as possible unless I need to point out which version of SQL or SharePoint is needed.

 

SQL Build

One of the first step before building out SharePoint is building out SQL. SQL is an integral part of a SharePoint farm; in fact without SQL, SharePoint can’t exist. Please review the following table for SQL version and SharePoint version

 

* You need to install SharePoint 2007 (MOSS and WSS) SP1 or later, SQL Server 2008 does not support SharePoint 2007 RTM

** You need to install SharePoint 2007 (MOSS and WSS) SP2 or later, SQL Server 2008 R2 does not support SharePoint 2007 SP1

*** You need to install SharePoint 2010 SP1, SQL Server 2012 does not support SharePoint 2010 RTM.

SQL Instance

SQL is very selfish with memory and it is a memory-intensive application. It is recommended that SharePoint have their own SQL Server instance and is should only be dedicated to SharePoint. If by chance that you need to couple SharePoint SQL instance with another application, monitoring SQL performance will be crucial. Two SQL Server performance counters that you would need to monitor:

Moreover, if you have multiple instance of SQL databases running in your one SQL box, it is best practice to at least to configure the Minimum and Maximum server memory, after building SQL. This can be found in the Server Properties | Memory. Again, this will be necessary if you are running multiple instances in the SQL Server, which I wouldn’t recommend.

Service Accounts:

I have seen and continue to see SQL being run on one service account, if any. At times, it is left to the Local System to run SQL. Not being a SQL DBA, but talking to a few, it doesn’t take a lot to build AD service accounts and yet it is readily neglected. There are four service accounts that I usually create, depending on the SharePoint environment

Database Service Account: This account would be the account that runs the database engine.

SQL Service Account: This account is the account that run the Maintenance Plan in SQL

Reporting Service Account: If SharePoint is going to leverage Reporting Services or any type of Business Intelligence, this account is a necessity.

Analysis Service Account: Project 2013 relies on analysis service, which requires it to have a separate service account.

Collation:

SQL collation is pretty much how SQL will sort the data within their database as well as how characters are ruled within SQL. During the installation of SQL, there is an option to set your Collation setting. By default, SQL set the collation setting as SQL_Latin1_General_CP1_CI_AS. The optimal collation setting for SharePoint is Latin1_General_CI_AS_KS_WS. If you set the collation setting during the installation, then the model database will set that all new database created in that SQL instance be the optimal setting for SharePoint. I would highly encourage to build out the collation setting during the install process.

Initial Size:

The default setting for new databases is small, set at 1MB, and the Autogrowth option is enabled. What that means is that when a user upload a 10MB document, for example, SQL would lock the database in order for the database to autogrow enough to fit that 10MB file. It is best practice, depending on the role of the database, to increase the setting from 1MB, to say 50MB or 100MB. There is no right number to set this setting, but rather it is wise to find out the role of the database and the average size of file that will be used in that database. The log files is set to 10 percent, which should be set as a fixed amount instead of a percentage. Again, this really depends on the environment and the organization. You must do this for every database you create, because SQL model database will not recognize the initial size if you change it.

Recovery Model:

By default, it is set to Full. What this means is that the information is written to the data files and you retain the information in the transaction log. This is the preferable option, but the only issue is that you need to clear out the transaction log. At times, the transaction logs can grow more than the data file. I have seen that happen multiple times. To avoid a large transaction log file, you need to perform a full database backup and then run a full transaction log backup. Afterward, you can delete the transaction log backup.

BACKUP DATABASE <databasename> TO DISK = ‘<path>’

BACKUP LOG <databasename> TO DISK = ‘<path>’

DELETE ‘<LOGBACKUP path>’

SharePoint Build:

Again, building out SharePoint isn’t as easy as just clicking everything as default. There needs to be some preparation for the build.

Service Accounts:

Just like SQL, service accounts is very important to create prior to a SharePoint build. I have seen many SharePoint farm that only has one service account. I have seen multiple farms using one service account, which is completely mind boggling. In fact, in that instance, they improperly changed the password and five farms were no longer working. Again, it doesn’t take much to request or build out service accounts. It is quite important to do so.

To build any version of SharePoint, you really only need two accounts. One of these accounts will be designated the farm account. The farm account has control over the Central Administration of SharePoint, it is the account that runs all the SharePoint timer jobs. It is also the account that User Profile leverages in order to synchronize with Active Directory. With SharePoint 2013, it controls AppFabric, which utilize one of the new feature, Distributed Cache. Previously, if you needed to change the farm account, you would use STSADM command –updatefarmcredential. This does not work in SharePoint 2013. It works in SharePoint 2010 and 2007.

http://technet.microsoft.com/en-us/library/cc262150(v=office.12).aspx

The other service account you would need is the web application pool service account. After building SharePoint, you need to give sites an account with so application pool can run in IIS. SharePoint limitation with application pool is 10 per web front end. The reason for this limit is the fact that each application pool runs a process that approximately eats up 650MB of RAM. So it is best practice to build one or two application pool. But this expose a security issue. If you decide to create a web application with sensitive information and couple it with a web application with regular information, both in the same application pool. If somebody hacks into that application pool, both web applications are compromised. So this is a question about balance between performance and security in terms of planning out application pool. But it is important to create a service account for an application pool.

There are other service accounts that needs to be considered, because those mentioned will build you a basic SharePoint farm. You would need at least two service accounts to run search. One to run the crawl and the other to run the application pool. For User Profile Service Application, you would need one service account, one that will have access to Active Directory. If you turn on publishing feature, you need two accounts: superreader and superuser to avoid certain SharePoint errors.

The following link can give you a list of SharePoint accounts that needs to be created:

http://www.toddklindt.com/blog/lists/Posts/post.aspx?ID=237

Microsoft also has a great link that also details all the service accounts you need or may need to create, depending on which services you are planning to leverage.

http://technet.microsoft.com/en-us/library/cc263445.aspx

Again, I cannot stress enough that service accounts are important.

Slipstreaming

With SharePoint, you can extract Service Packs, Public Updates, Cumulative Updates and nest those files into the Update folder in your SharePoint installation media files. Once you do that and run the install, the SharePoint installation will automatically update the SharePoint build to the latest version depending what you slipstreamed.

This works wonders until it doesn’t work at all. I ran into an issue that I slipstreamed a SharePoint 2010 build with the latest cumulative update and it pretty much didn’t completely install all the updates. Some failed. Unfortunately, I wanted to add another server to that farm, but it wouldn’t let me unless I find those updates. In the end, I had to manipulate SQL configuration database to bypass the check in order to add a server to a farm. Then reverted the db back to that broken state.

It is highly advisable to test slipstream in a separate farm before implementing it in production. It is also know that certain CUs, especially April 2013 CU failed during slipstreaming. So I would caution using slipstream.

<GUID> amock

When you finish installing the SharePoint server, the next step is to run the configuration wizard. This Wizard will essentially build you a functioning SharePoint farm. The first farm you build will be the Central Administration server. After the build, if you check out SQL, you will notice two databases that was created: The SharePoint Configuration Database and the Admin_Content Database. Unfortunately, the Admin_Content Database has this long GUID appended to the database name. This is quite annoying because it can cause issue with your SQL DBA. In order to avoid the <GUID>, after you install the SharePoint binaries, do not open the configuration wizard. Instead, open PoweShell as an administrator. Type the following command:

New-SPConfigurationDatabase –Databasename SharePoint_Config –DatabaseServer <SQL Server Instance> -AdministrationContentDatabaseName SharePoint_Admin_Content

A few more dialog box will appear, but by going through Powershell, you will avoid the <GUID> issue. The only other time you need to avoid the <GUID> issue again is when you build out the Search Service Application. There is a script by Spence Harbar that I leveraged several times.

http://www.harbar.net/articles/sp2013mt.aspx

Service Applications

Now there is a myriad of service applications that one can leverage on the new farm. It is best to review your governance and the purpose of building SharePoint to determine which service applications you would like to install. These service applications is also dependent on which version of SharePoint you are running, either the free version (Foundation) or the two licenses ones (Standard or Enterprise).

*No longer available as its separate service application, but rather it has merged within SharePoint 2013 Search Service Application

So with so many choices how do we determine which one to build first? It really depends on the business solution that your organization would like to resolve first. As politically correct that answer is, it is truly the right one. But if was up to me, I would first build State Service Application. Some features as well as some other service applications depends on State being implemented. My close second is Search…I can’t think of any organization that wouldn’t want search available. And if properly scoped, Search can definitely add lots of value to an organization. The rest of these service applications, I would let the business drivers dictate when they are needed.

Loopback

There are times, during search, that I encounter weird and out of the place errors. The issue seems to be always loopback. Because I would rather be proactive than reactive, I would fix the loopback issue during the farm build. What is loopback? This is a security fix that Microsoft introduced awhile back. It prevents users to open up web applications within the server that is hosting the web application. Unfortunately, this security fix also affects SharePoint and causes more problems; breaking several SharePoint functions.

There are two ways of truly fixing this problem, either disable the loopback or create exemptions. The most popular that I have seen was the former than the latter, although it is not considered best practice. I have done both fixes and prefer the latter than the former. Microsoft has provided us a link to fix the issue.

http://support.microsoft.com/kb/896861

Conclusion:

This is my “cheat sheet” on how I would start building out a farm environment. Of course, each administrator can create their own, but I thought put thoughts into paper would be helpful to other administrators.