question

MSTechie-7364 avatar image
1 Vote"
MSTechie-7364 asked AlbertoMorillo commented

Event driven way to capture changes to azure SQL database changes

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

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo commented

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



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AlbertoMorillo
Can we convert normal tables into temporal tables ?

0 Votes 0 ·

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

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

You can have triggers in Azure SQL as well.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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/

0 Votes 0 ·

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.

0 Votes 0 ·