Generic SQL Server Setup Guidelink

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

Prerequisiteslink

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

SQL Server 2012 or later
Your database host's IP (e.g., 1.2.3.4) or domain (your.server.com)
Your database's port (usually 1433)

Setup instructionslink

Choose connection method link

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

Connect directly (TLS required)

IMPORTANT: You must have TLS enabled on your database to connect directly to Fivetran. Follow Microsoft's TLS setup instructions to enable TLS on your database.

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 IPs for your database's region. How you do this will vary based on how your SQL Server database is hosted (cloud platform, on-premises, etc.)

Connect using SSH (TLS optional)

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, do the following:

In your connector setup form, select Connect via an SSH tunnel to expose Fivetran's public SSH key. Copy the key by clicking the blue clipboard icon:
Add the public key 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.
If you want Fivetran to tunnel SSH over TLS, follow Microsoft's TLS setup instructions to enable TLS on your database.

Allow TCP/IP protocollink

Verify that your database server is configured to allow TCP/IP connections. If your database instance does not have TCP/IP protocol enabled, do the following:

Open SQL Server Configuration Manager.
In the tree pane, click SQL Server Network Configuration to expand it.
Click Protocols for YourInstanceName. If you specified the default instance during installation, the instance name will be MSSQLSERVER.
In the Status column, verify that TCP/IP is Enabled.

TIP: 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 your database's port number (usually 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.

TIP: 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 (YourInstanceName), then click Restart.

Create userlink

Create a database user for Fivetran's exclusive use. The Fivetran user must be a SQL database user, not an Active Directory user.

Connect to your SQL Server database as an admin user.
Create a user for Fivetran by executing the following SQL commands. Choose a memorable name (for example, fivetran). Replace <database> with the name of your database and <password> with a password of your choice:
```
USE [<database>];
CREATE LOGIN fivetran WITH PASSWORD = '<password>';
CREATE USER fivetran FOR LOGIN fivetran;
```

Grant user permissionslink

Grant the Fivetran user SELECT permission for the databases, schemas, tables, or specific columns you want 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 updateslink

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. Depending on whether you are connecting Fivetran to your primary instance or an availability group replica, you may be limited in the mechanism you can choose. See our Supported Configurations documentation for more information.

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 CT 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 CT enabled:
```
GRANT VIEW CHANGE TRACKING ON [<schema>].[<table>] TO fivetran;
```

Change data capture

Enable change data capture at the database level:
```
USE [<database>];
EXEC sys.sp_cdc_enable_db;
```
Enable CDC for each table you want to Fivetran to sync:
```
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. Our syncs only include columns that are present in a CDC instance.

Finish Fivetran configurationlink

In your connector setup form, enter a destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.
In the Host field, enter your database host's IP (for example, 1.2.3.4) or domain (for example, your.server.com).
Enter your database instance's port number. The port number is usually 1433.
Enter the Fivetran-specific user that you created in Step 3 (for example, fivetran).
Enter the password for the Fivetran-specific user that you created in Step 3.
Enter the name of your database (for example, your_database).
Choose your connection method. If you selected Connect via an SSH tunnel, provide the following information:
- SSH hostname (do not use a load balancer's IP address/hostname)
- SSH port
- SSH user
- If you enabled TLS on your database in Step 1, set the Require TLS through tunnel toggle to ON.
Click Save & Test. Fivetran will take it from here and sync your data from your SQL Server database.

Documentation