1.0 Overview
Syniti Data Replication (Formerly DBMoto) software makes it easy to implement heterogeneous data replication, change data capture and data transformation capabilities. It can replicate data from on-premise / cloud RDBs or any major database to on-premise / cloud RDBs , on-premise/Cloud Analytics .It makes corporate data available for data storage and analytics in the cloud & on-premise business application. It can keep data synchronized across legacy system and various enterprise databases.
This document will provide information about how to set up DBMoto connection between source and target & vice versa.
Data are replicated from source to target using Snapshot or Refresh, CDC or Mirroring and Synchronization.
- Refresh or Snapshot: Refresh mode copies all selected data from source to target database.
- CDC or Mirroring: Change data capture or mirroring mode copies only change data from source database to target database. Native database log helps to identify the changes since the last replication.
- Synchronization : Synchronization replicates changed data from source to target and target to source database keeping data in sync on both source and target.
2.0 Architecture
Syniti Data Replication software uses widely available windows .NET data provider to create connection to source and target system for most databases. Use the management center wizard to setup connections and replications. Replication agent used to schedule and run replications.
Server agent manages replication metadata in multiple clients either additional management centers or custom application developed using APIs.
3.0 Data Replication DBMoto Setup
There are multiple steps involved in setting up DBMoto and running the application.
- Define a Source Connection: Define a source connection to access any information for native logs.
- Define Target Connection: Define a target connection to replicate data in the target. Create target tables if needed.
- Create Replication: Once source and target connections are in place, create replication specifying where the data are coming from, which fields are going to be included in the mapping and schedule execution.
- Run Replication: Lastly run replication and review & manage results using tools in the Syniti data replication management center.
In Data Replication - Management Center (DRMC) the default metadata database stores all the replication setup information.
3.1 Sources
To create a new source in DRMC, go to Source and right click to Add New Connection.
3.1.1 Add New Legacy Source Connection (PC3_700 | Oracle)
Add Oracle source database in Sources.
It opens Add Source Connection Wizard, click on ‘Next’
In source connection wizard enter the name of the source, database, provider details as shown below.
Name must match ADM data source name (thus PC3_700)
Assembly: D:\app\client\product\12.2.0\client_2\odp.net\bin\4\Oracle.DataAccess.dll
Click on ‘Next’ and go to ‘Set connection string ‘, enter data source name, user id and password.
Data Source must be the correct server’s TNS name contained in file D:\app\client\product\12.2.0\client_2\Network\Admin\tnsnames.ora (this information must be provided by the Oracle DBAs):
Once test connection is successful, click on ‘Next’ and select tables which are in replication scope.
Click on ‘Next’ and move to summary screen then click on finish
Click on ‘Finish’ source connection and ‘PC3_700’ will be created in source.
3.1.2 Add New SAP CFIN (Central Finance) Source Connection (QXA_710 | SAP HANA)
Add SAP HANA database system in sources
It opens the Add Source Connection Wizard, click on ‘Next’ button
Click on ‘Next’ and go to select provider screen. Source DB name and provider details as shown below.
Name must match ADM data source name (thus QXA_710)
Assembly: C:\Program Files\sap\hdbclient\ado.net\v4.5\Sap.Data.Hana.v4.5.dll
Click on ‘next’ and go to ‘Set Connection String’ and enter connection details as shown below.
Data Source must be the combination of SAP HANA Server hostname/IP + port
(Usually 3<instance number>15)
Username is HQDADQXHSYDR
Click on ‘Test’ displays success message if server is successfully connected.
Once connection is successful then click on ‘Next’ and go to screen ‘Select Tables’ as shown below.
Click on ‘Next’ and go to summary screen as shown below and click on ‘Finish’.
Click on ‘Finish’ and Source ‘QXA_710’ gets created in Source.
3.2 Targets
3.2.1 Setup SQL database user before adding the target
Log in to SQL Server Management Studio then go to Security -> Logins, select ‘DBMOTO’ then right click and go to ‘Properties’ as shown below.
Click on ‘Properties’ to display new screen. Under ‘Select a page’ select ‘User mapping’ as shown below.
- Database must be the target DB which will have the data imported into. The DBMoto Target name must match the name in the ADM target (Collect)
- Check target data base exist in ADM Collect and database name is same as DBMoto target. If target database does not exist in ADM Collect, then create target database in Common. Common -> Configuration -> Data Source Registry-> Add (target)
Add Target data source in ADM target
3.2.2 Add New Targets
Go to Syniti DRMC, right click on ‘Targets’ then click on ‘Add New Connections’.
It opens ‘Add target Connection Wizard’ as shown below
Click on ‘Next’ button will open ‘Select Provider‘ screen . Enter the details as shown below.
Note: The Name field must match the ADM (collect) target
Click on ‘Next’ button to move on ‘Set Connection String’ screen. Enter connection details.
Data Source will be ADM’s Database Server, user will be DBMOTO
Click on ‘Test ‘ Button, it will display success or failure message for connection.
Once connection is successful, click on ‘Next’ Button and move to ‘Select Table’ screen. Select the tables which are in replication scope.
Click on ‘Next ‘ Button
Again, click on ‘Next’ button
Now click on ‘Finish’. It will save target.
Once target database is created one needs to check all tables from source database exist in the Target database. If not, then create target table under Target database. In our case source tables exist in the target database so we selected all source tables while creating the target database in DBMoto..
If tables are not created in target database, we can create target table in DBMoto after creating target database.
How to create target tables in target database:
Drag the table ‘Customers’ from Source database to target database to open up Create Target table Wizard. Example shown below.
Then click on ‘Next’ button to open next screen. The source connection is Oracle source and table is ‘Customers’ table as shown below.
Now click on ‘Next‘ to move to next screen where target connection is HANA as shown in below screen shot.
Click on ‘Next’ and go to next screen as shown below. This will show the structure of ‘customers’ table which will create in the HANA database. Here you can modify the fields’ type or size (if necessary).
Then click on ‘Next’ it will display SQL statement which creates the table. Screen shot given below.
Click on ‘Next’ and go to ‘Action’ as shown below.
Click on ‘Next’ and go to summary screen then click on ‘Finish‘ to create table in HANA Database as shown below.
Once you click on ‘Finish’ it shows a success message and a table is automatically created in HANA database. However, it is worth checking.
Once you hit ‘OK’ in success message, a pop-up screen displays
Click ‘Yes’, it will add ‘Customers’ table in target database table list.
3.3 Create Replications
This section will give details on how to create new replication in DBMoto. Right click the table in the source which needs to be replicated, click on Replication -> ‘Create New replication’.
Once you click on ‘Create New Replication’, it opens the replication wizard.
Click on ‘Next’ button on the Replication Wizard. Enter Replication Name and Type as shown below. Click on ‘Next’.
The replication name can have a different name to reflect the source/target combination.
Note: In above screen, the system might take some seconds to read the structure which is normal. Click on ‘Next’ and go to next screen.
Make sure that the correct target is selected and choose the corresponding table. Click on ‘Next’ opens next screen as shown below.
Click on ‘Next’ to move into next screen, enter the replication details like start time etc. as shown below.
Click on ‘Next‘ button, it will take you to next screen as shown below.
Again, click on ‘Next’ button, it will move to next screen ‘Summary’ as shown below.
In this screen click on ‘Finish’, it creates replication as shown below.
The Replication Monitor will show the replication information as shown in above screen shot. The system will first refresh full table before replication starts.
After the full refresh completes, the system will by default mirror the information from source to target every minute. The Replication Monitor will show that information.
3.4 Create Groups
Replication groups can be created to optimize database connection and manage replication properties from multiple replications at one time. You can create new groups. Click on Groups->Add New Groups
It will open ‘Create Group Wizard’
Click on ‘Next’ and enter group name as shown in below screen shot
Click on ‘Next’, it will display list of Replication.
Click on ‘Next’ and move to next screen as shown below.
Click on ‘Next’ and go to ‘Summary’ screen then click on ‘Finish’ as shown in below screen shot.
Once you click on ‘Finish’ group ‘ADRC’ is created in DBMoto.
3.5 Create Master Log Objects
As HANA connections use trigger-based replications, we require a DB user with privileges to create triggers in SAPHANADB schema + create log objects in SYNITIDR (or whatever name is set for the schema by the DB Administrators). The user selected in this example is SYSTEM as it is most likely to never be removed – and the removal of a user might cause the deletion of its objects which we don’t want .
Click on ‘Connection Properties’ it will pop up below screen.
Click on Connection as highlighted in above screen, it will display connection details screen as shown below.
Note: In above example Trigger log is already created that’s why ‘Enable’ button is not editable. If log is not created, then ‘Enable’ will become available. Select enable and then follow below steps. It will open ‘Enable Transactional replication Wizard.
Here the system asks for the Schema value, as the File section is automatically populated
Then just hit Next and the system will create the objects. At this point the Master Log can be verified through the same menu by clicking on ‘Manage’.
Below is current system setting, click on manage and verify log.
3.6 Run Replication
Once replication setup is done, run the replication. The Enable Replication option must be checked. Go to Replication Browser -> select Replication -> Right click -> check Enable Replication.
Note: when replication is run for the first time, ensure both ‘Run Initial Refresh ‘ and ‘Enable Replication’ are checked.
You can run a replication in the following ways:
Interactively from the Windows Start menu.
Choose Start, then Programs, then Syniti Data Replication, then Service Monitor.
In the Windows Tool Tray, click the right mouse button over the Replication Agent icon
Choose Replication Agent, then Start, then either Service or Application
3.7 Review/ Manage Results
Replication results can be reviewed and managed via the Replication monitor.
3.8 DBMoto Script
C# or Visual Basic .NET is Script language in DBMoto. Default Script language is C#. However, Script language for a specific metadata can be changed in the Global Script Editor. Once Script language is changed then code become commented in all the scripts which were already saved before language change. Use Replication Script Editor to write a script.
Go to DMRC Metadata explorer, select replication for which you want to create Script. Then go to replication property and select ‘Use Script’ .
Click on ‘Script’ button, it open Replication script editor.
4.0 Reference Document
Comments
0 comments