SQL Server 設計考量SQL Server Design Considerations

重要

已不再支援此版本的 Operations Manager,建議升級至 Operations Manager 2019This version of Operations Manager has reached the end of support, we recommend you to upgrade to Operations Manager 2019.

System Center Operations Manager 需要能夠存取執行 Microsoft SQL Server 之伺服器的執行個體,才能支援操作、資料倉儲和 ACS 稽核資料庫。System Center Operations Manager requires access to an instance of a server running Microsoft SQL Server to support the operational, data warehouse, and ACS audit database. 當您在管理群組中部署第一部管理伺服器時,需要使用並建立操作和資料倉儲資料庫,同時在管理群組中部署 ACS 收集器時,則會建立 ACS 資料庫。The operational and data warehouse databases are required and created when you deploy the first management server in your management group, while the ACS database is created when you deploy an ACS collector in your management group.

在實驗室環境或 Operations Manager 的小型規模部署中,可以將 SQL Server 共置於管理群組中的第一部管理伺服器上。In a lab environment or small-scale deployment of Operations Manager, SQL Server can be co-located on the first management server in the management group. 在中型到企業規模分散式部署中,SQL Server 執行個體應該位於專用的獨立伺服器或 SQL Server 高可用性組態中。In a medium to enterprise-scale distributed deployment, the SQL Server instance should be located on a dedicated standalone server or in a SQL Server high-availability configuration. 不論如何,在您開始安裝第一部管理伺服器或 ACS 收集器之前,SQL Server 必須已經存在並可存取。In either case, SQL Server must already exist and is accessible before you start the installation of the first management server or ACS collector.

SQL Server 需求SQL Server requirements

下列版本的 SQL Server Enterprise 與 Standard Edition 支援現有 System Center Operations Manager 版安裝,以裝載報表伺服器、作業、資料倉儲與 ACS 資料庫:The following versions of SQL Server Enterprise & Standard Edition are supported for an existing installation of System Center Operations Manager version to host Reporting Server, Operational, Data Warehouse, and ACS database:

  • 含累積更新 8 (CU8) 或更新版本的 SQL Server 2019,如這裡 (英文) 所詳述SQL Server 2019 with Cumulative Update 8 (CU8) or later, as detailed here

    注意

    • Operations Manager 2019 支援含 CU8 或更新版本的 SQL 2019;不過,其不支援 SQL 2019 RTM。Operations Manager 2019 supports SQL 2019 with CU8 or later; however, it does not support SQL 2019 RTM.
    • 請使用 ODBC 17.3 或更新版本,以及 MSOLEDBSQL 18.2 或更新版本。Use ODBC 17.3 or later, and MSOLEDBSQL 18.2 or later.
  • SQL Server 2017 與累計更新,如這裡 (英文) 所詳述SQL Server 2017 and Cumulative Updates as detailed here

  • 這裡有 SQL Server 2016 和 Service Pack 的詳細資訊SQL Server 2016 and Service Packs as detailed here

下列版本的 SQL Server Enterprise 與 Standard Edition 支援現有 System Center Operations Manager 版安裝,以裝載報表伺服器、作業、資料倉儲與 ACS 資料庫:The following versions of SQL Server Enterprise & Standard Edition are supported for an existing installation of System Center Operations Manager version to host Reporting Server, Operational, Data Warehouse, and ACS database:

  • SQL Server 2017 與累計更新,如這裡 (英文) 所詳述SQL Server 2017 and Cumulative Updates as detailed here
  • 這裡有 SQL Server 2016 和 Service Pack 的詳細資訊SQL Server 2016 and Service Packs as detailed here

在升級到 SQL Server 之前,請參閱 2017 的升級資訊2019 的升級資訊Before upgrading SQL Server, see upgrade information for 2017, and upgrade information for 2019.

在升級到 SQL Server 2017 之前,請參閱 2017 的升級資訊Before upgrading to SQL Server 2017, see upgrade information for 2017.

下列版本的 SQL Server Enterprise 與 Standard Edition 支援全新或現有 System Center Operations Manager 1801 版安裝,以裝載報表伺服器、作業、資料倉儲與 ACS 資料庫:The following versions of SQL Server Enterprise & Standard Edition are supported for a new or existing installation of System Center Operations Manager version 1801 to host Reporting Server, Operational, Data Warehouse, and ACS database:

  • 這裡有 SQL Server 2016 和 Service Pack 的詳細資訊SQL Server 2016 and Service Packs as detailed here

下列版本的 SQL Server Enterprise 與 Standard Edition 支援全新或現有 System Center 2016 - Operations Manager 安裝,以裝載報表伺服器、作業、資料倉儲與 ACS 資料庫:The following versions of SQL Server Enterprise & Standard Edition are supported for a new or existing installation of System Center 2016 - Operations Manager to host Reporting Server, Operational, Data Warehouse, and ACS database:

  • 這裡有 SQL Server 2016 和 Service Pack 的詳細資訊SQL Server 2016 and Service Packs as detailed here
  • 這裡有 SQL Server 2014 和 Service Pack 的詳細資訊SQL Server 2014 and Service Packs as detailed here
  • 這裡有 SQL Server 2012 和 Service Pack 的詳細資訊SQL Server 2012 and Service Packs as detailed here

注意

System Center Operations Manager 資料庫必須使用相同版本的 SQL Server、SQL Server 定序設定必須是下列其中一個支援的類型 (如該節所述),而且 SQL Server 全文檢索搜尋是操作和資料倉儲資料庫的 必要項System Center Operations Manager databases must use the same version of SQL Server, the SQL Server collation setting must be one of the following supported types as described in that section, and SQL Server Full Text Search is required for both the operational and data warehouse databases. Operations Manager 資料庫元件支援的 Windows Server 2016 安裝選項 (Server Core、伺服器含桌面體驗和 Nano 伺服器),取決於 SQL Server 支援的 Windows Server 安裝選項。The Windows Server 2016 installation options (Server Core, Server with Desktop Experience, and Nano Server) supported by Operations Manager database components, are based on what installation options of Windows Server are supported by SQL Server.

注意

System Center Operations Manager 報表無法以並存方式與先前版本的報表角色一起安裝,而且 必須 僅以原生模式安裝。System Center Operations Manager Reporting cannot be installed in a side-by-side fashion with a previous version of the Reporting role and must be installed in native mode only. (不支援 SharePoint 整合模式)。(SharePoint integrated mode is not supported.)

您的設計規劃中適用的其他硬體及軟體考量︰Additional hardware and software considerations apply in your design planning:

  • 建議您在 NTFS 檔案格式的電腦上執行 SQL Server。We recommend that you run SQL Server on computers with the NTFS file format.
  • 必須至少有 1024 MB 的可用磁碟空間,供操作和資料倉儲資料庫使用。There must be at least 1024 MB of free disk space for the operational and data warehouse database. 建立資料庫時會強制執行此需求,而且可能會在安裝後大幅成長。It is enforced at the time of database creation, and it will likely grow significantly after setup.
  • 需要 .NET Framework 4。.NET Framework 4 is required.
  • Windows Server Core 不支援報表伺服器。Reporting Server is not supported on Windows Server Core.

如需詳細資訊,請參閱安裝 SQL Server 2014 (部分機器翻譯) 或 2016 的硬體和軟體需求For more information, see Hardware and Software Requirements for Installing SQL Server 2014 or 2016

注意

在操作資料庫的初始安裝期間,只會使用裝載 Operations Manager 操作資料庫的 SQL Server 上的 Windows 驗證。During the initial installation of the operational database, only use Windows Authentication on the SQL Server that hosts the Operations Manager operational database. 請勿使用「混合模式」(Windows 驗證和 SQL Server 驗證),因為在操作資料庫初始安裝期間使用 SQL Server 驗證模式可能會發生問題。Do not use Mixed Mode (Windows Authentication and SQL Server Authentication) because using SQL Server Authentication mode during the initial installation of the operational database can cause issues. 儘管可以在裝載 Operations Manager 操作資料庫的 SQL Server 上啟用「混合模式」安全性,但它不支援只使用 Windows 帳戶實現與資料庫連線的情況。Although enabling Mixed Mode security is possible on the SQL Server hosting the Operations Manager operational database, it is not supported as all contact with the database is accomplished using Windows accounts only.

SQL Server 定序設定SQL Server collation setting

System Center Operations Manager 支援下列 SQL Server 和 Windows 定序。The following SQL Server and Windows collations are supported by System Center Operations Manager.

注意

若要避免比較或複製作業中發生任何相容性問題,建議您針對 SQL 與 Operations Manager DB 使用相同的定序。To avoid any compatibility issues in comparing or copying operations, we recommend you use the same collation for the SQL and Operations Manager DB.

SQL Server 定序SQL Server collation

  • SQL_Latin1_General_CP1_CI_ASSQL_Latin1_General_CP1_CI_AS

