Brighter and SQL Server: How to setup and use Brighter with MS SQL

In this article, we’ll focus on integrating Brighter with Microsoft SQL Server. Introduction to Microsoft SQL Server Microsoft SQL Server is a robust relational database management system (RDBMS) widely adopted in .NET ecosystems. While traditionally used for transactional data storage, SQL Server can also act as a message broker in distributed systems. Brighter—a command-processing library—supports SQL Server as a messaging backend, offering a pragmatic solution for scenarios where deploying dedicated brokers like RabbitMQ or Kafka is impractical (e.g., small projects or legacy environments). Requirement .NET 8 or superior A .NET project with these NuGet packages Paramore.Brighter.MessagingGateway.MsSql: Enables SQL Server as a message broker. Paramore.Brighter.ServiceActivator.Extensions.DependencyInjection: Bridge Brighter with AWS messaging services. Paramore.Brighter.ServiceActivator.Extensions.Hosting: Hosts Brighter as a background service. Serilog.AspNetCore: For structured logging (optional but recommended). Brighter Recap Before continuing about Microsoft SQL Server configuration, let's recap what we already know about Brighter. Request (Command/Event) Define messages using IRequest: public class Greeting() : Event(Guid.NewGuid()) { public string Name { get; set; } = string.Empty; } Commands: Single-recipient operations (e.g., SendEmail). Events: Broadcast notifications (e.g., OrderShipped). Message Mapper Translates between Brighter messages and your app objects: public class GreetingMapper : IAmAMessageMapper { public Message MapToMessage(Greeting request) { var header = new MessageHeader(); header.Id = request.Id; header.TimeStamp = DateTime.UtcNow; header.Topic = "greeting.topic"; // The target topic to be publish header.MessageType = MessageType.MT_EVENT; var body = new MessageBody(JsonSerializer.Serialize(request)); return new Message(header, body); } public Greeting MapToRequest(Message message) { return JsonSerializer.Deserialize(message.Body.Bytes)!; } } Request Handler Processes incoming messages: public class GreetingHandler(ILogger logger) : RequestHandler { public override Greeting Handle(Greeting command) { logger.LogInformation("Hello {Name}", command.Name); return base.Handle(command); } } Configuring Brighter with SQL Server Step 1: Database Setup Create a table to store messages (e.g., QueueData): CREATE TABLE [dbo].[QueueData]( [Id] BIGINT IDENTITY(1,1) PRIMARY KEY, [Topic] NVARCHAR(255) NOT NULL, [MessageType] NVARCHAR(1024) NOT NULL, [Payload] NVARCHAR(MAX) NOT NULL ); CREATE NONCLUSTERED INDEX [IX_Topic] ON [dbo].[QueueData] ([Topic]); Step 2: Configure SQL Server Connection Define connection settings in C#: var sqlConfig = new MsSqlConfiguration( connectionString: "", queueStoreTable: "QueueData" // Table created above ); Step 3: Consumer Configuration Subscribe to an SQL Server queue and: .AddServiceActivator(opt => { opt.Subscriptions = [ new MsSqlSubscription( subscriptionName: new SubscriptionName("greeting-subscription"), channelName: new ChannelName("greeting.topic"), // Maps to the Topic column isAsync: false // Set to true if using async handlers ) ]; opt.ChannelFactory = new ChannelFactory(new MsSqlMessageConsumerFactory(sqlConfig)); }); Step 4: Producer Configuration Publish events to SQL Server: .UseExternalBus(new MsSqlProducerRegistryFactory(sqlConfig, new[] { new Publication { Topic = new RoutingKey("greeting.topic") } }).Create()); Key Considerations Schema Ownership: You must manually create the QueueData table and index. Brighter does not auto-generate schemas. Performance: SQL Server is not optimized for high-throughput messaging. Use it for lightweight scenarios or prototyping. Conclusion Conclusion Brighter's SQL Server integration provides a pragmatic messaging solution for .NET applications where dedicated brokers are unavailable. By leveraging SQL Server’s transactional guarantees, this approach ensures reliable message delivery with minimal infrastructure overhead. For production workloads, pair it with monitoring (e.g., SQL Server Profiler) and error-handling strategies. Reference GitHub Repository

public class Greeting() : Event(Guid.NewGuid())
{
    public string Name { get; set; } = string.Empty;
}

public class GreetingMapper : IAmAMessageMapper<Greeting>
{
    public Message MapToMessage(Greeting request)
    {
        var header = new MessageHeader();
        header.Id = request.Id; 
        header.TimeStamp = DateTime.UtcNow;
        header.Topic = "greeting.topic"; // The target topic to be publish
        header.MessageType = MessageType.MT_EVENT;

        var body = new MessageBody(JsonSerializer.Serialize(request));
        return new Message(header, body);
    }

    public Greeting MapToRequest(Message message)
    {
        return JsonSerializer.Deserialize<Greeting>(message.Body.Bytes)!;
    }
}

public class GreetingHandler(ILogger<GreetingHandler> logger) : RequestHandler<Greeting>
{
    public override Greeting Handle(Greeting command)
    {
        logger.LogInformation("Hello {Name}", command.Name);
        return base.Handle(command);
    }
}

CREATE TABLE [dbo].[QueueData](
    [Id] BIGINT IDENTITY(1,1) PRIMARY KEY,
    [Topic] NVARCHAR(255) NOT NULL,
    [MessageType] NVARCHAR(1024) NOT NULL,
    [Payload] NVARCHAR(MAX) NOT NULL
);
CREATE NONCLUSTERED INDEX [IX_Topic] ON [dbo].[QueueData] ([Topic]);

var sqlConfig = new MsSqlConfiguration(
    connectionString: "",
    queueStoreTable: "QueueData" // Table created above
);

.AddServiceActivator(opt =>
{
    opt.Subscriptions = [
        new MsSqlSubscription<Greeting>(
            subscriptionName: new SubscriptionName("greeting-subscription"),
            channelName: new ChannelName("greeting.topic"), // Maps to the Topic column
            isAsync: false // Set to true if using async handlers
        )
    ];
    opt.ChannelFactory = new ChannelFactory(new MsSqlMessageConsumerFactory(sqlConfig));
});

.UseExternalBus(new MsSqlProducerRegistryFactory(sqlConfig, new[]
{
    new Publication
    {
        Topic = new RoutingKey("greeting.topic")
    }
}).Create());

Related Posts

ABSTRACTION IN JAVA

Apr 24, 2025  0

Social Media Engagement in Early 2025

Apr 27, 2025  0

Want to read about learning AWS through work experience...

Apr 28, 2025  0