🔧 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<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)!;
    }
}

Request Handler

Processes incoming messages:

public class GreetingHandler(ILogger<GreetingHandler> logger) : RequestHandler<Greeting>
{
    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: "<Your_Connection_String>",
    queueStoreTable: "QueueData" // Table created above
);

Step 3: Consumer Configuration

Subscribe to an SQL Server queue and:

.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));
});

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