The data integration mode of Data Transmission Service (DTS) allows you to schedule data migration tasks to run on a regular basis. This mode is typically used for recurring migrations in a large data warehouse system. For example, you can schedule a data migration that recurs every night and transfers the transactional data generated during the day to a data warehouse.

Features

  • Provides multiple orchestration methods that match your various use cases. For example, you can choose to migrate data to a new database every time or always to the same database.
  • For MySQL databases, hourly, daily, weekly, and monthly recurring migrations are supported. For other database types, daily, weekly, and monthly recurring migrations are supported.

Supported sources and targets

The supported source and target data stores are the same as the ones supported by the data migration mode. For more information, see Supported sources and targets.

Orchestration methods

Orchestration method Description Example use case
New database every time

For each occurrence of your migration task, DTS creates a new database in the target database system and replicates the selected schema objects and data objects into the new database. For each new database, DTS appends a timestamp-based suffix (_yyyymmdd_HH_mm_ss) to the name.

Note Using this orchestration method, DTS may take a large amount of storage in the target system. Therefore, you must make sure that the target system has enough storage for the data to be replicated.
You may need to replicate transactional data from a production system to multiple test systems on a regular basis.
Always to the same database For each occurrence of your migration task, DTS always replicates the selected schema objects and data objects into the same database. Between two occurrences, you must clear the schema objects and data objects in the target database. Otherwise, the next occurrence of the data integration task fails. You may need to replicate the transactional data from an operational database to an analytics system on a regular basis. This deployment is a useful option when you want to deploy near-real time replications between two databases that are not supported by the data synchronization mode of DTS.
Timestamp-based differentials

For each occurrence of your migration task, DTS replicates only the differentials over the last occurrence. The differentials are determined based on a timestamp column that you specify.

Note
  • This orchestration method supports only migrations between MySQL databases.
  • Each source table must have a timestamp column.
  • Each source table must not have foreign key constraints.
  • Updates that physically remove data objects, such as DELETE and TRUNCATE operations, cannot be replicated.
You may need to schedule a data migration that recurs every night and transfers the transactional data generated during the day to a data warehouse so that your analytics system can work on one-day-old data.