OpsMgr 2012 App Advisor Web Console Failed To Run Reports After DW Database Move

Previously I’ve blogged my experience in migrating from OpsMgr 2012 RTM to SP1 which involved moving all databases to a new database server running SQL 2012. Since then, I’ve noticed the instruction in moving Operational DB published in TechNet is actually missing a step, which I’ve blogged here.

Over the last few days, since it was the long weekend down here in Victoria, I spent some time setting up the Talking Heads web application to explorer APM in OpsMgr 2012. I was following the instructions in Chapter 9 in the Mastering System Center 2012 Operations Manager book. The instructions in this chapter is exactly the same (almost word to word) as the 3-part series in Kevin Greene’s blog (Part 1, Part 2, Part 3). – Looks like we know who’s the author for this chapter. - Thanks Kevin

Anyways, I’ve learnt a lot from this chapter as I’ve never dealt with Avicode in 2007 or APM in 2012. While I was exploring the App Diagnostic and App Advisor consoles, I’ve noticed my App Advisor console won’t run any reports! No matter which reports I tried to run, I always get this error:

and as suggested, I got this warning event (EventID 1309) from Windows Application log:

In the exception stack, it shows I cannot connect to the SQL server:

Exception message: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source ‘AppMonitoringSource’. —> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

After spending time trying to fix this issue, I created a DNS Alias record with my old OpsMgr SQL server name pointing to the new SQL server, and it fixed the problem! By doing this, I knew there is one place that I haven’t updated the SQL server name yet. I went through the Technet instructions for moving Operational DB, DW DB and Reporting Server again, I couldn’t find which step was I missing.

Today, I had a second look in the SSRS instance for OpsMgr reporting, I noticed there is an additional data source called AppMonitoringSource under Application Monitoring –>.Net Monitoring. the connection string for this data source was still pointing to the old SQL server:

According to the Technet guide “How to Move the Data Warehouse Database”, as per step 8, only the Data Warehouse Main data source needs to be updated (and of course, if we’ve manually created another data source for operational DB, which is very common and lots of community report MPs requires this datasource, we’d also update it too.). The AppMonitoringSource was not mentioned in the instructions – which I found interesting because step 10 actually covered changing the DB server name in dbo. MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring, table, we can tell by name is used for APM.

As result, after I’ve changed the connection string in this data source, deleted the DNS alias and flushed DNS cache on my web console server, the problem is now fixed.

So, not sure if the AppMonitoringSource was overlooked by the author of the Technet guide. For anyone who has moved the DW DB in OpsMgr 2012, it’s probably worth checking this data source (by going to http://<reporting server>/reports). I only found out now only because I’ve started using APM in my environment.