User Guide

Overview

BryteFlow uses a micro services architecture where each source and target data replication pipeline is independent and can be decoupled. The source and destination pair can run on premises or on the cloud or on another server as per the requirements. This prevents a single point of failure, and allows concurrency and independent scaling. One large source does not affect the performance of the other sources. Each Ingest instance comes along with the companion software modules – XL Ingest, TruData and Blend. These borrow the configuration from the Ingest instance. It is recommended that these are located with Ingest, but again, these could be decoupled for performance or security considerations. ControlRoom provides a consolidated interface across all the data pipelines.

Prerequisites for software on the server

The following softwares are required to be installed on the server:

  1. The server should have 64-bit Open jdk 1.8
  2. Google Chrome
  3. For SQL Server sources and SQL Server destinations,  Install bcp – Microsoft Utility and below drivers:

Setup BryteFlow Suite

Setting up BryteFlow suite of products on an On premises windows server can be done in few easy steps. Please ensure there is connectivity to the sources and targets from this server.

  1. Download the software from the link provided on the server where the software needs to be installed.
  2. Unzip it to the installation directory of your choice .
  3. This will have entire BryteFlow suite under the main directory named ‘bryteflow’.
  4. If you are planning to setup more than one BryteFlow instances, its good to rename the main install directory ‘bryteflow’ to ‘bryteflow-<source1>’ to avoid any conflict.

 

Setup BryteFlow Ingest

Setting up BryteFlow Ingest in few easy steps:

  1. Once the software is downloaded and unzipped.
  2. Go to ‘ingest’ directory under the main directory ‘bryteflow’.
  3. In order to install the service open windows command prompt with ‘Run as Administrator’
  4. Change the directory to ‘ingest’
  5. Execute  “Install-BryteFlow Ingest Service.bat”
  6. ‘BryteFlow Ingest’ service is now installed on the server.
  7. Go to Windows ‘services’ and start the service for Ingest.
  8. Launch the user interface from google chrome browser with the URL: localhost:8081 
  9. Proceed to complete Ingest setup by configuring the Source db and Destination db.
  10. Make sure you have a valid licence before starting the replication. For more details on how to get the licence key refer this section.

Prerequisites for Source Databases

In order to capture the database changes the software needs access to database logs. To make these available there are certain permissions required for each databases. Depending on the source database please perform the prerequisite steps.

Prerequisite for MS SQL Server source

SQL Server prerequisites depend on the replication option chosen, Change Tracking, Change Data Capture or Transaction Logs. Prerequisites for each option are described in detail below.

Using SQL Server Change Tracking

1. bcp – Microsoft Utility has been installed for MS SQL Server as source
 
As per prerequisites please check that bcp has been installed on the server by opening a command window and typing “bcp -v” . If not please refer to the section above.
 
 2. Enable Change Tracking for a database in MS SQL Server

This section applies to MS SQL Server, the version should be higher than 2008.

  • To enable Change Tracking at the database level execute the following query:
    ALTER DATABASE databasename
    SET CHANGE_TRACKING = ON
    (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON)
  • Enable Change Tracking at the table level as below execute the following query:
    ALTER TABLE tablename
    ENABLE CHANGE_TRACKING
    WITH (TRACK_COLUMNS_UPDATED = ON)
  • Enable Change Tracking at the database and table level for all the databases and tables to be replicated.
  • To enable view permission to event viewer logs execute the following query:

3. Grants and Permission

  • GRANT VIEW SERVER STATE TO “AccountName”
  • The BryteFlow Ingest database replication login  user should have VIEW CHANGE TRACKING permission to view the Change Tracking information.
    GRANT VIEW CHANGE TRACKING ON schema.tablename TO BryteFlow user;

*** Please Note:  If you are configuring BryteFlow Ingest for a completely new SQL Server database, Please make sure to perform at least ‘One Transaction’ on the database to generate the log sequence number for BryteFlow to start with.

Using SQL Server Transaction logs

