Issue Deploying Lync Server 2013 Monitoring Reports to SQL Mirror

Update 6/23/13 - Added information about modifying the Monitoring Reports Data Sources connection string.

I was playing around with the Lync Server 2013 Monitoring Reports and decided to deploy it to both of my SQL Servers in my lab.  I have a SQL mirror setup and I installed Reporting Services on both servers.  My initial thought was to use a hardware load balancer to distribute the traffic to both Reporting Services Servers.  This would also give me high availability within the site for the Monitoring Reports.  However, when I went to deploy the Monitoring Reports to the server acting as the SQL mirror, I received the following error:

Cannot grant ReportsReadOnlyRole to user "LAB\srvLyncReports". For details, see the following error message:
Exception calling "Create" with "0" argument(s): "Create failed for User 'LAB\srvLyncReports'."

From the error message it looks like the Deploy Monitoring Reports wizard can't assign rights to the service account in SQL.  Opening up the SQL instance in SQL Server Management Studio on the SQL mirror you can see that all of the databases are listed and mirroring is working correctly:

However when I go into the properties for the service account and click on the User Mapping page, I received the following message:

One or more databases are inaccessible and will not be displayed in list.

When I tried the same thing on the primary SQL Server, all of the databases are listed correctly and you can see that the service account is assigned ReportsReadOnlyRole:

It appears that you can only assign permissions to databases on the primary SQL Server in the mirror and the Deploy Monitoring Reports wizard won't continue unless it can access the databases, even though the permissions are already set on the primary.  At this point the Monitoring Reports are only deployed to the primary SQL Server.  They don't get replicated to the mirror, since Reporting Services doesn't participate in the SQL mirroring that is configured.  Looking at the SQL Server Reporting Services (SSRS) website on the SQL mirror, you can see that this is the case:

So in order to publish the Monitoring Reports to the SQL mirror you will need to fail the databases (LcsCDR and QoEMetrics) over to the SQL mirror.  You can accomplish this by running the Invoke-CsDatabaseFailover cmdlet:

This will make just the Monitoring Server databases active on the SQL mirror.  You can confirm this in SQL Server Management Studio:

With the Monitoring Server databases active on the SQL mirror, you can now run the Deploy Monitoring Reports wizard and deploy the reports:

You can confirm the reports were published by going to the SSRS website on the SQL mirror:

Once you have successfully deployed the Monitoring Reports to the SQL mirror you can fail the Monitoring Server databases back to the primary SQL Server.  Now that both SQL Servers have the Monitoring Reports deployed, the Lync Server 2013 Control Panel will display both servers under the View Monitoring reports section:

However, if you try to view the Monitoring Reports from the SQL Server that isn't currently the primary for the Monitoring databases, you will receive the following error message:

  • An error has occurred during report processing.
    (rsProcessingAborted)
    • Cannot create a connection to data source 'CDRDB'.
      (rsErrorOpeningConnection)
      • For more information about this error navigate to
        the report server on the local server machine, or enable remote errors

This is because connection string data sources is pointing to the local SQL Server:

Connection string: data source=(local);initial catalog=LcsCDR

In order to make the reports more resilient, you need to edit the connection string and add the SQL mirror.  The connection string should look like:

data source=(local);Failover Partner=LAB-SQL2;initial catalog=LcsCDR

You will need to do this for both Data Sources (CDRDB and QMSDB) on both the primary SQL Server and on the SQL mirror.  This way, no matter which server hosts the Monitoring databases and which SSRS website you view the reports from, it will just work!  Now I could load balance the SSRS website to both SQL Servers if I wanted.

In the example above, I chose to install SSRS on both the primary SQL Server and on the SQL mirror.  If you were just using a single SSRS website, then you would just need to do the last steps to modify the connection string on the Data Sources.  That process is documented in the Associating Monitoring Reports with a Mirror Database TechNet article.