Windows 定序Windows collation

  • Latin1_General_100_CI_ASLatin1_General_100_CI_AS
  • French_CI_ASFrench_CI_AS
  • French_100_CI_ASFrench_100_CI_AS
  • Cyrillic_General_CI_ASCyrillic_General_CI_AS
  • Chinese_PRC_CI_ASChinese_PRC_CI_AS
  • Chinese_Simplified_Pinyin_100_CI_ASChinese_Simplified_Pinyin_100_CI_AS
  • Chinese_Traditional_Stroke_Count_100_CI_ASChinese_Traditional_Stroke_Count_100_CI_AS
  • Japanese_CI_ASJapanese_CI_AS
  • Japanese_XJIS_100_CI_ASJapanese_XJIS_100_CI_AS
  • Traditional_Spanish_CI_ASTraditional_Spanish_CI_AS
  • Modern_Spanish_100_CI_ASModern_Spanish_100_CI_AS
  • Latin1_General_CI_ASLatin1_General_CI_AS
  • Cyrillic_General_100_CI_ASCyrillic_General_100_CI_AS
  • Korean_100_CI_ASKorean_100_CI_AS
  • Czech_100_CI_ASCzech_100_CI_AS
  • Hungarian_100_CI_ASHungarian_100_CI_AS
  • Polish_100_CI_ASPolish_100_CI_AS
  • Finnish_Swedish_100_CI_ASFinnish_Swedish_100_CI_AS

如果您的 SQL Server 執行個體未以先前所列支援的對照項之一來設定,執行 Operations Manager 安裝程式的全新安裝將會失敗。If your SQL Server instance is not configured with one of the supported collations listed earlier, performing a new setup of Operations Manager setup will fail. 不過,就地升級將可順利完成。However, an in-place upgrade will complete successfully.

防火牆設定Firewall configuration

Operations Manager 依賴 SQL Server 裝載其資料庫和報告平台,以分析並呈現歷史操作資料。Operations Manager depends on SQL Server to host its databases and a reporting platform to analyze and present historical operational data. 管理伺服器、Operations 和 Web 主控台角色必須能夠成功地與 SQL Server 進行通訊,而且必須了解通訊路徑和連接埠,才能正確地設定您的環境。The management server, Operations, and Web console roles need to be able to successfully communicate with SQL Server, and it is important to understand the communication path and ports in order to configure your environment correctly.

如果您要設計的分散式部署將需要 SQL AlwaysOn 可用性群組,才能為 Operations Manager 資料庫提供容錯移轉功能,則有一些其他防火牆組態設定必須包含在防火牆安全性策略中。If you are designing a distributed deployment that will require SQL Always On Availability Groups to provide failover functionality for the Operations Manager databases, there are additional firewall configuration settings that need to be included in your firewall security strategy.

下表可協助您找出 SQL Server 所需的防火牆連接埠,您至少必須允許這些防火牆連接埠,才能讓 Operations Manager 管理群組中的伺服器角色成功進行通訊。The following table helps you identify the firewall ports required by SQL Server that will need to be allowed at a minimum in order for server roles in your Operations Manager management group to successfully communicate.

案例Scenario PortPort DirectionDirection Operations Manager 角色Operations Manager Role
裝載 Operations Manager 資料庫的 SQL ServerSQL Server hosting Operations Manager databases TCP 1433 *TCP 1433 * 輸入Inbound 管理伺服器和 Web 主控台 (用於 Application Advisor 和 Application Diagnostics)management server and Web console (for Application Advisor and Application Diagnostics)
SQL Server Browser 服務SQL Server Browser service UDP 1434UDP 1434 輸入Inbound 管理伺服器management server
SQL Server 專用管理員連接SQL Server Dedicated Admin Connection TCP 1434TCP 1434 輸入Inbound 管理伺服器management server
SQL Server 所使用的其他連接埠Additional ports used by SQL Server
- Microsoft 遠端程序呼叫 (MS RPC)- Microsoft remote procedure calls (MS RPC)
- Windows Management Instrumentation (WMI)- Windows Management Instrumentation (WMI)
- Microsoft 分散式交易協調器 (MS DTC)- Microsoft Distributed Transaction Coordinator (MS DTC)
TCP 135TCP 135 輸入Inbound 管理伺服器management server
SQL Server AlwaysOn 可用性群組接聽程式SQL Server Always On Availability Group Listener 系統管理員設定的連接埠Administrator configured port 輸入Inbound 管理伺服器management server
裝載 Operations Manager 報表伺服器的 SQL Server Reporting ServicesSQL Server Reporting Services hosting Operations Manager Reporting Server TCP 80 (預設)/443 (SSL)TCP 80 (default)/443 (SSL) 輸入Inbound 管理伺服器和 Operations 主控台management server and Operations console

* 雖然 TCP 1433 是 Database Engine 預設執行個體的標準連接埠;但是,如果您在獨立 SQL Server 上建立具名執行個體,或已部署 SQL AlwaysOn 可用性群組,則會定義自訂連接埠,並應該加以記錄以供參考,讓您正確地設定防火牆,並在安裝期間輸入此資訊。* While TCP 1433 is the standard port for the default instance of the Database Engine, when you create a named instance on a standalone SQL Server or have deployed a SQL Always On Availability Group, a custom port will be defined and should be documented for reference so that you properly configure your firewalls and enter this information during setup.

如需 SQL Server 防火牆需求的更詳細概觀,請參閱將 Windows 防火牆設定為允許 SQL Server 存取For a more detailed overview of the firewall requirements for SQL Server, see Configure the Windows Firewall to Allow SQL Server Access.

容量與存放裝置考量Capacity and storage considerations

Operations Manager 資料庫Operations Manager database

Operations Manager 資料庫是一種 SQL Server 資料庫,其中包含 Operations Manager 為日常監視所需的所有資料。The Operations Manager database is a SQL Server database that contains all of the data needed by Operations Manager for day-to-day monitoring. 調整資料庫伺服器大小以及設定資料庫伺服器對於管理群組的整體效能至關緊要。Sizing and configuration of the database server is critical to the overall performance of the management group. Operations Manager 資料庫所使用的最重要資源是儲存子系統,但 CPU 和 RAM 也很重要。The most critical resource used by the Operations Manager database is the storage subsystem, but CPU and RAM are also significant.

影響 Operations Manager 資料庫負載的因素包括︰Factors that influence the load on the Operations Manager database include:

  • 操作資料收集的速率。Rate of operational data collection. 操作資料包含代理程式所收集的所有事件、警示、狀態變更和效能資料。Operational data consists of all the events, alerts, state changes, and performance data collected by agents. Operations Manager 資料庫所使用的大部分資源在資料進入系統時,都用來將此資料寫入至磁碟。Most of the resources that are used by the Operations Manager database are used to write this data to disk as it comes into the system. 收集操作資料的速率通常會提高,因為系統會匯入額外的管理組件,並加入額外的代理程式。The rate of operational data collected tends to increase as additional management packs are imported and additional agents are added. 代理程式將監視之電腦的類型也是判斷操作資料收集整體速率時所使用的一個重要因素。The type of computer that an agent is monitoring is also an important factor used when determining the overall rate of operational data collection. 例如,相較於監視執行包含大量資料庫的 SQL Server 執行個體之伺服器的代理程式,可以預期監視業務關鍵桌上型電腦的代理程式所收集的資料較少。For example, an agent that is monitoring a business-critical desktop computer can be expected to collect less data than an agent monitoring a server that is running an instance of SQL Server with a large number of databases.
  • 執行個體空間變更的速率。Rate of instance space changes. 相對於寫入新的操作資料,在 Operations Manager 資料庫中更新此資料的成本高昂。Updating this data in the Operations Manager database is costly relative to writing new operational data. 此外,當執行個體空間資料變更時,管理伺服器會對 Operations Manager 資料庫提出其他查詢,以計算設定和群組的變更。In addition, when instance space data changes, the management servers make additional queries to the Operations Manager database in order to compute configuration and group changes. 當您將額外的管理組件匯入至管理群組時,執行個體空間變更的速率便會提高。The rate of instance space changes increases as you import additional management packs into a management group. 將新的代理程式加入至管理群組也會暫時提高執行個體空間變更的速率。Adding new agents to a management group also temporarily increases the rate of instance space changes.
  • 同時執行 Operations 主控台及其他 SDK 連線的數目。Number of Operations Consoles and other SDK connections running simultaneously. 每個 Operations 主控台都會從 Operations Manager 資料庫讀取資料。Each Operations console reads data from the Operations Manager database. 查詢此資料會耗用可能極大量的存放裝置 I/O 資源、CPU 時間及 RAM。Querying this data consumes potentially large amounts of storage I/O resources, CPU time, and RAM. 在事件檢視、狀態檢視、警示檢視和效能資料檢視中顯示大量操作資料的 Operations 主控台可能會對資料庫造成最大的負載。Operations consoles that display large amounts of operational data in the Events View, State View, Alerts View, and Performance Data View tend to cause the largest load on the database.

