> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Migrate and Replicate Data with Qlik Replicate

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

[Qlik](https://www.qlik.com/) offers a service called Qlik Replicate that you can use to do the following:

* [Migrate data to CockroachDB](#migrate-and-replicate-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, Qlik supports the following database [sources](https://www.qlik.com/us/products/data-sources):

* Cassandra
* Couchbase
* DB2 for iSeries
* DB2 for LUW
* DB2 for z/OS
* HP Nonstop Enscribe (AIS)
* HP Nonstop SQL/MP (AIS)
* IBM Informix
* IMS/DB
* MariaDB
* Microsoft SQL Server
* MongoDB
* MySQL
* OpenVMS RMS
* Oracle
* Percona
* PostgreSQL
* SAP HANA
* SAP Sybase ASE
* Other via ODBC (with or without CDC)

This page describes the Qlik Replicate functionality at a high level. For detailed information, refer to the tutorial and documentation provided when [signing up for Qlik Replicate](https://www.qlik.com/us/products/qlik-replicate).

## Before you begin

Complete the following items before using Qlik Replicate:

* Ensure you have a secure, publicly available CockroachDB cluster running the latest **v23.2** <InternalLink version="releases" path="index">production release</InternalLink>, and have created a <InternalLink path="security-reference/authorization#sql-users">SQL user</InternalLink> that you can use for your Qlik Replicate target endpoint.
  * Set the following <InternalLink path="set-vars#supported-variables">session variables</InternalLink> using <InternalLink path="alter-role#set-default-session-variable-values-for-a-role">`ALTER ROLE... SET session variable`</InternalLink>:

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER ROLE {username} SET copy_from_retries_enabled = true;
    ```

    ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    ALTER ROLE {username} SET copy_from_atomic_enabled = false;
    ```

    This prevents a potential issue when migrating especially large tables with millions of rows.
* If you are migrating to a CockroachDB Cloud cluster and plan to [use replication as part of your migration strategy](#migrate-and-replicate-data-to-cockroachdb), you must first **disable** <InternalLink path="take-backups-with-revision-history-and-restore-from-a-point-in-time">revision history for cluster backups</InternalLink> for the migration to succeed.

<Danger>
  You will not be able to run a <InternalLink path="take-backups-with-revision-history-and-restore-from-a-point-in-time#point-in-time-restore">point-in-time restore</InternalLink> as long as revision history for cluster backups is disabled. Once you verify in the Qlik Replicate Monitor view that the migration succeeded, you should re-enable revision history.
</Danger>

* If the output of <InternalLink path="show-schedules">`SHOW SCHEDULES`</InternalLink> shows any backup schedules, run <InternalLink path="alter-backup-schedule">`ALTER BACKUP SCHEDULE {schedule_id} SET WITH revision_history = 'false'`</InternalLink> for each backup schedule.
* If the output of `SHOW SCHEDULES` does not show backup schedules, [contact Support](https://support.cockroachlabs.com/) to disable revision history for cluster backups.
* Manually create all schema objects in the target CockroachDB cluster. Qlik can create a basic schema, but does not create indexes or constraints such as foreign keys and defaults.
  * If you are migrating from PostgreSQL, MySQL, Oracle, or Microsoft SQL Server, <InternalLink version="cockroachcloud" path="migrations-page">use the **Schema Conversion Tool**</InternalLink> to convert and export your schema. Ensure that any schema changes are also reflected on your tables, or add transformation rules. If you make substantial schema changes, the Qlik Replicate migration may fail.

<Note>
  All tables must have an explicitly defined primary key. For more guidance, see <InternalLink version="molt" path="migration-strategy#schema-design-best-practices">Migration Strategy</InternalLink>.
</Note>

## Migrate and replicate data to CockroachDB

You can use Qlik Replicate 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 Qlik change data capture (CDC).

In the Qlik Replicate interface, the source database is configured as a **source endpoint** with the appropriate dialect, and CockroachDB is configured as a PostgreSQL **target endpoint**. For information about where to find the CockroachDB connection parameters, see <InternalLink path="connect-to-the-database">Connect to a CockroachDB Cluster</InternalLink>.

<Note>
  To use a CockroachDB Standard or Basic cluster as the target endpoint, set the **Database name** to `{host}.{database}` in the Qlik Replicate dialog. For details on how to find these parameters, see <InternalLink version="cockroachcloud" path="connect-to-your-cluster?filters=connection-parameters#connect-to-your-cluster">Connect to your cluster</InternalLink>. Also set **Secure Socket Layer (SSL) mode** to **require**.
</Note>

* To perform both an initial load and continuous replication of ongoing changes to the target tables, select **Full Load** and **Apply Changes**. This minimizes downtime for your migration.
* To perform a one-time migration to CockroachDB, select **Full Load** only.

To preserve the schema you manually created, select **TRUNCATE before loading** or **Do nothing**.

## See also

* <InternalLink version="molt" path="migration-overview">Migration Overview</InternalLink>
* <InternalLink version="cockroachcloud" path="migrations-page">Schema Conversion Tool</InternalLink>
* <InternalLink path="change-data-capture-overview">Change Data Capture Overview</InternalLink>
* <InternalLink path="third-party-database-tools">Third-Party Tools Supported by Cockroach Labs</InternalLink>
* <InternalLink path="aws-dms">Migrate with AWS Database Migration Service (DMS)</InternalLink>