NotifUs, LLC
Level up your call center.
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 ;
GODECLARE @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.