1.User account that connects to the source needs the sysadmin fixed server role
Ensure the BryteFlow user in the connection details has sysadmin fixed server role
 2. The recovery model must be set to Bulk logged or Full

Please run the following command to enable this

ALTER DATABASE databasename SET RECOVERY FULL;

3. The tables that are replicated have Primary keys defined at the database level

This is a limitation of SQL Server, where transaction logs cannot be used for replication for tables that have no primary keys defined. For tables with no primary keys, please use Change Data Capture (CDC ) described in the next section

Steps to be performed on MS SQL Server to use transaction Logs:

  1. For log-based capture from the SQL Server Enterprise Edition, Standard or Developer Edition, BryteFlow requires  SQL Server Replication Components option to be installed.
  2. A user with a sysadmin privilege must create a distribution database for the SQL Server instance, unless one already exists. To do this, a user with a sysadmin privilege should run SQL Server wizard Configure Distribution Wizard, which can be run by clicking Replication > Configure Distribution… Any database name can be supplied (click Next > Next > Next).If Always On AG is installed, then only one distribution database should be configured. Either this can be set up inside the first node and the other nodes get a distributor that points to it. Or the distribution database can be located outside the Always On AG cluster and each node gets a distributor that points to it there.

    Working with SQL Server AlwaysOn availability groups

    The SQL Server AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. To use AlwaysOn Availability Groups as a source:

    • Enable the Distribution option on all SQL Server instances in your Availability Replicas.
    • In the Source Connection tab, For Database Host, specify the Domain Name Service (DNS) name or IP address that was configured for the Availability Group Listener.

Using SQL Server Change Data Capture

1. bcp – Microsoft Utility has been installed for MS SQL Server as source
 
As per prerequisites please check that bcp has been installed on the server by opening a command window and typing “bcp -v” . If not please refer to the section above.
 
 2. Enable Change Data Capture for a database in MS SQL Server

This section applies to MS SQL Server, the version should be higher than 2008.

  • To enable Change Data Capture at the database level execute the following query:
    Use <databasename>;
    EXEC sys.sp_cdc_enable_db;
    
  • Enable Change Data Capture at the table level as below execute the following query:
    USE <databasename>
    GO
    EXEC sys.sp_cdc_enable_table
    @source_schema = ‘<schema_name>’,
    @source_name = ‘<table_name>’,
    @role_name = null,
    @supports_net_changes = 0;
    
  • Enable Change Data Capture at the database and table level for all the databases and tables to be replicated.

3. Check if the SQL Server Agent has been started

Use Microsoft configuration Manager to turn on the SQL Server Agent.
The SQL Server Agent should be running.

Prerequisite for Oracle source

1. Enable Change Tracking for an On-Premises Oracle Server

Execute the following queries on Oracle Server to enable change tracking.

  • Oracle database should be in ARCHIVELOG mode.
  • The supplemental logging has to be turned on at the database level. Supplemental logging is required so that additional details are logged in the archive logs.
    To turn on supplemental logging at the database level, execute the following statements:

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
  • Alternatively to turn on minimal database supplemental logging execute the following statements:
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 
    ALTER DATABASE FORCE LOGGING;
  • In Oracle, ensure that supplemental logging is turned on at the table level. To turn on supplemental logging at the table level, execute the following statement:
    ALTER TABLE <schema>.<tablename> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
  • SELECT access on all tables to be replicated
  • SELECT access on V$ARCHIVED_LOG
  • The following security permissions should be assigned to the userCREATE SESSION
    SELECT access on V_$LOGMNR_CONTENTS
    SELECT access on V_$LOGMNR_LOGS
    SELECT access on ANY TRANSACTION
    SELECT access on DBA_OBJECTS
    EXECUTE access on DBMS_LOGMNR
  • Run the following grant statements for <user> for the above requirements:
    GRANT SELECT ON V_$ARCHIVED_LOG TO <user>;
    GRANT SELECT ON V_$LOGMNR_CONTENTS TO <user>;
    GRANT EXECUTE ON DBMS_LOGMNR TO <user>;
    GRANT SELECT ON V_$LOGMNR_LOGS TO <user>;
    GRANT SELECT ANY TRANSACTION TO <user>;
    GRANT SELECT ON DBA_OBJECTS TO <user>;
    GRANT LOGMINING to <user>; -- This is required for Oracle 12c and higher versions
    CREATE PUBLIC SYNONYM DBMS_LOGMNR FOR SYS.DBMS_LOGMNR; -- This is required for Oracle 10g

