Probably the most important and the most disregarded rule of thumb is to use a dedicated server running SQL Server for SharePoint 2013. To ensure optimal performance for farm operations, it is recommend that you install SQL Server 2008 R2 with SP1 or SQL Server 2012 on a dedicated server that does not run other farm roles and does not host databases for other applications.

You do not want to enable autocreate statistics on a server that hosts SQL Server for SharePoint 2013. Enabling autocreate statistics is not supported for SharePoint Server. SharePoint Server configures the required settings during provisioning and upgrade. Manually enabling autocreate statistics on a SharePoint database can significantly change the execution plan of a query. The SharePoint databases either use a stored procedure that maintains the statistics (proc_UpdateStatistics) or rely on SQL Server to do this.

Another consideration is to use SQL Server client aliases on SharePoint 2013 servers to reference your SQL Server instance(s). The main reason to use SQL aliases is to help SharePoint find its databases through a consistent name. Regardless of the name of the SQL instance, by using SQL aliases, SharePoint is always expecting its database(s) to be on the same alias name. This allows administrators to move SharePoint databases to new hardware, and then change alias settings and have SharePoint find the new hardware with minimal downtime. A second advantage to using aliases is that you can now use descriptive, friendly names, such as SP_Content_Alias, for the server that is hosting your SharePoint content databases.

There are essentially three methods of configuring SQL Server client aliases. The method that you choose should be driven by primarily infrastructure and organizational information technology governance. For instance, if the SharePoint 2013 implementation team members are restricted from logging on directly to the servers running SQL Server, it is possible that they would want remote administration capabilities available from the SharePoint servers. In this situation, installing the full set of management tools would be an option. The SharePoint 2013 implementation team could then use the SQL Server Configuration Manager to create and configure the SQL Server client aliases. It is also recommended to create both 64-bit and 32-bit matching aliases in the event that you would want to utilize the SQL client alias to connect to the database instance with a client application such as SQL Server Management Studio or other 32-bit-based client application. If scripting the implementation of the SharePoint 2013 environment with Windows PowerShell, you may want to use scripts to create the SQL Server client aliases directly in the system registry. Of course, the most common method is to use the operating system provided SQL Client Configuration Utility, Cliconfg.exe. The 64-bit version is found in the default location of C:WindowsSysWOW64 directory, while the 32-bit version of the utility is found in the default location of C:WindowsSystem32. Each of these three methods is described in this section.

SQL Server Configuration Manager configuration of SQL Server client aliases

To configure a SQL Server client alias via SQL Server Configuration Manager, follow these steps:

1. Verify that the user account that is performing this procedure is a member of either the sysadmin or the serveradmin fixed server role.

2. Run Setup for SQL Server on the target computer, and install the following client components:

   • Connectivity Components

   • Management Tools

3. Open SQL Server Configuration Manager.

4. In the navigation pane, click SQL Native Client Configuration.

5. In the main window, under Items, right-click Aliases and select New Alias.

6. In the Alias New -dialog box, in Alias Name, enter a name for the alias.

7. In Port No, enter the port number for the database instance. Make sure that the protocol is set to TCP/IP.

8. In Server, enter the name of the computer that is running SQL Server.

9. Click Apply, and then click OK.

10. Verification: You can test the SQL Server client alias by using SQL Server Management Studio (SSMS), which is available when you install SQL Server client components.

    If you use SSMS to verify, you will need to also create an identical alias in the SQL Native Client Configuration (32bit) | Aliases node, as SSMS is a 32-bit application.

11. Open SSMS.

12. When you are prompted to enter a server name, enter the name of the alias that you created, and then click Connect. If the connection is successful, SSMS is populated with objects that correspond to the remote database.

After you have completed steps 1–12 you should have an alias created within the SQL Configuration Manager similar to Figure 5-7.

Figure 5-7. Configure the SQL client aliases via SQL Server Configuration Manager.

