SharePoint guidance for Microsoft
SQL Server 2008database administrators
Microsoft Corporation
Published: October 2009
Author: Microsoft Office System and Servers Team ()
Abstract
This book is a guide for database administrators who implement or maintain Microsoft Office SharePoint Server 2007. It provides guidelines for improving the performance of SharePoint Server solutions running on Microsoft® SQL Server™ 2008.
This guide provides information about key considerations for configuring storage and SQL Server before installing SharePoint Server; the required steps in installing SharePoint Server when using DBA-created databases; backup and recovery considerations for SharePoint Server, including what can and cannot be protected by SQL Server backups; and maintaining and monitoring SQL Server databases that support a SharePoint Server implementation.
The content in this book is a copy of selected content in the Office SharePoint Server technical library () as of the publication date. For the most current content, see the technical library on the Web.
1
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred.
© 2009 Microsoft Corporation. All rights reserved.
Active Directory, Excel, Outlook, SharePoint, Microsoft, Windows, WindowsServer, and WindowsVista are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
1
Contents
Getting Help
I. Configuring storage and SQL Server for use with SharePoint
Introduction to storage
Know your needs and plan ahead
Planning recommendations
Physical topology recommendations
Start with a dedicated server running SQL Server 2008
Consider scaling out in addition to adding resources
Follow the SQL Server guidelines when choosing hardware
Memory
CPU cache
Bus bandwidth
Disk and SAN interfaces
Disk topology
Physical storage recommendations
Use appropriate disks and RAID arrays
Proactively manage data and log file growth
Limit content database size to enhance manageability
Separate and prioritize your data among disks
Use multiple data files for large content databases and the SSP search database
Use multiple data files for content databases
Use multiple data files for the SSP search database
Follow vendor configuration recommendations
II: Deploying SharePoint using DBA-created databases
Deploy using DBA-created databases
About deploying by using DBA-created databases
Required database hardware and software
Required accounts
Create and configure the databases
Database types and descriptions
Office SharePoint Server 2007 deployed in a server farm environment
After installation
After creating a Web application
After starting the search service
After creating an SSP
After server setting for single sign-on
Office SharePoint Server 2007 deployed in a stand-alone environment
After installation
After server setting for single sign-on
III: Backing up and recovering SharePoint
Choose what to protect
Protecting content databases
Protecting content stored in external data sources
Protecting search
Protecting SSP databases
Protecting single sign-on
Protecting configuration settings
Protecting and recovering IIS configuration settings
Protecting and recovering the configuration database and the Central Administration content database
Protecting customizations
Protecting solution packages
Protecting customizations that are not packaged as solutions
Protecting binary files
Choose backup and recovery tools
Available tools
Built-in backup and recovery tools
Central Administration
Stsadm command-line tool
External backup and recovery tools
Microsoft SQL Server 2008 backup and recovery
Microsoft System Center Data Protection Manager 2007
Windows Server Backup
Third-party solutions and custom tools
Third-party solutions
Solutions built on Volume Shadow Copy Service
IV: Maintain and monitor databases and storage
Maintain the databases that support Office SharePoint Server 2007
Shrink data files
Shrinking a database by using Transact-SQL commands
DBCC SHRINKDATABASE
NOTRUNCATE compacts the data in data files by moving allocated pages from the end of a file to unallocated pages in the front of the file.
DBCC SHRINKFILE
Summary
Fine tune index performance by setting fill factor
Measure and reduce fragmentation
Measure fragmentation in a SQL Server 2008 database (sys.dm_db_index_physical_stats)
Measure fragmentation in a SQL Server 2000 database (DBCC SHOWCONTIG)
Reducing Fragmentation for a Database
Reducing fragmentation for a specific table and its indexes
Using ALTER INDEX
About DBCC CHECKDB
DBCC CHECKDB and performance
Monitoring and troubleshooting storage
Monitor and troubleshoot storage performance
Physical server counters to monitor
Disk counters to monitor
Other monitoring tools
Troubleshooting
Additional references
1
Getting Help
Every effort has been made to ensure the accuracy of this book. This content is also available online in the Office System TechNet Library, so if you run into problems you can check for updates at:
If you do not find your answer in our online content, you can send an e-mail message to the Microsoft Office System and Servers content team at:
If your question is about Microsoft Office products, and not about the content of this book, please search the Microsoft Help and Support Center or the Microsoft Knowledge Base at:
1
I. Configuring storage and SQL Server for use with SharePoint
Introduction to storage
Proper configuration of Microsoft® SQL Server® and data input/output (I/O) subsystems is critical to the optimal performance and operation of server farms that are running Windows® SharePoint® Services 3.0 or Microsoft Office SharePoint Server 2007. (The term SharePoint Products and Technologies is often used to refer to either or both Windows SharePoint Services and Office SharePoint Server.) This topic describes some of the most common practices that the SharePoint Products and Technologies team recommends with respect to storage configuration and monitoring.
Proper planning of the infrastructure, regular monitoring, and fine tuning assure that the products and technologies in the environment are built on a solid foundation.
Our internal investigations have shown that it is best to plan for capacity from the bottom of the stack to the top. First plan your SQL Server capacity, and then plan your application server and front-end Web server capacity. We have also found that it is best to monitor capacity from the bottom of the stack to the top, because stress on the server that is running SQL Server is likely to cause stress on application servers and front-end Web servers. For example, if your server that is running SQL Server is taking significantly more time to respond to a request from a front-end Web server, and if the front-end Web server is receiving requests from end users at the normal rate, requests on the front-end Web server start queuing up, eventually pushing it over its limits.
Know your needs and plan ahead
Before you purchase hardware, determine your information architecture (logical structure and approximate sizing), reliability, and performance requirements for the environment in which you will run SharePoint Products and Technologies and SQL Server.
Your resource plan is dependent on the scenario you are deploying for. As you plan your deployment, review the following planning content:
Planning and architecture for Office SharePoint Server 2007 ()
Planning and architecture for Windows SharePoint Services 3.0 technology ()
If you are upgrading an existing deployment, keep in mind that Windows SharePoint Services 3.0 and Office SharePoint Server 2007 provide a richer platform than their predecessor versions. To support the updated and additional capabilities, the current versions of SharePoint Products and Technologies consume more infrastructure resources than older versions, even if your deployment does not use all of the new capabilities.
Planning recommendations
In planning, your goals are to optimize performance, scale, and manageability.
Optimize performance—Get the most performance out of the hardware. Plan and monitor to avoid bottlenecks on resources when other suitable resources are under-utilized. Proper planning and deployments may yield better results than increasing hardware resources.
Plan for scale—Always take future growth parameters into account. Plan the SQL Server deployment, the storage space, and the sites and SSP deployment in a way that will allow for future growth without the need to rebuild any component of the farm, an event that may require relatively long shut-down periods.
Plan for manageability—Plan your storage infrastructure so that day-to-day administrative tasks (such as loading backups or running SQL Server reindexing jobs) and less frequent jobs (such as restoring content or failing over to a remote site) will be easy to perform.
Physical topology recommendations
The topology of your system’s database tier, and your network, physical storage, and caching can significantly affect system performance.
When you plan your hardware, remember that Office SharePoint Server 2007 is the last version of Office SharePoint Server that will run on 32-bit operating systems and databases.
Important:
If you are using the gradual upgrade method, to maintain reasonable response times from the server running SQL Server 2008, it might be necessary to increase the SQL Server 2008 resources supporting Office SharePoint Server 2007 by at least a factor of two.
The following sections give recommendations that are based on the best practices we have found for SQL Server 2008 databases hosting Office SharePoint Server 2007.
Start with a dedicated server running SQL Server 2008
The following recommendations apply to the database tier in your topology:
Always put SQL Server 2008 on a dedicated server that is not running any other farm roles or hosting databases for any other application, unless you are deploying your system on a stand-alone server.
We highly recommend that you install SQL Server 2008 64-bit version on a 64-bit operating system, unless you have a significant business reason not to.
For optimal performance, use Office SharePoint Server 2007 with SQL Server 2008 with the most recent service pack, unless you have a significant business reason to use an earlier version.
Use SQL Server connection aliases when you configure your server farm. A connection alias is an alternate name that can be used to make a connection to a SQL Server instance. If a database server fails, you can adjust the alias on the front-end Web server to point to another server. For more information, see How to: Set a SQL Server Alias (SQL Server Management Studio) ().
Ensure that the SQL Server 2008 input/output (I/O) channels to the disks are not shared by other applications, such as the swap file and Internet Information Services (IIS) logs.
Consider scaling out in addition to adding resources
It is important to track the following three resource components of a server running SQL Server 2005: CPU, memory, and I/O subsystem. When one or more of the components seem stretched, analyze the appropriate course of action based on the current and projected work load. Then, determine whether to add more resources or to scale out to a new server running SQL Server 2005. In general, we recommend that you consider scaling out in addition to adding more resources. For more information, see Troubleshooting Performance Problems in SQL Server 2008 ().
We recommend that you deploy an additional server running SQL Server 2008 when you have more than four Web servers running at full capacity.
Follow the SQL Server guidelines when choosing hardware
The following sections contain recommendations from the SQL Server 2008 team for hardware that can optimize performance of Office SharePoint Server 2007.
Memory
For the purposes of determining the amount of memory required for the computers running SQL Server 2008, first determine whether the planned deployment is small, medium, or large in terms of memory consumption.
Determine your deployment size by using the following table:
If your deployment parameters are generally less than the listed values, your deployment can be considered small.
If your deployment parameters are approximately equivalent to the listed values, your deployment can be considered medium.
If your deployment parameters are generally greater than the upper limits of most of the listed values, your deployment can be considered large.
Metric / ValueContent database size / 100 GB
Number of content databases / 20
Number of concurrent requests to SQL Server 2008 / 200
Users / 1000
Number of items in regularly accessed list / 2000
Number of columns in regularly accessed list / 20
For SQL Server 2008, 4 gigabytes (GB) is the minimum required memory, 8 GB is recommended for medium size deployments, and 16 GB and greater is recommended for large deployments.
Other factors that can influence your memory needs include:
The use of SQL Server 2008 mirroring.
The frequent use of files larger than 15 megabytes (MB).
CPU cache
On the server running SQL Server 2008, we recommend that the L2 cache per CPU have a minimum of 2 MB to improve memory.
Bus bandwidth
Greater bus bandwidth helps improve reliability and performance. Consider that the disk is not the only user of bus bandwidth— for example, you must also account for network access.
The following list provides some best practices and recommendations for optimizing bus bandwidth.
For medium to large-sized servers, greater bus bandwidth improves the system’s reliability, especially with added multi-pathing software. Conversely, greater bus bandwidth does not give a significant increase in reliability for smaller systems. The bus bandwidth’s reliability is improved through the redundant paths in the system and by avoiding single-point-of-failure in hardware devices.
Greater bus bandwidth provides improved performance in systems that frequently use large block transfers and sequential I/O.
In smaller servers that use mostly sequential I/O, PCI becomes a bottleneck with three disks. For a small server that has eight disks performing mostly random I/O, PCI is sufficient. However, it is more common for PCI-X to be found on servers ranging from small to very large.
Greater bus bandwidth is necessary to support a large number of disks.
The capacity of bus bandwidth might be limited by the topology of the system. If the system uses direct attached disks, the number of slots limits the bus bandwidth capacity. However, for storage area network (SAN) systems, there is no physical limiting factor.
More expensive servers typically have larger and faster buses. There is often no way to increase the capacity of the buses’ bandwidth without replacing the servers. However, the largest servers are more configurable. Consult with server providers for specifications.
Disk and SAN interfaces
The interfaces you use in your system can affect reliability and performance. Larger drives, all else being equal, increase mean seek time. Use the information in the following table to inform your choice of interface.
Interface / Benefits / Disadvantages / NotesSmall Computer System Interface (SCSI) / Supports forcing data to be written to disk, improving recoverability.
SCSI with Tagged Command Queueing (TCQ) supports multiple I/O requests.
Supports hot-swapping.
SCSI can have up to 15 drives per channel.
Less restrictive on physical cable length. / Overloading the channels increases the chance of reaching the transfer rate limit.
Integrated Device Electronics (IDE) / Supports hot-swapping.
IDE has high transfer rates only if there is one drive attached per channel.
Typically greater capacity than SCSI.
Typicallly cheaper per GB than SCSI drives. / Can only handle one outstanding I/O request per channel.
Interface / Benefits / Disadvantages / Notes
Serial Advanced Technology Attachment (SATA) / SCSI with TCQ supports multiple I/O requests.
Supports hot-swapping.
Most are explicitly designed to support only one drive per channel; however, multiple SATA channels of 2 to 12+ on interface cards are also available.
Typically greater capacity than SCSI.
Typically cheaper per GB than SCSI drives.
Serial-attached SCSI (SAS) / Very fast.
Supports SCSI protocol.
Allows for a larger number of disks than SCSI. / Applicable to Direct-attached Storage (DAS) only.
Replacement technology for parallel SCSI.
Backward compatible with SATA drives.
Database redundancy within a data center
You should provide redundancy for either type of storagewithin a data center.