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

摘要: 了解 SharePoint Server 2016 與 SQL Server 的關係,以及如何與資料庫互動。Summary: Learn about the SharePoint Server 2016 relationship with SQL Server and how you can interact with the databases.

SharePoint Server 2016 伺服器陣列中資料庫伺服器的最低需求如下:The minimum requirements for a database server in a SharePoint Server 2016 farm are as follows:

  • 64 位元版本的 Microsoft SQL Server 2014 Service Pack 1 (SP1)64-bit edition of Microsoft SQL Server 2014 with Service Pack 1 (SP1)

  • Microsoft SQL Server 2016Microsoft SQL Server 2016

  • Microsoft SQL Server 2017 RTMMicrosoft SQL Server 2017 RTM

注意

不支援搭配使用 SQL Server Express 與 SharePoint Server 2016。SQL Server Express is not supported with SharePoint Server 2016.

注意

不支援搭配使用 Linux 上的 SQL Server 2017 與 SharePoint Server 2016。SQL Server Express is not supported with SharePoint Server 2016.

根據安裝的版本不同,您可以使用 SQL Server 的特定功能,例如報告和商業智慧 (BI)。如需詳細資訊,請參閱<SharePoint Server 2016 的硬體及軟體需求>。Depending on the installed version, you can use specific features of SQL Server, such as reporting and business intelligence (BI). For more information, see Hardware and software requirements for SharePoint Server 2016.

SharePoint Server 2016 支援下列項目:SharePoint Server 2016 supports the following:

  • SQL Server 2016 Reporting Services (SSRS)SQL Server 2016 Reporting Services (SSRS)

  • SQL Server 2016 Analysis Services (SSAS)SQL Server 2016 Analysis Services (SSAS)

注意

如果您想要使用 Microsoft SQL Server Power Pivot for SharePoint 或 Microsoft Power View for SharePoint for BI 解決方案,則必須安裝適用於 SQL Server 2016 RTM 的 Power Pivot 或 Power View 增益集。SQL Server 2014 (SP1) Power Pivot for SharePoint 和 Power View for SharePoint BI 解決方案無法與 SharePoint Server 2016一起使用。If you want to use Microsoft SQL Server Power Pivot for SharePoint or Microsoft Power View for SharePoint for BI solutions you must install the Power Pivot or Power View add-ins for SQL Server 2016 RTM. The SQL Server 2014 (SP1) Power Pivot for SharePoint and Power View for SharePoint BI solutions do not work with SharePoint Server 2016.

SharePoint Server 2016 與 SQL Server 資料庫引擎SharePoint Server 2016 and the SQL Server database engine

SharePoint Server 2016 應用程式建置在 SQL Server 資料庫引擎上,且 SQL Server 2014 (SP1)、SQL Server 2016 和 SQL Server 2017 RTM 中大部分的內容與設定都會儲存在關聯式資料庫中。下表列出 SharePoint Server 2016 所使用的資料庫。The SharePoint Server 2016 application is built on the SQL Server database engine. Most content and settings in SQL Server 2014 (SP1), SQL Server 2016, and SQL Server 2017 RTM are stored in relational databases. The following table shows the databases that SharePoint Server 2016 uses.

資料庫類型Database type 描述Description
組態Configuration
「設定」資料庫與 管理中心 內容資料庫統稱為設定資料庫。其中包含關於伺服器陣列設定的資料,例如所使用的資料庫、Internet Information Services (IIS) 網站或 Web 應用程式、解決方案、網頁組件套件、網站範本、預設配額和封鎖的檔案類型等。一個伺服器陣列只能有一組設定資料庫。The Configuration database and Central Administration content database are called configuration databases. They contain data about farm settings such as the databases that are used, Internet Information Services (IIS) web sites or web applications, solutions, Web Part packages, site templates, default quota, and blocked file types. A farm can only have one set of configuration databases.
內容Content
內容資料庫會儲存所有的網站內容:Content databases store all site content:
網站文件,例如文件庫中的檔案Site documents, such as files in document libraries
清單資料List data
網頁組件屬性Web Part properties
SharePoint 應用程式 的資料Data for apps for SharePoint
Project Server 2016 資料和物件Data and objects for Project Server 2016
使用者名稱與權限User names and permissions
每個 Web 應用程式皆可包含多個內容資料庫。雖然一個內容資料庫可與多個網站集合相關聯,但每個網站集合只能與一個內容資料庫相關聯。Each web application can contain many content databases. Each site collection can be associated with only one content database, although a content database can be associated with many site collections.
服務應用程式Service application
服務應用程式的資料庫會儲存服務應用程式所使用的資料。Databases for service applications store the data that service applications use.

