Azure SQL Managed Instance Setup Guide

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 a public endpoint for a managed instance

1. Select the SQL managed instance on which you want to configure the public endpoint.

2. In the Security settings, select the Virtual network tab.

3. Set the Public endpoint toggle to Enable.

   

4. Click Save to update the configuration.

5. Copy the Host value. You will need it to fill in your Fivetran setup form.

Allow public endpoint traffic on the network security group

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

2. Click the Virtual network/subnet link, which takes you to the virtual network configuration page.

   

3. In the left configuration pane of your virtual network, select the Subnets tab.

4. Note the SECURITY GROUP for your managed instance.

   

5. Return to the resource group that contains your managed instance. You should see the Network security group name you noted above.

6. Click the Network security group name to go to the network security group configuration page.

7. Select the Inbound security rules tab.

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

9. Save.

Change tracking

1. First, enable change tracking at the database level:

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

2. Next, enable it 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>;
   

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

Change data capture

1. First, enable change tracking at the database level:

   USE [<database>];
   EXEC sys.sp_cdc_enable_db;
   

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