Migrate and Replicate Data with Striim

On this page Carat arrow pointing down

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.

Note:

To minimize downtime for your migration, configure a separate continuous replication application before you deploy the initial load application. Once the initial load is complete, deploy the continuous replication 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:

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

See also


Yes No
On this page

Yes No