如需支援 SharePoint Server 2016 的所有資料庫清單,請參閱<SharePoint Server 中的資料庫類型和描述>。如需支援 SharePoint Server 2016 的資料庫圖形表示,請參閱《快速參考指南:SharePoint Server 2016 資料庫》。您也可以下載這個 SharePoint Server 2016 資料庫海報,下載為 PDFVisio 檔案格式。For a full list of all of the databases that support SharePoint Server 2016, see Database types and descriptions in SharePoint Server. For a graphical representation of the databases that support SharePoint Server 2016, see Quick reference guide: SharePoint Server 2016 databases. You can also download this SharePoint Server 2016 database poster, as either a PDF or Visio file.

使用支援 SharePoint Server 2016 的 SQL Server 資料庫Working with the SQL Server databases that support SharePoint Server 2016

支援 SharePoint Server 2016 的資料庫可隨 SharePoint 產品設定精靈 而自動建立,或是由資料庫管理員在設定 SharePoint Server 時手動建立。The databases that support SharePoint Server 2016 are either created automatically with the SharePoint Products Configuration Wizard or manually by database administrators when they configure SharePoint Server.

Microsoft 不支援對支援 SharePoint Server 2016 的資料庫直接進行查詢或修改。在 SharePoint Server 2016 中,Usage and Health Data Collection 資料庫可支援結構描述修改。Microsoft does not support directly querying or modifying the databases that support SharePoint Server 2016. In SharePoint Server 2016 the Usage and Health Data Collection database does support schema modifications.

支援 SharePoint Server 2016 的 SQL Server 資料庫在調整大小方面有所限制,且在設定上除了既有的 SQL Server 相關建議外,還須依循其他建議。如需詳細資訊,請參閱規劃及設定儲存設備與 SQL Server 容量 (SharePoint Server)The SQL Server databases that support SharePoint Server 2016 are subject to sizing limitations and to configuration recommendations that are not standard for SQL Server. For more information, see Storage and SQL Server capacity planning and configuration (SharePoint Server).

SQL Server 2014 Service Pack 1 (SP1)SQL Server 2014 with Service Pack 1 (SP1)

比起 SQL Server 2014,SQL Server 2014 (SP1) 搭配 SharePoint Server 2016可提供更好的效能、可用性和管理性。雖然您無法使用 SQL Server Power Pivot for SharePoint 或 Power View for SharePoint 搭配 SQL Server 2014 (SP1),但您可以搭配使用部分商業智慧解決方案和 SharePoint Server 2016。例如,您可以安裝 Office Online 伺服器 以使用 Excel Online。SQL Server 2014 (SP1) provides greater performance, availability, and manageability with SharePoint Server 2016 than SQL Server 2014. While you can't use SQL Server Power Pivot for SharePoint or Power View for SharePoint with SQL Server 2014 (SP1), you can use some business intelligence solutions with SharePoint Server 2016. For example, you can install Office Online Server to use Excel Online.

如需詳細資訊,請參閱<不同版本的 SQL Server 2014 支援功能。如需有關 Office Online 伺服器的詳細資訊,請參閱<Configure Office Online Server for SharePoint Server 2016>。For more information, see Features Supported by the Editions of SQL Server 2014. For detailed information about Office Online Server, see Configure Office Online Server for SharePoint Server 2016.