Preparing On-premises MySQL

To prepare MySQL for change tracking perform the following steps.

  1. Enable Change Tracking

To enable binary logging, the following parameters need to be configured as below in my.ini file on MySQL on Windows or in my.cnf file on MySQL on UNIX:

Parameter Value
server_id Any value from 1.
E.g. server_id = 1
 log_bin=<path> Path to the binary log file.
E.g. log_bin = D:\MySQLLogs\BinLog
binlog_format binlog_format=row
expire_logs_days To avoid disk space issues it is strongly recommended not to use the default value (0).
E.g. expire_log_days = 4
 binlog_checksum This parameter can be set to binlog_checksum=none.
BryteFlow does support CRC32 as well
binlog_row_image binlog_row_image=full
 

 

Pre-requisites for SAP Odata based extraction for S/4HANA System

SAP S/4HANA System: 

For ODP Based Extraction or CDS view Based Extraction below components are needed

  1. SAP NetWeaver Version: 7.5 Support Pack Level 4 & Above
  2. SAP Gateway Component (SAP_GWFND) should be installed
  3. SAP BW Version: 7.5 SP4 & Above
  4. System Alias has to be Defined, if not define it at least LOCAL System alias is needed.
  5. SAP Gateway needs to be Activated.
  6. BW extractor needs to be installed from Business Content (RSA5).
  7. Once installed, Check for ODP Release check mark in transaction RSA6.
  8. OData Service / CDS views needs to build on Extractor & registered for data extraction.

Pre-requisites for SAP Odata based extraction for SAP BW System

For ODP Based Extraction on SAP BW System, below components are needed: 

  1. SAP Netweaver Version: 7.5 Support Pack Level 4 & Above
  2. SAP Gateway Component (SAP_GWFND) should be installed
  3. SAP BW Version: 7.5 SP4 & Above
  4. System Alias has to be Defined, if not define atleast LOCAL System alias is needed.
  5. SAP Gateway needs to be Activated.
  6. BW extractor needs to be created for the info providers & release the extractor for ODP by using program “BS_ANLY_DS_RELEASE_OD
  7. OData Service needed to build on Extractor & registered for data Extraction.

Verification for SAP Odata source

  1. SAP BW version can be checked in the transaction SPRO.  The following are to be checked:

2. System Alias has to be Defined, if not defined. At least LOCAL System alias is needed.

 

3. SAP Gateway needs to be Activated. If not, then please activate the SAP Gateway.

 

4.  Check for ODP Release check mark for ODP extractors in transaction RSA6.

 

Preparing BryteFlow Ingest for SAP source via ODP as an Odata service

BryteFlow can replicate SAP data via ODP ( Operation Data Provisioning) as an Odata service. Any SAP extractor that is whitelisted by SAP for ODP framework can be used to extract data from and expose that data as an OData service for consumption.

List of steps to be performed to make this possible can be broadly classified as

  1. Extractor setup
  2. Enabling ODP
  3. OData Service generation

1. Enable SAP BW extractor :

This step is a typical SAP BW extractor enabling step.

1.1 Activate Extractor
Go to transaction RSA5, expand sub tree and find extractor

1.2 Validate Extractor

To make sure extractor is active and to view the fields of extractor go to transaction RSA6, expand subtree and find the extractor. Double click on the extractor to view the fields.

1.3 Test Extractor

To perform a test run, go to transaction RSA3 and type in extractor name and click on the Extraction button. Click on the ALV Grid button to view the data.

2. Enabling ODP for Odata 

