Replication -> Replicating Data between different DBMS

If a company uses only DBMS from a specific vendor, there are usually no problems with replicating data between databases. Nearly every manufacturer offers - usually free of charge - solutions that fulfil this purpose.

But: They can only work with their own products. Database systems of other manufacturers are generally not supported!

What to do if a company wants to replicate data to another DBMS, and maybe even back?

Image Replication

Other solutions are needed here. The ability to operate with different database systems is based on the connectivity capabilities and possibilities of such a solution.

Over the decades technologies have emerged on the market that can connect to any SQL database:

  • ODBC
  • JDBC
  • .NET Provider

Native drivers, on the other hand, are usually used by the in-house tools of the respective manufacturers. Most of them are highly optimized for their own product, fast and the best choice for replicating data between databases of this manufacturer, provided that the functionality of the tool covers the requirements of the desired replication.

But if different DBMSs are involved, the question arises:
What is the structure of the participating databases? Are they structured in the same way (same table names as well as the same field names and data types) or are there differences?
Better replication tools of course allow for variations in the names, although there should be equality or compatibility with the field data types, as otherwise there will be hardly any solvable detailed problems.

Modern replication solutions support the user already during preparation. They are able to scan the provided databases automatically and present the user with a preselection of the tables to be used. Of course, the fewer structural deviations that occur for database tables and columns, the easier it is to do this.

Image Another Replication

Cutting-edge systems go one step further and scan not only the names and data types of the database tables and fields, but also their interdependencies. They sort the tables in the correct sequence required for the referential integrity between them when they execute the replication.

Once these qualitative aspects have been resolved, the question arises about the quantities:
Do I replicate all the contents of a database, such as in a complete online backup, or do I need filters when selecting the source data?

For this case replication solutions should be able to offer a flexible, table and field-level filtering capability.

Since some DBMS may need to take certain characteristics into consideration, the possibility of executing freely definable SQL statements (scripts) before, during and after a replication is certainly an advantage and of interest.

Many replications take place at night, because then the traffic on the databases is usually lowest.
Most operating systems offer to start programs at a certain time (scheduler). However, this feature should be used with caution, since a schedule that has been set up will continue to run until it is deactivated. This can cause problems if a replication is not to be executed cyclically. Some replication solutions offer additional security mechanisms, such as the explicit clearance of a single replication task, as well as the clearance of the replication solution itself for unattended execution. In this way, the security level can be increased to avoid unintentional replications.

In summary, the functionalities and the usability of modern replication solutions have clearly evolved to the benefit of the user, whereby modern systems are affordable and do not tear a deep hole in the department's cash register.
The time saved through the increasing implementation of automatisms in modern replication solutions also improves the profitability of such programs through shorter amortization times.
The use of graphical user interfaces when setting up a replication also does a great deal in terms of user-friendliness.