Implementing the outbox pattern with Azure SQL

Veli-Jussi Raitila 426

I'm aware of mechanisms for implementing the outbox pattern with PostgreSQL with logical replication as well as with Cosmos DB by relying on change feeds. The latter is even documented by Microsoft here.

But what would be the proper method for achieving the same with Azure SQL?

Accepted answer

GeethaThatipatri-MSFT 29,502 Reputation points Microsoft Employee

2022-12-14T15:19:20.75+00:00
Hi, @Veli-Jussi Raitila Thanks for posting your question in the Microsoft Q&A forum.
Excellent article from Davide Mauri here with code samples/examples:

https://devblogs.microsoft.com/azure-sql/azure-sql-change-stream-with-debezium/

https://learn.microsoft.com/en-us/samples/azure-samples/azure-sql-db-change-stream-debezium/azure-sql-db-change-stream-debezium/

Please let me know if this helps.

Regards
Geetha
Please sign in to rate this answer.
GeethaThatipatri-MSFT 29,502 Reputation points Microsoft Employee

2022-12-16T18:00:19.907+00:00

Hi, @Veli-Jussi Raitila We haven't heard back from you. Just wanted to check if you find the above reply useful. If yes, do click on the 'Mark as answer' link in the above reply. This will help other community members facing similar queries to refer to this solution.

Regards
Geetha

Veli-Jussi Raitila 426 Reputation points

2022-12-19T10:38:46.55+00:00

I am deeply familiar with both Debezium and Kafka. I just have several issues with this suggestion - and one of them is how MS positions Azure Event Hubs (referred to in the articles) with regards to, for example, Azure Service Bus. This position differs a lot from e.g. Confluent's. Microsoft seems to characterize AEH as an "IoT" thing, applicable to "big data streaming" and draws a rather hard distinction to enterprise messaging use cases.

In addition to the dubious compatibility with the rest of the Kafka ecosystem in AEH and the requirement to manage you own components in this solution... I don't now. I suppose I would have expected something more Azure-native.

Regardless, I have accepted the answer as it seems that a solution which I would find fully satisfying does not exist.

GeethaThatipatri-MSFT 29,502 Reputation points Microsoft Employee

2022-12-22T03:09:10.063+00:00

@Veli-Jussi Raitila Please check below @Anonymous answer if it satisfies your ask.

Regards
Geetha
Sign in to comment

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

2 additional answers

Alberto Morillo 34,451 Reputation points MVP

2022-12-04T22:49:26.967+00:00

You can communicate with Azure Service Bus Queue through REST API and as you can read here you can now make REST API calls from a stored procedure on Azure SQL Database.

In addition, on Azure SQL Managed Instance you can create a CLR (Common Language Runtime) Stored Procedure and call the Azure Service Bus through REST APi. See this video to know how to use CLR on Azure SQL Managed instance.
Please sign in to rate this answer.
Veli-Jussi Raitila 426 Reputation points

2022-12-05T10:34:31.517+00:00

Is this really the only way? Feels really hacky to me and an architectural smell to deploy code to be executed within the SQL compute in this situation. It would be completely separate from the rest of the business logic etc.

How to make this fault tolerant? Holistic error handling, monitoring and logging? Managing secrets? Testability?

At least at a first glance really not a fan of this approach :)

I would've expected something relying on SQL change tracking or even this.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Drew Skwiers-Koballa 1 Reputation point Microsoft Employee

2022-12-21T23:05:44.76+00:00

With Azure Functions you can utilize a SQL trigger - which monitors change tracking on the Azure SQL Database - to pass data changes to a component such as Azure Event Hubs or Service Bus.

https://devblogs.microsoft.com/azure-sql/developing-with-azure-sql-bindings-and-azure-sql-trigger-for-azure-functions/
Please sign in to rate this answer.
Veli-Jussi Raitila 426 Reputation points

2023-01-12T09:53:37.1733333+00:00

Seems to be the same solution I myself linked to in one of my earlier comments.

I find this to be the closest I am looking for. Judging by the documentation, although I cannot see an explicit mention of it, this approach should also handle missed changes (to the extent SQL database change tracking retention times allow). Is this correct?
Sign in to comment

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

Share via

Implementing the outbox pattern with Azure SQL

2 additional answers

Your answer