Migrate and Replicate Data with Striim

Striim offers a managed service and a self-hosted platform that you can use to do the following:

• Migrate data to CockroachDB from an existing, publicly hosted database containing application data, such as PostgreSQL, MySQL, Oracle, or Microsoft SQL Server.

As of this writing, Striim supports the following database sources:

• HP NonStop SQL/MX
• MariaDB
• MariaDB Galera Cluster
• MySQL
• Oracle
• PostgreSQL
• SQL Server
• Sybase
• Teradata

This page describes the Striim functionality at a high level. For detailed information, refer to the Striim documentation.

Before you begin

Complete the following items before using Striim:

• Ensure you have a secure, publicly available CockroachDB cluster running the latest v22.2 production release, and have created a SQL user that you can use to configure your Striim target.

• Manually create all schema objects in the target CockroachDB cluster. Although Striim offers a feature called Auto Schema Conversion, we recommend converting and importing your schema before running Striim to ensure that the data populates successfully.

  • If you are migrating from PostgreSQL, MySQL, Oracle, or Microsoft SQL Server, use the Schema Conversion Tool to convert and export your schema. Ensure that any schema changes are also reflected on your tables.
  Note:

  All tables must have an explicitly defined primary key. For more guidance, see the Migration Overview.

Migrate and replicate data to CockroachDB

You can use Striim to migrate tables from a source database to CockroachDB. This can comprise an initial load that copies the selected schemas and their data from the source database to CockroachDB, followed by continuous replication of ongoing changes using Striim change data capture (CDC).

Initial load

To perform the initial load, create a Striim application and configure the source database using one of the Initial Load sources. Configure CockroachDB as a PostgreSQL target. For information about where to find the CockroachDB connection parameters, see Connect to a CockroachDB Cluster. Do the following before deploying the application:

• Specify the Connection URL in JDBC format while appending the reWriteBatchedInserts=true property, and without specifying the username. For example:

  jdbc:postgresql://{host}:{port}/{database}?password={password}&sslmode=verify-full&reWriteBatchedInserts=true
  

• After creating the target, export the application and add the field _h_ConnectionRetryCode: '40001' to the TQL file. For example:

  CREATE OR REPLACE TARGET cockroach USING Global.DatabaseWriter ( 
    ConnectionRetryPolicy: 'retryInterval=30, maxRetries=3', 
    ParallelThreads: '5', 
    CheckPointTable: 'CHKPOINT', 
    BatchPolicy: 'EventCount:128,Interval:60', 
    Password_encrypted: 'true', 
    StatementCacheSize: '4', 
    CDDLAction: 'Process', 
    Password: 'xxxxx', 
    Tables: 'public.test_table_large,public.test_table_large', 
    CommitPolicy: 'EventCount:128,Interval:60', 
    DatabaseProviderType: 'Postgres', 
    PreserveSourceTransactionBoundary: 'false', 
    ConnectionURL: 'jdbc:postgresql://{host}:{port}/{database}?password={password}&sslmode=verify-full&reWriteBatchedInserts=true', 
    Username: 'root', 
    _h_ConnectionRetryCode: '40001', 
    adapterName: 'DatabaseWriter' ) 
  

  Then import the modified TQL file to create a new application.

Deploy this application to perform the initial load of data to CockroachDB. Remember that you should have already created the schema objects on CockroachDB.

Continuous replication

To perform continuous replication of ongoing changes, create another Striim application and configure the source database using one of the CDC sources. Configure CockroachDB as a PostgreSQL target. For information about where to find the CockroachDB connection parameters, see Connect to a CockroachDB Cluster. Do the following before deploying the application:

• Configure the CDC reader as described in the Striim documentation.

• Set up your source database for continuous replication as described in the Striim for BigQuery documentation.

• Repeat the guidance for creating the initial load application:

  • When configuring CockroachDB as a target, specify the Connection URL in JDBC format while appending the reWriteBatchedInserts=true property, and without specifying the username.
  • After creating the target, export the application and add the field _h_ConnectionRetryCode: '40001' to the TQL file. Then import the modified TQL file to create a new application.

Deploy this application once the initial load application has finished running.

See also

• Migration Overview
• Schema Conversion Tool
• Change Data Capture Overview
• Third-Party Tools Supported by Cockroach Labs