As I have described in the previous posts SAP Sybase Replication Server (SRS) is a very powerful tool for real-time data replication inside the SAP RTDP platform supporting different DBMS’s for both primary and replicate data (ASE, Oracle, MSSQL … etc). Even though SRS is not designed for data transformation and thus not being its strong point I have shown an interesting technique that will allow you to achieve some basic transformations when replicating data using SRS (function string customization: http://scn.sap.com/community/services/blog/2013/03/01/data-replication-techniques-i–use-of-function-strings).
In today’s post I’d like to introduce you to a new solution called “SAP Sybase Replication Server Change Data Capture”. The solution combines SRS, one of the most used and powerful tools for real-time data replication, with SAP Data Services, one of the most used and powerful ETL tools. Implementing this new solution gives you the ability to perform real-time replication with complex data transformations for your business processes.
SRS Change Data Capture Architecture
The workflow in this new solution is:
- Modifications to primary data performed in source DB by customer applications
- Data modifications captured by Replication Agent and sent to SRS
- SRS applies modifications to CDC staging DB installed in ASE
- Data Services retrieves CDC data from CDC staging DB to perform Data Quality or Transformation and update master data table at any DBMS supported
- The Job Scheduler in ASE 15.7 where the CDC staging DB resides is responsible for purging records once they have been processed by SAP Data Services. This purging is carried out executing a stored procedure created for this purpose
In this solution SAP Sybase PowerDesigner is responsible for modeling and for scripts generation as follows:
- CDC staging database data model:
The CDC staging DB model is not the same as the primary DB one due to the fact that each replicated table has some extra columns added to enable the capture only modified data. Also a special login/user is created and a stored procedure to purge CDC staging DB data. SAP Sybase PowerDesigner can be used to carry out these tasks.
- SRS data movement model:
SAP Sybase PowerDesigner can also be used to create a data movement model. Based on this model SAP Sybase PowerDesigner will generate all scripts related to SRS that will allow you to activate Change Data Capture and move changes to the CDC staging DB.
- Source DB can be any supported DBMS for SRS primary data
- SAP Sybase Replication Server version 15.7.1 or higher
- SAP Sybase ASE version 15.7 for CDC staging database
- SAP Data Services 4.2
- SAP Sybase PowerDesigner 16.5
The following document details an example configuration for SAP Sybase Replication Server for Change Data Capture in Data Services 4.2, the steps described are related to SAP Sybase products (SAP Sybase ASE, SAP Sybase Replication Server, and SAP Sybase PowerDesigner). For SAP Data Services configuration see “Data Services 4.2 Designer Guide chapter 19.4” (link below):
More details in SAP Sybase documentation:
- “Replication Server 15.7.1 SP100 manuals“
- “Heterogeneous Replication Guide 15.7.1 SP100“
- “Data Services 4.2 Designer Guide (See chapter 19.4)”
- Follow the “Database Services Content Library” to access the entire series of Database Services Blogs and join the conversation on Twitter @SAPServices
- Learn more about SAP Database Services here “http://www.sap.com/services-and-support/data-and-technology/database-services.epx“