Deployment Details for Lync Monitoring Reports

NextHop_Team · ‎May 20 2019

First published on TECHNET on Jun 22, 2011

It can be difficult to work with Microsoft Lync Server 2010 Monitoring Reports and SQL Server Reporting Services. In this post we describe how to avoid some possible issues in a distributed scenario where the SQL Server cluster (database engine) is on dedicated servers and the SQL Server Reporting Services and Lync Server 2010 Monitoring Server are on another server.

Author : Peter Seidel

Publication date : June 2011

Product version : Lync Server 2010

Introduction

Installing Microsoft Lync Server 2010 Monitoring Server Reports can be a simple task if the Microsoft SQL Server deployment is not complex. However, an enterprise installation likely has the SQL Server resources hosted on multiple servers, which can make the installation more complicated. In a scenario where the SQL Server database engine and the SQL Server Reporting Services are running on different servers, the deployment does not work as smoothly as when they running on a single server. This article can help you avoid some of the issues you might encounter with this type of installation. The following are some pain points:

If you have to enter the SQL Server database engine instance or the Reporting services instance, take care to enter them correctly

You may encounter authentication issues such as Kerberos delegation issues

The high-level points covered in this article are the following:

Getting Started

Publishing the Topology for the Monitoring Server

Deploying SQL Server Reporting Services

Deploying Monitoring Reports

Solving Kerberos Authentication Issues

Getting Started

This section covers some general information and recommendations about the Monitoring Server and the Monitoring Server Reports.

Why do I need that Lync Server 2010 server role?

The Monitoring Server collects data from the call detail recording (CDR) and Quality of Experience (QoE) databases and presents that data with the help of the SQL Server Reporting Services and the predefined Monitoring reports.

Which components are needed for this server role to work?

The following components are necessary:

Data Collection Agents (installed on Front End Server)

CDR and QoE databases (running on the SQL Server database engine instance)

Message Queuing (installed on each Front End Server and Monitoring Server)

The Monitoring Server reports are optional, but we recommend them because they help you to view the collected data. The following components are also necessary:

SQL Server Reporting Services (own SQL Server instance hosted on the Monitoring Server or SQL Server database engine server)

Reports database (hosted on the SQL Server database engine instance)

General information

It is not necessarily an advantage to split the SQL Server database engine from SQL Server Reporting Services, but typically enterprises are separating data from additional services or applications. That simplifies the operational tasks per server.

SQL Server Reporting Services are not cluster-aware; therefore if you are hosting the SQL Server database engine on a cluster it is a best practice to separate Reporting Services.

In this article we are assuming that Microsoft SQL Server 2008 with latest service pack is used to provide SQL Server database engine and SQL Server Reporting Services.

Note : The use of SQL Server 2008 makes the installation of Internet Information Services (IIS) 7.0 obsolete because SQL Server Reporting Services 2008 is able to publish websites for Reports and Report Manager on its own.

This article focuses on the Lync Server Monitoring Server Reports installation and configuration. For details and background information about Lync Monitoring and SQL Server Reporting Services, see the Lync Server 2010 documentation and SQL Server Reporting Services on the Microsoft TechNet Library website.

Prerequisite

You need to use an account that is a member of the RTCUniversalServerAdmins group in order to complete the installation and configuration steps described in this article.

Publishing the Topology for the Monitoring Server

In discussing Monitoring Server Reports, it is important to highlight publishing the topology for the installation of the Lync Server Monitoring Server.

In Topology Builder, the Monitoring Server needs to be added and associated to one or more Standard Edition or Front End pools. Also, you must specify in which SQL Server instance the new databases (the CDR and QoE databases) should be hosted. It is also possible to add a further SQL Server instance for hosting these databases. A reason for separating the Lync Server Monitoring databases from the pool databases might be that you want to separate usage data (which is collected from the Monitoring Server role) from the other databases, which are necessary for providing Lync services such as configuration data or user specific information.

If you are using Standard Edition servers only for hosting Microsoft Lync 2010 data, then you need a new SQL Server instance for the Monitoring Server databases.

Important : The creation of the new databases can fail if a new SQL Server instance has been used. The reason for this failure is most likely that the routine does not find the right database paths on the server running SQL Server. The probability of such a failure increases if you use a SQL Server cluster.