高可用性解決方案High Availability Solutions

在 SQL Server 2014 Reporting Services (SP1) 中,建議您使用 AlwaysOn 可用性群組和 AlwaysOn 容錯移轉叢集執行個體作為高可用性解決方案。其他的高可用性解決方案包括資料庫鏡像與記錄傳送。AlwaysOn 可用性群組與容錯移轉叢集執行個體解決方案都須使用 Windows Server 容錯移轉叢集 (WSFC)。We recommend AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances for high availability in SQL Server 2014 Reporting Services (SP1). Other high availability solutions are database mirroring, and log shipping. Both AlwaysOn Availability Groups and Failover Cluster Instances solutions require and use Windows Server Failover Clustering (WSFC).

注意

在適用於 SharePoint Server 2016 的 SQL Server 2014 (SP1)、SQL Server 2016 和 SQL Server 2017 RTM 中,建議您使用 AlwaysOn 可用性群組作為高可用性解決方案,而不要使用資料庫鏡像。如需詳細資訊,請參閱<SQL Server 高可用性解決方案的概觀>。We recommend that you use AlwaysOn Availability Groups instead of database mirroring for your high availability solution with SQL Server 2014 (SP1), SQL Server 2016, and SQL Server 2017 RTM for SharePoint Server 2016. For more information, see Overview of SQL Server High-Availability Solutions.

如需詳細資訊,請參閱<AlwaysOn 可用性群組 (SQL Server)>和<AlwaysOn 可用性群組的先決條件、限制和建議 (SQL Server)>。如需有關 SQL Server Reporting Services 高可用性的資訊,請參閱<高可用性 (Reporting Services)>。For more information, see AlwaysOn Availability Groups (SQL Server), and Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).For information about high availability for SQL Server Reporting Services, see High Availability (Reporting Services).

記錄傳送Log Shipping

SQL Server 記錄傳送可為每個位於 SQL Server 個別執行個體上的單一主要資料庫和多個次要資料庫,提供災害復原解決方案。記錄傳送會備份實際執行伺服器上的交易記錄,將記錄複製到備份或次要執行個體,然後記錄傳送就可用來還原記錄備份。接著您可以設定警示,讓您在實際執行伺服器發生問題時收到通知。然後您可以從實際執行伺服器容錯移轉到備份伺服器,如此一來,當實際執行伺服器發生問題時,其中一個備份或次要伺服器即可上線作為實際執行伺服器。如需詳細資訊,請參閱<關於記錄傳送 (SQL Server)>。SQL Server Log shipping provides a disaster recovery solution for single primary databases and multiple secondary databases where each are located on separate instances of SQL Server. Log shipping backs up the transaction log on the production server, copies the log to the backup or secondary instances, and is then available to restore the log backup. You can then configure alerts to notify you when the production server fails. Then you can fail over from the production server to the backup servers so if the production server fails one of the backup or secondary servers can be brought online to act as the production server. For more information, see About Log Shipping (SQL Server).

Reporting Services SharePoint 模式Reporting Services SharePoint mode

在 SharePoint Server 2016中設定 Reporting Services 時,您會建立報表伺服器。報表伺服器是 Reporting Services 的中心元件。此元件包含兩個處理引擎和一組唯一的擴充功能,可處理驗證、資料處理、轉譯和傳遞作業。When you setup Reporting Services with SharePoint Server 2016 you create a report server. The report server is the central component of Reporting Services. This component contains two processing engines and a set of unique extensions that handle authentication, data processing, rendering, and delivery operations.

