Scale Out Database Servers

Any system is only as strong as its weakest link. In a SharePoint farm, the database role is usually the most common performance bottleneck. Furthermore, content databases store the data most critical to the users. These two reasons alone justify the importance of focusing on the database role to achieve greater performance and fault tolerance. Scaling the database also increases the total storage potential of the SharePoint farm.

Each of the sections that follow delivers at least one of these three goals: increasing performance, increasing fault tolerance, and increasing total storage space. When scaling, always keep your requirements in mind to ensure that you address the most important need. Whereas SQL Server Standard Edition can address all these goals, SQL Server Enterprise Edition is the most scalable and best performing.

Adding New SQL Servers

You can add new SQL servers to SharePoint by specifying a different server name when you’re creating a new content or application service database. This approach sounds simple, but you should have a carefully mapped-out strategy of database server usage to minimize maintenance and recovery. For example, you might choose to store all search databases on one SQL server, whereas content, configuration, and other service databases are on a second server.

For any new SQL server that you add, you must make sure that the farm account (introduced in Chapter 1) has been granted the dbcreator and securityadmin server role permissions. If you’ll be using Windows Authentication to connect to the SQL server (specified when you’re creating the database), the database server must be in the same AD domain or in a trusted domain as other farm servers.

When adding new SQL servers, follow these guidelines:

• For production environments, we strongly recommend that you keep SQL servers dedicated to a single SharePoint farm.
• A typical ratio of WFE-to-database servers is about 4:1.
• Search databases are both read and write intensive with crawler and query activities. As the farm grows to the medium size and beyond, consider having a dedicated SQL server for your search databases.

Here are guidelines for optimizing databases (these tasks are usually done by the SQL database administrator):

• Separate database and log files onto separate physical disks or LUNs (logical unit numbers).
• For large and active databases, including tempdb, add secondary data files to the PRIMARY file group and spread these files across separate physical disks or LUNs. In general, the number of data files should match the number of CPU cores for the server.
• If disk space exists, presize databases that are expected to grow large. If the extra space is not used, the database can be shrunk.
• Have your database administrators (DBAs) set up a database maintenance plan for the server. The most important task (assuming backups are handled separately) is to check database integrity.
• A storage area network (SAN) is usually faster than direct attached storage (DAS).
• When using a SAN, consult your hardware storage vendor for optimal configuration settings.

For more guidance on scaling database servers including estimating the amount of space needed for database servers, see the article “Storage and SQL Server Capacity Planning and Configuration” at http://technet.microsoft.com/en-us/library/cc298801.aspx.

Scaling Using Database Mirroring

Database mirroring is an active/passive design that adds fault tolerance to one or more databases. Active/passive means that only the active server is responding to live requests; the passive server takes over when the active server fails. Hence, mirroring does not increase performance and, depending on the configuration, adds a small degree of overhead.

Mirroring requires at least two SQL server instances, which should be on separate servers. It works by applying transactions (the changes recorded by SQL Server) from the primary database to its mirrored copy running on another server.

Mirroring is configured at the database level, meaning you can choose which databases to mirror. Implementing database mirroring is done inside SQL Server and should be done by qualified DBAs.

SharePoint can automatically redirect requests to the mirror if the primary database is unavailable. SharePoint calls the mirror a failover server. A failover server can be configured when you’re creating or editing content or application service databases. Figure 5.23 shows the failover server settings in the Add Content Database window.

Figure 5.23: Configuring a failover database server

When a primary database (called the principal) or its server goes down, it must be failed over to the mirror database for SharePoint to function. In SQL Server, this can be done automatically by using a third SQL server called the witness server, or it can be done manually by a DBA. For failover to work properly, make sure that SharePoint’s farm account and application pool accounts have login permissions on the mirrored server. For information on how to mirror SQL databases, see the article “How to: Configure a Database Mirroring Session (SQL Server Management Studio)” at http://msdn.microsoft.com/en-us/library/ms188712.aspx.

Scaling Using Clustered SQL Servers

Like mirroring, clustering a SQL server is designed to increase fault tolerance. With clustering, you have two or more identical servers that appear as one server to database clients (like SharePoint). Unlike mirroring, clustering is done at the server level so it’s a more holistic, high-availability solution. SQL Server clustering is an active/passive design and does not increase performance. SQL Server clustering is built on top of clustering at the operating system level (called Microsoft Cluster Service). With SQL Server 2008 Enterprise Edition, you can have up to 16 cluster nodes, whereas with Standard Edition, you can only have 2. In either case, you still only have one active node per SQL instance.

SQL Server clustering is done entirely at the database server and SharePoint is blissfully unaware of it—no SharePoint configuration is needed! Properly implementing a SQL Server cluster is a complex process and should be done by a qualified DBA and storage experts. For more information on clustering, see the article “How to: Create a New SQL Server Failover Cluster (Setup)” at http://msdn.microsoft.com/en-us/library/ms179530.aspx.