Azure SQL Database Setup Guide

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

Prerequisiteslink

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

• SQL Server version 2012 or above
• An Azure account with a DB Owner, SQL Server Contributor, or SQL Security Manager role
• ALTER ANY USER permissions in your database server
• Your database host’s IP (e.g., 1.2.3.4) or domain (e.g., your.server.com)
• Your database’s port (usually 1433)
• (If you want to connect using SSH) An SSH server

Setup instructionslink

Choose connection method link

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

Enable accesslink

Configure a server firewall to grant Fivetran’s data processing servers access to your database.

1. Log in to the Azure console.

2. On the Azure main page, select SQL databases.

3. Click on the SQL database that you want to connect to Fivetran.

   

4. On the database overview page, find the server name and make a note of it. You will need it to configure Fivetran.

   

5. Click Set server firewall.

   

6. On the Firewall settings page, add a new firewall rule with a memorable name (for example, Fivetran). What you enter in the IP fields depends on whether you’re connecting directly or using an SSH tunnel.

   • If you’re connecting directly, enter Fivetran’s IPs for your database’s region.
   • If you’re connecting using an SSH tunnel, enter your SSH tunnel server’s IP address.

   

7. Click Save.

Create userlink

1. Open a connection to your Azure SQL database.

2. Add a container database user for Fivetran by running the following command. Replace <database> with the name of your database, <username> with the username of your choice, and <password> with a password of your choice:

   USE [<database>];
   CREATE USER <username> WITH PASSWORD = '<password>'; 
   

Grant user permissionslink

Once you’ve created the Fivetran user, grant it SELECT permission for the database, 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 <username>;

or all tables in a given schema:

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

or a specific table:

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

or a set of specific columns in a table:

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

or all but a set of specific columns in a table:

GRANT SELECT ON [<schema>].[<table>] TO <username>;
DENY SELECT ON [<schema>].[<table>] ([<column X>], [<column Y>], ...) TO <username>;

Enable incremental updateslink

We use SQL Server’s built-in change tracking (CT) mechanism for incremental updates. When enabled, CT keeps a record of the table rows that have changed in a certain window of time (the default window is the most recent 2 days). This mechanism lets 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. To learn more about incremental updates, see our updating data documentation.

1. Enable change tracking at the database level:

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

2. Enable change tracking for each table you want to integrate:

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

3. 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 <username>;
   

Finish Fivetran configurationlink

1. 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.

2. In the Host field, enter your database host’s IP (for example, 1.2.3.4) or domain (for example, your.server.com).

3. Enter your database instance’s port number. The port number is usually 1433.

4. For the User, enter <username>@<servername>. The <servername> is the first part of the server name you found in Step 2 (for example, <servername>.database.windows.net).

5. For the Password, enter the password for the Fivetran-specific user that you created in Step 3.

6. For the Database, enter the name of the database you want to connect to Fivetran.

7. 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.

8. Click Save & Test. Fivetran tests and validates our connection to your Azure SQL database. Upon successful completion of the setup tests, you can sync your data using Fivetran.

Setup testslink

Fivetran performs the following tests to ensure that we can connect to your Azure SQL database and that it is properly configured:

• The Connecting to SSH Tunnel Test validates the SSH tunnel details you provided in the setup form. It then checks that we can connect to your database using the SSH Tunnel. (We skip this test if you aren’t connecting using SSH.)
• The Connecting to Host Test validates the database credentials you provided in the setup form. It then verifies that the database host is not private and checks that we can connect to the host.
• The Validating Certificate Test generates a pop-up window where you must choose which certificate you want Fivetran to use. It then validates that certificate and checks that we can connect to your database using TLS. (We skip this test if you aren’t connecting directly.)
• The Connecting to Database Test checks that we can access your database.
• The Checking Access to Schema Test checks that we have the correct permissions to access the schemas in your database. It then verifies that your database contains at least one table.
• The Validating Replication Config Test verifies that your database has an incremental update mechanism enabled (either CDC or CT).

NOTE: The tests may take a few minutes to finish running.

Related Contentlink

description Connector Overview

account_tree Schema Information

assignment Release Notes

settings API Connector Configuration

home Documentation Home