針對 SharePoint 伺服器陣列 SQL Server 的最佳作法Best practices for SQL Server in a SharePoint Server farm

摘要:了解如何在 SharePoint Server 2016 和 SharePoint Server 2013 伺服器陣列中為 SQL Server 實作最佳作法。Summary: Learn how to implement best practices for SQL Server in a SharePoint Server 2016 and SharePoint Server 2013 farm.

當您設定及維護 SQL Server 2014 Service Pack 1 (SP1) 或 SQL Server 2016 上的 SharePoint Server 2016 關聯式資料庫,必須選擇提能夠升效能和安全性的選項。同樣地,當您設定及維護 SQL Server 2008 R2 Service Pack 1 (SP1)、SQL Server 2012、SQL Server 2014 上的 SharePoint Server 2013 關聯式資料庫,也必須選擇提升效能和安全性的選項。When you configure and maintain SharePoint Server 2016 relational databases on SQL Server 2014 with Service Pack 1 (SP1) or SQL Server 2016, you have to choose options that promote performance and security. Likewise, you have to choose options that promote performance and security when you configure and maintain SharePoint Server 2013 relational databases on SQL Server 2008 R2 with Service Pack 1 (SP1), SQL Server 2012, and SQL Server 2014.

本文中的最佳作法會依據應用順序排列,包括從安裝與設定 SQL Server、部署 SharePoint Server 以及維護伺服器陣列。大部分的作法都會套用到所有版本的 SQL Server。SQL Server 各版本專屬的作法將以獨立章節說明。The best practices in this article are ordered based on the sequence in which they would apply, from installing and configuring SQL Server, to deploying SharePoint Server, and then maintaining the farm. Most of the practices apply to all versions of SQL Server. Practices that are unique to SQL Server versions are shown in separate sections.

注意

如果您想要在 SharePoint Server 2016 伺服器陣列中使用 SQL Server 商業智慧元件,則必須使用 SQL Server 2016 CTP 3.1 或更新版本。您現在可以下載 SQL Server 2016 CTP 3.1 或更新版本以使用 SQL Server Power Pivot for SharePoint 增益集。您也可以透過在 SharePoint 整合模式中安裝 SQL Server Reporting Services (SSRS),以及從 SQL Server 安裝媒體安裝 SSRS 前端增益集來使用 Power View。If you plan to use SQL Server Business Intelligence components in a SharePoint Server 2016 farm you must use SQL Server 2016 CTP 3.1 or later. You can now download SQL Server 2016 CTP 3.1 or later to use the SQL Server Power Pivot for SharePoint add-in. You can also use Power View by installing SQL Server Reporting Services (SSRS) in SharePoint-integrated mode and the SSRS front-end add-in from the SQL Server installation media.

如需詳細資訊,請下載最新 Deploying SQL Server 2016 PowerPivot and Power View in SharePoint 2016白皮書。如需在多部伺服器 SharePoint Server 2016 伺服器陣列中設定及部署商業智慧的詳細資訊,請下載 Deploying SQL Server 2016 PowerPivot and Power View in a Multi-Tier SharePoint 2016 FarmFor more information, download the new Deploying SQL Server 2016 PowerPivot and Power View in SharePoint 2016 white paper. For details about configuring and deploying business intelligence in a multiple server SharePoint Server 2016 farm, download Deploying SQL Server 2016 PowerPivot and Power View in a Multi-Tier SharePoint 2016 Farm.

注意

如果您打算在 SharePoint Server 2013 伺服器陣列中使用 SQL Server 商業智慧元件,必須使用 SQL Server 2012 Service Pack 1 (SP1) 或SQL Server 2014。如需有關 SQL Server 2012 SP1 商業智慧 (BI) 和 SharePoint Server 2013 的詳細資訊,請參閱<安裝具有 SharePoint 2013 的 SQL Server BI 功能 (SQL Server 2012 SP1)>。如需有關 SQL Server 2014 和 SharePoint Server 2013 的詳細資訊,請參閱安裝 SQL Server 2014 商業智慧功能If you plan to use SQL Server Business Intelligence components in a SharePoint Server 2013 farm you must use SQL Server 2012 with Service Pack 1 (SP1) or SQL Server 2014. For information about SQL Server 2012 with SP1 BI and SharePoint Server 2013, see Install SQL Server BI Features with SharePoint 2013 (SQL Server 2012 SP1). For more information about SQL Server 2014 and SharePoint Server 2013, see Install SQL Server 2014 Business Intelligence Features.

