Return
-
Title
How to move the SQL Server PI repository to a different host or server -
Description
How can a SQL Server PI repository be migrated from to another (different) MS SQL Server machine?
-
Resolution
The follow steps can be used to move a SQL Server PI repository between different systems.
- Disconnect the PI extension from all of the agents by selecting all of the database agents with PI using the repository from Global View (Databases dashboard), clicking Settings then Administration, and then selecting the agent checkboxes and Disable in the SQL Performance Investigator panel
- Delete the SQL PI repository agent (with the letters SPI ) in the agent type from Agent Status (DBSS-SPIRepository, SPIRepository, etc).
- Move the PI database to the new instance (i.e. detach/attach, backup restore, etc.)
- Add the PI extension back to one database agent to recreate the SPI agents by Navigating to the Global View (Databases) dashboard, clicking Settings then Administration, and then selecting the agent checkbox and Enable in the SQL Performance Investigator panel. Fill in any of the necessary connection details when prompted in the popups.
- When prompted in the schema creation popup to confirm the repository database path locations, choose an existing PI database in the pulldown option at the bottom of the popup.
- Navigate to PI, wait a couple minutes to make sure the data is collecting and extend the range to one week or more to look at historical data
- Add the PI extension the rest of the database agents by navigating to the Global View (Databases) dashboard, selecting the other DB agents, clicking Settings then Administration, and then selecting the agent checkbox and Enable in the SQL Performance Investigator panel.