Operations Manager 資料庫是管理群組的單一失敗來源,因此可以使用支援的容錯移轉設定,讓它成為高可用性,例如 SQL Server AlwaysOn 可用性群組或容錯移轉叢集執行個體。The Operations Manager database is a single source of failure for the management group, so it can be made highly available using supported failover configurations such as SQL Server Always On Availability Groups or Failover Cluster Instances.

Operations Manager 資料倉儲資料庫Operations Manager data warehouse database

System Center - Operations Manager 會將資料以近乎即時的方式插入報表資料倉儲;因此,這部伺服器務必要有足夠容量才能支援將收集的所有資料寫入報表資料倉儲。System Center – Operations Manager inserts data into the Reporting data warehouse in near-real time, it is important to have sufficient capacity on this server that supports writing all of the data that is being collected to the Reporting data warehouse. 如同 Operations Manager 資料庫,報表資料倉儲上最重要的資源是存放裝置 I/O 子系統。As with the Operations Manager database, the most critical resource on the Reporting data warehouse is the storage I/O subsystem. 在大多數的系統上,報表資料倉儲的負載與 Operations Manager 資料庫的負載類似,但可能會有所不同。On most systems, loads on the Reporting data warehouse are similar to the Operations Manager database, but they can vary. 此外,因為報告而施加在報表資料倉儲上的工作負載與因為 Operations 主控台使用狀況而施加在 Operations Manager 資料庫上的工作負載不同。In addition, the workload put on the Reporting data warehouse by reporting is different than the load put on the Operations Manager database by Operations console usage.

影響報表資料倉儲負載的因素包括︰Factors that influence the load on the Reporting data warehouse include:

  • 操作資料收集的速率。Rate of operational data collection. 為允許報告更有效率,除了有限的原始資料量之外,報表資料倉儲還會計算並儲存彙總的資料。To allow for more efficient reporting, the Reporting data warehouse computes and stores aggregated data in addition to a limited amount of raw data. 由於這個額外工作的緣故,相較於 Operations Manager 資料庫,報表資料倉儲操作資料收集的成本可能會稍高一點。Doing this extra work means that operational data collection to the Reporting data warehouse can be slightly more costly than to the Operations Manager database. 這個額外的成本通常是透過降低報表資料倉儲與 Operations Manager 資料庫處理探索資料的成本來平衡。This additional cost is typically balanced by the reduced cost of processing discovery data by the Reporting data warehouse versus the Operations Manager database.
  • 同時產生報表使用者或排程報表的數目。Number of concurrent reporting users or scheduled report generation. 因為報表經常摘要大量的資料,因此每個報表使用者都可能會在系統上增加大量負載。Because reports frequently summarize large volumes of data, each reporting user can add a significant load on the system. 同時執行的報表數目和即將執行的報表類型都會影響整體容量需求。The number of reports run simultaneously and the type of reports being run both affect overall capacity needs. 一般而言,查詢廣大日期範圍或大量物件的報表需要額外的系統資源。Generally, reports that query large date ranges or large numbers of objects require additional system resources.

根據這些因素,有數個要在調整報表資料倉儲大小時所應考量的建議做法︰Based on these factors, there are several recommended practices to consider when sizing the Reporting data warehouse:

  • 選擇適當的存放子系統。Choose an appropriate storage subsystem. 因為報表資料倉儲是流經管理群組的整體資料不可或缺的一部分,所以為報表資料倉儲選擇適當的存放子系統十分重要。Because the Reporting data warehouse is an integral part of the overall data flow through the management group, choosing an appropriate storage subsystem for the Reporting data warehouse is important. 如同 Operations Manager 資料庫,RAID 0 + 1 通常是最佳選擇。As with the Operations Manager database, RAID 0 + 1 is often the best choice. 一般而言,報表資料倉儲的存放子系統應該類似於 Operations Manager 資料庫的存放子系統,而且適用於 Operations Manager 資料庫的指導方針也適用於報表資料倉儲。In general, the storage subsystem for the Reporting data warehouse should be similar to the storage subsystem for the Operations Manager database, and the guidance that applies to the Operations Manager database also applies to the Reporting data warehouse.
  • 請考慮適當的資料記錄檔與交易記錄檔的位置。Consider appropriate placement of data logs vs. transaction logs. 至於 Operations Manager 資料庫,將 SQL 資料和交易記錄檔分開通常是您擴大代理程式數目時的合適選項。As for the Operations Manager database, separating SQL data and transaction logs are often an appropriate choice as you scale up the number of agents. 如果 Operations Manager 資料庫與報表資料倉儲都位於同一部伺服器上,而且您想要將資料和交易記錄檔分開,則您必須將 Operations Manager 資料庫的交易記錄檔放在不同於報表資料倉儲的實體磁碟區和磁碟主軸上,才能獲得所有好處。If both the Operations Manager database and Reporting data warehouse are located on the same server and you want to separate data and transaction logs, you must put the transaction logs for the Operations Manager database on a separate physical volume and disk spindles from the Reporting data warehouse to receive any benefit. Operations Manager 資料庫與報表資料倉儲的資料檔案可以共用相同的實體磁碟區,前提是磁碟區可以提供適當的容量,而且磁碟 I/O 效能不會負面影響監視和報告功能。The data files for the Operations Manager database and Reporting data warehouse can share the same physical volume as long as the volume provides adequate capacity and disk I/O performance does not negatively impact monitoring and reporting functionality.
  • 請考慮將報表資料倉儲放在不同於 Operations Manager 資料庫的伺服器上。Consider placing the Reporting data warehouse on a separate server from the Operations Manager database. 雖然較小規模的部署通常可以將 Operations Manager 資料庫與報表資料倉儲彙總在相同的伺服器上,但是建議您在擴大代理程式數目以及傳入操作資料量時,將兩者分開。Although smaller-scale deployments can often consolidate the Operations Manager database and Reporting data warehouse on the same server, it is advantageous to separate them as you scale up the number of agents and the volume of incoming operational data. 報表資料倉儲和報表伺服器位於與 Operations Manager 資料庫不同的伺服器上時,報表效能較好。When the Reporting data warehouse and Reporting Server are on a separate server from the Operations Manager database, you experience better reporting performance.

Operations Manager 資料倉儲資料庫是管理群組的單一失敗來源,因此可以使用支援的容錯移轉設定,讓它成為高可用性,例如 SQL Server AlwaysOn 可用性群組或容錯移轉叢集執行個體。The Operations Manager data warehouse database is a single source of failure for the management group, so it can be made highly available using supported failover configurations such as SQL Server Always On Availability Groups or Failover Cluster Instances.

SQL Server AlwaysOnSQL Server Always On

SQL Server AlwaysOn 可用性群組支援適用於一組特定使用者資料庫 (可用性資料庫) 的容錯移轉環境。SQL Server Always On availability groups support failover environments for a discrete set of user databases (availability databases). 每一組可用性資料庫均是透過可用性複本來裝載。Each set of availability databases is hosted by an availability replica.

使用 System Center 2016 - Operations Manager 和更新版本時,若要為資料庫提供高可用性,建議優先使用 SQL Always On 而非容錯移轉叢集。With System Center 2016 and later - Operations Manager, SQL Always On is preferred over failover clustering to provide high availability for databases. 原生模式 Reporting Services 安裝 (使用兩個資料庫分隔持續資料儲存與暫時儲存需求) 以外的所有資料庫,都可以裝載在 AlwaysOn 可用性群組上。All databases except the native mode Reporting Services installation, which uses two databases to separate persistent data storage from temporary storage requirements, can be hosted in an AlwaysOn Availability Group.

若要設定可用性群組,您需要部署 Windows Server 容錯移轉叢集 (WSFC) 來裝載可用性複本,並在叢集節點上啟用 AlwaysOn。To set up an availability group you'll need to deploy a Windows Server Failover Clustering (WSFC) cluster to host the availability replica, and enable Always On on the cluster nodes. 然後,您可以新增 Operations Manager SQL Server 資料庫做為可用性資料庫。You can then add the Operations Manager SQL Server database as an availability database.

SQL Server AlwaysOnSQL Server Always On

SQL Server AlwaysOn 可用性群組支援適用於一組特定使用者資料庫 (可用性資料庫) 的容錯移轉環境。SQL Server Always On availability groups support failover environments for a discrete set of user databases (availability databases). 每一組可用性資料庫均是透過可用性複本來裝載。Each set of availability databases is hosted by an availability replica.