如需詳細資訊,請參閱<支援的 SharePoint 和 Reporting Services 伺服器組合與增益集 (SQL Server 2016)>當您搭配 SharePoint Server 2016 以整合模式執行報表伺服器時,將會提供下列整合層級。For more information, see Supported Combinations of SharePoint and Reporting Services Server and Add-in (SQL Server 2016). The following levels of integration are provided when you run a report server in integrated mode with SharePoint Server 2016.

  • 共用儲存Shared storage

  • 共用安全性Shared security

  • 對所有商業文件的相同網站存取,例如報告、報告模型與共用資料來源Same site access for all business documents such as reports, report models, and shared data sources

Reporting Services 以 SharePoint 整合模式執行時,SharePoint 內容資料庫與報告伺服器資料庫都會儲存內容與中繼資料。下表列出每個資料庫所儲存的報告伺服器資料。When Reporting Services runs in SharePoint integrated mode, both the SharePoint content and report server databases store content and metadata. The following table shows the report server data that each database stores.

資料庫的名稱Name of database 報告伺服器資料Report server data
SharePoint 內容SharePoint content
下列資料的主要儲存區:Primary storage for the following data:
已發佈的報告Published reports
報告模型Report models
共用資料來源Shared data sources
資源Resources
屬性Properties
權限Permissions
SharePoint 設定SharePoint configuration
您在 管理中心 中進行的所有報告伺服器設定包括:All report server configuration settings that you make in Central Administration including:
報告伺服器 URLReport server URL
報告伺服器 Reporting Services 帳戶資訊Report server Reporting Services account information
伺服器上使用之驗證提供者的相關資訊Information about the authentication provider that is used on the server
限制或啟用報告歷程記錄與記錄的網站層級設定Site-level settings that limit or enable report history and logging
報告伺服器Report server
內容與中繼資料的內部複本 (也會儲存在 SharePoint 內容資料庫中),以及下列報告資料:Internal copies of report content and metadata, which are also stored in the SharePoint content database, and the following report data:
排程Schedules
訂閱Subscriptions
報告歷程記錄或報告執行的快照Snapshots for report history or report execution
報告伺服器 TempReport server Temp
暫存資料,包括下列項目:Temporary data, including the following:
工作階段資料Session data
為訂閱處理、互動式報告或報告快取而建立以提升效能的暫存快照Temporary snapshots created for subscription processing, interactive reporting, or report caching as a performance improvement

SQL Server 2016 中的 SharePoint 模式是 SharePoint 共用服務,您可以在 SharePoint 管理中心網站 中設定或透過使用 Reporting Services SharePoint 模式 Microsoft PowerShell cmdlet 設定。SharePoint 模式支援 SQL Server Reporting Services 服務應用程式和統一登入服務 (ULS) 追蹤記錄的 SharePoint Server 2016 備份與還原。SharePoint 模式也支援聲明式驗證。SharePoint mode in SQL Server 2016 is a SharePoint shared service that you configure in either the SharePoint Central Administration website or by using Reporting Services SharePoint mode, Microsoft PowerShell cmdlets. SharePoint mode supports SharePoint Server 2016 backup and restore for SQL Server Reporting Services service application and Unified Logging Service (ULS) trace logs. SharePoint mode also supports claims-based authentication.

要使用 SharePoint 模式,必須讓 Reporting Services 的報告伺服器元件在 SharePoint Server 伺服器陣列內執行。這表示 SharePoint 應用程式伺服器必須與已安裝的 Reporting Services 共用服務和至少一個 Reporting Services 服務應用程式並存。SharePoint mode requires that a report server component of Reporting Services must run within a SharePoint Server farm. This means that a SharePoint application server must exist with the Reporting Services shared service installed and at least one Reporting Services service application.

如需詳細資訊,請參閱<Reporting Services 報表伺服器 (SharePoint 模式)>、<Reporting Services 報表伺服器>和<Reporting Services SharePoint 的 PowerShell cmdlet>。For more information, see Reporting Services Report Server (SharePoint Mode), Reporting Services Report Server, and PowerShell cmdlets for Reporting Services SharePoint Mode.

SQL Server 2016SQL Server 2016

