Database Management in SharePoint

Database Management

The bulk of SharePoint Server 2013 content is almost entirely contained in SQL Server. As such, a properly designed and managed SQL Server infrastructure is critical to a well-running SharePoint Server environment. Because SQL Server has many books dedicated to the product, you’ll be introduced only to the topics every SharePoint Server administrator should know in this section. Database management is contained in the Application Management section of Central Administration. The majority of Application Management deals with web applications, service applications, and site collections. Although databases are used with all three of these, there is a dedicated section for database management, as shown in 

 Databases are contained in the Application Management grouping.

Content Databases

There are many farm-level settings and configuration options you should be aware of with content databases. When the first content database is created during web application creation, it includes several default options. The following configuration options should be taken into consideration when managing content databases:

·         Size of the content database

·         Number of site collections per content database

·         Status of content databases

·         Read-only content databases

·         Location on the SQL Server physical disk

Controlling Database Sizes

SharePoint Server 2013 does not provide direct functionality to limit the content database size. Although SQL Server can provide this option, it is generally recommended that you control the content database sizes with SharePoint Server 2013 site quotas. First, you need to know that site quotas are actually site collection quotas. There is no native method to limit site quotas. Second, you can limit the number of site collections in a database, but you cannot limit the number of sites. Again, the Central Administration interface is ambiguous on sites versus site collections. When we’re discussing items within Central Administration, the word “sites” always references site collections. To limit the size of a content database using SharePoint Server options, you need to combine the following three SharePoint Server 2013 settings:

·         Maximum Number of Sites That Can Be Created in This Database—This setting is found in Central Administration, Application Management, Manage Content Databases, after selecting a content database.

·         Quotas of the Sites (site collections) Contained in the Database—These settings can be found in Central Administration, Application Management, Configure Quotas and Locks.

 The Database Capacity Settings in Central Administration enables you to limit the number of sites in each content database.

 Site collection quota settings in the Site Collections and Locks section found in Central Administration, Application Management, Configure Quotas and Locks.

·         Percent of Site (site collection) Used for the Second-Stage Recycle Bin—These settings are located in Central Administration, Manage Web Applications, General Settings on the Web Applications tab.

 Configuring the Recycle Bin settings for the web application.

Using the settings just shown, you define the maximum database size by using the following formula:

·         (Maximum number of sites) × (site quota) × (1 + % of live site quota for second stage)

Configuring the Number of Site Collections per Content Database

The default number of sites (site collections) per content database should almost assuredly be changed. The default settings of thousands of sites as the maximum is entirely a fail-safe mechanism in the product. Using the formula previously mentioned, here is the result for a 15,000-site maximum:

·         15,000 sites × 10GB site quota × 1(.50 second stage) = possible database size of 219 terabytes

A more likely scenario is this:

·         20 sites × 10GB site quota × 1(.20 second stage) = possible database size of 250GB

The maximum database size recommended is somewhere between 200GB and 300GB. Your databases can be much larger in theory, but the practical daily management becomes difficult beyond the recommended limit.

NOTE

You should be very careful with maximum site collection sizes (the site quota settings). Large, busy site collections are likely to have SQL locking/blocking errors. A general rule is to have large site collections and a few users or small site collections with a large user population.

If you must have large content databases, try to isolate very busy site collections in a dedicated content database. This gives you the flexibility of managing the disk I/O of the site collection at the SQL level.

Configuring Content Database Status

The Content Database Status can be set to either Ready or Offline. The status of Offline is a bit confusing because the real purpose of taking a content database offline is to not allow more site collections to be created therein. In fact, site collections contained in an offline content database can still be seen and written to. The safest way to limit the number of site collections in a content database is by following these steps:

1.     Turn off warning events by setting the threshold to zero.

2.     Set the maximum number of site collections to the current number listed in the user interface. Be sure to create a new content database before creating a site collection; otherwise, the creation will fail.

Configuring Read-Only Content Databases

SharePoint Server 2013 also supports read-only SQL Server content databases. When you set a content database to Read-Only, the permissions in all site collections will automatically be reflected in the users’ web browsers.

 This is an example of a document library contained in a Read/Write database.

 When the hosting database is set to Read-Only, no editing commands are available, and there will be an informational message across the top of the site.

You can see the current state of a content database by browsing to Central Administration, Application Management, Manage Content Databases, and selecting the relevant database. SharePoint Server 2013 displays only the status, however, and cannot be used to set the database state. To set a database to Read-Only, you must do so from SQL Server Management Studio. To configure a database to be Read-Only, do the following on the SQL Server console:

1.     Open SQL Server Management Studio. (Its location will vary based on your version and edition of SQL Server.)

2.     Locate the SQL Server database you want to modify, right-click, and select Properties.

3.     Select the Options page, and under Other Options scroll down until you see the State options.

4.     Locate Database Read-Only and click False, as shown in.

 Select the down-arrow to the right of False to change the database state to Read-Only.

5.     Change the status from False to True, and click OK.

6.     Restart the SharePoint Servers in the farm.

