Azure Synapse — an Azure SQL Database Link connection

If you want to have your data placed inside a SQL Server/Azure SQL database to be ingested into Synapse SQL dedicated pool. You can create an ETL process and refresh the data with a schedule. If you use Synapse link connection, you can have your data ingested near instantly.

Let’s do some test. Go to Synapse studio and create a link connection.

As you see at the screen bellow, the table need to fulfill demanding like primary key existence.

Select a target pool.

Then select a compute capacity you want to use, and scheduling. If you want to have the data ingested in real time, select a continuous option. If you prefer to refresh data in a time range, select a schedule option.

Your link connection is present at a pipelines section.

A new table has been created inside the selected synapse SQL dedicated pool.

After new records are added into a source table. They are ingested into a synchronized Synapse table.

Here is a script which adds one record every one second into a source (an Azure SQL Database).

DECLARE @counter int = 0
DECLARE @Value varchar(50)
WHILE @counter < 20
BEGIN 
  SET @value = (SELECT CONCAT('Value', @counter))
  INSERT INTO dbo.popluation_synapse_link (County, Population)
  VALUES (@value, @counter)
  WAITFOR DELAY '00:00:01'
  SET @counter = @counter + 1
END;

After the script is run. New records are added near instantly.