How SQL Server SEQUENCE works together with the SAGA pattern in microservices

In a monolithic application, everything happens inside a single database transaction. But in microservices, one business transaction is broken into multiple services. For example, when we place an order in Amazon or any e-commerce site Order Service creates order, Payment Service charges, Inventory Service reserves stock, Shipping Service prepares delivery and Notification Service sends updates. All are different microservices with different databases. There is no single ACID transaction across them.

So how do we guarantee:

Ø  Reliability

Ø  Failure handling

Ø  Rollback behavior

Ø  Traceability

This is where SAGA Pattern comes in.

Saga simply means is Instead of one big transaction, we break the workflow into multiple local transactions and if something fails, we run compensation steps.

Architectural diagram of SAGA design pattern

SAGA works on two approaches

Orchestration Saga

It is a microservices design pattern where a central coordinator (orchestrator) manages a distributed transaction by commanding participating services, ensuring data consistency through a sequence of local transactions and triggering compensating actions if any step fails, offering centralized control but creating a potential single point of failure.

For example,

it calls Payment,
then Inventory,
then shipping
and controls the full workflow.

Choreography Saga

It is a design approach in microservices for managing distributed transactions, where services coordinate by publishing and subscribing to events without a central controller, ensuring data consistency through a series of local transactions and compensating actions for failures, ideal for simpler workflows with few participants.

For example,

No central controller.

Everything runs using events like Kafka / RabbitMQ.
OrderCreated event → PaymentService
PaymentDone event → InventoryService
StockReserved event → ShippingService

If anything fails, compensating events happen, like refund payment or restock inventory.

Let’s see the demo

we need a single Order ID right from the beginning. This Order ID must travel across:
Payment logs, Inventory records, Shipping service, Notifications, Audit trails, Retry events.

If we use IDENTITY, it will not work because it is generated after insert. Here we want ID before inserting the records. SQL Server SEQUENCE allows us to generate numbers before inserting data. It means We can generate Order ID first, use it everywhere, insert later. Sequence is perfect for microservices. Perfect for messaging systems. Perfect for Saga.

 Creating sequence

CREATE SEQUENCE SAGA_Demo_Seq     AS BIGINT     START WITH 500000     INCREMENT BY 1;

Sequence created successfully. Now creating few tables.

CREATE TABLE orders   (      orderid      BIGINT PRIMARY KEY,      customername VARCHAR(100),      amount       DECIMAL(10, 2),      status_cd    VARCHAR(50)   );  CREATE TABLE orderaudit   (      auditid INT IDENTITY(1, 1) PRIMARY KEY,      orderid BIGINT,      message VARCHAR(200),      logtime DATETIME DEFAULT Getdate()   );

Tables created successfully.

Now we can use these in application.

When a customer places the order then first of all we will get the order ID using the sequence and this ID we can use during the entire process.

Getting the Order Id from the sequence.

DECLARE @OrderId BIGINT = NEXT VALUE FOR SAGA_Demo_Seq; SELECT @OrderId AS GeneratedOrderId;

See the order id

we have generated Order ID even before inserting into database. This is exactly what microservices need. Order ID is born here, and now it will travel everywhere.

--Generating order id DECLARE @OrderId BIGINT = NEXT VALUE FOR SAGA_Demo_Seq; SELECT @OrderId AS GeneratedOrderId;  --We first log in audit table. INSERT INTO OrderAudit(OrderId, Message) VALUES(@OrderId, 'Order Created Event Published');  --Then payment succeeds INSERT INTO OrderAudit(OrderId, Message) VALUES(@OrderId, 'Payment Successful');  --Then inventory reserved INSERT INTO OrderAudit(OrderId, Message) VALUES(@OrderId, 'Inventory Reserved');  --Finally we are inserting the details in order table INSERT INTO Orders(OrderId, CustomerName, Amount, Status_Cd) VALUES(@OrderId, 'Bagesh', 45000.50, 'Completed');

See the records in the table

Here we can easily track the order history. In fact, if our transaction gets failed, we ca track it see below.

 

--Generating order id DECLARE @OrderId BIGINT = NEXT VALUE FOR SAGA_Demo_Seq; SELECT @OrderId AS GeneratedOrderId;   INSERT INTO OrderAudit(OrderId, Message) VALUES(@OrderId, 'Inventory Failed – Running Compensation');   INSERT INTO OrderAudit(OrderId, Message) VALUES(@OrderId, 'Payment Refunded');   INSERT INTO Orders(OrderId, CustomerName, Amount, Status_Cd) VALUES(@OrderId, 'Bagesh', 45000.50, 'Failed');

See the data

So with the help of customer id we can get the step where our transaction got fail.

SEQUENCE offers flexibility, reuse, multi-table support, predictable control, and better manageability than IDENTITY — but it does not guarantee continuity, rollback safety, or gap-free behavior.