Setting the Database Location on a SQL Server Physical Disk

Although SharePoint Server 2013 can create databases and perform a minimal SQL Server database setup, you still want to do basic configuration of the databases on the SQL Server physical disks. Maintenance plans and recovery models can be quite extensive and are not covered in this section. It is recommended that you leave the recovery model as it is set by the SharePoint Server Configuration Wizard, unless you have advanced SQL Server experience and can verify that you’ll be in a supported configuration.

NOTE

For more information on SQL Server maintenance plans and system configuration, seehttp://technet.microsoft.com/sqlserver. However, some aspects for regular SQL maintenance do not apply to SharePoint Serve 2013. An example is autocreate statistics. SQL DBAs should validate any maintenance plan changes with the SharePoint Administrator before implementation.

If your SQL Server content must be highly available, service a significant number of requests, or both, you should separate the transaction log files and data files. Content is always written to the transaction log first, regardless of the recovery model. This allows the database to be brought back into a consistent state if you need to recover the database using SQL Server restore tools. Next, a SQL Server checkpoint process runs at regular intervals and writes the transactions to the data file.

NOTE

In the Full Recovery model, transaction logs are retained until you back up the database, at which time the transaction logs are truncated.

When users are viewing your web applications, they are almost always consuming the data file on SQL Server. By contrast, write actions are processed in the transaction log. Therefore, it is safe to assume that in a read-only server farm, the data file physical disk will be the most utilized. Because of the nature of SharePoint Server transactions, the transaction log and data file are usually equally used in a collaborative environment.

By default, SQL Server places both the data files and transaction logs on the same volume on SQL Server. You can change this default behavior by modifying the default SQL Server settings. To change the default location for new databases, do the following on your SQL Server console:

1.     Open SQL Management Studio.

2.     Right-click the server name and select Properties.

3.     Select Database Settings.

4.     In the Database Default Locations Settings, choose a previously created volume.

Note that if multiple volumes share the same physical disks, you will not see a performance increase. If possible, you should separate the transaction logs and the data files on separate physical disks and not on the system volume.

 You can change the database default locations in SQL Server Properties.

NOTE

For current best practice information on separating the disk location of transaction log files and data files, browse to http://technet.microsoft.com/en-us/library/bb402876.aspx.

NOTE

For information on testing the SQL Server I/O subsystem, browse tohttp://technet.microsoft.com/en-us/library/cc966412.aspx.

Changing the Default Database Server

When you installed SharePoint Server 2013, you selected a database server for the configuration database. The SQL Server you selected became the default content database server. You can change this default at any time from Central Administration, Application Management, Specify the Default Database Server. Unless you are in a specialized environment, do not use SQL Server authentication. Windows Authentication is almost always the correct choice. Do not fill in the Database Username and Password fields when using Windows Authentication. SharePoint Server 2013 automatically configures the SQL Server permissions when using Windows Authentication.

Configuring Data Retrieval Service

The Data Retrieval Service was first introduced in Windows SharePoint Services 2.0 and allowed for a connection to internal or external data sources via web services. SharePoint Server 2013 continues to build on the service, and it can be configured for the entire server farm or on a per–web application basis. For the most part, you leave this configuration set to default unless you are requested to change it by a designer or developer. For example, you might need to change it when requiring access to stored procedures on a non–SharePoint Server database, external content source (OLEDB), or XML and SOAP web services from within SharePoint Server 2013.

Configure the Data Retrieval Service

To configure the Data Retrieval Service, browse to Central Administration, Application Management, Configure the Data Retrieval Service. There are seven configuration options:

·         Web Application—Be sure you are selecting the correct web application before continuing. Note that the user interface refers to Global Settings—those are also selected in the web application drop-down menu, as shown in  By default, the global settings for the Data Retrieval Service load when you access the page. If you select a web application from the drop-down list, you are given the option to inherit the global settings.

 Select Change Web Application or Global Settings using the drop-down menu.

·         Customize Web Application—If you want to use web-application scoped settings, clear this option. If you want to override prior web-application changes, you can also select this box to reapply the global settings. This is useful if you made a mistake configuring a specific web application.

·         Enable Data Retrieval Services—Be careful when deciding whether to turn off this option. Both SharePoint Designer 2013 and Visual Studio 2012 might leverage these services via web parts and custom code. Check with your development team before disabling these services.

·         Limit Response Size—Unless directed by your development team, the default OLEDB response size should be selected. You should monitor your server’s memory utilization if you increase the defaults, and you should do so over a period of several days. Large OLEDB queries can quickly use server memory.

·         Update Support—This option is disabled by default, but many developers will want to enable this option. A common reason for doing so is that custom code might call a stored procedure in a non–SharePoint Server 2013 database. This is often more efficient than bringing the data into .NET for processing.

·         Data Source Time-Out—Unless you are calling data sources over a wide area network (WAN), the default timeouts should be sufficient.

·         Enable Data Source Controls—Data Source Controls allow controls to bind to other controls without the need for custom code. This option is usually enabled.