重要

本文中的最佳作法適用於具有 SharePoint Server 之 SQL Server 的關聯式資料庫管理系統 (RDBMS)。Best practices in this article apply to the Relational Database Management System (RDBMS) of SQL Server with SharePoint Server.

使用 SQL Server 的專用伺服器Use a dedicated server for SQL Server

為了確保伺服器陣列作業的最佳效能,建議您在未執行其他伺服器陣列角色或未裝載其他應用程式之資料庫的專用伺服器上安裝 SQL Server。唯一的例外是在單一伺服器伺服器陣列角色中部署 SharePoint Server 2016 或在獨立伺服器上部署 SharePoint 2013,這用於開發或測試,並不建議用於實際執行環境。如需詳細資訊,請參閱<SharePoint Server 2016 中 MinRole 和相關聯服務的描述>或<在含 SQL Server 的單一伺服器上安裝 SharePoint Server 2016>。To ensure optimal performance for farm operations, we recommend that you install SQL Server on a dedicated server that does not run other farm roles and does not host databases for other applications. The only exception is deployment of SharePoint Server 2016 in a Single-Server farm role or SharePoint 2013 on a stand-alone server, which is meant for development or testing, and is not recommended for production use. For more information, see Description of MinRole and associated services in SharePoint Server 2016 and Install SharePoint Server 2016 on one server.

注意

針對關聯式資料庫使用專用伺服器的建議同樣適用於在虛擬環境中部署 SQL Server。The recommendation to use a dedicated server for relational databases also applies to deploying SQL Server in virtual environments.

設定特定的 SQL Server 設定後再部署 SharePoint ServerConfigure specific SQL Server settings before you deploy SharePoint Server

為了確保行為與效能一致,請先設定下列選項和設定後,再部署 SharePoint Server。To ensure consistent behavior and performance, configure the following options and settings before you deploy SharePoint Server.

  • 請不要在 SharePoint 內容資料庫上啟用自動建立統計資料功能。SharePoint Server 不支援啟用自動建立統計資料功能。SharePoint Server 會在佈建和升級期間進行必要設定。手動啟用 SharePoint 資料庫的自動建立統計資料功能會大幅改變查詢的執行計畫。SharePoint 資料庫可使用維護統計資料 (proc_UpdateStatistics) 的預存程序或仰賴 SQL Server 進行。Do not enable auto-create statistics on SharePoint content databases. Enabling auto-create statistics is not supported for SharePoint Server. SharePoint Server configures the required settings during provisioning and upgrade. Manually enabling auto-create 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.

  • 針對代管 SharePoint 資料庫的 SQL Server 執行個體將平行處理原則 (MAXDOP) 的最大程度設為 1,確保單一 SQL Server 程序能夠因應每個要求。Set max degree of parallelism (MAXDOP) to 1 for instances of SQL Server that host SharePoint databases to make sure that a single SQL Server process serves each request.

    重要

    將平行處理原則的最大程度設成其他任何數字可能導致查詢計劃無法最佳化,進而降低 SharePoint Server 效能。Setting the max degree of parallelism to any other number can cause a less optimal query plan to be used that will decrease SharePoint Server performance.

  • 若要協助簡化維護程序,例如輕鬆將資料庫移至其他伺服器,可針對 SQL Server 的所有執行個體建立指向 IP 位址的 DNS 別名。如需 DNS 或主機名稱別名的詳細資訊,請參閱如何為 SQL Server 執行個體新增主機名稱別名 (英文)。To help simplify maintenance, such as to make it easier to move databases to another server, create DNS aliases that point to the IP address for all instances of SQL Server. For more information about DNS or Hostname aliases, see How to Add a Hostname Alias for a SQL Server Instance.

如需這些 SQL Server 設定和選項的詳細資訊,請參閱<設定 SQL Server 選項>。For more information about these SQL Server settings and options, see Set SQL Server options.

強化資料庫伺服器後再部署 SharePoint ServerHarden the database server before you deploy SharePoint Server

建議您先規劃並強化資料庫伺服器後,再部署 SharePoint Server。如需詳細資訊,請參閱:We recommend that you plan for, and harden the database server before you deploy SharePoint Server. For more information, see:

設定資料庫伺服器的效能與可用性Configure database servers for performance and availability

