Implementing Outbox Pattern Using Azure SQL Server And Azure Functions

Hi and welcome to the video of implementing Outbox pattern using Azure SQL Database and Azure Function Apps. First of all, let's see why every distributed system needs to implement Outbox pattern. Imagine you are placing an order. Your API saves the order to the database, then tries to publish an order placed event to a message boss so downstream services like email or inventory or billing can react. What happens if the message broker is temporarily down at the exact moment? Without the outbox pattern, you face a brutal choice. You either lose the event entirely or you try to roll back the database right, introducing complex compensating logic that's hard to get right under the failure conditions. The dual-write problem. Writing to two separate systems atomically is fundamentally impossible without coordinating mechanism. This is called dual-write problem. and it is the root cause of subtel data consistency bugs in the distributed systems. There are some examples that we can review now like saving to database crash event lost. Database write succeeds but the process crashes before publishing. The event is gone forever. Downstream services never learned the order was placed. Another example publish event crash a duplicate on retry. Event is published. then the db writes fails on retry you publish again downstream services process event twice without knowing the order never committed for example broker unavailable at publish time service bus has a blip your code throws an exception on publish now you must decide roll back the database right retry the background thread queue it in a memory or any other complexity outpost pattern will solve all of these problems It writes the business record and the event row in a single local transaction. A separate relay process publishes from the outbox and automaticity is guaranteed by your database. Okay, what is outbox pattern? The outbox pattern stores integration events in the same database as your business data using a dedicated integration events table. Of course, the name of the table is optional. Because both the business record and the event row are written in a single database transaction, they are either both committed or both rolled back. Automacity is guaranteed by your database. A separate background process, in our case, an Azure function with a SQL trigger, then reads on processed events from the Outbox table and publishes them to the message broker. if publishing fails the event remains in the outbox and then it is retried if it is succeeds the event is marked as processed we are going to go through these steps one by one and i'm going to show you each of them in the code and also in the portal first api receives command a post request arrives for example place place an order your api builds the domain entity and construct the corresponding integration event object and after that in our example ef core opens the transaction it inserts the order row and also the integration event row containing the serialized event payload as a json missing the same transaction And then it causes the save changes so the transaction takes place and we place two records in the order table and integration events table. And then the next step is that the Azure function detects new row. An Azure function with the SQL trigger watches the integration events table. The moment a new row is committed, Azure function will fire automatically. After that, Azure function tries to publish to the service boss. The function deserializes the event payload and publishes it to the appropriate Azure service boss topic or queue. If this fails, the trigger retries. The event stays in the outbox until the publish gets successfully. After that, when the message was published successfully, The function updates the integration event row. We will have a field or column in the database integration table called processedAt, which at the time when we insert the record, it is null. And after the publishing, we will populate that field with the current date time. And this component works together. So we will have a .NET 10 minimal API with an Azure SQL database. which contains two tables, orders, integration events, as we are going to show in the demo. And we will have a function app, which here acts as a relay, and it publishes the event to the service bus. Now, let's dive to the code, and I will explain how these things work. Okay, here we are in JetBrains Rider, and I go to the... file system first because I would like to show you the script file when you have your database on Azure created so you can create the two required table for this demo which is called the order and integration events as we can see it's really simple straightforward in the orders we have an ID a customer name and a total amount status and created at also in the integration event we have id event type payload which is the object serialized and occur that and processed at date times also after that we need to enable the cdc change tracking in the database first with this command and then enable it on the table which is the integration table and after that you can verify that your cdc basically is working let's open the solution view and we have two projects here order api and outbox relay i open order api project and in this project we have a folder called endpoints and here we have our order endpoint class which is a post request and accepts a create order request object which has a customer name and total amount. At the very beginning it builds the domain entity and then serialize the integration event payload as JSON and then it creates or instantiates an integration event. After that, we create a transaction and we save all our, both our entities to the database inside the transaction and then we return the success results. Inside our integration event, we have order placed event, the same field as I also showed you in the database script. And if we go to our data folder, we have here the DB context. which has two model sets and also we are configuring these models to the corresponding tables in the database as you can see here also in the app settings file I just have a connection to the database this is the this is the connection string to the Azure SQL database. You can change it to your own connection string later on. And also in the program class, nothing too fancy going on here. We map our endpoints and also we add our DB context to the dependency injection. Okay, let's go to the Outbox Relay project. Here we have Outbox pattern with SQL class, which is an Azure function. It has SQL trigger. It needs the table name and also the SQL connection string. And based on that, it is listening to the changes, which is the insert mode and basically goes through the events which are published from the CDC. and it tries to find out the topic name which we have a convention when we insert the record i will show you so at the end of the event string it just takes out the order place or it depends on the name of the event and it just sends it to the azure service bus by that and based on that we create here our Azure service bus clients and we just send a message to the service bus and that's it in the apps local app settings we have at the moment in the local development i have a sql connection string but i'm going to create environment variable in the Azure functions in the portal which I show you in a minute and also we have a service boss namespace only the namespace because I will show you how you can connect your Azure function to service boss with managed identity also let's open the program class also in the program class we register the service boss client that was injected to our function class. And also we have here the integration event object, which is exactly the same as we had in the API project. Okay, I am logged in in Azure and for saving the time, I created already the resources, but I will walk you through each of them and I will give you the important hints. So as you can see, I have a resource group here called RG Outbox. and inside that i deployed an azure function with app insights and also storage accounts we have our database created here and also the service bus for that and also this one app service plan is related to the azure function as you know okay let's go to the database first and i will give you the hints When you try to create your database, you need to be careful to create a database that supports CDC. So in my case, I went with general purpose serverless G5, which is the perfect choice for the demonstration of this demo. And also if we go to the firewall section, I selected my network my IP address here to be able to make connection to this database from my local machine because I am not going to deploy the API to the portal for saving the time and of course if you deploy your API to Azure you can definitely use managed identity also for that and remove the public access to your database which is the best practice. I'm going to bring up now data grip to show you what I have in the database. So I have in this database which is called outbox in my case you can name it however you want and I created here two tables by the script which I showed you at the beginning of the video. And we can see at the moment there are nothing there. And also when I enabled the CDC, this table gets created for me. And we are going to see these tables after we basically inserted or sent some requests to our API. Okay, let's go back to the portal. And I am going to now show you my Azure function here. so i created a flex consumption plan based on linux so you can also go with the same pattern and i just published the project here so i have only one function here outbox relay and in the environment variables section i added here the service bus namespace and also the connection string to the SQL database. So you need to also add these two when you deploy your code. Then the next step, what needs to be done here is that we need to enable the managed identity here. So I just went here with the system assigned managed identity. and you turn it on and click save based on this you will create a identity inside active directory or intra id and your out your application is able then to automatically use and validate itself with other related services like service boss in our case so let's go to the service boss I also have created here a standard service bus and here first of all I went to access control and you need to here assign a role for example let's search here for service bus so the role that is needed here is the service bus data sender role which is already assigned in my case but for demonstration i will i don't know for example choose this one and i click next but be careful to have this service bus status sender role for your azure function then here you need to go with the managed identity because this is what we need to do for our azure function and click select member here and your subscription managed identity select and here it shows that we have one function app which has the which has enabled the managed identity we can select it and here you just need to save it i am not going to do that because i already have the role okay with that in place you don't need to store any secrets or keys or something like that inside your code base Let's continue going to the service boss and I created already a topic inside the service boss which is called order placed and what we need to do here first we need to create a subscription because we want to see the messages. I create a subscription here I will call it test and just create. It seems that our subscription is created. and currently we don't have any messages here. I will bring up the rider and I go to our API project. Here we have an HTTP file so we make use of this to send a request to our API but first of all obviously we need to run our application. It seems that it is up and running and now I am going to fire this http request let's put this i don't know 50 for example and send this request and we got a success message from our endpoint which is of course running locally at the moment but we were able to insert the records to the sql database i will bring up the data group we are going to query the order table And as you can see here, we got a record with the customer name Sherwin and total amount of 50. And also let's see our integration events. Inside our integration events, we got our event type, which we were extracting this part of this string in the Azure function code. getting out this part event so order placed was coming from this convention in my case but you can definitely change it to something better if you want and also we have here our payload which is the object customer name shelvin total and 150 and so on which is serialized to json we had here the order the occurred at date time and processed at date time because it seems that our Azure function already processed this record other than that this was not we can verify that if now we go again to the portal and I refresh this basically page and we see that the message count is changed to one so let's go here to the service bus explorer and i choose here the subscription test let's pick from the start we have here one event and if we click we see the json object appearing in the service bus topic and of course in our subscription okay now i'm going to show you what happens if our message relay application is not working and it is stopped so let's go to the azure function here and stop it i will open up rider and we send another request request this time i change it to 60 and click the post okay it seems that the records are created in our database let's go to data group and again query the integration events and as you can see here in this time the processed at column is null because azure function is off to process this event let's go back to the portal I try to turn it on so start and let's see if we are able to process the event here from our subscription it seems that we got the message so if we open i have here now this 60 appeared at the top and also we can verify that if we go to the data group and here query again we should be able to see that the processed column got the processed time and that was it for today i really do hope that you enjoyed this video and it had a value for you and you can use it in your upcoming or existing projects. I would like to thank you for watching and hopefully see you in the next video. Until then, take care. Bye.

One of the most important patterns in distributed systems — built end to end on Azure. We use a single database transaction to write both your business data and a pending event, then an Azure Function picks it up via SQL trigger and publishes to Service Bus. Atomic. Resilient. No polling.
Stack: .NET 10 Minimal API · EF Core · Azure SQL · Azure Functions (SqlTrigger) · Azure Service Bus
What's covered:
→ Why dual-writes silently corrupt distributed systems
→ How the Outbox Pattern solves it without distributed transactions
→ Full code: Orders table + IntegrationEvents outbox table
→ Azure Function as the event relay (SQL change tracking trigger)
→ Managed identity auth for both SQL and Service Bus
→ Live demo with broker failure simulation
If you're building microservices or event-driven systems on Azure, this is the pattern you need before you ship.

Source code:
https://github.com/YouTubeSource/OutboxPatternWithSqlServerAndAzureFunctions