When migrating a Management Reporter database to a new SQL server, we followed the exact steps in KB 2744330.
- Backup Management Reporter on the old SQL Server.
- Remove the integration in Configuration Console.
- Remove the Application and Process services in Configuration Console.
- Restore Management Reporter database on the new SQL Server.
- Run script provided by Microsoft against the database on the new server.
- Install Application and Process services in Configuration Console.
- Install Data Mart integration in Configuration Console.
The reports appeared to be working, but we were receiving errors in the Service and Deployment logs.
‘Integration source ‘NEWSQLSERVER’ with discriminator :DYNAMICS not found.’
‘An unrecognized exception was thrown [Microsoft.Dynamics.Performance.Reporting.DataAccess.Server::0x80131509].’ Which listed the service account running the Management Reporter services.
To fix the issue with the database not found, we queried the ControlIntegrationSource table.
The issue was the SQL server name in that table was still listed as the old server name. To update, we ran the following query against the migrated Management Reporter database.
SET Name = ‘NEWSQLSERVER’