MDR Check & SQL Reliability

Examining methods and best practices for reliable statistics

Presented by Patrick Labbett for NAEO

https://slides.com/calltheory/mdr/live

Follow the presentation live!

Hi, I'm Patrick.

I build things for the call center and answering service industries.

What is MDR/CTE?

Explaining why you should care.

The answer is simple

MDR/CTE are specific to Infinity platforms.

Message Detail Reporting

The primary database for billing statistics

Call Tracking Events

Provides insight into what happened on an individual call

Where does data come from?

How do MDR and CTE get their data?

MDR Capture Program

Access or SQL-driven

I recommend using SQL when possible.

MDR Capture Service

Separates function from interface

Runs as a system service. The GUI is a separate program. This allows data capture after reboot without requiring a user to be logged into the machine.

What happens if capture fails?

Run multiple MDR Capture/Service applications

(You also need a database for each capture program you have running)

Server Redundancy

ServerA

MDR Capture/Service
Database: Local
DB: MDR1, CTE1

ServerB

MDR Capture/Service
Database: Local
DB: MDR2, CTE2

If ServerA or ServerB fails, you are still capturing on the other.

WE DON'T HAVE ENOUGH TIME

support+sql-docs@calltheory.com

Free guide to SQL HA Groups

(You'll need the secret word from attending the webinar)

Quick Explanation

Mirroring is dead

Use Basic Availability Groups to replace it. Included with Windows Server Standard 2019 and SQL Server Standard 2019.

Instead of Principal/Mirror/Witness, you have Replica servers and listeners

Server and Database Redundancy

ServerA

MDR Capture/Service
SQL host: SQL-1
DB: MDR1, CTE1

ServerB

MDR Capture/Service
SQL host: SQL-2
DB: MDR2, CTE2

SQL-1

AG1 Primary: MDR1, CTE1
AG2: MDR2, CTE2

SQL-2

AG1 - MDR1, CTE1
AG2 Primary - MDR2, CTE2

Quick tip for virtual environments:

Try to place the two SQL servers and two app servers on different physical hosts.

Physical Host A

Physical Host B

MDR Capture Server A

MDR Capture Server B

SQL Server A

SQL Server B

AG Listener A

AG Listener B

AG Replica 1

AG Replica 2

Powered by Windows Failover Cluster

What's in MDR?

mAlarmCapture
mCallBusied
mCallEnd
mCallSegment
mClientMaint
mConfBridge
mDial
mDialService
mDirectoryChange
mMesgEdit
mMessageDelivered
mMessageTaken

mModemIn
mModemOut
mOnCallAdd
mOnCallAlert
mOnCallDelete
mOpInterval
mOpLogout
mOpOnOff
mTraffic
mVMScripting
mVoiceErased
mWebCall
mWebConf

A closer look at mCallEnd

Records are saved at the completion of a call
(Many more fields than shown in screenshot)
Remember RecID, Timestamp, and Call for later

https://support.amtelco.com/sites/default/files/MDR/MDRdbTableFieldDescrip.pdf

Table Structure

MDR

Each table is structured differently based on it's purpose.

mCallEnd and mCallSegment are structured similar, except mCallSegment includes an mCallEnd record for each step of the call, not just the final completed call.

What's in CTE?

AcctChng
CallerHungup
CaptureAlarm
ConfHangup
ConfJoin
Dialout
EndCall

FromIVR
InitModemJob
NewCall
OpDist
OpHangup
Overdial
ToIVR

A closer look at NewCall

New call events
(Many more fields than shown in screenshot)
Remember RecID, Timestamp, and Call?

https://support.amtelco.com/sites/default/files/MDR/MDRdbTableFieldDescrip.pdf

Table Structure

CTE

Each table is structured identically.

Using the Call and CallOrder field, a timeline of call events can be created. The best example of this is the popular CTE Call Progress Report.

Commonality

(I told you to remember!)

RecID - Used to identify individual records
Call - Used to link records together
Timestamp - Used to narrow down records

Deviance

Timestamps are different.

MDR

Number of minutes that have passed since 1900

CTE

Number of seconds that have passed since 1900

Quick side-bar

An EPOCH is an instant in time that is used for reference in calendar systems

UNIX/POSIX epoch: January 1st, 1970 00:00 UTC

NT time epoch: January 1st, 1601

Microsoft COM, Google Sheets, Excel: 0 January 1900

Because of how calendar systems interact with each other and with leap years, 0 January 1900 and 31 December 1899 are logically equivalent, but may be represented as December 30 1899.

Pop quiz, hotshot.

NT time epoch: January 1st, 1601

What is special about this date?

https://devblogs.microsoft.com/oldnewthing/20090306-00/?p=18913

say hello to Y2038

Remember Y2K?

Systems that used 1900 as the epoch and counted by years with 2-digits of storage would lose their precision.

In 2038, dates using the Unix Epoch stored as a signed 32-bit integer will lose their precision.

Only 18 more years!

What we've configured so far is high availability of our statistics in two separate databases.

But what if capture does fail, and one database has more or less records than the other due to this outage?

Recap so far

Two MDR Capture programs running
Two sets of MDR/CTE databases
Databases are highly available

How do we get NSYNC?

MDR Sync

Written by Mike Liebowitz

Works with a Primary/Secondary database

Uses temp tables to sync differences

Can be ran as a command line program

Download MDR Check right from the NAEO website

SQL Native

SQL Server has built in features that we can leverage to keep our databases in sync.

Linked Servers
Maintenance Plans
SQL Server Agent

The problem?

It's a lot more complicated and requires some knowledge of T-SQL and SQL server

Linking SQL servers

-- Add a linked server

USE [master]  
GO  
EXEC master.dbo.sp_addlinkedserver   
    @server = N'SERVER\INSTANCE',   
    @srvproduct=N'SQL Server' ;  
GO  


-- Test it!

SELECT name FROM [SERVER\INSTANCE].master.sys.databases ;  
GO

DECLARE @DaysToSync INT; SET @DaysToSync = -60;

-- Insert into mdr2 mAlarmCapture
INSERT INTO [mdr2].[dbo].[malarmcapture]
SELECT [recid],
       [timestamp],
       [call],
       [account],
       [billing],
       [client],
       [orgaccount],
       [orgbilling],
       [orgclient],
       [acpacketrec],
       [acswitchrec],
       [acschedrec]
FROM   [mdr].[dbo].[malarmcapture]
WHERE  Dateadd(minute, [mdr].[dbo].[malarmcapture].[timestamp], '12/31/1899') 
		>= Dateadd(day, @DaysToSync, Getdate())
       AND NOT EXISTS (SELECT [recid],
                              [timestamp],
                              [call],
                              [account],
                              [billing],
                              [client],
                              [orgaccount],
                              [orgbilling],
                              [orgclient],
                              [acpacketrec],
                              [acswitchrec],
                              [acschedrec]
                       FROM   [mdr2].[dbo].[malarmcapture]
                       WHERE  [mdr2].[dbo].[malarmcapture].[recid] 
                       = [mdr].[dbo].[malarmcapture].[recid]);

-- Insert into mdr mAlarmCapture
INSERT INTO [mdr].[dbo].[malarmcapture]
SELECT [recid],
       [timestamp],
       [call],
       [account],
       [billing],
       [client],
       [orgaccount],
       [orgbilling],
       [orgclient],
       [acpacketrec],
       [acswitchrec],
       [acschedrec]
FROM   [mdr2].[dbo].[malarmcapture]
WHERE  Dateadd(minute, [mdr2].[dbo].[malarmcapture].[timestamp], '12/31/1899')
       >=
              Dateadd(day, @DaysToSync, Getdate())
       AND NOT EXISTS (SELECT [recid],
                              [timestamp],
                              [call],
                              [account],
                              [billing],
                              [client],
                              [orgaccount],
                              [orgbilling],
                              [orgclient],
                              [acpacketrec],
                              [acswitchrec],
                              [acschedrec]
                       FROM   [mdr].[dbo].[malarmcapture]
                       WHERE  [mdr].[dbo].[malarmcapture].[recid] =
                              [mdr2].[dbo].[malarmcapture].[recid]);

Using Linked Servers, we can adapt the T-SQL to sync records across linked servers.

DECLARE @DaysToSync INT; SET @DaysToSync = -60;

-- Insert into mdr2 mAlarmCapture
INSERT INTO [mdr2].[dbo].[malarmcapture]
SELECT [recid],
       [timestamp],
       [call],
       ...
FROM   [mdr].[dbo].[malarmcapture]
WHERE  Dateadd(minute, [mdr].[dbo].[malarmcapture].[timestamp], '12/31/1899') 
		>= Dateadd(day, @DaysToSync, Getdate())
       AND NOT EXISTS (SELECT [recid],
                              [timestamp],
                              [call],
                              ...
                       FROM   [linkedserver].[mdr2].[dbo].[malarmcapture]
                       WHERE  [linkedserver].[mdr2].[dbo].[malarmcapture].[recid] 
                       = [mdr].[dbo].[malarmcapture].[recid]);

Typically used for ongoing maintenance like index rebuilds, database optimization, backups, and more.

SQL Maintenace Plans

Run jobs using SQL Server Integration Services Package

By default, SQL Server Agent is set to manual during installation. Make sure you turn it on in order to take advantage of Maintenance Plans.

SQL Server Agent

You should set this service to run as Automatic (Delayed Startup)

Downsides

Because there's always a tradeoff.

TSQL has to be written for each individual table as using `SELECT * FROM` does not work with `INSERT INTO`
SQL Server Agent has to be properly configured
SQL servers have to be network reachable to each other
TempDB needs monitored if syncing large volumes of data

Remove

The

Complexity

What Shuttle does

Enter unlimited # of MDR/CTE databases to keep in sync
Analyze missing time and SQL requirements for syncing
Creates T-SQL for Linked Servers on each server
Creates T-SQL for syncing MDR/CTE records on each server
Schedules SQL Maintenance Plan by SQL Server Agent
Auto updates to the latest version every time you open it
Not required to be running once SYNC is setup
Manually target specific ranges or one-time sync databases
Trusted by Microsoft Smart Filter

MDR Check & SQL Reliability

Hi, I'm Patrick.

What is MDR/CTE?

The answer is simple

Message Detail Reporting

Call Tracking Events

Where does data come from?

MDR Capture Program

Access or SQL-driven

MDR Capture Service

Separates function from interface

What happens if capture fails?

Run multiple MDR Capture/Service applications

Server Redundancy

ServerA

ServerB

WE DON'T HAVE ENOUGH TIME

Quick Explanation

Mirroring is dead

Server and Database Redundancy

ServerA

ServerB

SQL-1

SQL-2

What's in MDR?

A closer look at mCallEnd

Table Structure

MDR

What's in CTE?

A closer look at NewCall

Table Structure

CTE

Commonality

(I told you to remember!)

Deviance

Timestamps are different.

Quick side-bar

Pop quiz, hotshot.

say hello to Y2038

Recap so far

How do we get NSYNC?

MDR Sync

SQL Native

The problem?

Linking SQL servers

SQL Maintenace Plans

SQL Server Agent

Downsides

Remove

The

Complexity

What Shuttle does

Coming Soon™

Thank you

Questions?