Multiplicity Progress Data Replication Methodologies Webster

Multiplicity – Progress Data Replication Methodologies

Webster says… • • • mul·ti·plic·i·ty noun ˌməl-tə-ˈpli-sə-tē plural mul·ti·plic·i·ties Definition of MULTIPLICITY 1 a : the quality or state of being multiple or various b : the number of components in a system (as a multiplet or a group of energy levels) 2 : a great number 3 : the number of times a root of an equation or zero of a function occurs when there is more than one root or zero <the multiplicity of x = 2 for the equation (x − 2)3 = 0 is 3>

What we want out of Replication Michael Keaton 1996

What we end up with…

Current Choices • Choice 1: ODBC Direct Reporting - Use ODBC and run reports on live OLTP database • Choice 2: Nightly do a complete rebuild - Dump entire db and build a new data warehouse every night. • Choice 3: Nightly Incremental Load - Only dump the changed records and add to the data warehouse nightly • Choice 4: ODBC Incremental Data Pull • Choice 5: Build your own replication engine • Choice 6: Pro 2 Enterprise (Progress, MS SQL, Oracle)

Choice 1: ODBC Direct Reporting Use ODBC and run reports on live OLTP database Pros: • No extra database needed • Live reporting Cons: • ODBC setup • ODBC Security • ODBC Performance • Impact of live reporting on OLTP Database • This is not a data warehouse.

Choice 2: Nightly Complete Rebuild Dump entire db and build a new data warehouse every night. Pros: • Least Complicated • Scriptable • Target can be a data warehouse (you can transform) • You can use a different database Cons: • Huge load on OLTP server and network • Can it complete in time? ? ? • Requires a lot of storage space • Accessibility of data during dump and load (dirty data) • Code Page Compatibilities (ASCII) • Both source and target databases unavailable.

Choice 3: Nightly Incremental Load Only dump the changed records and add to the data warehouse nightly Pros: • Less Space/Network/CPU required • Scriptable • Target can be a data warehouse • Less downtime of Data Warehouse • You can use a different database Cons: • Identifying changed/added records? (deltas) • How do you know what records were deleted? ? • Can it complete in time? ? ?

Choice 4: ODBC Incremental Data Pull Pros: • Target can be a data warehouse (you can transform) • You can use a different database Cons: • More complex to configure and code • ODBC setup • ODBC Security • ODBC Performance • Data Type Incompatibilities • Identifying changed/added records? (deltas) • How do you know what records were deleted? ? • Can it complete in time? ? ?

Choice 5: Build own replication engine Pros: • Infinitely customizable • Open. Edge supports ABL and Java Replication triggers • Target can be a data warehouse Cons: • Very complicated to code • Limited development time and resource • Unproven stability and accuracy • Risk of loss of tribal knowledge • Unsupported

Being Aware of Technology.

Choice 6: Pro 2 Enterprise Pro 2 SQL, Pro 2 Oracle, Pro 2 Pro Pros: • Proven application • Stable and tested in multiple environments • Near Real-time data replication and availability • Full end user administration suite • Continual product improvements • Maintenance and ongoing support • Large install base • Automated source to target schema creation and mapping. • Multithreaded, Native Progress Technology • Customizable thru Progress 4 GL transformation layer • WAN and LAN support Cons: • Upfront investment • Ongoing Maintenance costs • Requires Progress Data. Server Components • Only support replication to Progress, MS SQL, and Oracle (No My. SQL) • Target cannot be used as part of disaster recovery solution • Only supported on Unix, Linux, and Windows

Pro 2 Enterprice Product Suite • Near real-time replication from Open. Edge to MS-SQL, Oracle, and Progress • Supports Progress 8. 3 – OE 10. 3 • Mature Product – Version 1 released in 2006 – Version 4 released May 2011 – Version 5 to be released Summer 2013 • Large install base – > 350 -400 implementations – >150+ customers world wide

Pro 2 Features • Extremely low-impact, scalable architecture – Replication speeds of 50, 000 -100, 000 rows/minute are typical (LAN environment) – Replication speeds of 20, 000 -50, 000 rows/minute are typical (WAN environment) • Captures both ABL and SQL changes • Customizable replication – by table/row/column – Custom transformations using ABL supported – Datatype and name transformation

Pro 2 Features • Management of Datatype issues – MS-SQL Dates – Column Widths – Null (unknown value) issues • Administrative Console • Monitoring – Email/SMS alerting

Typical Pro 2 Uses • • Data Warehousing Reporting Databases B 2 B/B 2 C Support Consolidation of divisions Roll-up of financial data End-of-Month reporting etc Not a D/R solution!

Pro 2 Release 4 WAN replication module available Leverages OE compression features Multiple replication threads Multiple replication targets Http monitor Usability enhancements Transaction batching for performance improvement with Oracle

Pro 2 Overview Schema Holder Standard Progress DB’s Replication (Progress) 6 Tables Progress Data. Servers Schema Holder Replication Layer ORACLE SQL Server

A Pro 2 SQL Sample MFG/Pro (Progress) 773 Tables Standard Progress DB’s Sports 2000 (Progress) 42 tables MFGHolder (Progress) ? ? Tables MFGSchema (MSSQL) 773 Tables Replication (Progress) 6 Tables Progress Data. Server MSSQL 2005 DB’s Replication Layer Sports. Holder (Progress) ? ? Tables My. Sport (MSSQL) 42 Tables

Pro 2 Http Monitor

Pro 2 Http Properties

Pro 2 Http Tools

Pro 2 Http DB Mapping

Questions?