# SQL Alias name to create
$alias = "SharePointDB"
# Server Name of SQL Server
$server = "SQL1"
# Registry locations for the SQL Aliases
$x86 = "HKLM:SoftwareMicrosoftMSSQLServerClientConnectTo"
$x64 = "HKLM:SoftwareWow6432NodeMicrosoftMSSQLServerClientConnectTo"
# Check if the ConnectTo key already exists, and create it if it doesn't.
if ((test-path -path $x86) ne $True)
{
    New-Item $x86
}
if ((test-path -path $x64) ne $True)
{
    New-Item $x64
}
# Additional information for creating Registry entry value
$tcpAliasValue = "DBMSSOCN," + $server
# Creation of TCP Aliases registry entries
New-ItemProperty -Path $x86 Name $alias PropertyType String Value $tcpAliasValue
New-ItemProperty -Path $x64 Name $alias PropertyType String Value $tcpAliasValue
Drive Assignments

If at all possible during initial installation and configuration, increase all data files and log files to their expected final size, or schedule times when you will increase the sizes. Consider any changes that would affect database size and make the size change prior to the system change. Autogrowth is enabled by default. However, it is recommended to change the default autogrowth value for new databases from 1 MB to a much larger fixed number. It is not recommended to use a percentage factor for the autogrowth value. As a general rule, the bigger the database, or the bigger it is expected to be, that should drive larger autogrowth increments in the data and log files for the given database. Rather than using autogrowth as a management feature, consider it a risk contingency for unexpected growth. As a good practice, you should maintain at least 25 percent available space across all database data and log drives to accommodate the growth and peak usage patterns for temporary database objects.

As SQL Server is mission critical to the SharePoint 2013 platform, it is strongly recommended that you continuously monitor the SQL Server storage and performance to ensure that each database is able to deliver the load required of it. Additionally, monitoring over time provides information for future growth and service scenarios. However, ensure that you not only monitor specific SQL Server resources, but also the systems that support the computer running SQL Server, such as the server CPU, memory, cache/hit ratio, and especially the I/O subsystem (disk). Although you can manually monitor the performance counters on the SQL Server system, if an enterprise monitoring solution, such as Microsoft System Center Operations Manager (SCOM) or similar system, is in place, use it to proactively monitor and alert you of issues prior to them resulting in system downtime.

A significant boost to SharePoint 2013 backup operations can be the use of SQL Server backup compression. It is available in SQL Server 2008 R2 SP1 Standard, Enterprise, and Datacenter editions and SQL Server 2012 Standard, Business Intelligence, and Enterprise editions. If you set the compression option in your backup script or configure SQL Server to compress by default, you can significantly reduce the size of your database backups and/or shipped logs.

The configuration database is the “brain” of the SharePoint 2013 farm. It is responsible for maintaining data about the entire farm, including all database, Internet Information Services (IIS) websites, web applications, and solutions. It must be collocated with the SharePoint 2013 Central Administration content database. Typically, it is recommended to initially allocate 2 GB for the configuration database. Over time, it may grow beyond the initial size, but fairly slowly—approximately 40 MB for each 50,000 site collections. Additionally, growth can increase with farm solutions, as they are stored here, and other items, such as administrative Microsoft InfoPath forms. There can be only one configuration database per SharePoint 2013 farm, so it can only be scaled up. The configuration database is read-intensive. The default recovery model is initially set to Full, but it is recommended to be switched to the simple recovery model to restrict the growth of the log file, if possible.

The SharePoint 2013 Central Administration content database is similar to any other content database except, of course, that it is the content database of the administration web application. Technically, it should be considered a configuration database. Initially, it is recommended to size the Central Administration content database to at least 1 GB, but platform usage scenarios may grow the database, especially if using integrated SQL Server 2012 SP1 BI functionality in the farm, specifically PowerPivot. Also, because of this characteristic, the read/write characteristics could vary. Similar to the configuration database, the Central Administration database must be scaled up, as only one Central Administration content database is supported per farm. The default recovery model is Full.

