Home > Articles

This chapter is from the book

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 Figure 2.15.

FIGURE 2.15

FIGURE 2.15 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.

    FIGURE 2.16

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

    FIGURE 2.17

    FIGURE 2.17 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.

    FIGURE 2.18

    FIGURE 2.18 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.

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. For example, Figure 2.19 shows an example of a document library contained in a read/write content database, and Figure 2.20 is the same document library after setting the content database to Read-Only.

FIGURE 2.19

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

FIGURE 2.20

FIGURE 2.20 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 Figure 2.21.

    FIGURE 2.21

    FIGURE 2.21 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.

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.

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. Figure 2.22 shows an example of changing the data file location to the D: volume and the transaction logs to the L: volume.

FIGURE 2.22

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

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 Figure 2.23. 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.

    FIGURE 2.23

    FIGURE 2.23 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.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020