Most of the extractors have ODP enabled. This can be verified by checking the ODP icon in RSA5/6 which indicates if the extractor is whitelisted by SAP or not. Furthermore, open the contents of table ROOSATTR to verify if there is an entry in the table for extractor and EXPOSE_EXTERNAL indicator is set.

3. OData Service generation

After ODP extractor is set up a service needs to be created to expose the extractor data. Follow the below steps to configure OData service.

3.1 Create Project & Service

3.2 Generate Runtime Objects

3.3 Service Maintenance

After the service is successfully generated provide the URLs for API call

  1. Metadata:  http://<server>:<port>/sap/opu/odata/sap/<service name>/$metadata
  2. Complete URL of the service would like

http://<server>:<port>/sap/opu/odata/sap/<service name>/EntifyOf<ODP name>/ (to view data in browser)

http://<server>:<port>/sap/opu/odata/sap/<service name>/ DeltaLinksOfEntifyOf<ODP name>/

 

Please Note: For extracting custom CDS views, annotation needs to be added to CDS view “@OData.publish: true”

Preparing Salesforce account for BryteFlow Ingest

On Salesforce Change Data Capture is turned on by default, please do not turn it off.

You would need to generate a security token to be used with Bryteflow Ingest.
A security token is a case-sensitive alphanumeric key that appended to your Salesforce password.

eg. Your Salesforce password to be used with Ingest will be “<your Salesforce password ><security_token>”

A token can be generated by following these steps:
1. Log in to your salesforce account and go to My Setting > Personal > Reset my security token.
2. Click on Reset Security Token button. The token will be emailed to the email account associated with your salesforce account.

Prerequisites for PostgreSQL source

  1. Use a PostgreSQL database that is version 9.4.x or later
  2. The IP address of the BryteFlow machine must be added to the pg_hba.conf
    configuration file with the “replication” keyword in the database field.
    Example:
    host replication all 189.452.1.212/32 trust
  3. Set the following parameters and values in the postgresql.conf configuration file:Set wal_level = logicalSet max_replication_slots to a value greater than 1.

    The max_replication_slots value should be set according to the number of tasks that you want to run. For example, to run four tasks you need to set a minimum of four slots. Slots open automatically as soon as a task starts and remain open even when the task is no longer running. You need to manually delete open slots.

    Set max_wal_senders to a value greater than 1.

    The max_wal_senders parameter sets the number of concurrent tasks that can run.

    Set wal_sender_timeout =0

    The wal_sender_timeout parameter terminates replication connections that are inactive longer than the specified number of milliseconds. Although the default is 60 seconds, we recommend that you set this parameter to zero, which disables the timeout mechanism.

    Note:- After changing these parameters, a restart is needed for PostgreSQL

  4. Grant superuser permissions for the user account specified for the PostgreSQL source database. Superuser permissions are needed to access replication-specific functions in the source.

Preparing for BryteFlow Trigger solution

BryteFlow can also use database triggers to get the change data.

For this solution there are certain prerequisites which needs to be implemented:

  1. BryteFlow replication user should have ‘select‘ access on the tables to be replicated
  2. BryteFlow replication user should have access to ‘create triggers’ to be replicated
  3. BryteFlow replication user should have access to ‘create tables’ on the source database. The solution requires tables to be created on the source by BryteFlow, so please create a schema where these tables can reside, so that it can be configured in the software.

Please provide relevant grants to BryteFlow replication user in order to proceed with the Trigger Solution.

 

 

Setup BryteFlow XL Ingest

BryteFlow XL Ingest is a companion software to BryteFlow Ingest. It does the initial loads for huge volume tables.

The setup is easy as it reads through the Ingest config for source and destination databases.

