SQL Server Setup Guide

Follow these instructions to replicate your generic SQL Server database to your destination via Fivetran.

Prerequisites

To connect your SQL Server database to Fivetran, you need:

SQL Server version 2012 or above
IP (e.g. 1.2.3.4) or host (your.server.com)
Port (usually 1433)

Choose a connection method

Decide whether to connect your SQL Server database directly or using an SSH tunnel.

Connect directly

Fivetran connects directly to your database instance. This is the simplest and most secure connection method.

To connect directly, configure your firewall and/or other access control systems to allow incoming connections to your SQL server host and port (usually 1433) from Fivetran's IP.

Connect using SSH

Fivetran connects to a separate server in your network that provides an SSH tunnel to your database. You must connect through SSH if your database is in an inaccessible subnet.

To connect using SSH, add the Fivetran public SSH key from the connector setup form to the authorized_keys file of your SSH server. The key must be all on one line, so make sure that you don't introduce any line breaks when cutting and pasting.

Allow TCP/IP protocol

Verify that your SQL server is configured to allow TCP/IP connections. If TCP/IP protocol is not enabled for your database instance, follow these steps:

Open SQL Server Configuration Manager.
In the tree pane, click SQL Server Network Configuration to expand it, then click Protocols for MyInstanceName.

If you specified the default instance during installation, the instance name will be MSSQLSERVER.
In the Status column, verify that Enabled appears next to TCP/IP.

If Disabled appears, right-click TCP/IP, then click Enable.
Right-click TCP/IP, then select Properties.
Go to the IP Addresses tab and scroll all the way down.
In the IPAll section, enter 1433 for the TCP Port, then click Apply.
Click OK in the warning dialog box that pops up.
Click OK in the CP/IP Properties dialog box.
In the tree pane, click SQL Native Client Configuration to expand it, then click Client Protocols.
In the right-hand column, verify that Enabled appears next to TCP/IP.

If Disabled appears, right-click TCP/IP, then click Enable.
Right-click TCP/IP, then select Properties.
Verify that the Default Port is 1433 and that Yes appears next to Enabled.
Click OK to exit the TCP/IP Properties dialog box.
In the tree pane, click SQL Server Services.
In the right pane, right-click SQL Server (MyInstanceName), then click Restart.

Create user

Connect to your SQL Server database as an admin user.
Execute the following SQL commands to create a user for Fivetran. Replace <database> with the name of your database and <password> with a password of your choice:

Note: This must be a SQL database user and not an Active Directory login.

USE [<database>];
CREATE LOGIN fivetran WITH PASSWORD = '<password>';
CREATE USER fivetran FOR LOGIN fivetran;

Grant user permissions

Grant the fivetran user SELECT permission for the set of databases, schemas, tables, or specific columns you would like Fivetran to sync.

You can grant access to everything in a given database:

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

or all tables in a given schema:

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

or a specific table:

GRANT SELECT ON [<schema>].[<table>] TO fivetran;

or a set of specific columns in a table:

GRANT SELECT ON [<schema>].[<table>] ([<column 1>], [<column 2>], ...) TO fivetran;

Enable incremental updates

We use one of SQL Server's two built-in tracking mechanisms for incremental updates: change tracking (CT) and change data capture (CDC). When enabled, both CT and CDC keep a record of the table rows that have changed in a certain window of time (the default window is the most recent 2 days). These mechanisms let Fivetran copy only the rows that have changed since the last data sync so we don't have to copy the whole table every time.

Choose to enable either change tracking or change data capture. To learn more about CT and CDC, see our updating data documentation.

Change tracking

Enable change tracking at the database level:

ALTER DATABASE [<database>] SET CHANGE_TRACKING = ON;

Enable it for each table you want to integrate:

ALTER TABLE [<schema>].[<table>] ENABLE CHANGE_TRACKING;

Grant the fivetran user VIEW CHANGE TRACKING permission for each of the tables that have change tracking enabled:
```
GRANT VIEW CHANGE TRACKING ON [<schema>].[<table>] TO fivetran;
```

Change tracking needs to be enabled at this step before continuing.

Change data capture

First, enable change data capture at the database level:
```
USE [<database>];
EXEC sys.sp_cdc_enable_db;
```

Next, enable it for each table you want to integrate:

EXEC sys.sp_cdc_enable_table  
@source_schema = [<schema>],
@source_name   = [<table>],
@role_name     = [<role>];

Note: Fivetran only supports tables with a single CDC capture instance that includes every column.

Choose schema prefix

Each database from your source will be mapped to a schema in the destination by adding a prefix to the source database name. For example, if your source database names are "foo" and "bar" and if you choose the prefix "source1", then you will get schemas "source1_foo" and "source1_bar" in the destination.

Connector Overview

Schema Information

Release Notes

API Connector Configuration

Documentation Home