資料庫伺服器的設定與前端伺服器和應用程式伺服器相同,會影響 SharePoint Server 的效能。有些資料庫必須和其他資料庫設置在同一台伺服器。相反地,有些資料庫則無法和其他資料庫設置在同一部伺服器。如需詳細資訊,請參閱 SharePoint Server 2016 中 MinRole 和相關聯服務的描述規劃及設定儲存設備與 SQL Server 容量 (SharePoint Server)As is the case with front-end servers and application servers, the configuration for database servers affects how well SharePoint Server performs. Some databases have to be on the same server as other databases. Conversely, some databases cannot be on the same server as other databases. For more information, see Description of MinRole and associated services in SharePoint Server 2016 and Storage and SQL Server capacity planning and configuration (SharePoint Server).

如需使用鏡像的高可用性資料庫指引,請參閱資料庫鏡像 (SQL Server)For guidance about highly available databases that use mirroring, see Database Mirroring (SQL Server).

SQL Server 容錯移轉叢集和 Always On 可用性群組SQL Server Failover Clustering and Always On Availability Groups

SQL Server 2012 已引進「AlwaysOn 可用性群組」功能。這項功能是高可用性和災害復原解決方案,為資料庫鏡像和記錄傳送解決方案的替代方案。AlwaysOn 可用性群組現在最多支援九個可用性複本。SQL Server 2012 introduced the AlwaysOn Availability Groups feature. This feature is a high availability and disaster recovery solution that's an alternative to database mirroring and log shipping solutions. AlwaysOn Availability Groups now support up to nine availability replicas.

注意

SQL Server 的未來版本會取代資料庫鏡像。建議使用 Always On 可用性群組。Database mirroring will be deprecated in future versions of SQL Server. We recommend using Always On Availability Groups.

AlwaysOn 可用性群組需要 Windows Server 容錯移轉叢集 (WSFC) 叢集。可為每個已建立的可用性群組建立 WSFC 資源群組。如需詳細資訊,請參閱下列資源:AlwaysOn Availability Groups require a Windows Server Failover Clustering (WSFC) cluster. A WSFC resource group is created for every availability group that is created. For more information, see the following resources:

針對最佳傳送量和管理性設計儲存Design storage for optimal throughput and manageability

建議您區隔資料庫伺服器磁碟機中的資料,並排定優先順序。在理想的狀況下,應可將 tempdb 資料庫、內容資料庫、使用狀況資料庫、搜尋資料庫及交易記錄檔放在獨立的實體硬碟內。下列清單將提供一些指引。如需詳細資訊,請參閱<設定資料庫>。We recommend that you separate, and prioritize your data among the drives on the database server. Ideally, you should place the tempdb database, content databases, usage database, search databases, and transaction logs on separate physical hard disks. The following list provides some guidance. For more information, see Configure databases.

  • 對於共同作業或更新頻繁的網站,可使用下列排名進行儲存散佈。For collaboration or update-intensive sites, use the following ranking for storage distribution.

    最高排名項目應該在最快速的磁碟機中。The highest ranked item should be in the fastest drives.

    1) tempdb 資料檔案與交易記錄,2) 內容資料庫交易記錄檔案,3) 搜尋資料庫 (搜尋管理資料庫除外),4) 內容資料庫資料檔案tempdb data files and transaction logs, 2) Content database transaction log files, 3) Search databases, except for the Search administration database, 4) Content database data files

  • 在讀取密集的的入口網站中,依下列內容排定資料和交易記錄搜尋的優先順序。In a heavily read-oriented portal site, prioritize data and search over transaction logs as follows.

    最高排名項目應該在最快速的磁碟機中。The highest ranked item should be in the fastest drives.

    1) tempdb 資料檔案與交易記錄,2) 內容資料庫資料檔案,3) 搜尋資料庫 (搜尋管理資料庫除外),4) 內容資料庫交易記錄檔案tempdb data files and transaction logs, 2) Content database data files, 3) Search databases, except for the Search administration database, 4) Content database transaction log files

  • 測試和使用者資料顯示,tempdb 的磁碟 I/O 不足會大幅妨礙整體伺服器陣列的效能。若要避免此問題,請為儲存 tempdb 資料檔案的磁碟機配置專用磁碟。Testing and user data shows that insufficient disk I/O for tempdb can significantly impede overall farm performance. To avoid this issue, allocate dedicated disks for the drive that stores tempdb data files.

  • 若要達到最佳效能,請針對儲存 tempdb 資料檔案的磁碟機使用 RAID 10 陣列。tempdb 資料檔案的數量應與 CPU 核心的數量相同,而且每個 tempdb 資料檔案應設定成相同大小。For best performance, use a RAID 10 array for the drive that stores tempdb data files. The number of tempdb data files should equal the number of CPU cores, and each tempdb data file should be set to the same size.

  • 區隔不同磁碟中的資料庫資料和交易記錄檔案。若資料和記錄檔案因空間限制而必須共用磁碟,請將使用模式不同的檔案放在同一個磁碟,可減少同時存取的要求。Separate database data and transaction log files across different disks. If data and log files must share disks due to space limitations, put files that have different usage patterns on the same disk to minimize concurrent access requests.

  • 針對使用頻繁的內容資料庫使用多個資料檔案,並將每個檔案放在其專屬的磁碟中Use multiple data files for heavy-use content databases, and put each on its own disk

  • 若要改善管理性,請加以監控並視需求調整,將內容資料庫容量維持在 200 GB 以下,而非限制資料庫大小。To improve manageability, monitor and make adjustments as needed to keep content databases below 200 GB, rather than restrict the database size.

    注意

    若是在 SQL Server 中手動限制資料庫大小,當容量不足時會造成未預期的系統停機。If you manually restrict database size in SQL Server, you can cause unexpected system downtime when the capacity is exceeded.

