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).
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
Go to Azure portal, navigate to your Azure SQL Server, select Identity, and then set System assigned managed identity to On.
Navigate to Networking, then check Allow Azure services and resources to access this server.
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.
Expand Databases, right select the database you created above, and select New Query.
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>;
You can create a table with your own schema; the following is just an example for a
CREATE TABLEquery. 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
Launch Synapse Studio.
Open the Manage hub, navigate to SQL pools, and select + New.
Enter a unique pool name, use the default settings, and create the dedicated pool.
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.
Create the Azure Synapse Link connection
Open the Integrate hub, and select + Link connection(Preview).
Under Source linked service, select New.
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.
Select Test connection to ensure the firewall rules are properly configured and the workspace can successfully connect to the source Azure SQL Database.
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.
Select one or more source tables to replicate to your Synapse workspace and select Continue.
A given source table can only be enabled in at most one link connection at a time.
Select a target Synapse SQL database and pool.
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.
We recommend starting low and increasing as needed.
With the new Azure Synapse Link connection open, you can update the target table name, distribution type and structure type.
- 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.
Select Publish all to save the new link connection to the service.
Start the Azure Synapse Link connection
Select Start and wait a few minutes for the data to be replicated.
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.
Monitor the status of the Azure Synapse Link connection
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).
Navigate to the Monitor hub, and select Link connections.
Open the Azure Synapse Link connection you started and view the status of each table.
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.
In the Data hub, under Workspace, open your target database, and within Tables, right-click one of your target tables.
Choose New SQL script, then Select TOP 100 rows.
Run this query to view the replicated data in your target Synapse dedicated SQL pool.
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
Database=databasename@poolnameas another connection string parameter when connecting via SSMS (or other tools).
Add/remove table in existing Azure Synapse Link connection
You can add/remove tables on Synapse Studio as following:
Open the Integrate Hub.
Select the Link connection you want to edit and open it.
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.
You can directly add or remove tables when a link connection is running.
Stop the Azure Synapse Link connection
You can stop the Azure Synapse Link connection in Synapse Studio as follows:
Open the Integrate Hub of your Synapse workspace.
Select the Link connection you want to edit and open it.
Select Stop to stop the link connection, and it will stop replicating your data.
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.
If you are using a different type of database, see how to:
Submit and view feedback for