Call Quality Dashboard – Part 1: The QoE Archive Database

Overview of Call Quality Dashboard (CQD)

The QoE database is replicated to another SQL database (named the ‘QoeEArchive’) and then is manipulated throw a user web portal using a SQL Analysis Service (CUBE). The CQD is composed of 3 components: The QoE Archive DB, The Cube and the Portal.

You can read more information on the TechNet article: ‘Plan for Call Quality Dashboard for Skype for Business Server 2015’.
These article also components can be installed in one single server, or distributed up to 3 (I say that can go to 4) servers.

Inspired on this I decided to split the subject in three posts: how to install, and also how each element works. Besides it’s easier to read, it allows you to understand how to deploy on a multiserver or single server.

Installing CQD – QoE Archiving Database

As seen on the above picture, the QoE Archive is a database with some procedures that replicates the data from a Lync/Skype4B ‘QoEmetrics’ database.
What do you need as pre-requisites to install this:

  • A SQL database service (recommended a dedicated one)
    You need the Enterprise or Business Intelligence edition if you to use ‘multiple partitions’ which allow better CUBE processing performance for large amounts of data
  • The SQL agent service must be running (automatic startup) on that SQL server. An agent job will be running periodically to replicate data. If
  • An account with db_datareader role/permissions on the QoEmetrics database
    CQD-QoE-DBuser
    This account will also be granted db_owner on the QoEArchive and it will be impersonated (proxy) to connect to the QoEmetrics.
  • You must run the install package on the SQL server where you want to install the Archive database. The setup reads this info from the local system and doesn’t allow you to change ( using the GUI 😉 )

After downloading the CQD package, the setup process is the following:

  1. Proceed throw the welcome screen, and choose the binaries install location
  2. For this part I will just select the QoE Archive (deselect the others)

    Configurations options:
    sqlname-vs-instance• QoEMetrics SQL Server: SQL Server and instance name where the QoE Metrics database is located.
    • QoE Archive SQL Server Instance:the A local SQL Server instance name for where the Archive DB is to be created. Leave this field blank for a default SQL setup.
    • QoE Archive Database: create a new or use an existing one (useful for recovery/migration/connect new source scenarios -it will rebuild the ACL’s, connectors and jobs-)
    • Database File Directory: location where the new database files are to be created. Recommended a separate disk volume.
    • Use Multiple Partitions: ‘Multiple partition’ requires Business Intelligence edition or Enterprise edition of SQL Server. ‘Single Partition’ only requires for a Standard edition, but cube processing performance may be impacted.
    • Partition File Directory: (if using ‘Multiple partition’) Path to where the partitions for the QoE Archive database should be placed.
    • SQL Agent Job User – User Name & Password: Domain service account used to connect to the QoEmetrics database and replicate on the QoEArchive

  3. After the databases, instances and account access validation the installation will ask to proceed until completion, hopefully with any error 🙂
    CQD-setup-Ready_CQD-setup-ArchiveCompleted

Behind the CQD QoE Archive Database

What happened and was configured after the previous installation steps?
This component setup didn’t installed any specific binaries. The installation was in fact a series of configurations on the SQL server used for the CQD Archive database:

  • QoEArchive database was created
  • ‘SQL Agent Job User’ login created and assigned db_owner of the QoEArchive
  • a credential created with the ‘SQL Agent Job User’. This will be used to impersonate the connection to the QoEMetrics on the source SQL server
  • A linked server source, mapping all the databases on the source SQL server
  • A SQL Agent Job and proxy. This is the ‘heart’ that will sincronize the QoEMetrics and the QoEArchive

Known ‘caveats’ regarding the installation and architecture process:

  • Both database and transaction log files are going to be installed on the same folder. You can only change this after using SQL tools and procedures.
  • Not 100% sure about this (need to investigate this one), but I couldn’t find documented support for a QoEMetrics mirrored database.
    If the database fails to the other node the synchronization process fails.
  • Don’t use a domain user account password starting with ‘+’. The setup SQL procedure will ignore it and then you will get the following on the SQL job and the data will not get replicated:
    “Unable to start execution of step 1 (reason: Error authenticating proxy LAB\service.CQD, system error: The user name or password is incorrect.).  The step failed.”
    You can solve this by manually setting the correct password on the ‘QoEArchiveCredential’

How to manage and monitor the CQD QoE Archive process ?

As I told before the QoE Archive is a data synchronization process between the Lync/Skype4B QoEmetrics database and the QoEArchive.
This is done using a SQL agent job that runs, by default, every 15 minutes:
CQD-archive-agentjob

This ‘simple’ job triggers a series of store procedures and will sync the databases tables.
You can see the sync jobs status and errors on a particular table. If you open the tables on the QoEMetrics and QoEArchive, you will confirm that (the second one will have some more tables that are used to control the sync process:

I used the word ‘DB synchronize/replication’ to simplify the idea. In fact, it does what the name means: ‘collects data and add to the existing archive’. “CQD’s QoE Archive database provides a second copy of the QoE Metrics data with much longer retention capabilities”.

If you have multiple Skype4B pools, each with its own Monitoring Server, “CQD does not merge data from multiple QoEMetrics databases!”. “Each CQD instance must point to one QoEMetrics database!”.(*)
“However, because CQD will move much of the reporting workload off of the Monitoring Server, large organizations that deployed one Monitoring Server per Skype4B Pool topology should consider using one Monitoring Server for all topologies”.
But this can compromise using the Monitoring Reports tool to analyse (older) data in a different way and doesn’t handle the other bigger and heavier monitoring database: the LcsCDR. – This is an open topic for a future blog 🙂

You can monitor the replication process, not just by the Agent Job logs, but there is also a table that contains the history. For example the Agent job will report an error if there is no new data to replicate from and you can only see that here:
CQD-archive-logs.png

Now you have a replica of your QoE data to analyse with the tools described on part 2.

Wait ! eastern egg!

For those who manage to read until here without falling asleep, here’s a ‘gooddie’.
Here’s how can automate the previous setup from the command line (you just need to replace the orange text with your settings):

Msiexec /i CallQualityDashboard.msi ADDLOCAL=QoEArchive REBOOT=ReallySuppress CQD_INSTALLDIR=”D:\Skype4B\CQD” QOE_METRICS_SQL_SERVER=”LYNC-BE.my.lab\INST1” ARCHIVE_SQL_SERVER=”LYNC-CQD.my.lab\CUBE” INSTALL_NEW_ARCHIVE=True ARCHIVE_FILE_DIRECTORY=”E:\Databases\CQD” DISABLE_ARCHIVE_MULTIPLE_PARTITION=True ARCHIVE_SQL_AGENT_USER=”mydomain\cqdserviceaccount” ARCHIVE_SQL_AGENT_PASSWORD=”itsAsecret/qb!

The interesting part is that (for SQL standard/single partition deployments) you can run this setup command from another server that is not the CQD SQL database one (as long as you have the SQL client tools installed on the one you run).

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s