Tickertape

Tickertape is a custom Reporting application for CCE.

A Tickertape report consists of rows with each row showing Calltype and Skill group Data for a 'collection' or grouping of specific CCE Calltypes and Skill groups / Precision Queues.

CCE does have a grouping of Skill groups called Enterprise Skill Groups (ESG) - but ESGs are a deprecated feature and cannot be used on PCCE. CCE does not have any built in grouping capability for Call types.

Tickertape Configuration GUI is built on the PHP framework laravel.

The Tickertape database resides on the CCE AW database, as it contains SQL Views of the specific CCE ucce_awdb tables. How to install a resilient tickertape config.

• SQL should be installed with Replication Enabled (which is included in the CCE install guide)
• Restore tickertape database to A side Server
• Truncate the tables - to give you a clean empty config

TRUNCATE TABLE reports
TRUNCATE TABLE report_collections
TRUNCATE TABLE linecollections
TRUNCATE TABLE linecollection_report
TRUNCATE TABLE linecollection_calltype
TRUNCATE TABLE linecollection_skillgroup
TRUNCATE TABLE activity_log

• Create a SQL user account tickertapeuser (default database master) - do not force password expiry or require user to change at next logon
  • Enable the following mappings for this account
    • owner of the tickertape database
    • Read access for awdb
  • Note - because this database was a backup of an existing database - you likely need to delete any 'orphaned' sql accounts associated with the database (such as tickertapeuser - before assigning the newly created user), e.g. Tickertape→Security→Users
  • Assign the tickertape user and configure the Correct Database in the Laravel config file - as per below example

DB_CONNECTION=sqlsrv
DB_HOST=ucce-hds-2a
DB_PORT=1433
DB_DATABASE=tickertape
DB_USERNAME=tickertapeuser
DB_PASSWORD=myPa$$w0rd!

The Tickertape database has a number of many to many tables, e.g. linking the Calltypes to a specific Collection etc. Because these tables do not contain a primary key - is it not possible to use transactional replication.

However we can use snapshot replication - e.g. every x minutes a snapshot can be taken and pushed to a subscriber database. This is what we will do here. Note - this resilience is for reports (end users). If the Primary server is down, no Tickertape config changes can be made. i.e. All config changes are made via the Laravel GUI to Primary server only - and replicated to the B side server.

Reference: tutorial-preparing-the-server-for-replication

• Backup the tickertape database on the 'A' side (Primary server) and restore to the 'B' side.
• Create the following local windows accounts on the A side Server
  • repl_snapshot
  • repl_distribution
• Create the following local windows accounts on the B side Server
  • repl_distribution
• Make sure that the SQL Agent Service is running - and running under the account NT SERVICE\SQLSERVERAGENT (default setup)
• Make sure that the local security user rights assignment policy of Replace a process level token contains both the SQL and SQL Agent accounts - as per below:

Overview

• Install / Enable Distribution Database
  • Enables the Distribution System Database
  • Assign repl_snapshot and repl_distribution their correct Explorer Folder permissions & SQL Mappings
• Enable Tickertape Publisher
  • Create snapshots every X hours
  • Check the SQL job and confirm it ran without error
• Enable Tickertape Subscriber
  • Pushes snapshot config to subscriber database
  • Check the SQL job and confirm it runs without error

NOTE: Since the database replicates only when the Snapshot Job runs - you can run the snapshot job to do an immediate copy. The Snapshot job runs and then complets in a few seconds. The distribution job is running continuously - but does not do anything until the snapshot is updated.

These steps create a distribution database (contained within 'system databases') Note - because are pushing the config from A side to the B side, we do not need to create a network share. We do need to assign privileges to our local windows accounts.

As per step 3 above the snapshot database is stored at the following location

C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\ReplData

Configuring the above folder to have the full control rights for the user repl_snapshot Configuring the above folder to have the read and execute, List Folder Contents and Read rights for the user repl_distribution

e.g. as per below:

Add the local windows user accounts repl_snapshot and repl_distribution to the A and B side SQL Databases. e.g. serverA\repl_distibution on the A side and serverB\repl_distibution on the B side. note the repl_snapshot account should only exist on the A side server.
Assign the owner roles for the:

• tickertape database (on A and B side Database)
• distribution database (on the A side Database)

After this runs - check the SQL Job (which has just been created (press F5 to refresh sql job) and confirm it runs without error as per below:

The first version of the reports uses the same approach as the legacy Tickertapes reports. i.e. ASP. This is a known working solution, but likely can be improved on.

To install capability for these classic ASP application, enable the IIS Application options

• ASP
• ASP 3.5
• Setup the IIS Application to use the .NET 2.0 Classic Application Pool - as per below.

Authentication for the ticker database uses integrated authentication as per the web.config file below: Note - the Server is also detailed here - so on the “A Side” asp folder we detail in the web.config the A side Server - e.g. UCCE-HDS-2A and database (i.e Catalog) 'tickertape' etc.

Summary

1. Set the Connection String Name for the tickertape database in the web.config file to use integrated Authentication - (as per below)
2. Set the ASP website up with a domain account - as outlined below
3. Assign this account read access to the tickertape and ucce-awdb databases
4. Note for ASP this user must also have read / write access to the ASP temp folder:

C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET Files

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
	<connectionStrings>
		<add name="tickertape" connectionString="Data Source=UCCE-HDS-2A;Initial Catalog=tickertape;Integrated Security=SSPI" providerName="System.Data.SqlClient" />
	</connectionStrings>
</configuration>

*

If you get the following error:

Server Error in '/reports' Application.
The current identity (DOMAIN\svc_cce_lab) does not have write access to 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET Files'.

This can be corrected by running the following command

C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Aspnet_regiis.exe -ga LANGROUP\svc_cce_lab

Start granting LANGROUP\svc_pcce_lab access to the IIS metabase and other directories used by ASP.NET. Finished granting LANGROUP\svc_pcce_lab access to the IIS metabase and other directories used by ASP.NET.

Reference: https://zebrahost.com/docs/windows/current-identity-does-not-have-write-access-to-temporary-asp.net-files/ Sample command below - and also one for NET 4.0

C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Aspnet_regiis.exe -ga server\user
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Aspnet_regiis.exe -ga server\user