Get started with Azure Synapse Link for Azure SQL Database (Preview)

This article provides a step-by-step guide for getting started with Azure Synapse Link for Azure SQL Database. For more information, see Synapse Link for Azure SQL Database (Preview).

Important

Azure Synapse Link for SQL is currently in PREVIEW. See the Supplemental Terms of Use for Microsoft Azure Previews for legal terms that apply to Azure features that are in beta, preview, or otherwise not yet released into general availability.

Prerequisites

  • Create a new Synapse workspace to get Azure Synapse Link for SQL. Make sure to check "Disable Managed virtual network" and "Allow connections from all IP address" when creating Synapse workspace.

  • For DTU-based provisioning, make sure your Azure SQL Database service is at least Standard tier with a minimum of 100 DTUs. Free, Basic, or Standard tiers with fewer than 100 DTUs provisioned are not supported.

Configure your source Azure SQL Database

  1. Go to Azure portal, navigate to your Azure SQL Server, select Identity, and then set System assigned managed identity to On.

    Screenshot of turning on system assigned managed identity.

  2. Navigate to Networking, then check Allow Azure services and resources to access this server.

    Screenshot of configuring firewalls for your SQL DB using Azure portal.

  3. Using Microsoft SQL Server Management Studio (SSMS) or Azure Data Studio, connect to the Azure SQL Server. If you want to have your Synapse workspace connect to your Azure SQL Database using a managed identity, set the Azure Active Directory admin on Azure SQL Server, and use the same admin name to connect to Azure SQL Server with administrative privileges in order to have the privileges in step 5.

  4. Expand Databases, right select the database you created above, and select New Query.

    Select your database and create a new query.

  5. If you want to have your Synapse workspace connect to your source Azure SQL Database using a managed identity, run the following script to provide the managed identity permission to the source database.

    You can skip this step if you instead want to have your Synapse workspace connect to your source Azure SQL Database via SQL authentication.

    CREATE USER <workspace name> FROM EXTERNAL PROVIDER;
    ALTER ROLE [db_owner] ADD MEMBER <workspace name>;
    
  6. You can create a table with your own schema; the following is just an example for a CREATE TABLE query. You can also insert some rows into this table to ensure there's data to be replicated.

    CREATE TABLE myTestTable1 (c1 int primary key, c2 int, c3 nvarchar(50)) 
    

Create your target Synapse SQL pool

  1. Launch Synapse Studio.

  2. Open the Manage hub, navigate to SQL pools, and select + New.

    Create a new SQL dedicated pool from Synapse Studio.

  3. Enter a unique pool name, use the default settings, and create the dedicated pool.

  4. You need to create a schema if your expected schema is not available in target Synapse SQL database. If your schema is dbo, you can skip this step.

  1. Open the Integrate hub, and select + Link connection(Preview).

    Select a new link connection from Synapse Studio.

  2. Under Source linked service, select New.

    Select a new linked service.

  3. Enter the information for your source Azure SQL Database.

    • Select the subscription, server, and database corresponding to your Azure SQL Database.
    • If you wish to connect your Synapse workspace to the source DB using the workspace's managed identity, set Authentication type to Managed Identity.
    • If you wish to use SQL authentication instead and know the username/password to use, select SQL Authentication instead.

    Enter the server, database details to create a new linked service.

  4. Select Test connection to ensure the firewall rules are properly configured and the workspace can successfully connect to the source Azure SQL Database.

  5. Select Create.

    Note

    The linked service that you create here is not dedicated to Azure Synapse Link for SQL - it can be used by any workspace user that has the appropriate permissions. Please take time to understand the scope of users who may have access to this linked service and its credentials. For more information on permissions in Azure Synapse workspaces, see Azure Synapse workspace access control overview - Azure Synapse Analytics.

  6. Select one or more source tables to replicate to your Synapse workspace and select Continue.

    Note

    A given source table can only be enabled in at most one link connection at a time.

  7. Select a target Synapse SQL database and pool.

  8. Provide a name for your Azure Synapse Link connection, and select the number of cores. These cores will be used for the movement of data from the source to the target.

    Note

    We recommend starting low and increasing as needed.

  9. Select OK.

  10. With the new Azure Synapse Link connection open, you can update the target table name, distribution type and structure type.

    Note

    • Consider heap table for structure type when your data contains varchar(max), nvarchar(max), and varbinary(max).
    • Make sure the schema in your Synapse dedicated SQL pool has already been created before you start the link connection. Azure Synapse Link for SQL will create tables automatically under your schema in the Synapse dedicated SQL pool.

    Edit Azure Synapse Link connection from Synapse Studio.

  11. Select Publish all to save the new link connection to the service.

  1. Select Start and wait a few minutes for the data to be replicated.

    Note

    When being started, a link connection will start from a full initial load from your source database followed by incremental change feeds via the change feed feature in Azure SQL database. For more information, see Azure Synapse Link for SQL change feed.

You may monitor the status of your Azure Synapse Link connection, see which tables are being initially copied over (Snapshotting), and see which tables are in continuous replication mode (Replicating).

  1. Navigate to the Monitor hub, and select Link connections.

    Monitor the status of Azure Synapse Link connection from the monitor hub.

  2. Open the Azure Synapse Link connection you started and view the status of each table.

  3. Select Refresh on the monitoring view for your connection to observe any updates to the status.

Query replicated data

Wait for a few minutes, then check the target database has the expected table and data. You can also now explore the replicated tables in your target Synapse dedicated SQL pool.

  1. In the Data hub, under Workspace, open your target database, and within Tables, right-click one of your target tables.

  2. Choose New SQL script, then Select TOP 100 rows.

  3. Run this query to view the replicated data in your target Synapse dedicated SQL pool.

  4. You can also query the target database with SSMS (or other tools). Use the dedicated SQL endpoint for your workspace as the server name. This is typically <workspacename>.sql.azuresynapse.net. Add Database=databasename@poolname as another connection string parameter when connecting via SSMS (or other tools).

You can add/remove tables on Synapse Studio as following:

  1. Open the Integrate Hub.

  2. Select the Link connection you want to edit and open it.

  3. Select +New table to add tables on Synapse Studio or select the trash can icon to the right or a table to remove an existing table. You can add or remove tables when the link connection is running.

    Screenshot of link connection to add table.

    Note

    You can directly add or remove tables when a link connection is running.

You can stop the Azure Synapse Link connection in Synapse Studio as follows:

  1. Open the Integrate Hub of your Synapse workspace.

  2. Select the Link connection you want to edit and open it.

  3. Select Stop to stop the link connection, and it will stop replicating your data.

    Screenshot of link connection to stop link.

    Note

    If you restart a link connection after stopping it, it will start from a full initial load from your source database followed by incremental change feeds.

Next steps

If you are using a different type of database, see how to: