INTRODUCTION
I have just configured SQL Server Reporting “cluster” or in SQL Server term: “Scale Out Deployment”. What this allows me to do is to achieve High Availability (HA) for the reports ie. when one report server is down, the other node can serve the reports.
I then install MS CRM to use this clustered report service. For this example I have 2 SQL servers namely SQL01 and SQL02 – and each has reporting service (SSRS) installed – and the cluster node name is SQLCL. Therefore, my report server cluster URL is http://sqlcl/reportserver.
Installing CRM to use this cluster is easy; simply specify the URL above as the report URL in the CRM installation wizard.
What’s a bit tricky is when it comes to installing the MS CRM Reporting Extension component. I thought it would be as easy as running the wizard twice, once on each of the SQL node. When I run it on SQL01 or SQL02 I got the following warning message:
Reports will not be published for some organizations because the Report Server instances used by these organizations differ from the instance selected during Microsoft Dynamics CRM Reporting Extensions Setup
What that means is, no reports will be deployed in CRM. If you go to CRM > Reports, none of the reports will appear in there.
RESOLUTION
Therefore, to resolve it please follow the following steps:
1. Go to SQL01 and install MS CRM Reporting Extension. It will give you the warning above but will not prohibit you from progressing with the install.
2. Go to SQL02 and install MS CRM Reporting Extension. It will give you the warning above but will not prohibit you from progressing with the install.
3. Go to CRM server > Deployment Manager then Disable the organisation.
4. Select the disabled organization then select Edit Organisation.
5. Update the Report Server URL with the report server URL of the first node (eg. http://sql01/reportserver). Click Next and let CRM updates the organisation.
6. Go to SQL Management Studio then open MSCRM_CONFIG table. Make sure AreReportsPublished is False!
7. Then go to SQL01 and uninstall MS CRM Reporting Extension.
8. Then still at SQL01 and reinstall MS CRM Reporting Extension. On the confirmation screen you will no longer get the warning message. Proceed until completion.
9. Repeat 4-9 but this time use SQL02.
10. Finally back at the CRM > Deployment Manager, Edit Organisation again and update the Report Server URL back to the cluster URL http://sqlcl/reportserver.
11. Enable the organisation and that’s it!
This resolution is applicable for both MS CRM 2011 and 2013.
Hope this helps,
Tommy
