Transcript VisionSolutionsGraphics+Logos2012

Data: Migrating, Distributing and Audit Tracking
Michelle Ayers, Advisory Solution Consultant
Email: [email protected]
Mark Scanlon, UKI Regional Director
October 2013
visionsolutions.com
1
Vision Solution Offerings
Data Migration
Traditional Approach – ‘Big Bang’
All
Users
Off
System
Backups
Copy Data
Transform Data
Testing/verify
Users
Back
On
System
DOWNTIME
EOD
Friday
Saturday
Sunday
Monday
AM
Issues:
• Hope you can complete all steps in available time window…
• Data volumes becoming ever larger – takes longer to copy and
convert data
• Business becoming more 24 x 7 - reduced downtime window
opportunities
• Final testing/verification limited to what remaining time you have
• Many stages are dependant on completion of previous stage
visionsolutions.com
3
Data Migration
Alternative Approach – ‘Continuous Replication’
visionsolutions.com
4
Data Migration
Benefits
visionsolutions.com
5
Data Migration
More Complex:
Administration & Monitoring
(Director, MonCon & Console)
IBMi
Oracle
LAN / WAN
DT Share
Captures Changes
Data path
Source
Data
DT Share
Applies trans to
Staging Database
Staging
Data
Custom
Programs
Custom
Programs
Target
Data
Staging
Data
DT Share
Applies trans to
Staging Database
visionsolutions.com
LAN / WAN
DT Share
Captures Changes
Data path
6
Data Sharing
Typical Approaches:
Apps
ERP Application
Application Database
User Queries
Business Intelligence
Website
• Lots of demands on the central database
• All users/applications using same data therefore all should be consistent
However:
• Performance issues – too much access of same data
• Users running inefficient SQL statements
• Increased security risks from many different communication jobs
Can lead to productivity issues for core business processes
visionsolutions.com
7
Data Sharing
Typical Approach Using Offloaded Data Warehouse:
ERP Application
Application Database
Data Warehouse
User Queries
Business Intelligence
Offloaded data warehouses have been around some years
However:
• Data often loaded by batch overnight
• May even need to reload whole tables each night
Issues:
• Data is out of date quickly
• Every day the database grows a little bit bigger, therefore each night the
load job takes a little bit longer…
visionsolutions.com
8
Data Sharing
Alternative Approach – ‘CDC’ Change Data Capture
Customer Story – Red Wing Shoe Company - USA
OFFLOAD QUERY & REPORTING PROJECT
Many cost effective tools available
on MS SQL server
platform for query reports
Manufacturing
Production System
Double-Take Share
Offload Query System
Real time CDC replication
with transformation
Query reports
Data Warehouse load
IBM System i DB2
Lawson M3 (Movex)
Reduce CPU and I/O overhead
on production system
improve user response times
visionsolutions.com
MS SQL Server
Data is already partially
‘scrubbed’ and available
for loading data warehouses
and data marts without
performance impact on
production system
9
Data Sharing
Alternative Approach – ‘CDC’ Change Data Capture
Customer Example – Application Interface APPLICATION INTEGRATION PROJECT
Double-Take Share Replaced
batch reload of data warehouse
with DT Share creating
incremental extracts of last
changed row, and deleted
transactions.
Production System
Double-Take Share
Real time CDC replication
with transformation
Bi-directional replication
IBM System i DB2
Manufacturing ERP
Production line
information is
transformed and
sent to ERP
MS SQL Server
MS SQL Server
Third Party Inspection
Application
Query reports
visionsolutions.com
Introduced automated manufacturing inspection tracking
and also faster Data Warehouse Load.
10
Data Auditing
Traditional Approach to Field Audit Tracking
Application Database
Before and after changes captured via
embedded program code
Or trigger programs
Audit Files
• Older applications may be using embedded code to capture changes –
high overhead to maintain and ensure nothing missed
• Trigger based capture can have database overhead though should ensure
call changes captured
• ERP provided solution can be limited:
• May only support pre-selected tables/columns
• Restricted data filtering capabilities
visionsolutions.com
11
Data Auditing
Alternative Approach – ‘CDC’ Change Data Capture
FIELD AUDIT TRACKING PROJECT
Application Database
Double-Take Share
Real time CDC replication captures before
and after values
IBMi uses journal receiver entries
•
•
•
•
•
•
•
Audit Files
Target audit
files can be held
in same database
or different, say
MS SQL Server
Capture changes made within or outside the ERP application
Optionally add row/column filtering
Choose columns from any database table, including local modification
Target audit files are custom designed
Add target columns to hold trans type, timestamp, user id
Access to audit data via custom queries/reports
IBMi M3 java database has journaling switched on by installation – DT
Share can use these journal receivers.
visionsolutions.com
12
Double-Take Share
visionsolutions.com
13
Thank You!
visionsolutions.com
businesscontinuitytoday.com
twitter.com/vsichannel
visionsolutions.com
facebook.com/vsichannel
youtube.com/vsichannel
linkedin.com/companies/6542
14