SQL Server 2016 提供適用於 SharePoint Server 2016 的商業智慧解決方案。SQL Server 2016 的 SharePoint 模式提供 SQL Server Analysis Services 和 SQL Server Reporting Services 的功能。如需詳細資訊,請參閱<SQL Server 2016 的版本及支援功能>。SQL Server 2016 provides business intelligence solutions for SharePoint Server 2016. The SharePoint mode of SQL Server 2016 provides features for SQL Server Analysis Services and SQL Server Reporting Services. For more information, see Features Supported by the Editions of SQL Server 2016.

當您在 SharePoint Server 2016 伺服器陣列中安裝 SQL Server 2016 Analysis Services (SSAS) 和 SQL Server 2016 Reporting Services (SSRS) 後,即可使用下列商業智慧解決方案:When you install SQL Server 2016 Analysis Services (SSAS) and SQL Server 2016 Reporting Services (SSRS) in a SharePoint Server 2016 farm the following business intelligence solutions are available:

  • SQL Server 2016 Power PivotSQL Server 2016 Power Pivot

  • SQL Server 2016 Power ViewSQL Server 2016 Power View

  • 以 Power Pivot 或 Analysis Services 表格式資料模型執行的 Reporting Services 互動式報告設計工具Reporting Services interactive report designer that runs on Power Pivot or Analysis Services tabular data models

當您升級至 SQL Server 2016 RTM 後,即可使用下列 SharePoint Server 2016 商業智慧功能:The following SharePoint Server 2016 business intelligence features are available when you upgrade to SQL Server 2016 RTM:

  • Power Pivot 資源庫Power Pivot Gallery

  • 重新整理排定的資料Scheduled Data Refresh

  • 作為資料來源的活頁簿Workbooks as a Data Source

  • Power Pivot 管理儀表板Power Pivot Management Dashboard

  • Power View 報表Power View reports

  • Power View 訂閱Power View Subscriptions

  • 報表警示Report Alerting

如需詳細資訊,請下載最新 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 和 Reporting Services 伺服器組合與增益集 (SQL Server 2016)>及<安裝 SQL Server 2016 商業智慧功能>。For more information, see Supported Combinations of SharePoint and Reporting Services Server and Add-in (SQL Server 2016) and Install SQL Server 2016 Business Intelligence Features.

Power Pivot for SharePointPower Pivot for SharePoint

部署 Power Pivot for SharePoint 2016 需有 SQL Server 2016 RTM。Power Pivot for SharePoint 2016 是 SQL Server 2016 RTM Feature Pack 中提供的增益集。SQL Server 2016 Analysis Services 必須在 SharePoint 模式中執行。這在 SharePoint 伺服器陣列中提供了一個裝載 Power Pivot 資料的伺服器。如需詳細資訊,請參閱<在 Power Pivot 模式中安裝 Analysis Services>。裝載 Power Pivot for SharePoint 2016 的伺服器可以在 SharePoint Server 2016 伺服器陣列之外。SQL Server 2016 RTM is required to deploy Power Pivot for SharePoint 2016. Power Pivot for SharePoint 2016 is an add-in that is available in the SQL Server 2016 RTM Feature Pack. SQL Server 2016 Analysis Services must be run in SharePoint mode. This provides a server that hosts Power Pivot data in a SharePoint farm. For more information, see Install Analysis Services in Power Pivot Mode. The server that hosts Power Pivot for SharePoint 2016 can be outside a SharePoint Server 2016 farm.

SQL Server 2016 Analysis Services 提供三種分析模式:多維度、表格式、Power Pivot for SharePoint。請注意,每個伺服器模式皆獨立於其他模式,且每個模式僅支援在該模式下執行的分析資料庫類型。如需有關 SQL Server 2016 Analysis Services 的詳細資訊,請參閱<Analysis Services>。SQL Server 2016 Analysis Services provides three modes for analysis, Multidimensional, Tabular, and Power Pivot for SharePoint. Note that each server mode is independent of the others, and each supports a type of analytical database that only runs in that modality. For more information about SQL Server 2016 Analysis Services, see Analysis Services.