妥善設定 I/O 子系統對於達到 SQL Server 系統最佳效能與運作而言非常重要。如需詳細資訊,請參閱監視磁碟使用量 (英文)。Proper configuration of I/O subsystems is very important to the optimal performance and operation of SQL Server systems. For more information, see Monitoring Disk Usage

提示

請考慮測量磁碟速度的方式會因資料檔案和記錄檔案而異。對於資料庫資料而言速度最快的磁碟機,不一定對於記錄檔案也是如此。請將使用模式、I/O 及檔案大小列入考量。Consider that how you measure disk speed varies between data files and log files. The fastest drives for database data may not be the fastest for log files. Consider usage patterns, I/O, and file size.

主動管理資料與記錄檔案的增長Proactively manage the growth of data and log files

下列是針對主動管理資料與記錄檔增長所提出的建議:Following are recommendations to proactively manage the growth of data and log files:

  • 儘量將所有資料檔案和記錄檔案提升到預期的最終尺寸,或是定期在設定期間提升尺寸,例如每個月或每六個月;或者在導入全新儲存頻繁的網站前進行,例如檔案移轉期間。When possible, increase all data files and log files to their expected final size, or periodically increase these at set periods, for example, every month or every six months, or before rollout of a new storage-intensive site such as during file migrations.

  • 將資料庫自動成長設為保護措施,可避免耗盡資料與記錄檔案的空間。請考慮下列內容:Enable database autogrowth as a protective measure to make sure that you do not run out of space in data and log files. Consider the following:

    重要

    您必須將和使用自動成長功能有關的效能和操作問題列為重要因素。如需詳細資訊,請參閱考慮 SQL Server 中的「自動成長」和「自動壓縮」設定You must factor in the performance and operations issues associated with using autogrowth. For more information, see Considerations for the "autogrow" and "autoshrink" settings in SQL Server.

    • 全新資料庫預設為以 1 MB 為增量成長。由於自動成長的預設設定會增加資料庫的尺寸,因此請勿仰賴預設設定;而是使用<設定 SQL Server 選項>提供的指引。The default settings for a new database are to grow by 1 MB increments. Because this default setting for autogrowth results in increases in the size of the database, do not rely on the default setting. Instead, use the guidance provided in Set SQL Server options.

    • 將自動成長值設為固定的 MB 數值,而非百分比。資料庫越大,成長增量應該就越大。Set autogrowth values to a fixed number of megabytes instead of to a percentage. The bigger the database, the bigger the growth increment should be.

      注意

      設定 SharePoint 資料庫的自動成長功能時請務必留意。如果將資料庫設為依百分比自動成長,例如 10 個百分比 (%) 的成長率,則容量 5 GB 的資料庫每次就會成長 500 MB,資料檔案也必須隨之擴張。此時磁碟空間可能就會不足。Use care when you set the autogrowth feature for SharePoint databases. If you set a database to autogrow as a percentage, for example at a 10-percent (%) growth rate, a database that is 5 GB grows by 500MB every time that a data file has to be expanded. In this scenario, you could run out of disk space.

      請考慮像下列這樣的情況:內容不斷增加,而且以 100 MB 為增量、自動成長設為 10 MB。一個全新文件管理網站突然需要非常大量的資料儲存空間,初始大小可能就需要 50 GB。對於這樣的大型新增容量,以 500 MB 為增量成長,會比以 10 MB 為增量成長更合適。Consider for example, a scenario where content is gradually increased, say at 100MB increments, and autogrowth is set at 10MB. Then suddenly a new document management site requires a very large amount of data storage, perhaps with initial size of 50 GB. For this large addition, growth at 500 MB increments is more appropriate than 10MB increments.

    • 若為受管理的實際執行系統,請考慮自動成長僅僅只是非預期成長的一項應變狀況而已。請勿使用自動成長選項管理日常的資料和記錄成長,而是要將自動成長設為允許在一年內允許的約略大小,接著再新增 20% 的錯誤容忍範圍。另外也請設定警示,可在資料庫空間即將耗盡或接近大小上限時通知您。For a managed production system, consider autogrowth to be merely a contingency for unexpected growth. Do not use the autogrow option to manage your data and log growth on a day-to-day basis. Instead, set the autogrowth to allow for an approximate size in one year and then add a 20 percent margin for error. Also set an alert to notify you when the database runs low on space or approaches a maximum size.

  • 將磁碟機維持在至少 25% 的可用空間,方可適應成長與尖峰使用模式。如果要為 RAID 陣列新增磁碟機,或是配置更多需管理的儲存體,請務必嚴密監控容量,避免空間不足。Maintain a level of at least 25 percent available space across drives to accommodate growth and peak usage patterns. If you add drives to a RAID array or allocate more storage to manage, monitor capacity closely to avoid running out of space.