使用 System Center 2016 - Operations Manager 和更新版本時,若要為資料庫提供高可用性,建議優先使用 SQL Always On 而非容錯移轉叢集。With System Center 2016 and later - Operations Manager, SQL Always On is preferred over failover clustering to provide high availability for databases. 原生模式 Reporting Services 安裝 (使用兩個資料庫分隔持續資料儲存與暫時儲存需求) 以外的所有資料庫,都可以裝載在 AlwaysOn 可用性群組上。All databases except the native mode Reporting Services installation, which uses two databases to separate persistent data storage from temporary storage requirements, can be hosted in an AlwaysOn Availability Group.

若要設定可用性群組,您需要部署 Windows Server 容錯移轉叢集 (WSFC) 來裝載可用性複本,並在叢集節點上啟用 AlwaysOn。To set up an availability group you'll need to deploy a Windows Server Failover Clustering (WSFC) cluster to host the availability replica, and enable Always On on the cluster nodes. 然後,您可以新增 Operations Manager SQL Server 資料庫做為可用性資料庫。You can then add the Operations Manager SQL Server database as an availability database.

注意

在參與 SQL Always On 的 SQL 伺服器節點上部署 Operations Manager 之後,若要啟用 CLR 嚴格安全性,請在每個 Operations Manager 資料庫上執行 SQL 指令碼After deploying Operations Manager on the SQL server nodes participating in SQL Always On, to enable CLR strict security, run the SQL script on each Operations Manager database.

多重子網路字串Multisubnet string

Operations Manager 不支援連接字串關鍵字 (MultiSubnetFailover=True).Operations Manager does not support the connection string key words (MultiSubnetFailover=True). 由於可用性群組的接聽程式名稱 (在 WSFC 叢集管理員中亦稱為網路名稱或用戶端存取點) 取決於不同子網路的多個 IP 位址 (像是在跨網站容錯移轉組態中部署的情況),管理伺服器向可用性群組接聽程發出的用戶端連線要求會遭遇到連線逾時。Because an availability group has a listener name (known as the network name or Client Access Point in the WSFC Cluster Manager) depending on multiple IP addresses from different subnets, such as when you deploy in a cross-site failover configuration, client-connection requests from management servers to the availability group listener will hit a connection timeout.

在多重子網路環境的可用性群組中部署伺服器節點時,若要避開此限制,建議執行下列動作:The recommended approach to work around this limitation when you have deployed server nodes in the availability group in a multi-subnet environment, is to do the following:

  1. 將可用性群組接聽程式網路名稱設定為僅註冊 DNS 中單一作用中 IP 位址Set the network name of your availability group listener to only register a single active IP address in DNS
  2. 設定叢集針對註冊的 DNS 記錄使用低 TTL 值Configure the cluster to use a low TTL value for the registered DNS record

當容錯移轉至不同子網路中的節點時,這些設定會讓使用新 IP 位址的叢集名稱得以更快速地復原與解析。These settings allow, when fail over to a node in a different subnet, for quicker recovery and resolution of the cluster name with the new IP address.

在何一個 SQL 節點上執行下列 PowerShell 查詢,以修改其設定。Run the following PowerShell query on any one of the SQL nodes to modify its settings.

Import-Module FailoverClusters
Get-ClusterResource "Cluster Name"|Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource "Cluster Name"|Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource "Cluster Name"
Start-ClusterResource "Cluster Name"

如果使用 AlwaysOn 於接聽程式名稱,則應該也要變更接聽程式上的這些設定。If you are using Always On with a listener name, you should also make these configurations changes on the listener.

在目前裝載接聽程式的 SQL 節點上執行下列 PowerShell 查詢,以修改其設定。Run the following PowerShell query on the SQL node currently hosting the listener to modify its settings.

Import-Module FailoverClusters
Get-ClusterResource <Listener Cluster Resource name> | Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource <Listener Cluster Resource name> | Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource <Listener Cluster Resource name>
Start-ClusterResource <Listener Cluster Resource name>

當使用叢集或 AlwaysOn SQL 執行個體為高可用性時,您應該在管理伺服器上啟用自動復原功能,以避免每當節點之間發生容錯移轉時,Operations Manager 資料存取服務即重新啟動。When a clustered or an Always On SQL instance is used for high availability, you should enable the automatic recovery feature on your management servers to avoid the Operations Manager Data Access service restart anytime a failover between nodes occur. 如需如何設定的資訊,請參閱下列知識庫文章 System Center 管理服務在 SQL Server 執行個體離線之後停止回應For information on how to configure this, see the following KB article The System Center Management service stops responding after an instance of SQL Server goes offline.

最佳化 SQL ServerOptimizing SQL Server

一般而言,先前的客戶部署體驗顯示效能問題通常不是 SQL Server 本身高資源使用率 (即處理器或記憶體) 所造成,而是直接與儲存體子系統的設定有關。In general, previous deployment experience with customers shows that performance issues are typically not caused by high resource utilization (that is, processor or memory) with SQL Server itself; rather it is directly related to the configuration of the storage subsystem. 效能瓶頸通常歸因於針對 SQL Server 資料庫執行個體佈建儲存體時未遵循建議的設定指導方針。Performance bottlenecks are commonly attributed to not following recommended configuration guidance with the storage provisioned for the SQL Server database instance. 此類範例包括:Such examples are:

  • LUN 主軸的配置不足,無法支援 Operations Manager 的 IO 需求。Insufficient allocation of spindles for the LUNs to support the IO requirements of Operations Manager.
  • 將交易記錄檔與資料庫檔案裝載在相同的磁碟區上。Hosting transaction logs and database files on the same volume. 這兩個工作負載在 IO 和延遲特性方面不同。These two workloads have different IO and latency characteristics.
  • TempDB 對於放置、調整大小等方面的設定不正確。Configuration of TempDB is incorrect with respect to placement, sizing, etc.
  • 裝載資料庫交易記錄檔、資料庫檔案,以及 TempDB 之磁碟區的磁碟分割對齊錯誤Disk partition misalignment of volumes hosting the database transaction logs, database files, and TempDB
  • 忽略基本的 SQL Server 組態,例如,為資料庫和交易記錄檔使用 AUTOGROW、為查詢平行處理原則使用 MAXDOP 設定、針對每個 CPU 核心建立多個 TempDB 資料檔案等等。Overlooking the basic SQL Server configuration such as using AUTOGROW for database and transaction log files, MAXDOP setting for query parallelism, creating multiple TempDB data files per CPU core, etc.

存放裝置設定對於 Operations Manager 的 SQL Server 部署而言,是重要的元件之一。Storage configuration is one of the critical components to a SQL Server deployment for Operations Manager. 資料庫伺服器通常因為嚴格的資料庫讀取和寫入活動以及交易記錄檔處理的緣故,而與 I/O 有密切的關係。Database servers tend to be heavily I/O bound due to rigorous database read and write activity and transaction log processing. Operations Manager 的 I/O 行為模式通常是 80% 的寫入與 20% 的讀取。The I/O behavior pattern of Operations Manager is typically 80% writes and 20% reads. 因此,不當設定 I/O 子系統可能會導致 SQL Server 系統的效能及運作不佳,而且在 Operations Manager 中會變得明顯。As a result, improper configuration of I/O subsystems can lead to poor performance and operation of SQL Server systems and becomes noticeable in Operations Manager.

在部署 SQL Server 之前,執行 IO 子系統的輸送量測試,對於測試 SQL Server 設計十分重要。It is important to test the SQL Server design by performing throughput testing of the IO subsystem prior to deploying SQL Server. 請確定這些測試能夠以可接受的延遲,達到 IO 需求。Make sure these tests are able to achieve your IO requirements with an acceptable latency. 使用 Diskspd 公用程式評估支援 SQL Server 之儲存子系統的 I/O 容量。Use the Diskspd Utility to evaluate the I/O capacity of the storage subsystem supporting SQL Server. 下列部落格文章的作者為產品小組中檔案伺服器團隊的成員,其內容對於如何使用此工具搭配 PowerShell 程式碼來執行壓力測試,以及如何使用 PerfMon 擷取結果,提供詳細的指導方針和建議。The following blog article, authored by a member of the File Server team in the product group, provides detailed guidance and recommendations on how to go about performing stress testing using this tool with some PowerShell code, and capturing the results using PerfMon. 如需初始指導,您也可以參考 Operations Manager Sizing HelperYou can also refer to the Operations Manager Sizing Helper for initial guidance.

NTFS 配置單位大小NTFS allocation unit size

