Event Trigger Data Sync from SQL Server to Synapse via Azure Data Factory / Synapse Pipeline

Problem Statement :

Is it possible to instantaneously sync data from SQL server to Synapse in case of any data changes (Insert / Update /Delete) within a table.

Prerequisites :

SQL Server ( On Prem SQL Server / IaaS SQL Server / SQL MI)

Any SQL server wherein we can create a Linked server (So Azure SQL Servers are excluded)

2. Synapse / Azure SQL Datawarehouse (Dedicated Pool)

3. Azure Blob Storage

Solution :

Create a Linked server in the SQL server database instance with Synapse / SQL Dedicated pool.

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'EventLS', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'<<ServerName>>.database.windows.net', @catalog=N'<<SynapseName>>'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EventLS',@useself=N'False',@locallogin=NULL,@rmtuser=N'<<SQLUserName>>',@rmtpassword='<<SQLCreds>>'
GO

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO

2. Establish an External data source within Synapse to Azure Blob Storage and create an External file format

The SQL for the above commands are available at this GitHub location.

3. The purpose behind #2 is for us to leverage this in the CETAS creation which in turn would generate a file in the Azure blob storage which we can leverage for Blob trigger purpose. So next action is to create a Stored procedure in Synapse which can be triggered from the SQL server via Linked server created in #1.

4. Create a Copy activity Pipeline with SQL server as Source and Dedicated SQL Pool / Synapse as Sink with Blob Event type trigger

Note: The Blob path begins is equivalent to the Location specified in CETAS script in #3

5. The next aspect would be to set up Trigger in the SQL server for the table which needs Event sync set up nd execute the Stored procedure present in Synapse created in #3.

So the set up is established which would flow in the below order whenever any Data changes (Insert / Update / Delete) happens within the SQL server table :

1) SQL Trigger activated => 2) Synapse Stored Procedure Executed => 3) External Table creation (CETAS) => 4) File Creation in Blob => 5) ADF/Synapse Event Trigger activated => 6) ADF/Synapse Copy activity Pipeline triggered

6. Couple of Linked Server configurations aspects :

a) Enabling RPC OUT Property : It enables Remote Procedure Calls to the specified server (in this case Synapse)

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'rpc out', @optvalue=N'true'

If this Property is false :

b) Disabling remote proc transaction promotion property

EXEC master.dbo.sp_serveroption @server=N'EventLS', @optname=N'remote proc transaction promotion', @optvalue=N'false'

If this Property is true :

Within a trigger, you are inside a transaction defined by the statement that fired the trigger. By default, SQL Server attempts to promote local transactions to be a distributed transaction. As we can tell from the error message, this is not possible in this case (for Synapse), so we need to turn this Property off.

Execution Output:

Scenario 1 : Insert Data into SQL Server table