Multiplicity – Progress Data Replication Methodologies

1. Multiplicity – Progress Data Replication Methodologies

2. Webster says… mul·ti·plic·i·ty noun \ˌməl-tə-ˈpli-sə-tē\ pluralmul·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>

3. What we want out of Replication Michael Keaton 1996

4. What we end up with…

5. 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: Pro2Enterprise (Progress, MS SQL, Oracle)

6. Choice 1: ODBC Direct ReportingUse 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.

7. Choice 2: Nightly Complete RebuildDump 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.

8. Choice 3: Nightly Incremental LoadOnly 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???

9. 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???

10. Choice 5: Build own replication engine Pros: Infinitely customizable OpenEdge 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

11. Being Aware of Technology.

12. Choice 6: Pro2Enterprise Pro2SQL, Pro2Oracle, Pro2Pro 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 4GL transformation layer WAN and LAN support Cons: Upfront investment Ongoing Maintenance costs Requires Progress DataServer Components Only support replication to Progress, MS SQL, and Oracle (No MySQL) Target cannot be used as part of disaster recovery solution Only supported on Unix, Linux, and Windows

13. Pro2Enterprice Product Suite • Near real-time replication from OpenEdge to MS-SQL, Oracle, and Progress • Supports Progress 8.3 – OE10.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

14. Pro2 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

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

16. Typical Pro2 Uses • Data Warehousing • Reporting Databases • B2B/B2C Support • Consolidation of divisions • Roll-up of financial data • End-of-Month reporting • etc Not a D/R solution!

17. Pro2 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

18. Pro2 Overview Schema Holder ORACLE ORACLE Replication (Progress) 6 Tables Standard Progress DB’s Progress DataServers Schema Holder SQL Server SQL Server Replication Layer

19. A Pro2SQL Sample MFG/Pro (Progress) 773 Tables MFGHolder (Progress) ?? Tables MFGSchema (MSSQL) 773 Tables Replication (Progress) 6 Tables Standard Progress DB’s Progress DataServer MSSQL 2005 DB’s SportsHolder (Progress) ?? Tables MySport (MSSQL) 42 Tables Sports2000 (Progress) 42 tables Replication Layer

20. Pro2 Http Monitor

21. Pro2 Http Properties

22. Pro2 Http Tools

23. Pro2 Http DB Mapping

24. Questions?