MDR Check & SQL Reliability

Examining methods and best practices for reliable statistics

Presented by Patrick Labbett for NAEO

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

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

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?

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?

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

Coming Soon

Thank you

Questions?

MDR Check & SQL Reliability

By NotifUs, LLC

MDR Check & SQL Reliability

Learn how MDR and CTE databases can be kept in sync and how to approach SQL for high reliability. Presented Wednesday, August 5, 2020 at 2PM EDT.

  • 156