如何移動報表資料倉儲資料庫How to move the Reporting data warehouse database

重要

已不再支援此版本的 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 型電腦移至另一部電腦。After the initial deployment of System Center Operations Manager, you might need to move the Reporting data warehouse database from one Microsoft SQL Server-based computer to another.

移動期間,您需要停止管理伺服器上的服務、備份資料庫、還原資料庫、更新管理伺服器上的登錄、更新資料庫資料表、新增登入及修改登入的使用者對應設定。During the move, you need to stop services on your management servers, back up the database, restore the database, update the registry on management servers, update database tables, add new Logins, and modify User Mapping settings for Logins. 如需詳細資訊,請參閱 SQL Server 文件For more information, see SQL Server documentation.

注意

如果未正確執行且未在合理失敗時間長度內執行,此程序可能會導致資料遺失。This procedure can result in data loss if it is not performed correctly and within a reasonable length of time of the failure. 請務必正確遵循所有步驟,而且每個步驟之間沒有不必要的延遲。Ensure that you follow all steps precisely, without unnecessary delays between the steps.

步驟摘要Summary of steps

移動報表 DW 資料庫的摘要步驟

移動報表資料倉儲資料庫Moving the Reporting data warehouse database

停止 Operations Manager 服務Stop the Operations Manager services

在管理群組中的所有管理伺服器上,停止 Operations Manager 服務:On all the management servers in the management group, stop the Operations Manager services:

  • System Center 資料存取System Center Data Access
  • Microsoft Monitoring AgentMicrosoft Monitoring Agent
  • System Center 管理設定System Center Management Configuration

備份舊的 SQL Server 執行個體上的報表資料倉儲資料庫Backup the Reporting data warehouse database on the old SQL Server instance

  1. 在裝載報表資料倉儲資料庫的原始 SQL Server 執行個體上,使用 Microsoft SQL Server Management Studio 來建立資料庫的完整備份。On the original SQL Server instance hosting the Reporting data warehouse database, use Microsoft SQL Server Management Studio to create a full backup of the database. 預設名稱為 OperationsManagerDW。The default name is OperationsManagerDW.

    如需詳細資訊,請參閱如何:。備份資料庫 (SQL Server Management Studio)For more information, see How to: Back Up a Database (SQL Server Management Studio).

  2. 將備份檔案複製到新 SQL Server 執行個體的本機磁碟。Copy the backup file to a local drive of the new SQL Server instance.

更新 GlobalSettings 資料表Update the GlobalSettings table

在 OperationsManager DB 中,手動更新 GlobalSettings 資料表以反映主機\執行個體,亦即資料倉儲已移至其中的位置。In the OperationsManager DB, manually update the GlobalSettings table to reflect the host\Instance, where the data warehouse has been moved to.

在新的 SQL Server 執行個體上還原報表資料倉儲資料庫Restore the Reporting data warehouse database on the new SQL Server instance

  1. 使用 Microsoft SQL Server Management Studio 來還原報表資料倉儲資料庫Use Microsoft SQL Server Management Studio to restore the Reporting data warehouse database. (在上一個步驟中,您已將資料庫備份檔案移至新 SQL Server 執行個體的本機磁碟)。在此步驟中,您可以變更資料庫的名稱並選擇檔案位置。(In the previous step, you moved the database backup file to a local drive of the new SQL Server instance.) In this step, you can change the name of the database and choose the file location.

    如需詳細資訊,請參閱如何:。還原資料庫備份 (SQL Server Management Studio)For more information, see How to: Restore a Database Backup (SQL Server Management Studio).

  2. 在 SQL Server Management Studio 中,確認資料庫已上線。In SQL Server Management Studio, verify that the database is online.

更新管理伺服器和報表資料倉儲資料庫上的登錄Update the registry on the management servers and Reporting data warehouse database

將 Operations Manager 報表資料倉儲資料庫移至不同的 SQL Server 執行個體之後,您必須遵循下列步驟來重新設定管理群組中的所有管理伺服器,以參考新的電腦名稱和執行個體。After moving the Operations Manager Reporting data warehouse database to a different SQL Server instance, you will need to follow the steps below to reconfigure all management servers in the management group to reference the new computer name and instance. 這需要修改操作資料庫中的登錄、設定服務設定檔及數個資料表。This requires modifying the registry, the configuration service configuration file, and several tables in the operational database. 如何設定 Operations Manager 與 SQL Server 通訊中詳細說明這些步驟。The steps are detailed in the How to configure Operations Manager to communicate with SQL Server.

更新報表伺服器Update Reporting server

在報表伺服器上,您必須變更連接字串,以參考新的電腦名稱及裝載報表資料倉儲資料庫的 SQL Server 執行個體。On the reporting server, you will need to change the connection string to reference the new computer name and instance of the SQL Server instance hosting the Reporting data warehouse database. 如何設定 Operations Manager 與 SQL Server 通訊中詳細說明這些步驟。The steps are detailed in the How to configure Operations Manager to communicate with SQL Server.