每當在 RAID 裝置上建立磁碟區時,應該在檔案系統 (NTFS) 上執行磁碟區對齊方式 (通常指的是磁區對齊方式)。Volume alignment, commonly referred to as sector alignment, should be performed on the file system (NTFS) whenever a volume is created on a RAID device. 否則可能會導致效能大幅降低。這是資料分割與等量磁碟區單元界限對齊錯誤最常見的結果。Failure to do so can lead to significant performance degradation and are most commonly the result of partition misalignment with stripe unit boundaries. 它也可能會導致硬體快取對齊錯誤,進而造成陣列快取的使用率效率不彰。It can also lead to hardware cache misalignment, resulting in inefficient utilization of the array cache. 格式化將用於 SQL Server 資料檔案的磁碟分割時,建議您針對資料、記錄檔和 tempdb 使用 64 KB 配置單位大小 (也就是 65,536 個位元組)。When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size (that is, 65,536 bytes) for data, logs, and tempdb. 但是請注意,使用大於 4 KB 的配置單位大小會導致無法在磁碟區上使用 NTFS 壓縮。Be aware however, that using allocation unit sizes greater than 4 KB results in the inability to use NTFS compression on the volume. 雖然 SQL Server 支援壓縮磁碟區上的唯讀資料,但不建議。While SQL Server does support read-only data on compressed volumes, it is not recommended.

保留記憶體Reserve memory

為了支援 System Center - Operations Manager,要找出適當實體記憶體與處理器數量以配置給 SQL Server 的 Windows 伺服器並不容易 (甚至對於此產品以外的其他工作負載也不容易)。Identifying the right amount of physical memory and processors to allocate to the Windows server for SQL Server in support of System Center - Operations Manager is not an easy question to answer (not even for other workloads outside of this product for that matter). 產品小組所提供的調整大小計算機乃是以實驗室環境中執行的測試為依據,不一定會與現實世界中的一般工作負載和設定相符。調整大小計算機會根據工作負載規模 (亦即,500 個系統、1000 個系統等等) 提供指導方針,但是所聲稱的完整性常常會受到質疑。While the sizing calculator provided by the product group, which is based off of testing performed in a lab environment that may or may not align with the typical workload and configuration in the real-world, provides guidance based on workload scale (that is, 500 systems, 1000 systems, etc.) the integrity of what's stated is often brought into question. 這可以當做一開始的建議,但不會也無法將其視為最終的設定。It serves as an initial recommendation to start with, however it's not and cannot be considered the final configuration.

根據預設,SQL Server 可以根據可用的系統資源,以動態方式變更其記憶體需求。By default, SQL Server can change its memory requirements dynamically based on available system resources. 伺服器記憶體下限的預設值為 0,而伺服器記憶體上限的預設值為 2147483647。The default setting for min server memory is 0, and the default setting for max server memory is 2147483647. 您可以為伺服器記憶體上限指定的最小記憶體數量為 16 MB。The minimum amount of memory you can specify for max server memory is 16 megabytes (MB). 許多與效能和記憶體相關的問題是因為客戶沒有設定上限值。A number of performance and memory-related problems are because customer’s don’t set a value for Max. 而且未設定的原因是因為他們不知道要怎麼設定。Server Memory and they don’t do that because they don’t know what to set. 其他許多因素都會影響您配置給 SQL 的記憶體數量上限,此上限可確保作業系統具備足夠的記憶體,以支援在該系統上執行的其他處理序,例如 HBA 卡、管理代理程式、防毒即時掃描等等。否則,作業系統和 SQL 將會分頁到磁碟,接著磁碟 I/O 便會增加,進而降低效能並產生「連鎖」反應,這在 Operations Manager 中很明顯。A number of other factors influence the maximum amount of memory you allocate to SQL to ensure the operating system has enough memory to support the other processes running on that system, such as HBA card, management agents, anti-virus real-time scanning, etc. Otherwise, the OS and SQL will page to disk and then disk I/O increases, further decreasing performance and creating a "ripple" effect where it is noticeable in Operations Manager.

SQL Server 可讓您設定其處理序應該保留和使用的記憶體數量上下限。SQL Server allows you to configure the minimum and maximum amount of memory that should be reserved and used by its process. 將最小數量至少指定為 4 GB 的 RAM。Specify at least 4 GB of RAM as minimum amount. 您應該對裝載其中一個 Operations Manager 資料庫 (操作、資料倉儲、ACS) 的每個 SQL 節點進行這個動作。This should be done for every SQL node hosting one of the Operations Manager databases (Operational, Data warehouse, ACS).

先從為作業系統保留 1 GB 的 RAM 開始,若是 4-16 GB,則每安裝 4 GB 的 RAM,就保留 1 GB,如果超過 16 GB RAM,則每安裝 8 GB 的 RAM,便保留 1 GB。First start by reserving 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM. 接著,監視 Windows 中的記憶體\可用 MB 效能計數器,來判斷您是否可以將可用於 SQL Server 的記憶體增加到超過起始值Then monitor the Memory\Available MBytes performance counter in Windows to determine if you can increase the memory available to SQL Server above the starting value.

注意

此計數器應該保持在 150-300 MB 以上。This counter should remain above the 150-300 MB at a bare minimum. Windows 的 LowMemoryResourceNotification 通知閾值為 96 MB,因此您想要有緩衝區,但在具有 256 GB 或更多 RAM 的大型伺服器上,您應該考慮從 1 GB 開始。Windows signals the LowMemoryResourceNotification at 96 MB so you want a buffer, but you should consider starting above 1 GB on larger servers with 256 GB or higher RAM.

此方法通常非常適用於只專門執行 SQL Server 的伺服器。This approach has typically worked out well for servers that are dedicated to SQL Server. 您也可以透過解決作業系統、其他應用程式、SQL Server 執行緒堆疊和其他多頁配置器的特定記憶體需求,獲得更多技術以判斷在哪裡設定「伺服器記憶體上限」。You can also get much more technical with determining where to set 'max server memory' by working out the specific memory requirements for the OS, other applications, the SQL Server thread stack, and other multipage allocators. 這通常是 ((系統記憶體總數) – (用於執行緒堆疊的記憶體) – (作業系統記憶體需求 ~ 2-4 GB) – (用於其他應用程式的記憶體) – (用於多頁配置的記憶體;SQLCLR、連結的伺服器等)),其中用於執行緒堆疊的記憶體 = ((背景工作執行緒上限) (堆疊大小)),而 x86 系統的堆疊大小為 512 KB、x64 系統的堆疊大小為 2 MB、IA64 系統的堆疊大為 4 MB。Typically this would be ((Total system memory) – (memory for thread stack) – (OS memory requirements ~ 2-4 GB) – (memory for other applications) – (memory for multipage allocations; SQLCLR, linked servers, etc.)), where the memory for thread stack = ((max worker threads) (stack size)) and the stack size is 512 KB for x86 systems, 2 MB for x64 systems and 4 MB for IA64 systems. 「背景工作執行緒上限」的值可以在 sys.dm_os_sys_info 的 max_worker_count 資料行中找到。The value for 'max worker threads' can be found in the max_worker_count column of sys.dm_os_sys_info. 不過,除非您已經在計算其他應用程式時保留,否則使用下列其中一種方法的前提是,您希望 SQL Server 使用電腦上可用的所有項目。However, the assumption with either of these methods is that you want SQL Server to use everything that is available on the machine, unless you've made reservations in the calculations for other applications.

隨著越來越多的客戶邁向將環境中的 SQL Server 虛擬化,這個問題更接近如何決定 SQL Server 在虛擬機器中執行所需的記憶體量下限。As more customers move towards virtualizing SQL Server in their environment, this question is more relevant in determining what is the minimum amount of memory that a SQL Server will need to run in a virtual machine. 遺憾的是,沒辦法計算指定 SQL Server 執行個體實際上可能適合的理想記憶體量,因為 SQL Server 的設計是為快取緩衝集區中的資料,而且它通常會使用您竭盡所能提供的記憶體量。Unfortunately there is no way to calculate what the ideal amount of memory for a given instance of SQL Server might actually be since SQL Server is designed to cache data in the buffer pool, and it will typically use as much memory as you can give it. 當您看到配置給 SQL Server 執行個體的記憶體減少時,其中一件要牢記在心的事是,您最終將會了解,大量磁碟 I/O 存取的代價就是記憶體減少。One of the things to keep in mind when you are looking at reducing the memory allocated to a SQL Server instance, is that you will eventually get to a point where the lower memory gets traded off for higher disk I/O access.

如果您需要了解已經過度佈建的環境中,SQL Server 記憶體的理想設定,最好的方式就是嘗試從環境的基準以及目前的效能標準開始著手。If you need to figure out the ideal configuration for SQL Server memory in an environment that has been over provisioned, the best way to try to go about doing it is to start with a baseline of the environment and the current performance metrics. 要開始監視的計數器包括:Counters to initially monitor include:

  • SQL Server:Buffer Manager\Page Life ExpectancySQL Server:Buffer Manager\Page Life Expectancy
  • SQL Server:Buffer Manager\Page reads/secSQL Server:Buffer Manager\Page reads/sec
  • Physical Disk\Disk Reads/secPhysical Disk\Disk Reads/sec