Figure 1. Example of errors that might occur during database creation

To work around this issue, use the Windows PowerShell cmdlet Install-CsDatabase ( Install-CsDatabase -ConfiguredDatabases <SwitchParameter> -SqlServerFqdn <Fqdn> [-Clean <SwitchParameter>] [-Confirm [<SwitchParameter>]] [-DatabasePaths <String[]>] [-ExcludeCollocatedStores <SwitchParameter>] [-Force <SwitchParameter>] [-ForDefaultInstance <SwitchParameter>] [-ForInstance <String>] [-GlobalCatalog <Fqdn>] [-GlobalSettingsDomainController <Fqdn>] [-Report <String>] [-SkipPrepareCheck <SwitchParameter>] [-Update <SwitchParameter>] [-UseDefaultSqlPaths <SwitchParameter>] [-WhatIf [<SwitchParameter>]]. )

During my deployment it was sufficient to use the cmdlet with the switch ConfiguredDatabases. It was not necessary to use DatabasePaths.

However if your deployment is different from mine, I recommend using the DatabasePaths switch. When Install-CsDatabase is run with the DatabasePath parameter, the built-in algorithm is not used to choose the storage location for the database logs and data files. Instead, administrators can select the location for these logs and data files. To install both data files and SQL Server logs in the same location, specify the path to the folder where this data should be stored. For example:

-DatabasePath C:\SqlData

To store data files in one location and log files in a second location, specify the path to each folder, separating the two locations by using a comma (make sure that you don’t put a blank space before or after the comma):

-DatabasePath C:\SqlLogs,D:\SqlData

The log files will be stored on the first location specified, while data files will be stored in the second location.

Note : In order to run the Install-CsDatabase command locally, you must be a member of the domain, a member of the RTCUniversalReadOnlyAdmins group, a SQL Server administrator, and a local administrator on the computer where the SQL Server database engine is installed. Members of the RTCUniversalServerAdmins group are automatically granted the required SQL Server administrator permissions.

To get the role-based access control (RBAC) list (including any custom RBAC roles you have created yourself), run the following command from the Windows PowerShell prompt:

Get-CsAdminRole | Where-Object {$_.Cmdlets -match "Install-CsDatabase"}

For more details about installing Monitoring Server, see Deploying Monitoring in the Lync Server 2010 documentation.

Deploying SQL Server Reporting Services

The deployment of SQL Reporting Services is distributed in two phases:

Installation . The installation of the SQL Server Reporting Services is fairly simple. A wizard guides you through the installation.

Configuration . The configuration of the SQL Server Reporting Services is more difficult. The information needed to successfully configure it is covered in this article.

Installation

Note : Before you begin installing SQL Server Reporting Services, make sure you are running SQL Server 2008 with latest service pack. You can also use a slipstreamed version of SQL Server to avoid running the setup routine several times. For details, see Microsoft Knowledge Base article 955392, “ How to Update or Slipstream an Installation of SQL Server 2008 ” on the Microsoft Support website.

Not much information needs to be entered to install SQL Server Reporting Services on the same server as the Monitoring Server. Because SQL Server Reporting Services is the only SQL Server instance you need to install on the Monitoring Server, use the default instance. The SQL Server Reporting Services is its own SQL Server instance on the Monitoring Server. It coexists with the SQL Server express installation necessary for the Monitoring Server role.

Figure 2. Use the default SQL Server instance for the Monitoring Server

The installation routine will also show you the SQL Server Express instance needed for the local copy of the Lync configuration data.

Next, enter the account that is used for running the SQL Server Reporting Services. We recommend that you use a domain account.

Figure 3. Enter a domain account for running SQL Server Reporting Services

You don’t configure SQL Server Reporting Services until after you complete the installation. Therefore we recommend that you select the Install, but do not configure the report server option.

Figure 4. Choose to install without configuring

Important: We recommend that Windows Firewall is turned on for every server. To get the correct firewall rules with the exception for SQL Server, it is important that Windows Firewall is turned on in advance of the installation. For details about configuring Windows Firewall for SQL Server access, see the links provided in the “Additional Information” section later in this article.