在裝載報表資料倉儲資料庫的新 SQL Server 執行個體上更新安全性認證Update security credentials on the new SQL Server instance hosting the Reporting data warehouse database

  1. 在裝載報表資料倉儲資料庫的新 SQL Server 執行個體上,開啟 SQL Management Studio。On the new SQL Server instance hosting the Reporting data warehouse database, open SQL Management Studio.

  2. 展開 [安全性] ,然後展開 [登入] ,再新增資料寫入器帳戶。Expand Security , then expand Logins , and then add the data writer account. 如需詳細資訊,請參閱如何建立 SQL Server 登入For more information, see How to Create a SQL Server Login.

  3. 在 [登入] 底下,新增資料讀取器帳戶。Under Logins , add the data reader account.

  4. 在 [登入] 下,使用 "domain\user" 格式新增資料存取服務使用者帳戶。Under Logins , add the Data Access Service user account, using the form "domain\user".

  5. 針對資料存取服務 (DAS) 使用者帳戶,新增下列使用者對應:For the Data Access Service (DAS) user account, add the following user mappings:

    • db_datareaderdb_datareader
    • OpsMgrReaderOpsMgrReader
    • apm_datareaderapm_datareader
  6. 如果您要新增對應的 SQL 執行個體之前並沒有帳戶,SID 會自動從還原的資料倉儲資料庫挑選對應。If an account has not existed before in the SQL instance in which you are adding it, the mapping will be picked up by SID automatically from the restored data warehouse database. 如果 SQL 執行個體之前已有帳戶,您會收到錯誤,指出該登入失敗,但 [登入] 下會顯示帳戶。If the account has existed in that SQL instance before, you receive an error indicating failure for that login, although the account appears under Logins. 如果您要建立新的登入,請確定該登入和資料庫的使用者對應設定為與舊登入相同的值,如下所示:If you are creating a new login, ensure the User Mapping for that login and database are set to the same values as the previous login as follows:

    登入Login 資料庫Database
    DW 資料寫入器DW Data Writer - db_owner- db_owner
    - OpsMgrWriter- OpsMgrWriter
    - apm_datareader- apm_datareader
    - apm_datawriter- apm_datawriter
    DW 資料讀取器DW Data Reader - db_datareader- db_datareader
    - OpsMgrReader- OpsMgrReader
    - apm_datareader- apm_datareader
    DAS/設定帳戶DAS/Config account - db_datareader- db_datareader
    - OpsMgrReader- OpsMgrReader
    - apm_datareader- apm_datareader

    注意

    如果 DAS/設定帳戶使用 LocalSystem 帳戶,請使用 <domain><computername>$ 格式指定電腦帳戶。If the DAS/Configuration account uses the LocalSystem account, specify computer account in the form <domain><computername>$.

啟動 Operations Manager 服務Start the Operations Manager services

  1. 在管理群組中的所有管理伺服器上,啟動 Operations Manager 服務:On all the management servers in the management group, start the Operations Manager services:
    • System Center 資料存取System Center Data Access
    • Microsoft Monitoring AgentMicrosoft Monitoring Agent
    • System Center 管理設定System Center Management Configuration

更新 Kerberos 連線的服務主體名稱Update Service Principal Name for Kerberos Connections

若要更新搭配 SQL Server 使用的 Kerberos 驗證,您應該檢閱註冊 Kerberos 連線的服務主體名稱以讓管理伺服器使用 Kerberos 通訊協定向 SQL Server 驗證。To update Kerberos authentication with SQL Server, you should review Register a Service Principal Name for Kerberos Connections in order for management servers to authenticate with the SQL Server using Kerberos protocol.

確認資料倉儲資料庫已成功移動To verify a successful move of the data warehouse database

  1. 確認您是否可以順利從主控台執行報表。Verify that you can successfully run a report from the console.

  2. 確定管理群組中所有管理伺服器的健全狀況狀態為狀況良好。Ensure that the health state of all management servers in the management group are Healthy. 如果有任何管理伺服器的健全狀況狀態為「重大」,請開啟健全狀況總管,展開 [可用性- <server name>],然後繼續展開,直到您可以巡覽至資料倉儲 SQL RS 所部署的管理組件清單要求狀態。If the health state of any management server is Critical, open Health Explorer, expand Availability - <server name>, and then continue to expand until you can navigate to Data Warehouse SQL RS Deployed Management Pack List Request State. 請檢查相關聯的事件,以判斷是否有存取資料倉儲資料庫的問題。Check the associated events to determine if there is an issue accessing the data warehouse database.

  3. 檢查作業系統事件。Check operating system events.

    a.a. 開啟 [事件檢視器],然後巡覽至應用程式及服務記錄檔和 Operations Manager。Open the Event Viewer and navigate to Applications and Services Logs and Operations Manager.
    b.b. 在 Operations Manager 記錄檔中,搜尋具有「健全狀況服務模組來源」和「資料倉儲類別」的事件。In the Operations Manager log, search for events with a Source of Health Service Module and a Category of Data Warehouse. 如果移動成功,應存在事件編號 31570、31558 或 31554。If the move was successful, event number 31570, 31558, or 31554 should exist.
    c.c. 如果有存取資料倉儲資料庫的問題,則會存在事件編號 31563、31551、31569 或 31552。If there is an issue accessing the data warehouse database, event numbers 31563, 31551, 31569, or 31552 will exist.

  4. 檢查 Operations Manager 中的事件:Check events in Operations Manager:

    a.a. 在 Operations 主控台中,按一下 [監視]。In the Operations console, click Monitoring.
    b.b. 在 [監視] 工作區中,依序巡覽至 [監視]、[Operations Manager]、[Health Service Module Events] (健全狀況服務模組事件) 及 [Performance Data Source Module Events] (效能資料來源模組事件)。In the Monitoring workspace, navigate to Monitoring, Operations Manager, Health Service Module Events, and then to Performance Data Source Module Events.
    c.c. 在 [Performance Data Source Module Events] (效能資料來源模組事件) 窗格中,搜尋 [日期和時間] 晚於移動的事件。Search the Performance Data Source Module Events pane for events with a Date and Time that is later than the move.
    d.d. 如果資料倉儲資料庫發生問題,應存在具有「健全狀況服務模組來源」且事件編號為 10103 的事件。If there is a problem with the data warehouse database, events which have a Source of Health Service Module and an Event Number of 10103 should exist.

後續步驟Next steps