通常如果環境中有過多用於緩衝集區的記憶體,則 Page Life Expectancy 值將繼續每秒增加一個值,而且不會因為工作負載而減少,因為所有的資料頁面最後都會遭到快取。Typically if the environment has excess memory for the buffer pool, the Page Life Expectancy value will continue to increase by a value of one every second and it won't drop off under the workload because all of the data pages end up being cached. 同時,在快取增加之後,SQL Server:Buffer Manager\Page reads/sec 的數目將會減少,這也對應到 Physical Disk\Disk Reads/sec 的值會減少。At the same time, the number of SQL Server:Buffer Manager\Page reads/sec will be low after the cache ramp up occurs, which will also correspond to a low value for Physical Disk\Disk Reads/sec.

一旦您有環境的基準之後,請變更 sp_configure 的「伺服器記憶體上限」選項,以減少 1GB 的緩衝集區大小,然後在事情從初始快取排清 (一般可能是在環境中執行 RECONFIGURE 時發生) 穩定下來之後,監視對效能計數器的影響。Once you have your baseline for the environment, make a change to the sp_configure 'max server memory' option to reduce the size of the buffer pool by 1GB and then monitor the impact to the performance counters after things stabilize from the initial cache flushing that may typically occur when RECONFIGURE is run in the environment. 如果 Page Life Expectancy 的層級對於您的環境仍然可接受 (請記住,> = 300 的固定目標對於已安裝大量 RAM 的伺服器而言太荒謬),而且 SQL Server:Buffer Manager\Page reads/sec 的數目落在磁碟 I/O 子系統可以支援的範圍 (但效能不會降低),請重複減少 1 GB「伺服器記憶體上限」之 sp_configure 值,並繼續監視對環境之影響的程序。If the level of Page Life Expectancy remains acceptable for your environment (keeping in mind that a fixed target of >= 300 is ridiculous for servers with large amounts of RAM installed), and the number of SQL Server:Buffer Manager\Page reads/sec is within what the disk I/O subsystem can support without performance degradation, repeat the process of reducing the sp_configure value for 'max server memory' by 1GB and continuing to monitor the impact to the environment.

深入了解伺服器記憶體設定 (部分機器翻譯)。Learn more about server memory configuration.

深入了解伺服器記憶體設定 (部分機器翻譯)。Learn more about server memory configuration.

最佳化 TempDBOptimize TempDB

tempdb 資料庫的大小與實際位置可能會影響 Operations Manager 的效能。The size and physical placement of the tempdb database can affect the performance of Operations Manager. 例如,如果針對 tempdb 定義的大小太小,則每次您重新啟動 SQL Server 執行個體時,部分系統處理負載可能會隨著自動成長的 tempdb 一起佔用到支援工作負載所需的大小。For example, if the size that is defined for tempdb is too small, part of the system-processing load may be taken up with autogrowing tempdb to the size required to support the workload every time you restart the instance of SQL Server. 若要達到最佳 tempdb 效能,建議在生產環境中,對 tempdb 使用下列設定︰To achieve optimal tempdb performance, we recommend the following configuration for tempdb in a production environment:

  • 將 tempdb 的復原模式設定為 SIMPLE。Set the recovery model of tempdb to SIMPLE. 此模型會自動回收記錄檔空間,讓空間需求維持在小的狀態。This model automatically reclaims log space to keep space requirements small.
  • 將檔案大小設定為一個夠大的值,以容納環境中的一般工作負載,藉此預先配置所有 tempdb 檔案的空間。Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment. 它可以防止 tempdb 擴充過於頻繁,這可能會影響效能。It prevents tempdb from expanding too frequently, which can affect performance. tempdb 資料庫可以設定為自動成長,但這應該用來增加非計劃的例外狀況的磁碟空間。The tempdb database can be set to autogrow, but this should be used to increase disk space for unplanned exceptions.
  • 建立所需的檔案數目,將磁碟頻寬發揮到極致。Create as many files as needed to maximize disk bandwidth. 使用多個檔案可減少 tempdb 儲存體爭用,並明顯改善延展性。Using multiple files reduces tempdb storage contention and yields improved scalability. 不過,請不要建立太多檔案,因為這可能會降低效能並增加管理負擔。However, do not create too many files as it can reduce performance and increase management overhead. 一般指導方針是,在伺服器上為每個邏輯處理器建立一個資料檔案 (解釋任何相關性遮罩設定),然後再視需要,向上或向下調整檔案的數目。As a general guideline, create one data file for each logical processor on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. 基於一般規則,如果邏輯處理器的數目小於或等於 8,則與邏輯處理器使用相同數目的資料檔案。As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. 如果邏輯處理器的數目大於 8,請使用 8 個資料檔案,之後如果爭用情況持續發生,請增加 4 的倍數 (最多到邏輯處理器數目) 的資料檔案數目,直到爭用情況減少至可接受的層級,或變更工作負載/程式碼為止。If the number of logical processors is greater than 8, use eight data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code. 如果未減少爭用情況,您可能必須再增加資料檔案的數目。If the contention is not reduced, you may have to increase the number of data files more.
  • 讓每個資料檔案大小相同;這樣便可以獲得最佳的依比例填入效能。Make each data file the same size; allowing for optimal proportional-fill performance. 資料檔案大小相等相當重要,因為依比例填入演算法是以檔案大小為基礎。The equal sizing of data files is critical because the proportional fill algorithm is based on the size of the files. 如果建立的資料檔案大小不相等,依比例填入演算法便會嘗試使用最大的檔案進行 GAM 配置,而不是在所有檔案之間散佈配置,藉此廢除建立多個資料檔案的目的。If data files are created with unequal sizes, the proportional fill algorithm tries to use the largest file more for GAM allocations instead of spreading the allocations between all the files, thereby defeating the purpose of creating multiple data files.
  • 使用固態硬碟,將 tempdb 資料庫放在快速的 I/O 子系統,以獲得最佳效能。Put the tempdb database on a fast I/O subsystem using solid-state drives for the most optimal performance. 如果有許多直接連接的磁碟,請使用磁碟等量化。Use disk striping if there are many directly attached disks.
  • 將 tempdb 資料庫放在不同於使用者資料庫所使用的磁碟上。Put the tempdb database on disks that differ from those that are used by user databases.

若要設定 tempdb,您可以執行下列查詢,或在 Management Studio 中修改其內容。To configure tempdb, you can run the following query or modify its properties in Management Studio.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 8)
GO
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', NEWNAME = N'tempdb', SIZE = 2097152KB , FILEGROWTH = 512MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb2.mdf' , SIZE = 2097152KB , FILEGROWTH = 512MB )
GO

從 sys.sysprocesses 執行 T-SQL query SELECT *,以偵測 tempdb 資料庫的頁面配置爭用。Run the T-SQL query SELECT * from sys.sysprocesses to detect page allocation contention for the tempdb database. 在系統資料表輸出中,等候資源可能會顯示為 "2:1:1" (PFS 頁面) 或 "2:1:3" (共用全域配置對應頁面)。In the system table output, the wait resource may show up as "2:1:1" (PFS Page) or "2:1:3" (Shared Global Allocation Map Page). 根據爭用的程度而定,這可能也會導致 SQL Server 短暫地出現沒有回應。Depending on the degree of contention, this may also lead to SQL Server appearing unresponsive for short periods. 另一種方法是檢查動態管理檢視 [sys.dm_exec_request 或 sys.dm_os_waiting_tasks]。Another approach is to examine the Dynamic Management Views [sys.dm_exec_request or sys.dm_os_waiting_tasks]. 結果將會顯示這些要求或工作正在等待 tempdb 資源,而且當您執行 sys.sysprocesses 查詢時,所擁有的值與先前反白顯示的值類似。The results will show that these requests or tasks are waiting for tempdb resources, and have similar values as highlighted earlier when you execute the sys.sysprocesses query.

如果上述建議沒有明顯減少配置爭用情況,而且爭用情況是發生在 SGAM 頁面上,請在 SQL Server 的啟動參數中實作追蹤旗標 -T1118,如此一來,即使在回收 SQL Server 之後,追蹤旗標還是維持有效。If the previous recommendations do not significantly reduce the allocation contention and the contention is on SGAM pages, implement trace flag -T1118 in the Startup parameters for SQL Server so that the trace flag remains in effect even after SQL Server is recycled. 在此追蹤旗標之下,SQL Server 會將完整範圍配置給每個資料庫物件,藉此消除 SGAM 頁面的爭用情況。Under this trace flag, SQL Server allocates full extents to each database object, thereby eliminating the contention on SGAM pages. 請注意,此追蹤旗標會影響 SQL Server 執行個體上的每個資料庫。Note that this trace flag affects every database on the instance of SQL Server.

平行處理原則的最大限制Max degree of parallelism

