Create a user:  Create a database user for Fivetran's exclusive use

          CREATE USER <username> PASSWORD 'some-password';

Grant user read-only access: To grant access to a schema other than PostgreSQL's default public schema, replace public with the schema name

GRANT USAGE ON SCHEMA "public" TO <username>;

GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO <username>;

ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO <username>;

• Go to your Amazon RDS PostgresSQL master database (For more details with screenshots refer to step #7 in this link)

• Create new parameter group

  • a. Enable logical_replication flag by setting value as 1

  • b. set wal_sender_timeout to 0 to disable timeout mechanism

• Apply the above-created parameter group on the database and reboot the database. Or, while creating database, you can apply the parameter group created with above configuration.

• Login into database console with superuser credentials. Superusers have  rds_superuser role

• Create a logical replication slot for the database you want to sync by running this command:

  • SELECT pg_create_logical_replication_slot('fivetran_replication_slot', 'pgoutput');

• Grant permission to fivetran user to read replication slot using below command.

  • GRANT rds_replication TO <username>; 

• Log in in as Fivetran user and verify that Fivetran user can read the replication slot

  • SELECT count(*) FROM pg_logical_slot_peek_changes('fivetran_replication_slot', null, null);

Note: For more details about above steps use this link.

• Host

• Port

• User

• Password

• Database

• Connection Method: Connect Directly

• Update Method: Logical replication of the WAL using the pgoutput plugin

• Replication Slot: <Replication Slot created in the previous steps>

Create User

Run the following queries

USE [<database>];

CREATE LOGIN <username> WITH PASSWORD = '<password>';

CREATE USER <username> FOR LOGIN <username>;

Grant access

Run the following queries:

GRANT SELECT on DATABASE::<database> to <username>; 

OR

GRANT SELECT on SCHEMA::<schema> to <username>;

Enable TLS

To enable TLS on your database, follow MSSQL TLS Configuration instructions.

Allow incoming connections to your MS SQL Server

Fivetran connects directly to your MSSQL. To connect directly, configure your firewall and/or other access control systems to allow incoming connections to your MSSQL from Fivetran’s IPs. For more information on this please see this article.

Enable Incremental Update

The incremental update mechanism is Change Data Capture (CDC)

Enable CDC at the database level:

EXEC msdb.dbo.rds_cdc_enable_db [<database>];

To enable CDC for each table you want to integrate:

EXEC sys.sp_cdc_enable_table  

@source_schema = [<schema>],

@source_name   = [<table>],

@role_name     = [<username>];

Note: Fivetran only supports tables with a single CDC capture instance. Our syncs only include tables and columns that are present in a CDC instance. If you add new tables or columns, you must create a new CDC instance that includes them and delete the old instance. For more info please read this article.

When adding a SQL Server data source in Clari studio, an admin must provide the information to the right in the connection/authentication screen for their source system.

• Host

• Port

• User

• Password

• Database 

In the same screen as mentioned above, an admin must to make the following selections.

Connection Method: Connect Directly

Project ID

This is the ID of your BigQuery project.

Specify whether customer will use own service account or not

In the connector configuration page, this toggle should be turned on, if customer wants to use own service account.

If this option is disabled, Fivetran’s service account will be used. In order to set it up, grant the BigQuery User role to the auto-generated email address that's displayed in the connector setup form.

Dataset name

This is the BigQuery schema that needs to be connected. Note that only one schema can be specified.