Wednesday, March 16, 2016

Configuring Skype for Business CDR and QoE Reporting services to be highly available

Frequently I see customers successfully implement CDR and QoE reporting on two SQL reporting servers, but find that their reporting services web URL only works on the SQL server that is the primary node for these databases.   When you attempt a connection to the SQL Reporting server that the databases are not mounted on, you receive an error similar to the below:

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

In order to make Reporting services truly highly available, you need to complete the steps below from this Microsoft TechNet article Associating Monitoring Reports with a mirror database in Skype for Business Server 2015.

"To get Monitoring Reports to automatically failover to the mirror database, you must add the mirror database as a "failover partner" to the two databases that are used by Monitoring Reports (one database for Call Detail Record data, and the other for Quality of Experience (QoE) data). (Note that this step should be performed after you have installed Monitoring Reports.) You can add the failover partner information by manually editing the connection string values used by these two databases."

So on each http://SQLreportingServer01/Reports URL, click into the Reporting Instance:

Click into Reports_Content:

Click into CDRDB:

In the "Connection String" dialog you will see something similar to:
data source=(local);initial catalog=LcsCDR

Edit this content to add the following (in bold)
data source=(local);Failover Partner=SQLREportNode02;initial catalog=LcsCDR

If you had a configuration where you specified an instance name, this would look more like this:
data source=(local\CDRReporting);Failover Partner=SQLREportNode02\CDRReporting;initial catalog=LcsCDR

Note:  If you have security set to use credential stored securely, you will need to retype the password to make this change!

Once you complete these tasks for the CDRDB, repeat these steps on the QMSDB.

Once you complete these tasks on both CDRDB and QMSDB, repeat the process on your second SQL reporting node.

Once these tasks are completed, both of your reporting URLs should work regardless of where your S4B backend SQL CDR/QoE databases are mounted.