若要設定 Power Pivot for SharePoint,您可以使用 Power Pivot for SharePoint 2013 設定工具、SharePoint 管理中心網站 或 Microsoft PowerShell Cmdlet。下表列出每種方法,並說明程序:To configure Power Pivot for SharePoint you can use the Power Pivot for SharePoint 2013 Configuration tool, the SharePoint Central Administration website, or Microsoft PowerShell cmdlets. The following table lists each method and describes the process:

SharePoint 設定方法的 Power PivotPower Pivot for SharePoint Configuration method 描述Description
Power Pivot for SharePoint 2016 設定工具Power Pivot for SharePoint 2016 Configuration Tool
評估現有安裝,並判斷必須在 SharePoint 伺服器陣列與 Power Pivot for SharePoint 中進行的設定,然後執行各項必要的設定。Evaluates an existing installation and determines what needs to be configured in the SharePoint farm and Power Pivot for SharePoint and then configures everything required.
SharePoint Server 2016管理中心SharePoint Server 2016 Central Administration
管理中心 提供您建立用來存取 BI 伺服器陣列之 Power Pivot 管理儀表板的 SQL ServerPower Pivot 服務應用程式。Central Administration provides the SQL Server Power Pivot Service Application that you create to access the Power Pivot Management Dashboard for your BI farm.
Microsoft PowerShell CmdletMicrosoft PowerShell cmdlets
提供可用來建置 PowerShell 指令碼檔案 (.ps1) 及自動化 Power Pivot for SharePoint 設定程序的 Cmdlet。Provides cmdlets that you can use to build PowerShell script files (.ps1) and automate the configuration process for Power Pivot for SharePoint.

Power View for SharePointPower View for SharePoint

Power View 是隨附於適用於 Microsoft SharePoint 的Microsoft SQL Server 2016 Reporting Services 增益集中的功能。安裝適用於 SharePoint 的 SQL Server 2016 Reporting Services 增益集,然後設定伺服器以進行整合。當您部署 Power View for SharePoint 時,您可以從資料模型建立資料檢視並與其互動,資料模型是以 Power Pivot 資源庫中發行的 Power Pivot 活頁簿,或部署到 SSAS 的表格式模型為基礎。您也可以透過 SSRS 在 SharePoint 文件庫上建立和檢視報表。所有 Power View 報表會提供多種檢視方式,包括並排、交叉分析篩選器、圖表篩選和視覺化功能。如需詳細資運,請參閱<Reporting Services (SSRS) 的新功能>。Power View is a feature included with Microsoft SQL Server 2016 Reporting Services Add-in for Microsoft SharePoint. Install SQL Server 2016 Reporting Services Add-in for SharePoint, and then configure the servers for integration. When you deploy Power View for SharePoint you can create and interact with views of data from data models that are based on Power Pivot workbooks that are published in a Power Pivot Gallery, or tabular models that are deployed to SSAS. You can also create and view reports from SSRS on SharePoint document libraries. All Power View reports provide multiple views that feature tiles, slicers, chart filters, and visualizations. For more information, see What's New in Reporting Services (SSRS).

另請參閱See also

其他資源Other Resources

支援的 SharePoint 和 Reporting Services 伺服器組合與增益集 (SQL Server 2016)Supported Combinations of SharePoint and Reporting Services Server and Add-in (SQL Server 2016)

SQL Server 2016 的新功能What's New in SQL Server 2016

SQL Server 2016 中不建議使用和停用的 SQL Server 功能Deprecated and Discontinued SQL Server Features in SQL Server 2016

新功能 (Analysis Services)What's New (Analysis Services)

Analysis ServicesAnalysis Services

不同版本的 SQL Server 2014 支援功能Features Supported by the Editions of SQL Server 2014

SQL Server 2014 中不建議使用的資料庫引擎功能Deprecated Database Engine Features in SQL Server 2014