Database to Database Integration

This tutorial deals with implementing Database to Database integration using DBSync. The tutorial shows how data exchange can happen between 2 MYSQL databases.

Environment

• Source : MYSQL Database

• Target : MYSQL Database

• DBSync : DBSync Enterprise OnPremise Edition

1. Setup your Database for Integration. For this Tutorial I will use MySQL Database Named "salesforce".

2. Create tables "dbAccount" and "dbAccountNew" in Database with following script.

create table `salesforce`.`dbAccount`
 ( `AccountID` varchar(100) NOT NULL ,
 `AccountName` text NOT NULL ,
 `BillingStreet` text ,
 `BillingPostalCode` numeric(20) ,
 `AnnualRevenue` decimal(30,2) ,
 `CreatedDate` date ,
 `LastModifiedDate` datetime ,
 `NumberOfOfficeLocations` int ,
 PRIMARY KEY (`AccountID`)
 );
 
 create table `salesforce`.`dbaccountnew`
 (`AccountID` varchar (100) NOT NULL,
 `AccountName` varchar (300) NOT NULL,
 `BillingAddress` varchar (500),
 `AnnualRevenue` Decimal (50,2),
 `CreatedDate` datetime ,
 `LastModifiedDate` datetime ,
 `NumberOfOfficeLocations` int,
 PRIMARY KEY (`AccountID`)
 );

1. Enter some test data in "dbAccount" table.

2. Log in to your DBSync account and access the relevant Project.

3. Create New Project. Enter Project Name Database to Database & Save.

4. Click on the project DatabasetoDatabase. Then, create a new Task. Enter Task Name Database2Database.

5. Click on Connectors on the left panel. Then click on Create New Connector. Enter the Connector Name Database. Then, select the Connector type* Database Connector* & Save.

6. Enter your Database details in the Connector settings.

7. Username, Password, Type, Host, and Port fields should all be updated with your Database details. Note: The Database URL will be updated automatically.

8. Click Save. Then, validate the connection to confirm it.

9. Click on the project Database2Database. Then, click on Create New Workflow. Enter Workflow Name Execute & Save.

10. In the query action, you will see Query Builder, Advanced Query Builder, and Properties.

11. In this example, Advanced Query Builder is used to query. Click on Advanced Query Builder. Then, select the data source as Database.

12. In the Query Builder section, type the following query and click on Save.

select AccountID, AccountName, BillingStreet, BillingPostalCode, AnnualRevenue, CreatedDate from dbaccount

1. Select target Connector, Operation, Target object, and map - as per the requirement.

2. In this example, the Target Connector is the database, Operation is Insert, and target object is dbaccountnew.

3. Operations can be insert, update, or upsert. Insert: If this option is selected, then only new records from the source table will be pushed to the target data source.

4. Update: If this option is selected, it will update the target data source records with updated values in the source.

5. Upsert: Upsert is a combination of insert and update. If this option is selected, then new data will be inserted into the target data, and old data will be updated with changed data.

6. Click on Map. You will see a list of column names of target object dbaccountnew. You can map these columns to the required columns of the source object dbaccount. You just have to drag the required column name from the Schema and drop it in front of the required column name of the source. In Schema, you will see the list of columns of the source object.

7. Validate Row remains "true". AccountID, AccountName, and AnnualRevenue from the target are mapped with the value of source columns AccountID, AccountName, and AnnualRevenue, respectively.

8. Click Save and close.

9. Go to Database2Database. Make sure that the status of flow Execute is enabled. Click 'Run Now' to run the integration.

10. You will see the logs at the bottom in the console section.

11. If you go to target object dbaccountnew, you will see a list of records inserted from the source object dbaccount.