If SQL Server 2012 SP1 PowerPivot for SharePoint 2013 is installed, the Central Administration content database also stores the Microsoft Excel worksheets and the PowerPivot data files used in the PowerPivot Management Dashboard. Therefore, if you use PowerPivot for SharePoint 2013 and use the default settings that keep the usage data collection and data refresh history for 365 days, the Central Administration content database will grow over the year.

The SharePoint 2013 content databases store all content for the user web applications. By default, if no other content databases are added to the web application, all site collections will reside in the original, initial content database created at the time of web application creation. However, administrators can add additional content databases to distribute site collections across different content databases. However, keep in mind, a single site collection cannot span multiple content databases. The content includes documents, files, list data, audit logs, as well as user information. Content database sizing and limits varies significantly, based on two primary factors: usage scenario and the SQL Server storage (disk) subsystem input/output operations per second (IOPS). Microsoft still strongly recommends limiting the size of the content databases to 200 GB, but under certain combinations of usage scenarios and storage system IOPS, sets no explicit limit of what it will support. To surpass the secondary 4 TB supportable limit, the SharePoint 2013 site on the content database must be based on the Document Center or Records Center site template, as well as refrain from using alerts, workflows, link fix-ups, or item-level security on any SharePoint Server 2013 objects in the content database. For scaling, content databases supporting a single site collection must scale up, as a site collection cannot span multiple content databases. However, multiple content databases per web application is supported. The default recovery model for the content database is Full.

