Azure SQL Managed Instance Setup Guide

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

Prerequisiteslink

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

SQL Server version 2012 or above
Your database host’s IP (e.g., 1.2.3.4) or domain (e.g., your.server.com)
Your database’s port (usually 3342)

Choose a connection method link

Decide whether to connect your 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.

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

To connect directly, create a firewall rule to allow access to 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 on a virtual network.

To connect using SSH, create a firewall rule to allow access to your SSH tunnel server’s IP address.

Enable accesslink

Configure server firewalllink

Enable a public endpoint for a managed instance

Select the SQL managed instance on which you want to configure the public endpoint.
In the Security settings, select the Virtual network tab.
Set the Public endpoint toggle to Enable.
Click Save to update the configuration.
Copy the Host value. You will need it to fill in your Fivetran setup form.

Allow public endpoint traffic on the network security group

If you have the managed instance’s configuration page still open, navigate to the Overview tab. Otherwise, go back to your SQL managed instance resource.
Click the Virtual network/subnet link, which takes you to the virtual network configuration page.
In the left configuration pane of your virtual network, select the Subnets tab.
Note the SECURITY GROUP for your managed instance.
Return to the resource group that contains your managed instance. You should see the Network security group name you noted above.
Click the Network security group name to go to the network security group configuration page.
Select the Inbound security rules tab.
Add a new firewall rule.
- If you are connecting directly, use the following settings:
  - Source : Fivetran’s IP
  - Source Port Range : *
  - Destination : Any
  - Destination port ranges : 3342
  - Protocol : TCP
  - Action : Allow
  - Priority : Higher priority than the deny_all_inbound rule
- If you are connecting using an SSH tunnel, use the following settings:
  - Source : Your SSH tunnel server’s IP address
  - Source Port Range : *
  - Destination : Any
  - Destination port ranges : 3342
  - Protocol : TCP
  - Action : Allow
  - Priority : Higher priority than the deny_all_inbound rule
Save.

Enter host and port in setup formlink

In your Fivetran setup form, enter the host name and port number.

Host: The host you copied when you enabled the public endpoint.
Port: 3342

Create a Fivetran userlink

Execute the following command to add a container database user. 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 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>;

Enter user, password, and database in setup formlink

In your Fivetran setup form, enter your user, password, and database name.

For the User, enter <username>@<servername>, where <servername> is part of your Azure host URL: <servername>.database.windows.net
For the Password, enter the password you set when you created the Fivetran user.
For the Database, enter the database you want to replicate from.

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. To learn more about CT and CDC, see our updating data documentation.

Change tracking

First, enable change tracking at the database level:

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

Next, 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 <username>;
```

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

Change data capture

First, enable change tracking 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. 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.

Choose schema prefixlink

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.

Setup testslink

Fivetran performs the following tests to ensure that we can connect to your Azure SQL managed 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.

Prerequisiteslink

Choose a connection method link

Connect directly (TLS required)

Connect using SSH

Enable accesslink

Configure server firewalllink

Enable a public endpoint for a managed instance

Allow public endpoint traffic on the network security group

Enter host and port in setup formlink

Create a Fivetran userlink

Grant user permissionslink

Enter user, password, and database in setup formlink

Enable incremental updateslink

Change tracking

Change data capture

Choose schema prefixlink

Setup testslink

Related Contentlink

Didn’t find what you need?

Prerequisiteslink

Choose a connection method link

Connect directly (TLS required)

Connect using SSH

Enable accesslink

Configure server firewalllink

Enable a public endpoint for a managed instance

Allow public endpoint traffic on the network security group

Enter host and port in setup formlink

Create a Fivetran userlink

Grant user permissionslink

Enter user, password, and database in setup formlink

Enable incremental updateslink

Change tracking

Change data capture

Choose schema prefixlink

Setup testslink

Related Contentlink

Related articles

Didn’t find what you need?