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.
Enable access
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, apply the following procedure:
1. Run SQL Server Configuration Manager.
2. In the tree pane, click SQL Server Network Configuration to expand it, and then click Protocols for MyInstanceName.
If you specified the default instance during installation, the instance name will be MSSQLSERVER.
3. In the right pane, verify that, under the Status column, Enabled appears next to TCP/IP.
If Disabled appears, right-click TCP/IP, and then click Enable.
4. Right-click TCP/IP and select Properties.
Click on IP Addresses tab, scroll all the way down.
In section IPAll, enter 1433 for TCP Port, click Apply.
Click OK on the warning dialog box that pops up, and then click OK on the CP/IP Properties dialog box.
5. In the tree pane, click SQL Native Client Configuration to expand it, and then click Client Protocols.
In the right pane, verify that Enabled appears next to TCP/IP.
If Disabled appears, right-click TCP/IP, and then click Enable.
6. Right-click TCP/IP and select Properties. Verify that 1433 appears next to Default Port, and Yes appears next to Enabled.
Click OK to exit the TCP/IP Properties dialog box.
####### 7. In the tree pane, click SQL Server Services.
In the right pane, right-click SQL Server (MyInstanceName), and then click Restart.
Create user
Connect to your SQL Server database as an admin user and execute the following SQL commands to create a user for Fivetran. 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;
replacing <database> with the name of your database and <password> with a password of your choice.
Once user fivetran is created, grant it 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
SQL Server provides two built-in mechanisms that we use for replication: Change Tracking and Change Data Capture. When enabled, both mechanisms keep a record of the table rows that have changed for a window of time (the default being the most recent 2 days). These strategies enable us to copy only the rows that have changed since the last update, rather than having to copy the whole table every time. Choose to enable either Change Tracking or Change Data Capture.
Change Tracking
To turn on Change Tracking, first you need to enable it at the database level:
ALTER DATABASE [<database>] SET CHANGE_TRACKING = ON;
Next, you need to enable it for each table you would like to integrate:
ALTER TABLE [<schema>].[<table>] ENABLE CHANGE_TRACKING;
User fivetran will also need VIEW CHANGE TRACKING permission granted for each of the tables that have Change Tracking enabled:
GRANT VIEW CHANGE TRACKING ON [<schema>].[<table>] TO fivetran;
Please note that Change Tracking needs to be enabled at this step before continuing.
Change Data Capture
To turn on Change Data Capture, first you need to enable it at the database level:
USE [<database>];
EXEC sys.sp_cdc_enable_db;
Next, you need to enable it for each table you would like to integrate:
EXEC sys.sp_cdc_enable_table
@source_schema = [<schema>],
@source_name = [<table>],
@role_name = [<role>];
Note that 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 database 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 database.