Azure Synapse Link for SQL (Preview)

One of the key announcements in Microsoft Build 2022 was the Microsoft Intelligent Data Platform that accelerates innovation and build on a trusted, integrated platform with hybrid and multi-cloud capabilities. Amongst many capabilities under this umbrella, lies ‘Synapse Link for SQL’ in PREVIEW that enables near real-time insights on data stored in SQL Server 2022 and Azure SQL.

What is Synapse Link for SQL (Preview)?

Synapse Link for SQL enables seamless integration of operational data hosted on SQL Server 2022 or Azure SQL database. It allows team to run analytics, business intelligence and machine learning use cases at limitless scale, without any impact on the transactional database running on SQL Server 2022 or Azure SQL

This capability facilitates Hybrid Transactional and Analytical Processing (HTAP) and ensures:

• Transactional databases are not impacted as the new change feed feature in SQL Server 2022 and Azure SQL automatically identifies the delta/incremental records and seamlessly copies them over to Azure Synapse analytics without any degradation in source database performance
• With few configuration level setup, there is very little need to develop data pipelines that can handle both full and incremental data extraction for analytical use cases. New tables can be added and tables can be removed with minimal clicks
• Business can draw insights from the operational data in near real-time with minimal/no extra development efforts

It’s time to see this in action. In this demo we shall use Azure SQL DB as the source instance to extract and copy data into Azure Synapse Analytics using Synapse Link for SQL

Pre-requisites:

The following services should be deployed in advance:

• Azure SQL DB

1. Please ensure system assigned managed identity is ON
2. Allow Azure services and resources to access this server is enabled under Networking
3. vCore purchase model is selected

• Azure Synapse Analytics workspace
• A dedicated SQL Pool on the deployed Azure Synapse Analytics workspace

Steps:

• Open Synapse Studio from the workspace overview page
• Go to Manage hub and create a linked service for Azure SQL

• Go to the Integrate hub and click on + and add Link Connection (Preview)

• From the source database, select Azure SQL and select all the tables we are interested to replicate

• Select the target dedicated SQL pool where the tables will get replicated

• Provide a name to the Synapse Link Connection and select the number of cores. This is the compute infrastructure enables the data transfer from Azure SQL into Azure Synapse dedicated SQL endpoint
• In the next screen, map the tables between source and target

• Before the link is started, ensure to Publish all the changes to take effect.
• Take a snapshot of the source and target inventory before starting the link

Source - Azure SQL

Destination - Dedicated SQL Pool

• Click ‘Start’ to begin the replication process. This will take a few minutes to get started
• Once started, check the dedicated SQL pool and watch the tables being created one by one

• After we have checked the replicated data across tables, let’s run a check to see how fast incremental/delta updates on Azure SQL is reflected on Azure Synapse Analytics through the Synapse Link for SQL. I have updated few records in the SalesLT.ProductDescription hosted on the Azure SQL instance

• All these updates are replicated into the target table on Synapse almost near real-time

The change feed feature automatically identifies delta updates to the operational data and make it available for advance analytics on Azure Synapse. And this has been made possible with Synapse Link for SQL. This feature is in Preview, can’t wait for this to become GA.

Do try this out !

Key References:

1. What is Azure Synapse Link for SQL? (Preview) - Azure Synapse Analytics | Microsoft Docs
2. Azure Synapse Link for Azure Cosmos DB, benefits, and when to use it | Microsoft Docs
3. Known limitations and issues with Azure Synapse Link for SQL (Preview) - Azure Synapse Analytics | Microsoft Docs