In an IaaS deployment with Hyper-V, System Center 2012 R2 VMM, Operations Manager, SPF & Windows Azure Pack the most difficult and annoying part is to setup and configure Service Reporting for the chargeback. It is kind of exhausting getting Service Reporting up and running. But keep calm and continue reading.
First let us assume you have installed and configured Service Reporting the right way. The SQL Services for Service Reporting are set up with one or several domain accounts.
- SQL Server Integration Services
- SQL Server Analysis Services
- SQL Server
- SQL Server Agent
Then you have configured the WAP Usage REST API on the WAP servers successfully through PowerShell for the connection with Service Reporting.
Import-Module MgmtSvcAdmin
Set-MgmtSvcSetting –NameSpace UsageService –Name Username –Value ‘ServiceReporting’
Set-MgmtSvcSetting –NameSpace UsageService –Name Password –Value ‘Password1’ –Encode
You also have modified the sections OperationsManagerSQLServers, OperationsManagerDBs and WAPUsageServiceURI in the MaintenanceConfig.xml under C:Program FilesMicrosoft System Center 2012 R2Service ReportingMaintenance. Before you executed the PostDeploymentConfig.ps1 script, you had ensured that the Service Reporting SQL Server Agent service account has read permissions on the Operations Manager database.
.PostDeploymentConfig.ps1 –Username ServiceReporting –Password Password1
The next step after the deployment and configuration of Service Reporting is to test the installation if you get data out of the WAP database and into Service Reporting. You open the SQL management studio, connect to the Service Reporting SQL instance and run the Service Reporting DW System Job. Most of the time you will see that the job is failing at the second step and now you have to troubleshoot your Service Reporting installation.
But where do you start? Have a look first into the ETLProcess_Extract.log under C:Program FilesMicrosoft System Center 2012 R2Service ReportingSSISPackages. There you will get the necessary information to solve your issues with the Service Reporting DW System Job. But not all issues during the runtime are tracked in the ETLProcess_Extract.log. So I will give you the information you have to consider.
First make sure that your Service Reporting SQL Server Agent service account really has read permissions on the Operations Manager database and not on the Operations Manager data warehouse database. That point confuses the people, because the SPF service account must have read permissions on the Operations Manager data warehouse database. Be aware of this!
For the next one connect to the Service Reporting SQL Server Analysis Services instance and check if both the Service Reporting SQL Server service account and the Service Reporting SQL Server Agent service account are members of the SR_Administrator role.
Finally check the permissions for the Service Reporting SQL Server Agent service account on the folders WorkDir and SSISPackages under C:Program FilesMicrosoft System Center 2012 R2Service Reporting. On both folders the service account needs the permission full control.
To confirm that you get data out of the WAP database and into Service Reporting run the Service Reporting DW System Job again. Now you will see that the second step completes successfully.
For more troubleshooting information regarding the WAP usage metering & reporting process check out my session from SCU Europe this year.