SQL Server 對於 Operations Manager 中小型部署的預設設定適合大多數的需求。The default configuration of SQL Server for small to medium size deployments of Operations Manager is adequate for most needs. 不過,當管理群組的工作負載向企業等級案例向上調整 (通常是 2,000 個以上的代理程式管理系統和進階監視設定,其中包括具有進階綜合交易的服務層級監視、網路裝置監視、跨平台等等) 時,則必須將本文件章節中所述的 SQL Server 設定最佳化。However, when the workload of the management group scales upwards towards an enterprise class scenario (typically 2,000+ agent-managed systems and an advanced monitoring configuration, which includes service-level monitoring with advanced synthetic transactions, network device monitoring, cross-platform, and so forth) it is necessary to optimize the configuration of SQL Server described in this section of the document. 先前指導方針中未討論的其中一個設定選項為 MAXDOP。One configuration option that has not been discussed in previous guidance, is MAXDOP.

Microsoft SQL Server 平行處理原則的最大限制 (MAXDOP) 設定選項可控制在平行計劃中執行查詢所使用的處理器數目。The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. 此選項可決定用於以平行方式執行工作之查詢計劃運算子的運算和執行緒資源。This option determines the computing and thread resources that are used for the query plan operators that perform the work in parallel. 根據是在對稱式多處理 (SMP) 電腦、非統一記憶體存取 (NUMA) 電腦,還是已啟用超執行緒的處理器上安裝 SQL Server 而定,您必須適當地設定平行處理原則的最大限制選項。Depending on whether SQL Server is set up on a symmetric multiprocessing (SMP) computer, a non-uniform memory access (NUMA) computer, or hyperthreading-enabled processors, you have to configure the max degree of parallelism option appropriately.

在具有多個微處理器或 CPU 的電腦上執行 SQL Server 時,它會偵測平行處理原則的最佳限制,也就是為針對每個平行計劃執行來執行單一陳述式所採用的處理器數目。When SQL Server runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. 根據預設,此選項的值為 0,這可讓 SQL Server 決定平行處理原則的最大限制。By default, its value for this option is 0, which allows SQL Server to determine the maximum degree of parallelism.

因為與操作、資料倉儲,甚至稽核資料庫相關,而在 Operations Manager 中預先定義的預存程序和查詢不包含 MAXDOP 選項,因為在安裝期間,沒有辦法以動態方式查詢向作業系統呈現的處理器數目,也不會嘗試將此設定的值硬式編碼,這在執行查詢時,可能會發生負面後果。The stored procedures and queries pre-defined in Operations Manager as it relates to the operational, data warehouse, and even audit database do not include the MAXDOP option, as there is no way during installation to dynamically query how many processors are presented to the operating system, nor does it attempt to hardcode the value for this setting, which could have negative consequences when the query is executed.

注意

平行處理原則的最大限制設定選項不會限制 SQL Server 所使用的處理器數目。The max degree of parallelism configuration option does not limit the number of processors that SQL Server uses. 若要設定 SQL Server 使用的處理器數目,請使用相關性遮罩設定選項。To configure the number of processors that SQL Server uses, use the affinity mask configuration option.

  • 若伺服器使用的處理器超過 8 個,請使用下列設定︰MAXDOP=8For servers that use more than eight processors, use the following configuration: MAXDOP=8
  • 若伺服器使用的處理器不超過 8 個,請使用下列設定︰MAXDOP=0 to N 請注意,在此設定中,N 代表處理器數目。For servers that use eight or fewer processors, use the following configuration: MAXDOP=0 to N Note In this configuration, N represents the number of processors.
  • 對於已設定 NUMA 的伺服器,MAXDOP 不得超過指派給每個 NUMA 節點的 CPU 數目。For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
  • 對於已啟用超執行緒的伺服器,MAXDOP 值不得超過實體處理器的數目。For servers that have hyperthreading enabled, the MAXDOP value should not exceed the number of physical processors.
  • 對於已設定 NUMA 並啟用超執行緒的伺服器,MAXDOP 值不得超過每個 NUMA 節點的實體處理器的數目。For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.

您可以查詢 sys.dm_os_tasks,藉此監視平行背景工作的數目。You can monitor the number of parallel workers by querying sys.dm_os_tasks.
此伺服器的硬體設定之前是具有 24 個核心處理器的 HP Blade G6,以及 196 GB 的 RAM。The hardware configuration of this server was an HP Blade G6 with 24 core processors and 196 GB of RAM. 裝載 Operations Manager 資料庫之執行個體的 MAXMEM 設定為 64 GB。The instance hosting the Operations Manager database had a MAXMEM setting of 64 GB. 執行本節所建議的最佳化之後,即可改善效能。After performing the suggested optimizations in this section, performance improved. 不過,查詢平行處理原則瓶頸仍然存在。However, a query parallelism bottleneck still persisted. 測試不同的值之後,設定 MAXDOP=4 可找到最佳效能。After testing different values, the most optimal performance was found by setting MAXDOP=4.

初始資料庫大小Initial database sizing

在部署之後,評估 Operations Manager 資料庫 (特別是操作和資料倉儲資料庫) 未來在前幾個月內的成長並不是一個簡單的體驗。Estimating the future growth of the Operations Manager databases, specifically the operational and data warehouse databases, within the first several months after deployment is not a simple exercise. 雖然 Operations Manager Sizing Helper 在根據產品小組從實驗室中的測試所衍生的公式來評估潛在成長方面是合理的,但是它不會將近期和長期可能影響成長的數個因素納入考量。While the Operations Manager Sizing Helper is reasonable in estimating potential growth based on the formula derived by the product group from their testing in the lab, it does not take into account several factors, which can influence growth in the near term versus long term.

Sizing Helper 所建議的初始資料庫大小應該配置成預測的大小,以減少分散及對應的額外負荷,這可以在安裝時,為操作和資料倉儲資料庫指定。The initial database size, as suggested by the Sizing Helper, should be allocated to a predicted size, to reduce fragmentation and corresponding overhead, which can be specified at setup time for the Operational and Data Warehouse databases. 如果在安裝期間沒有足夠的儲存空間可用,之後可以使用 SQL Management Studio 擴充資料庫,然後重新編制索引以便之後進行磁碟重組並據此最佳化。If during setup not enough storage space is available, the databases can be expanded later by using SQL Management Studio and then reindexed thereafter to defragment and optimize accordingly. 此建議也適用於 ACS 資料庫。This recommendation applies also to the ACS database.

主動監視操作和資料倉儲資料庫的成長應該每日或每週執行。Proactive monitoring of the growth of the operational and data warehouse database should be performed on a daily or weekly cycle. 您必須識別非預期且明顯的成長陡增並開始進行疑難排解,才能判斷這是因為管理組件工作流程 (亦即,探索規則、效能或事件收集規則,或監視或警示規則) 中的一個錯誤所致,還是在發行管理程序的測和品質確保階段未識別的管理組件其他徵兆所致。This will be necessary to identify unexpected and significant growth spurts, and begin troubleshooting in order to determine if it is caused by a bug in a management pack workflow (that is, discovery rule, performance or event collection rule, or monitor or alert rule) or other symptom with a management pack that was not identified during testing and quality assurance phase of the release management process.

資料庫自動成長Database autogrow

在磁碟上保留的資料庫檔案大小已滿時,SQL Server 可以依百分比或固定數量,自動增加大小。When the databases file size that has been reserved on disk becomes full, SQL Server can automatically increase the size, by a percentage or by a fixed amount. 此外,您可以設定資料庫大小上限,以避免填滿磁碟上可用的所有空間。Moreover, a maximum database size can be configured, to prevent filling up all the space available on disk. 根據預設,Operations Manager 資料庫未設定為啟用自動成長;只有資料倉儲和 ACS 資料庫有。By default, the Operations Manager database is not configured with autogrow enabled; only the Data Warehouse and ACS databases are.

僅仰賴自動成長做為非預期成長的偶發事件。Only rely on autogrow as a contingency for unexpected growth. 自動成長會對效能帶來負面影響,處理高度交易的資料庫時,應將其納入考量。Autogrow introduces a performance penalty that should be considered when dealing with a highly transactional database. 效能的負面影響包括︰Performance penalties include:

  • 記錄檔或資料庫分散 (如果您沒有提供適當的成長增量)。Fragmentation of the log file or database if you don’t provide an appropriate growth increment.
  • 如果您執行的交易需要比可用空間還要多的記錄檔空間,而且您已經為該資料庫的交易記錄檔開啟自動成長選項,則交易完成所需的時間將會包含交易記錄檔依設定的量成長所需的時間。If you run a transaction that requires more log space than is available, and you have turned on the autogrow option for the transaction log of that database, then the time it takes the transaction to complete will include the time it takes the transaction log to grow by the configured amount.
  • 如果您執行需要記錄檔成長的大型交易,需要寫入交易記錄檔的其他交易也必須等到成長作業完成為止。If you run a large transaction that requires the log to grow, other transactions that require a write to the transaction log will also have to wait until the grow operation completes.

