Azure Synapse Setup Guide

Follow our setup guide to connect your Azure Synapse data warehouse as a destination to Fivetran.

Prerequisites

To connect Azure Synapse to Fivetran, you need the following:

• Permissions to create a user for Fivetran
• A Fivetran role with the Create Destinations or Manage Destinations permissions
• Permissions to add or change resource classes for user

IMPORTANT: Fivetran does not support Azure Synapse Serverless.

Setup instructions

Choose your deployment model

Before setting up your destination, decide which deployment model best suits your organization's requirements. This destination supports both SaaS and Hybrid deployment models, offering flexibility to meet diverse compliance and data governance needs.

See our Architecture documentation to understand the use cases of each model and choose the model that aligns with your security and operational requirements.

NOTE: You must have an Enterprise or Business Critical plan to use the Hybrid Deployment model.

Choose connection method

IMPORTANT: Do not perform this step if you want to use the Hybrid Deployment model for your data pipeline.

Decide whether to connect to your Azure Synapse data warehouse directly, using an SSH tunnel, or using Azure Private Link. For more information, see our destination connection options documentation.

Create warehouse

Create a dedicated warehouse using your Azure portal. Fivetran does not support Azure Synapse Serverless.

Find server details

1. Go to the Azure portal.

2. Click Azure Synapse Analytics.

3. Click on the SQL pool you created in Step 3.

4. In the Server name field, find the fully qualified server name and database name. Make a note of the names. You will need them to complete the destination setup in Fivetran.

Configure server-level firewall

Add Fivetran's IP address in your server-level firewall to allow incoming connections to your Azure Synapse data warehouse from Fivetran.

See Azure's documentation on how to create a server-level firewall rule for more information.

Create Fivetran user

1. Connect to your Azure Synapse data warehouse using SQL Server Management Studio or SQL Pro as an Admin user.

2. Execute the following SQL command to create a Fivetran user in the master database. In the SQL command, replace <username> with a username and <password> with a password of your choice.

   CREATE LOGIN <username> WITH PASSWORD = '<password>';
   

3. Switch to your data warehouse. Execute the following SQL commands to create a Fivetran user in the data warehouse:

   CREATE USER <username> FOR LOGIN <username>;
   

4. Execute the following command to grant the necessary permissions to the Fivetran user:

   GRANT CREATE TABLE, CREATE SCHEMA, SELECT, INSERT, ALTER, UPDATE, DELETE, ADMINISTER DATABASE BULK OPERATIONS TO <username>;
   

Add resource class

Add a suitable resource class to the Fivetran user depending upon the memory requirement for columnstore index creation. We recommend using static resource classes. You can start with the staticrc20 resource class that allocates 200 MB for the user irrespective of the performance level.

NOTE: Some connectors need higher resource class because of the greater number of columns. Higher volume of data requires more memory to create columnstore indexes. See Microsoft's documentation on memory and concurrency limits and resource classes for more information.

You must increase the allocated resource class, if the columnstore indexing fails with the current resource class. Execute the following command to increase the resource class. In the command, replace <username> with the username of the Fivetran user you created in Step 6.

 EXEC sp_addrolemember '<resource_class_name>', '<username>';

Configure external storage for Hybrid Deployment

IMPORTANT: Skip to the next step if you want to use Fivetran's cloud environment to sync your data. Perform this step only if you want to use Hybrid Deployment for your data pipeline.

In the Hybrid Deployment model, we temporarily stage your data in an external storage location before writing it to your Azure Synapse destination.

We use Azure Blob storage containers to stage your data.

Create Azure storage account

1. Create an Azure storage account by following the instructions in Azure Blob Storage's documentation. While creating the account, make sure you do the following:

   • In the Advanced tab:

     • select the Require secure transfer for REST API operations and Enable storage account key access checkboxes.
     • in the Permitted scope for copy operations drop-down menu, select From any storage account.

   • In the Networking tab, select one of the following Network access options:

     • If your Azure storage container and destination are in different regions, select Enable public access from all networks.
     • If your Azure destination is in the same region as your storage container, select Enable public access from selected virtual networks and IP addresses.

     IMPORTANT: Ensure the virtual network or subnet where your Azure destination resides is included in the allowed list for public access on the Azure storage account.

   • In the Encryption tab, choose Microsoft-managed keys (MMK) as the Encryption type.

2. If you selected Enable public access from selected virtual networks and IP addresses as the Network access option in Step 1, do the following:

   i. Log in to the Azure portal.

   ii. Go to your storage account.

   iii. On the navigation menu, click Networking under Security + networking.

   iv. Go to the Virtual networks section, Use the drop down to select virtual network or subnet where your Azure destination resides.

   v. In the Address range field, enter the IP address of the machine that hosts your Hybrid Deployment Agent.

   vi. Click Save.