Figure 5. If Windows Firewall is enabled on the local computer, the installation routine sets exceptions for the communication of SQL Server automatically

Configuration

After you finish the installation, start the Configuration Manager to begin configuring SQL Server Reporting Services. Enter the information for the instance of SQL Server Reporting Services (not which SQL Server database engine instance) that you want to connect to.

Figure 6. Enter the SQL Server Reporting Services instance you want to connect to

After connecting to the right instance, the section service account is already prefilled with the service account information. No further action is needed here, if you do not want to change the account again.

The next step is to create the virtual directory for the Web Service (the default is ReportServer). This service, and also the Report Manager, is provided directly from the SQL Server Reporting Services. No Web Server role needs to be installed on the computer.

Figure 7. Create the virtual directory

Important : If the Web Server role is also installed on the Monitoring Server for providing different services, keep in mind that IIS will also use port 80 (HTTP) and 443 (HTTPS). So either SQL Server Reporting Services or IIS needs to be configured with new ports.

After the Web Server role is installed, a database for storing the reports-related data needs to be created or chosen. If this is the first time you have run through this step, you probably need to create the database. The database is dedicated to reports, rather than one that already exists. So, for instance, don’t choose the QoE or CDR database.

Figure 8. Set up a database that is dedicated to reports

Next, generate the Report Manager URL. Again a Web Server certificate is needed to use HTTPS instead of HTTP.

Figure 9. Generate the Report Manager URL

You are now done with all the necessary configuration steps of the Reporting Services.

Deploying Monitoring Reports

The next task is to install the Lync Server 2010 Monitoring Server Reports. To start this task, open the Lync Server 2010 Deployment Wizard again, and then click Deploy Monitoring Server Reports to start the Deploy Monitoring Server Reports Wizard.

Figure 10. Start the Deploy Monitoring Server Reports Wizard

Next, you’ll need to enter the name of the Monitoring Server and the SQL Server Reporting Services instance.

Warning: The information is already prefilled. In my tests, the Reporting Services instance was wrongly prefilled with the SQL Server database engine instance for Monitoring Server, which is different in the described scenario from the SQL Server instance for Reporting Services. (This is because SQL Server databases are on a remote server or cluster, whereas Reporting Services are on the same server as the Monitoring Server.)

Figure 11. The SQL Server Reporting Services instance is pre-filled incorrectly, and the corrected version

The next step is to enter information about user accounts and groups. First enter the account you want to use for Reporting Services to access the Lync Monitoring database. That is typically the same account as the account that is used for running Reporting Services. Next, enter the group used to get read access to the reports.

Figure 12. Enter the account you want to use to access the Monitoring database and the group to get read access to the reports

At the end of the wizard, you’ll see a summary page, and the reports are installed.

Solving Kerberos Authentication Issues

At this point you may think you are done. And that’s true, at least with regard to the installation. However, it is likely that you have run into Kerberos authentication issues. These are exposed if you try to access the URLs (HTTP or HTTPS, as you have configured them) that you created when you configured SQL Server Reporting Services. Typically, what happens is that you cannot access these URLs.

However, if you try to access these websites through their IP addresses, then a credentials dialog box appears and the connection can be established. In that case instead of Kerberos (default in Active Directory domains), NTLM is used. The reason for this issue is that the Reporting Services account is used instead of the computer account.

To resolve this issue, you need to register the Service Principal Name (SPN) for HTTP with the Reporting Services Account:

Setspn – A http/<serverFQDN> domain\ReportingServicesAccount

Figure 13. Registering the SPN for HTTP with the Reporting Services Account

After registration of the SPN, a second step is required.

Open the properties of the user account, and then navigate to the Delegation tab. Change the setting from Do not trust this user for delegation to Trust this user for delegation to any service (Kerberos) . After you have completed this step, it is possible to access the configured websites through the URLs as well as through their IP addresses.

Figure 14. Successfully accessing the configured website through the URL

Summary

I hope that the information in this article about installing and configuring SQL Server Reporting Services, in addition to the Deployment information about the Monitoring reports, is helpful for you. When you use Monitoring reports, you’ll see that it is worth it to do the work.

Figure 15. Monitoring Server Reports