如果您合併自動成長和自動壓縮選項,您可能會產生不必要的負擔。If you combine the autogrow and autoshrink options, you might create unnecessary overhead. 請確定觸發成長和壓縮作業的臨界值將不會導致大小經常上下變更。Make sure that the thresholds that trigger the grow and shrink operations will not cause frequent up and down size changes. 例如,您可以執行導致交易記錄檔在認可之前成長 100 MB 的交易。For example, you may run a transaction that causes the transaction log to grow by 100 MB by the time it commits. 不久之後,自動壓縮便會啟動,並壓縮 100 MB 的交易記錄檔。Some time after that the autoshrink starts and shrinks the transaction log by 100 MB. 接著,您要執行相同的交易,而且該交易會再次導致交易記錄檔成長 100 MB。Then, you run the same transaction and it causes the transaction log to grow by 100 MB again. 在該範例中,您將產生不必要的負擔,而且可能會產生記錄檔分散,兩者都可能會對效能造成負面影響。In that example, you are creating unnecessary overhead and potentially creating fragmentation of the log file, either of which can negatively affect performance.

建議您謹慎地設定這兩個設定。It is recommended to configure these two settings carefully. 特定設定真的取決於您的環境。The particular configuration really depends on your environment. 一般而言,建議依固定數量增加資料庫的大小,以降低磁碟分散程度。In general, it is recommended to increase database size by a fixed amount in order to reduce disk fragmentation. 例如,請查看下圖,其中資料庫設定為每次需要自動成長時增加 1024 MB。See, for example, the following figure, where the database is configured to grow by 1024 MB each time autogrow is required.

叢集容錯移轉原則Cluster failover policy

Windows Server 容錯移轉叢集是一個高可用性平台,將持續監視叢集中節點的網路連線和健全狀況。Windows Server Failover Clustering is a high availability platform that is constantly monitoring the network connections and health of the nodes in a cluster. 如果無法透過網路連線到節點,則會採取復原動作來復原,並讓叢集中另一個節點上的應用程式和服務恢復連線。If a node is not reachable over the network, then recovery action is taken to recover and bring applications and services online on another node in the cluster. 現成可用的預設設定適用於伺服器完全中斷的故障情況,這會被視為「硬體」故障。The default settings out of the box are optimized for failures where there is a complete loss of a server, which is considered a ‘hard’ failure. 這些是無法復原的故障案例,例如,非備援硬體或電源故障。These would be unrecoverable failure scenarios such as the failure of non-redundant hardware or power. 在這些情況下,伺服器會中斷,而且目標是讓容錯移轉叢集非常快速地偵測到伺服器中斷,並快速地在叢集中的另一部伺服器上復原。In these situations, the server is lost and the goal is for Failover Clustering to quickly detect the loss of the server and rapidly recover on another server in the cluster. 若要完成從硬體故障快速復原,叢集健全狀況監視的預設設定非常積極。To accomplish this fast recovery from hard failures, the default settings for cluster health monitoring are fairly aggressive. 不過,這些設定都可以完全設定,以便在各種情況下都能夠靈活運用。However, they are fully configurable to allow flexibility for a variety of scenarios.

這些預設設定對大多數客戶都提供最佳的行為,但是因為叢集的距離會從英吋延伸至可能數英哩遠,因此叢集可能會暴露在節點之間其他可能不可靠的網路元件之下。These default settings deliver the best behavior for most customers, however as clusters are stretched from being inches to possibly miles apart, the cluster may become exposed to additional and potentially unreliable networking components between the nodes. 另一個因素是,商用伺服器的品質不斷提升,加上透過備援元件 (例如雙電源供應器、NIC 小組,和多重路徑 I/O) 的擴增復原,非備援硬體故障的數目可能相當少見。Another factor is that the quality of commodity servers is constantly increasing, coupled with augmented resiliency through redundant components (such as dual power supplies, NIC teaming, and multi-path I/O), the number of non-redundant hardware failures may potentially be fairly rare. 由於硬體故障可能比較不頻繁,因此部分客戶可能希望微調暫時性故障的叢集,其中叢集能夠更快地從節點之間的短暫網路故障復原。Because hard failures may be less frequent, some customers may wish to tune the cluster for transient failures, where the cluster is more resilient to brief network failures between the nodes. 您可以透過增加預設的故障閾值,將敏感度減少至持續一小段時間的短暫網路問題。By increasing the default failure thresholds, you can decrease the sensitivity to brief network issues that last a short period of time.

請務必了解,這裡並沒有正確的答案,而且最佳化的設定可能會因為特定的業務需求和服務等級協定而有所不同。It is important to understand that there is no right answer here, and the optimized setting may vary by your specific business requirements and service level agreements.

將 SQL Server 虛擬化Virtualizing SQL Server

在虛擬環境中,基於效能的考量,建議您將操作資料庫和資料倉儲資料庫儲存在直接連接的存放裝置上,不要儲存在虛擬磁碟上。In virtual environments, for performance reasons, it is recommended that you store the operational database and data warehouse database on a direct attached storage, and not on a virtual disk. 請務必使用 Operations Manager Sizing Helper 評估所需的 IOPS,以及您的資料磁碟要驗證的壓力測試。Always use the Operations Manager Sizing Helper to estimate required IOPS, and stress test your data disks to verify. 您可以為此工作運用 SQLIO 工具。You can leverage the SQLIO tool for this task. 如需虛擬化的 Operations Manager 環境的其他指導方針,另請參閱 Operations Manager 虛擬化支援See also Operations Manager virtualization support for additional guidance on virtualized Operations Manager environment.

AlwaysOn 與復原模式Always On and recovery model

雖然不是完全最佳化,但是關於 AlwaysOn 可用性群組中的重要考量是,根據設計,此功能必須在「完整」復原模式中設定資料庫。Although not strictly an optimization, an important consideration regarding Always On Availability Group is the fact that, by design, this feature requires the databases to be set in the “Full” recovery model. 也就是說,在完整備份完成之前,絕不會捨棄交易記錄檔,或是只會捨棄交易記錄檔。Meaning, the transaction logs are never discarded until either a full backup is done, or only the transaction log. 因此,備份策略並不是選擇性的,但卻是 Operations Manager 資料庫的 AlwaysOn 設計必要的一部分。For this reason, a backup strategy is not an optional but a required part of the AlwaysOn design for Operations Manager databases. 否則,含有交易記錄檔的磁碟會隨著時間而填滿。Otherwise, with time, disks containing transaction logs will fill up.

備份策略必須將環境的詳細資料納入考量。A backup strategy must take into account the details of your environment. 下表提供一個典型的備份排程。A typical backup schedule is given in the following table.

備份類型Backup Type 排程Schedule
僅限交易記錄檔Transaction log only 每一小時Every one hour
完整Full 每個星期日的上午 3:00Weekly, Sunday at 3:00 AM

最佳化 SQL Server Reporting ServicesOptimizing SQL Server reporting services

Reporting Services 執行個體做為 Proxy,以存取資料倉儲資料庫中的資料。The Reporting Services instance acts as a proxy for access to data in the Data Warehouse database. 它會根據儲存在管理組件內的範本,產生並顯示報表。It generates and displays reports based on templates stored inside the management packs.

Reporting Services 的幕後沒有裝載 ReportServer 和 ReportServerTempDB 資料庫的 SQL Server 資料庫執行個體。Behind the scenes of Reporting Services, there is a SQL Server Database instance that hosts the ReportServer and ReportServerTempDB databases. 適用於關於此執行個體之效能微調的一般建議。General recommendations regarding the performance tuning of this instance apply.

注意

從 SQL Server Reporting Services (SSRS) 2017 14.0.600.1274 版開始,預設安全性設定不允許資源延伸模組上傳。From SQL Server Reporting Services (SSRS) 2017 version 14.0.600.1274 and later, the default security settings do not allow resource extension uploads. 這會導致在部署報表元件元件期間,於 Operations Manager 中發生 ResourceFileFormatNotAllowedException 例外狀況。This leads to ResourceFileFormatNotAllowedException exceptions in Operations Manager during deployment of reporting components.

若要修正此問題,請開啟 SQL Management Studio,連線到您的 Reporting Services 執行個體,開啟 [內容]>[進階],並將 *.* 新增至 AllowedResourceExtensionsForUpload 的清單。To fix this, open SQL Management Studio, connect to your Reporting Services instance, open Properties>Advanced, and add *.* to the list for AllowedResourceExtensionsForUpload. 您也可以選擇將 Operations Manager 報表延伸模組的完整清單,新增至 SSRS 中的「允許清單」。Alternatively, you can add the full list of Operations Manager's reporting extensions to the allow list in SSRS.

後續步驟Next steps

若要了解如何設定在防火牆後面裝載報表資料倉儲,請參閱跨越防火牆連線報表資料倉儲To understand how to configure hosting the Report data warehouse behind a firewall, see Connect Reporting Data Warehouse Across a Firewall.