Find storage account name and access key

1. Log in to the Azure portal.

2. Go to your storage account.

3. On the navigation menu, click Access keys under Security + networking.

   

4. Make a note of the Storage account name and Key. You will need them to configure Fivetran.

   

   IMPORTANT: As a security best practice, do not save your access key and account name anywhere in plain text that is accessible to others.

(Optional and not applicable to Hybrid Deployment) Azure Private Link

IMPORTANT: Do not perform this step if you want to use Hybrid Deployment for your data pipeline. You must have a Business Critical plan to use Azure Private Link.

Fivetran uses Private Link to move your data securely between our system and your Azure Synapse destination.

Complete Fivetran configuration

1. Log in to your Fivetran account.

2. Go to the Destinations page and click Add destination.

3. Enter a Destination name of your choice and then click Add.

4. Select Azure Synapse as the destination type.

5. (Enterprise and Business Critical accounts only) Select the deployment model of your choice:

   • SaaS Deployment
   • Hybrid Deployment

6. If you choose Hybrid Deployment, do the following:

   i. In the Select an existing agent drop-down menu, select an existing Hybrid Deployment Agent or configure a new agent.

   NOTE: For more information about configuring a new agent, see our Hybrid Deployment setup guides.

   ii. Enter the following details of the Azure Storage Container you created in Step 8:

   • Azure Storage Account Name

   • Azure Storage Account Key

7. In the Host field, enter one of the following values:

   • (Direct or SSH connections) The host name you found in Step 4
   • (Private Link connections) The private endpoint URL that you found in Azure

8. Enter the Port number.

9. Enter the User name of the Fivetran user you created in Step 6. The username must be in the <username>@<server_name> format, where <server_name> is part of your Azure host URL: <server_name>.database.windows.net.

10. Enter your Password.

11. Enter the Database name you found in Step 4.

12. (Not applicable to Hybrid Deployment) Choose your Connection method:

    • Connect directly
    • Connect via an SSH tunnel
    • Connect via Private Link

      NOTE: The Connect via Private Link option is only available for Business Critical accounts.

13. (Not applicable to Hybrid Deployment) If you choose Connect via an SSH tunnel, enter the following details:

    • SSH Host
    • SSH Port
    • SSH User

14. (Not applicable to Hybrid Deployment) Choose the Data processing location. Depending on the plan you are on and your selected cloud service provider, you may also need to choose a Cloud service provider and cloud region as described in our Destinations documentation.

15. Choose your Time zone.

16. (Optional for Business Critical accounts and not applicable to Hybrid Deployment) To enable regional failover, set the Use Failover toggle to ON, and then select your Failover Location and Failover Region. Make a note of the IP addresses of the secondary region and safelist these addresses in your firewall.

17. Click Save and Test.

Fivetran tests and validates the Azure Synapse connection. Upon successful completion of the setup tests, you can sync your data using Fivetran connectors to the Azure Synapse destination.

In addition, Fivetran automatically configures a Fivetran Platform Connector to transfer the connector logs and account metadata to a schema in this destination. The Fivetran Platform Connector enables you to monitor your connectors, track your usage, and audit changes. The connector sends all these details at the destination level.

IMPORTANT: If you are an Account Administrator, you can manually add the Fivetran Platform Connector on an account level so that it syncs all the metadata and logs for all the destinations in your account to a single destination. If an account-level Fivetran Platform Connector is already configured in a destination in your Fivetran account, then we don't add destination-level Fivetran Platform Connectors to the new destinations you create.

Setup tests

Fivetran performs the following Azure Synapse connection tests:

• The Database Host Connection Test validates the database credentials you provided in the setup form. The test verifies that the host is not private and then checks the connectivity to the host.

• The SSH Tunnel Test validates the SSH tunnel details you provided in the setup form and then checks the connectivity to the instance using the SSH Tunnel if you are connecting using an SSH tunnel.

• The UserName Has ServerName Test verifies the user details you provided in the setup form. The test checks if you have appended the server name to the user name. For example, fivetran@server_name.

• The Azure Warehouse Connection Test checks the connectivity to the data warehouse.

• The Azure Warehouse Type Test checks if you are using Microsoft Azure SQL Data Warehouse.

• The Azure Warehouse Permission Test checks if we have permissions to create schemas and tables on the database.

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

Related articles

description Destination Overview

assignment Release Notes

settings API Destination Configuration

home Documentation Home