Event driven way to capture changes to azure SQL database changes

MS Techie 2,701

In on-premises SQL , we have triggers which will run , whenever any SQL data changes .

In Azure SQL , is there any event driven way to capture the data changes in Azure SQL tables
(not polling like Logic app polling azure SQL)

Please help

Accepted answer

Alberto Morillo 33,956 Reputation points MVP

2021-04-08T22:00:44.623+00:00
Would you consider using Temporal Tables? Please read here.

You can also use Azure SQL Database Audit to audit only CRUD operations. See example below using PowerShell:

Set-AzureRmSqlDatabaseAuditing -ResourceGroupName "resourceGroup" -ServerName "SQL Server Name" -DatabaseName "AdventureWorksLT" -StorageAccountName "storageAccount" -AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP", "BATCH_COMPLETED_GROUP" -AuditAction "UPDATE ON database::[AdventureWorksLT] BY [public]" -RetentionInDays 60
Please sign in to rate this answer.
MS Techie 2,701 Reputation points

2021-04-13T17:11:44.013+00:00

Hi @Alberto Morillo
Can we convert normal tables into temporal tables ?

Alberto Morillo 33,956 Reputation points MVP

2021-04-13T19:06:41.217+00:00

Please follow the steps outlined on the following article:
https://sqlrelease.com/convert-existing-table-to-temporal-table
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

Erland Sommarskog 111.8K Reputation points MVP

2021-04-08T21:58:31.037+00:00

You can have triggers in Azure SQL as well.
Please sign in to rate this answer.

1 person found this answer helpful.
MS Techie 2,701 Reputation points

2021-04-09T10:41:55.86+00:00

So event driven approach, we can use triggers for Azure SQL ?

I was earlier thinking of this below approach , but it requires SQL Agent , which is not available in Azure SQL ( i dont want to SQL Azure Managed Instance, its costly)

https://mrfoxsql.wordpress.com/2017/07/12/streaming-etl-send-sql-change-data-capture-cdc-to-azure-event-hub/

Erland Sommarskog 111.8K Reputation points MVP

2021-04-09T21:21:22.463+00:00

Not what I know exactly what you mean with "event-driven approach", but when it comes to triggers there are no differences between on-prem on Azure. Well, you cannot use CLR triggers in Azure, but I assume that you are using regular plain T-SQL triggers.

Agent is not available in Azure, but there are Elastic Jobs which is kind of similar. However, the feature Change Data Capture is not available in Azure SQL Database.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Your answer