持續監控 SQL Server 儲存與效能Continuously monitor SQL Server storage and performance

建議您持續監控 SQL Server 儲存與效能,確保每部實際執行資料庫伺服器都能妥善處理增加的負載。此外,持續監控也能讓您建立規劃資源時所使用的基準。We recommend that you continuously monitor SQL Server storage and performance to make sure that each production database server is adequately handling the load put on it. Additionally, continuous monitoring enables you to establish benchmarks that you can use for resource planning.

全面檢視資源監控。請勿限制監控 SQL Server 特定的資源。追蹤在執行 SQL Server 的電腦上的下列資源也同樣重要:CPU、記憶體、快取/點擊比率以及 I/O 子系統。Take a comprehensive view of resource monitoring. Do not limit monitoring to resources that are specific to SQL Server. It is equally important to track the following resources on computers that are running SQL Server: CPU, memory, cache/hit ratio, and the I/O subsystem.

當一或多個伺服器資源似乎速度過慢或負荷過大時,請依據目前與預計工作量考慮下列效能指引。When one or more of the server resources seems slow or overburdened, consider the following performance guidelines based on the current and projected workload.

使用備份壓縮加速備份作業並降低檔案大小Use backup compression to speed up backups and reduce file sizes

備份壓縮可以加速 SharePoint 備份作業。它可在 SQL Server 標準版和企業版中使用。如果您在備份指令碼中設定壓縮選項,或預設設定 SQL Server 進行壓縮,則可以大幅降低資料庫備份和傳送的記錄的大小。如需詳細資訊,請參閱<備份壓縮 (SQL Server)>和<資料壓縮 >或<啟用資料表或索引的壓縮>。Backup compression can speed up SharePoint backup operations. It is available in SQL Server Standard and Enterprise Edition. 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 shipped logs. For more information, see Backup Compression (SQL Server) and Data Compression, or Enable Compression on a Table or Index

致謝Acknowledgements

SharePoint Server 內容發佈小組感謝下列人員對本文的貢獻:The SharePoint Server Content Publishing team thanks the following contributors to this article:

  • Kay Unkroth,資深程式經理,SQL ServerKay Unkroth, Senior Program Manager, SQL Server

  • Chuck Heinzelman,資深程式經理,SQL ServerChuck Heinzelman, Senior Program Manager, SQL Server

另請參閱See also

概念Concepts

SharePoint Server 2016 環境中的 SQL Server 概觀Overview of SQL Server in a SharePoint Server 2016 environment

規劃及設定儲存設備與 SQL Server 容量 (SharePoint Server)Storage and SQL Server capacity planning and configuration (SharePoint Server)

其他資源Other Resources

保護 SharePoint 的安全:強化 SharePoint 環境中的 SQL ServerSecuring SharePoint: Harden SQL Server in SharePoint Environments