One quick note about Remote BLOB Storage (RBS) and database sizing, as there is a significant amount of bad information on the Internet regarding RBS and SharePoint 2013. If you are using RBS, the total volume of the combination of both the RBS storage subsystem and the metadata in the content database must not exceed the 200 GB limit (http://technet.microsoft.com/en-us/library/cc262787.aspx).

The App Management service application utilizes the App Management database to store the licenses and permissions downloaded from the SharePoint Store or App Catalog. You should scale-up when the database reaches 10 GB. It is only write-heavy during app installation and license renewal. The default recovery model is initially set to Full.

The Business Data Connectivity database simply stores connections, external content types, and related objects. As such, the database size is directly proportional to the number of connections to external data. It can only be scaled up within the service application, as only one Business Data Connectivity database is supported per Business Data Connectivity service application. It is read-heavy and has an initial default recovery of Full.

The SharePoint 2013 Search service application now has four databases to support the new Search service application architecture. Because search is such a critical component of any SharePoint 2013 environment, and with many new areas of functionality dependent on the SharePoint 2013 Search service application, it is important to have a good understanding of each of the databases, their roles, and their performance characteristics in supporting the SharePoint 2013 environment.

The Search Administration database stores configuration data for the Search service application, including topology, crawl rules, query rules, search schemas at farm, site, and web levels, managed property mappings, content sources, and crawl schedules. Additionally, it stores the Analytics settings for the SharePoint 2013 Search service application. Unlike the previous version’s Search Administration database, it no longer stores access control lists (ACLs). Although it is possible to deploy multiple Search Admin components for fault tolerance, there is only one active component, and thus only one Search Administration database, so the database supports only scale-up. It has an equal read/write ratio, and sizing depends on number of search configuration objects contained in the service application. The default recovery model of the Search Administration database is Simple.

The SharePoint 2013 Search service application Analytics Reporting database stores the results of usage analysis, as well as Search reports. The reports include item reports, such as number of views for an item over time and unique users viewing an item over time, and site-level reports for tenants, site collections, and websites. The data in the site-level reports are aggregated to monthly views every 14 days by default. Typically, the Analytics Reporting database is write-heavy during the default nightly analytics update. It can be scaled both up and out, with the utilization of splitting the database when the database becomes larger than 200 GB. The default recovery model for the Analytics Reporting database is Simple.

The SharePoint 2013 Search service application Crawl database is used by the crawl component and stores information about the crawled items and crawl history tracking, such as the last crawl time, last crawl ID, and the type of update during the last crawl. It should be noted in SharePoint 2013 Search, the crawl and index components are separate, unlike the previous SharePoint Server 2010 Search service application. However, this separation is similar to FAST Search Server for SharePoint 2010 crawl and index components. Each crawl component or server role communicates with all Crawl databases. Additionally, the same host can be distributed across multiple crawl databases. The Crawl database is read-heavy and can be scaled out by creating additional Crawl databases. A new Crawl database should be created for every 20 million items crawled. The default recovery model is Simple.

The SharePoint 2013 Search service application Link database stores links extracted by the Search service application’s Content Processing Component (CPC), as well as information about the number of times that users click a result from the search results. It is write-intensive during content processing. The Link database grows approximately 1 GB per 1 million documents processed and approximately 1 GB per 1 million queries. It should be scaled out with additional Link database(s) for every 60 million documents crawled. You should also add an additional Link database per 100 million expected queries per year. The default recovery model for the Crawl database is Simple.

The Secure Store database supports the SharePoint 2013 Secure Store service application and stores and maps credentials, such as account names, passwords, and now certificates. It is relatively small, depending on the number of target applications, fields per target application, and number of users stored in each target application defined within the Secure Store service application. If auditing, which is recommended, is turned on, the number of read and write operations against a given target application can affect the database sizing. It has an equal read/write ratio. This database should be scaled up. The default recovery model is Full.

The Usage database is used by the SharePoint 2013 Usage and Health Data Collection service application to store health monitoring and usage data temporarily. It also can be used for reporting and diagnostics. Interestingly, it is the only SharePoint 2013 database to support schema modifications. You can expect the database to be extremely large, depending on several factors, including retention factor, number of items enabled for logging and external monitoring, the number of web applications in the farm, the number of active users, and features enabled in the SharePoint 2013 environment. Unfortunately, this database must only scale up, as only one Usage database in one Usage and Health Data Collection service application is supported per SharePoint 2013 farm. The default recovery model of the Usage database is Simple.

The Subscription Settings database supports the SharePoint 2013 Microsoft SharePoint Foundation Subscription Settings service application, storing features and settings for hosted customers. Unlike the previous version of SharePoint, which used the service application and database only to support multitenancy, the new SharePoint App platform requires the implementation and configuration of the Microsoft SharePoint Foundation Subscription Settings service application. It is relatively small in size and primarily read-intensive. The default recovery model is Full.

The Profile database is one of three databases supporting the User Profile service application. It stores all user profiles, audiences, and activities. It also stores memberships in distribution lists and sites. The primary growth factors include the number of users and the use of news feeds, which grow with user activities. The default setting retains the last two weeks of activities. It is a primarily read-intensive database. Because there is only one Profile database per SharePoint 2013 User Profile service application, the database can only be scaled up. The default recovery model is Simple.

The User Profile service application Synchronization database stores configuration and staging data for use when the User Profile Synchronization Service is synchronizing user profile data with any number of services. It is important to note that if the new Active Directory Import synchronization connection is used, the Synchronization database still exists but will remain empty. The Synchronization database maintains an equal read/write ratio, while growth is determined by the number of users and systems being synchronized by the User Profile Synchronization service. Because each User Profile service application supports only one Synchronization database, scaling is done via scale-up. The default recovery model is Simple.

The Social database stores ratings, tags, and comments created by users, along with their respective URLs. Keep in mind, however, that some social information, such as ratings models and settings, is now contained in the content databases, which is a change from the Social database in the previous version. Sizing and growth of the Social database varies greatly and depends primarily on number of tags, ratings, and notes that have been created and used in the environment. Like the other User Profile service application databases, there is only one Social database per service application. Therefore, you would scale up this database. The default recovery model is Simple.

The Word Automation Services service application uses the Word Automation database to store information about pending and completed document conversions and updates. It is a small database and read-intensive, reading once per conversion item. This database also scales up and has a default recovery model of Full.

Supporting the Managed Metadata service application, the Managed Metadata database stores managed metadata, syndicated content types, and a hierarchical structure for items that are used for tagging content and building site collections. The primary initial sizing and growth factor is based on the amount of metadata and syndicated content types, but considered to be small to medium. It can be scaled up. It is read-intensive and has a default recovery model of Full.

The Machine Translation service application is a new service application in SharePoint 2013 providing automatic machine translation of files and sites. When a request is sent to the service, either synchronously or asynchronously, it is processed and forwarded to a cloud-hosted machine translation service, where the work is performed and returned. The Machine Translation Services database is small and read-intensive. The default recovery model is Full.

The State Service database stores temporary state information for InfoPath Forms Services, Microsoft Exchange Server, the Chart Web Part, and Microsoft Visio Services. It is read-intensive and scales up. Sizing and growth factors are solely dependent on the usage of features within the SharePoint 2013 farm that store data in the State Service database. The State Service database has a default recovery model of Full.

The PowerPivot database supports the SQL Server PowerPivot service application. As will be detailed later in the chapter, it requires SQL Server 2012 SP1 SQL Server Analysis Services (SSAS) and SQL Server 2012 SP1 Business Intelligence or Enterprise edition to be installed. The PowerPivot for SharePoint SSAS instance does not need to exist on the servers running SharePoint 2013. When SQL Server 2012 SP1 PowerPivot for SharePoint 2013 is used within the farm, the PowerPivot database stores data refresh schedules, and PowerPivot usage data then is copied from the usage data collection database. Additionally, SQL Server 2012 SP1 PowerPivot for SharePoint 2013 stores more data in the Central Administration Content database and Content databases, as mentioned previously. It is a small, read-intensive database, with typically insignificant growth and a default recovery model of Full.

The PerformancePoint Services database stores temporary objects and persisted user comments and settings to support the functionality of the PerformancePoint Services service application. It is a small, read-intensive database that should be scaled up per service application instance. It has a default recovery model of Full.

The new Access Services feature is a completely new service application available in SharePoint 2013. There is still the ability to use Access Services 2010, hosted in its own backward-compatible service application within SharePoint 2013, but it is not recommended. Although there isn’t a dedicated database for the Access Services 2013 service application per se, it does require SQL Server 2012 to store the user-created Microsoft Access 2013 databases. It is recommended to have a dedicated, separate SQL Server 2012 database engine instance for storing the user-created Access 2013 databases. In addition, it is important to remember to consider the Access 2013 App databases in disaster recovery plans because the content databases only contain links to the Access 2013 App, where the user-created functionality is in its own database.

The SSRS Report Server Catalog database stores all report metadata such as report definitions, report history, report snapshots, and report scheduling information. However, report documents are stored in the SharePoint 2013 content databases. The Report Server Catalog database must be located on the same database server as the Report Server TempDB database. It is small, read-heavy, and should only be scaled up. The Report Server Catalog database has a default recovery model set to Full.

The SSRS 2012 for SharePoint 2013 service application uses the Reporting Server TempDB to store all the temporary snapshots currently executing in the environment. It must be located on the same database server instance as the Report Server Catalog database. The sizing and growth can vary greatly, depending on the number and use of cached report snapshots and their caching settings. It is read-intensive and can only be scaled up. It has a default recovery model of Full.

The Report Server Alerting database of the SSRS 2012 for SharePoint 2013 service application is used for storing information about the Data Alerts and run-time data necessary to produce Data Alerts for Reporting Services reports. Data from reports is processed in the database based on the rules in the Reporting Services Alert Definitions. It must also be on the same database server as the Report Server Catalog database and can vary in size depending on the use of Data Alerts within the Reporting Services service application. If used, it will have an equal read/write ratio that is intensive. The recommended scaling method is scaled up to optimize the file I/O and memory consumption. The default recovery model is Full.