Follow below steps to do the install:

  1. Once BryteFlow Ingest is setup and running.
  2. Go to the main directory ‘bryteflow’->’xlingest’
  3. In order to install the service open windows command prompt with ‘Run as Administrator’
  4. Change the path to ‘xlingest’ directory
  5. Execute  “Install-BryteFlow XL Ingest Service.bat”
  6. ‘BryteFlow XL Ingest’ service is now installed on the server.
  7. Go to Windows ‘services’ and start the service for Ingest.
  8. Launch the user interface from google chrome browser with the URL: localhost:8084
  9. Make sure you have a valid licence before starting the replication. For more details on how to get the licence key refer this section.

Setup BryteFlow Trudata

BryteFlow Trudata is a companion software to BryteFlow Ingest. It reconciles the source and destination tables in near real-time.

The setup is easy as it reads through the Ingest config for source and destination database details.

Follow below steps to do the install:

  1. Once BryteFlow Ingest is setup and running.
  2. Go to the main directory ‘bryteflow’->’trudata’
  3. In order to install the service open windows command prompt with ‘Run as Administrator’
  4. Change the path to ‘trudata’ path
  5. Execute  “Install-BryteFlow Trudata Service.bat”
  6. ‘BryteFlow Trudata’ service is now installed on the server.
  7. Go to Windows ‘services’ and start the service for Ingest.
  8. Launch the user interface from google chrome browser with the URL: localhost:8085
  9. Make sure you have a valid licence before starting the replication. For more details on how to get the licence key refer this section.

Setup BryteFlow Blend

BryteFlow Blend is a companion software to BryteFlow Ingest. Its used to perform data transformations of AWS S3.

The setup is easy as it reads through the Ingest config for AWS details.

Follow below steps to do the install:

  1. Once BryteFlow Ingest is setup and running.
  2. Go to the main directory ‘bryteflow’->’blend’
  3. In order to install the service open windows command prompt with ‘Run as Administrator’
  4. Change the path to ‘blend’ path
  5. Execute  “Install-BryteFlow Blend Service.bat”
  6. ‘BryteFlow Blend’ service is now installed on the server.
  7. Go to Windows ‘services’ and start the service for Ingest.
  8. Launch the user interface from google chrome browser with the URL: localhost:8082
  9. Make sure you have a valid licence before starting the replication. For more details on how to get the licence key refer this section.

Applying Licence Key

BryteFlow Suite of products when setup in Non-AMI  environment comes with a validity which is specific to each customers agreement. Make sure you get the valid licence keys in order to begin with software usages. Each software comes with its own product id when setup individually. 

NOTE: Licensing is not applicable when sourced from the AWS Marketplace.

Here is how to apply licence keys for each:

Apply Licence Key in BryteFlow Ingest

To get a valid licence go to Configuration tab, then to the License tab and email the “Product ID” to the BryteFlow support team – support@bryteflow.com

Apply Licence Key in BryteFlow XL Ingest

To get a valid licence go to Configuration tab and email the “Product ID” to the BryteFlow support team – support@bryteflow.com

 

 

 

 

 

Apply Licence Key in BryteFlow TruData

To get a valid licence go to Configuration tab and email the “Product ID” to the BryteFlow support team – support@bryteflow.com

Apply Licence Key in BryteFlow Blend

To get a valid licence go to ‘Setup‘ tab then select ‘AWS Location‘ in the left menu. Please email the “Product ID” to the BryteFlow support team – support@bryteflow.com

 

Setup BryteFlow Control Room

Setting up BryteFlow Control Room on an On premises windows server can be done in few easy steps.

  1. Download the software from the link provided on the server where the software needs to be installed.
  2. Unzip it to the installation directory preferably at the same level of ‘bryteflow’
  3. In order to install the service open windows command prompt with ‘Run as Administrator’
  4. Change the directory to the path of ‘controlroom’
  5. Execute ‘Install-BryteFlow controlroom Service.bat’
  6. ‘BryteFlow Controlroom’ service is now installed on the server.
  7. Go to Windows ‘services’ and start the service for Ingest.
  8. Launch the user interface from google chrome browser with the URL: localhost:8091
  9. Make sure you have a valid licence before starting the replication. For more details on how to get the licence key refer to this section.

 

Suggest Edit