Replicating Data to Amazon Redshift

Syniti Data Replication supports use of Amazon Redshift for:

  • Refresh replications, both source and target
  • Mirroring replications as a target.

With Syniti Data Replication, you can replicate relational data to Amazon Redshift.

To define a target connection for Amazon Redshift:

  1. Make sure that you have downloaded and installed the Amazon Redshift ODBC driver. For information on where to obtain the ODBC driver, check the Supported .NET Providers article in the Help Center. The Help Center also provides database-specific setup notes in PDF format..

  2. In the Metadata Explorer, choose Targets, then Add New Connection from the right mouse button.

  3. In the Add Target Connection wizard, type a name for the connection and choose Amazon Redshift in the Database field.

  4. In the Set Connection String screen, click in the Connection String value field, and then on the ellipsis, to open the ODBC Driver Connection Dialog.
    Values entered in this dialog should reflect those needed to make a successful connection to an Amazon Redshift cluster via an ODBC connection string, with or without Syniti Data Replication. There are no fields specific to Syniti DR.

  5. Choose values for the following fields:

    Server Specify the Amazon Redshift cluster endpoint, as defined in the Amazon Redshift console.

    Port Type the port number for the database. The default is 5439..

    Database Type the name of the Amazon Redshift database.

    Auth TypeSyniti DR has been tested using the Standard authentication type, with an AWS user and password. However, Syniti DR uses the values entered here as part of an ODBC connection string passed to the server. Therefore, any set of values that can connect to the server are supported. Check the AWS documentation for all the connection options.

  6. In the Set Staging Connection String screen, enter the connection information for Amazon S3 used to bulk insert data into Redshift.
    NOTE: Use the Connection Properties dialog to change the bulk insert settings, if needed.

    Output Folder Local path to temporarily store replication output files
    Output folder Archive (Optional) Set a path to archive files that are generated and uploaded.
    Use IAM Role

    Set to False by default. Set to True to use an IAM role for connections instead of the Access Key/Secret Key approach. For more information about using IAM roles, see https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles.html.

    Note that IAM roles work only for access between AWS services (such as when using S3 as a staging connection for bulk uploads to Redshift.)
    IAM Role Parameter

    The IAM role parameter should be configured as follows to access S3:

    arn:aws:iam:<aws-account-id>:role/<role-name>

    The parameter information is required when using S3 in conjunction with Redshift. It is used in the COPY from statement to access files you imported from S3 to Redshift. For more information about using IAM roles with Redshift, see https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html
    S3 Access Key The Access Key from your AWS login ID. Not required if Use IAM Role is set to true.
    S3 Secret Key The Secret Access Key from your AWS login ID. Not required if Use IAM Role is set to true.
    S3 Bucket Name The bucket must already exist. It can either be a bucket that you are already using, or one you have created for this purpose.
    AWS SDK S3 Assembly Path The pathname to AWSSDK.S3.dll installed with the AWS .NET SDK.
    AWS SDK Core Assembly Path The pathname to the AWSSDK.Core.dll installed with the AWS .NET SDK.
    Create Manifest File When set to true, creates a JSON manifest file which is sometimes required by data-sharing applications.

  7. Complete the fields in the Target Connection wizard. Use the Syniti Data ReplicationUser Guide available from the Management Center Help menu or the Setup Guide, available for download in the Help Center as needed.

  8. Click OK to close the ODBC Driver Connection dialog.

  9. In the Select Tables screen, if tables already exist, choose the tables to use in replication. Note that you can also create tables, or add them at a later time.

  10. Click Next to display the Actions screen.

  11. Optional. Select the next wizard that you would like to run.

  12. Click Next to display the summary, then click Finish to create the connection.

Now you can set up replications from whichever source connection you have defined.

Further Resources