Share via

Issue Deploying Lync Server 2013 Monitoring Reports to SQL Mirror

  • Article

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.

Comments

  • Anonymous
    January 01, 2003
    @PeterSteen

    You care correct that the Monitoring Server reports are published to both the primary and mirror using the same SQL logon account.

  • Anonymous
    January 01, 2003
    @John Miller, Enabling Technologies Thanks for the feedback!  As far as mirroring the Reporting Services database, that would be based on the whether or not SQL supports mirroring of the Reporting Services database.  You are correct that when Lync sets up mirroring it doesn't do anything with the Reporting Services database.

  • Anonymous
    August 06, 2013
    Dear Sir please help me

  • Anonymous
    October 11, 2013
    Great article Doug, this will help us out a bunch! You talk about having SSRS on the instances on the Primary and Mirror.  What about mirroring the database for the ReportServer itself? I'm assuming that setting up Lync mirroring will not include setting up the mirror for this database.  I'm guessing the SQL admin would have to mirror this manually on the SQL servers? Thanks, --John

  • Anonymous
    November 11, 2013
    Great article! Just did this with my current deployment and it works like a charm! Thank you! :)

  • Anonymous
    June 26, 2014
    Very good article. i do miss 1 perequisites though.... I'd gues your are using the same account for running the reporting services on both SQL servers, Primary and mirror, right?

  • Anonymous
    July 20, 2014
    Hi Dodeitte,
    I am not an SQL guy, I am not able to get the option to edit the connection string. I tried edit with notepad and still I am not able to get this updated or saved. In My case I need to update the string with the cluster server name and instance. Please help, very urgent. Thanks in advance.

  • Anonymous
    September 10, 2014
    Hey Doug, having a weird error with one of my clients. When we put Failover Partner=<> in the connection string and hit Apply it's not being saved. Do you have any ideas of why this might be happening?

    Thanks,

  • Anonymous
    October 07, 2016
    If Monitoring Reports Server is installed only on the Mirror (Single SSRS), and if Monitoring databases are active on Primary instead of the Mirror, I am still getting the error "Cannot create a connection to data source 'CDRDB'. (rsErrorOpeningConnection)". The connection string on SSRS is "data source=(local);Failover Partner=LabPrimarySQL1\Archmon;initial catalog=LcsCDR"