SQL Best Practices for MOSS

Hello all, 

One question that arises often is how can we correctly configure SQL Server to work with MOSS?. Although the majority of best practices for SQL does apply, there are some diferences that it is important to be aware of.

1 - Architecture recommendation

1.1 - Limit content database size to enhance manageability

Plan for database sizing that will enhance manageability and performance of your environment.

· In most circumstances, to enhance the performance of Office SharePoint Server 2007, we discourage the use of content databases larger than 100 GB. If your design requires a database larger than 100 GB, follow the guidance below:

· Use a single site collection for the data.

· Use a differential backup solution, such as SQL Server 2005 or Microsoft System Center Data Protection Manager, rather than the built-in backup and recovery tools.

· Test the server that is running SQL Server 2005 and the I/O subsystem before moving to a solution that depends on a 100-GB content database.

· Whenever possible, we strongly advise that you split content from a site collection that is approaching 100 GB into a new site collection in a separate content database to avoid performance or manageability issues.

· Limit content databases that contain multiple site collections to approximately 100 GB.

Note: The limits we recommend apply only to a server that is running SQL Server 2005 hosting Office SharePoint Server 2007, and are not general guidance for SQL Server 2005.

1.2 - Configure autogrowth settings

· When you are planning content databases that exceed the recommended size (100 GB), set the database autogrowth value to a fixed number of megabytes instead of to a percentage. This is to reduce the frequency with which SQL Server increases the size of a file. Increasing file size is a blocking operation that involves filling the new space with empty pages.

Note: SQL Server 2005 that is running on Windows Server® 2003 supports instant file initialization. Instant file initialization can greatly reduce the performance impact of a file growth operation.

· When you are planning content databases smaller than the recommended size (100 GB), set the databases to 100 GB when they are created by using the ALTER DATABASE MAXSIZE property.

· If disk space is limited or databases cannot be sized, you should configure the autogrowth value to a fixed percentage. For example, configure the autogrowth value to 10 percent for databases under 500 GB and to a fixed number of megabytes if a database exceeds 500 GB.

2 - Physical storage recommendation

2.1 - Separate and prioritize your data among disks

Ideally, place the tempdb, content databases, and SQL Server 2005 transaction logs on separate physical hard disks.

The following list provides some best practices and recommendations for prioritizing data:

· When prioritizing data among faster disks, use the following ranking:

1. Tempdb data and transaction logs

2. Database transaction log files

3. Search database

4. Database data files

In a heavily read-oriented portal site, prioritize data over logs.

· Testing and customer data have shown that Office SharePoint Server 2007 farm performance can be significantly impeded by insufficient disk I/O for the tempdb. To avoid this issue, allocate dedicated disks for the tempdb. If a high workload is projected or monitored — that is, the average read operation or the average write operation requires more than 20 ms — you might need to ease the bottleneck by either separating the files across disks, or by replacing your disks with faster disks.

· For best performance, place the tempdb on a RAID 10 array. The number of tempdb data files should equal the number of core CPUs, and the tempdb data files should be set at an equal size. Count dual core processors as two CPUs for this purpose. Count each processor that supports hyper-threading as a single CPU.

· Separate database data and transaction log files across different disks. If files must share disks because the files are too small to warrant a whole disk or stripe, or you have a shortage of disk space, put files that have different usage patterns on the same disk to minimize simultaneous access requests.

· Consult your storage hardware vendor for information about how to configure all logs and the search databases for write optimization for your particular storage solution.

· Allocate dedicated spindles for the search database.

2.2 - Use multiple data files for large content databases and the SSP search database

For improved performance for large content databases and the SSP search database, consider using multiple data files.

Notes:

· The use of multiple data files for databases other than content databases and the SSP search database is not supported.

· The use of SQL Server partitioning is not supported for SharePoint databases. Use only simple data files.

2.2.1 - Use multiple data files for content databases

Follow these recommendations for best performance for content databases:

· Create files only in the primary filegroup for the database.

· Distribute the files across separate disks.

· The number of data files should be less than or equal to the number of core CPUs. Count dual core processors as two CPUs for this purpose. Count each processor that supports hyper-threading as a single CPU.

· Create data files of equal size.

2.2.2 - Use multiple data files for the SSP search database

For the search database, we recommend that filegroups be used to segregate the tables that are primarily used for crawl and query processing. The filegroup that hosts the tables most affected by crawling should be moved to a different set of spindles from the primary filegroup to provide the most reduction in impact on the I/O subsystem.

The following tables are primarily related to crawling:

3 - SQL Server recommended practices

· Proactively check the integrity of your databases by running DBCC CHECKDB (‘<DB Name>’) on a routine basis.

· Monitor SQL Server index fragmentation, and follow the SQL Server defragmentation guidelines for SharePoint Products and Technologies provided in the Knowledge Base article How to defragment Windows SharePoint Services 3.0 databases and SharePoint Server 2007 databases 

· Do not perform unsupported operations on the server that runs SQL Server. For details about operations that are not supported, see Microsoft Knowledge Base article 841057: Support for changes to the databases that are used by Office server products and by Windows SharePoint Services

This topics are fully covered in the following white paper Planning and Monitoring SQL Server Storage for Office SharePoint Server: Performance Recommendations and Best Practices (white paper).

Other great resource is located in Database maintenance for Office SharePoint Server 2007 (white paper)

Next post will be SQL Best Practices for Biztalk.

Have a wonderful Christmas!!