L13-Use Azure Synapse Link for SQL

Harini Mallawaarachchi
Dec 21, 2023
1 min read

Azure Synapse Link for SQL enables you to automatically synchronize a transactional database in SQL Server or Azure SQL Database with a dedicated SQL pool in Azure Synapse Analytics. This synchronization enables you to perform low-latency analytical workloads in Synapse Analytics without incurring query overhead in the source operational database.

Before you start

You'll need an Azure subscription in which you have administrative-level access.

Review the What is Azure Synapse Link for SQL? article in the Azure Synapse Analytics documentation.

Configure Azure SQL Database

Before you can set up Azure Synapse Link for your Azure SQL Database, you must ensure that the required configuration settings have been applied in your Azure SQL Database server.

In the Azure portal, browse to the dp203-xxxxxxx resource group that was created by the setup script, and select your sqldbxxxxxxxx Azure SQL server.

Note: be careful not to mix up the Azure SQL server resource sqldbxxxxxxxx) and the Azure Synapse Analytics dedicated SQL pool (sqlxxxxxxxx).

In the page for your Azure SQL Server resource, in the pane on the left, in the Security section (near the bottom), select Identity. Then under System assigned managed identity, set the Status option to On. Then use the 🖫 Save icon to save your configuration change.

System assigned managed identity

A system assigned managed identity enables Azure resources to authenticate to cloud services (e.g. Azure Key Vault) without storing credentials in code. Once enabled, all necessary permissions can be granted via Azure role-based-access-control. The lifecycle of this type of managed identity is tied to the lifecycle of this resource. Additionally, each resource (e.g. Virtual Machine) can only have one system assigned managed identity. Learn more about Managed identities.

In the pane on the left, in the Security section, select Networking. Then, under Firewall rules, select the exception to Allow Azure services and resources to access this server.

Use the ＋ Add a firewall rule button to add a new firewall rule with the following settings:

Note: This rule allows access to your server from any Internet-connected computer. We're enabling this to simplify the exercise, but in a production scenario you should restrict access to only network addresses that need to use your databases.

Use the Save button to save your configuration change:

Explore the transactional database

Your Azure SQL server hosts a sample database named AdventureWorksLT. This database represents a transactional database used for operational application data.

In the Overview page for your Azure SQL server, at the bottom of the, select the AdventureWorksLT database.

In the AdventureWorksLT database page, select the Query editor tab and log in using SQL server authentication with the following credentials:

Login SQLUser
Password: The password you specified when running the setup script.

When the query editor opens, expand the Tables node and view the list of tables in the database. Note that they include tables in a SalesLT schema (for example, SalesLT.Customer).

Configure Azure Synapse Link

Now you're ready to configure Azure Synapse Link for SQL in your Synapse Analytics workspace.

Start the dedicated SQL pool

In the Azure portal, close the query editor for your Azure SQL database (discarding any changes) and return to the page for your dp203-xxxxxxx resource group.
Open the synapsexxxxxxx Synapse workspace, and on its Overview page, in the Open Synapse Studio card, select Open to open Synapse Studio in a new browser tab; signing in if prompted.
On the left side of Synapse Studio, use the ›› icon to expand the menu - this reveals the different pages within Synapse Studio.
On the Manage page, on the SQL pools tab, select the row for the sqlxxxxxxx dedicated SQL pool and use its ▷ icon to start it; confirming that you want to resume it when prompted.

Wait for the SQL pool to resume. This can take a few minutes. You can use the ↻ Refresh button to check its status periodically. The status will show as Online when it is ready.

Create the target schema

In Synapse Studio, on the Data page, on the Workspace tab, expand SQL databases and select your sqlxxxxxxx database.
In the ... menu for the sqlxxxxxxx database, select New SQL script > Empty script.

In the SQL Script 1 pane, enter the following SQL code and use the ▷ Run button to run it.

CREATE SCHEMA SalesLT; 
GO

Wait for the query to complete successfully. This code creates a schema named SalesLT in the database for your dedicated SQL pool, enabling you to synchronize tables in the schema of that name from your Azure SQL database.

Create a link connection

In Synapse Studio, on the Integrate page, on the ＋ drop-down menu, select Link connection. Then create a new linked connection with the following settings:

Source type: Azure SQL database
Source linked service: Add a new linked service with the following settings (a new tab will be opened):
Name: SqlAdventureWorksLT
Description: Connection to AdventureWorksLT database
Connect via integration runtime: AutoResolveIntegrationRuntime
Connection String: Selected
From Azure subscription: Selected
Azure subscription: Select your Azure subscription
Server name: Select your sqldbxxxxxxx Azure SQL server
Database name: AdventureWorksLT
Authentication type: SQL authentication
User name: SQLUser
Password: The password you set when running the setup script

Source tables: Select the following tables:
SalesLT.Customer
SalesLT.Product
SalesLT.SalesOrderDetail
SalesLT.SalesOrderHeader

Continue to configure the following settings:

Note: Some target tables display an error due to the use of custom data types or because data in the source table is not compatible with the default structure type of clustered columnstore index.

Target pool: Select your sqlxxxxxxx dedicated SQL pool
Link connection name: sql-adventureworkslt-conn
Core count: 4 (+ 4 Driver cores)

In the sql-adventureworkslt-conn page that is created, view the table mappings that have been created. You can use the Properties button (which looks similar to 🗏*) to hide the Properties pane to make it easier to see eveything.
Modify thes structure types in the table mappings as follows:

At the top of the sql-adventureworkslt-conn page that is created, use the ▷ Start button to start synchronization. When prompted, select OK to publish and start the link connection.
After starting the connection, on the Monitor page, view the Link connections tab and select the sql-adventureworkslt-conn connection. You can use the ↻ Refresh button to update the status periodically. It may take several minutes to complete the initial snapshot copy process and start replicating - after that, all changes in the source database tables will be automatically replayed in the synchronized tables.

View the replicated data

After the status of the tables has changed to Running, select the Data page and use the ↻ icon at the top right to refresh the view.
On the Workspace tab, expand SQL databases, your sqlxxxxxxx database, and its Tables folder to view the replicated tables.
In the ... menu for the sqlxxxxxxx database, select New SQL script > Empty script. Then in the new script page, enter the following SQL code:

SELECT  oh.SalesOrderID, oh.OrderDate,
        p.ProductNumber, p.Color, p.Size,
        c.EmailAddress AS CustomerEmail,
        od.OrderQty, od.UnitPrice
FROM SalesLT.SalesOrderHeader AS oh
JOIN SalesLT.SalesOrderDetail AS od 
    ON oh.SalesOrderID = od.SalesOrderID
JOIN  SalesLT.Product AS p 
    ON od.ProductID = p.ProductID
JOIN SalesLT.Customer as c
    ON oh.CustomerID = c.CustomerID
ORDER BY oh.SalesOrderID;

Use the ▷ Run button to run the script and view the results. The query is run against the replicated tables in the dedicated SQL pool and not the source database, enabling you to run analytical queries without impacting business applications.

When you're done, on the Manage page, pause the sqlxxxxxxx dedicated SQL pool.