Connecting multiple SQL Server instances with a backplane

Component: Transport Bridge
NuGet Package NServiceBus.Bridge (2.x)
This is a community run project
Target NServiceBus Version: 7.x

The sample demonstrates how to use Switch from NServiceBus.Bridge package to connect endpoints running SQL Server transport that use different instances of SQL Server. This is an alternative to the multi-instance mode of SQL Server transport which has been removed in Version 4.

The RabbitMQ broker is used as a backplane in this sample.

Switch vs Backplane

Both Switch and Backplane approaches can be used replace the deprecated multi-instance mode in connecting endpoints that use different SQL Server databases. The following table contains a side-by-side comparison of both approaches

SwitchBackplane
Single switch for the entire solutionBridge-per-database, can be co-hosted in a single process
Requires DTC to ensure exactly-once processingExactly-once processing through de-duplication
All SQL Server instances must be in the same networkEach SQL Server instance can be in separate network or even data centre
Centralized forwarding configurationDistributed forwarding configuration

The Backplane approach, while more complex in terms of deployment, provides more flexibility e.g. some databases might be on-premise while others might be in the cloud.

Throughput

Both approaches can be used to increase the throughput of the entire system when performance of a single SQL Server instance becomes a bottle neck. The key to thing when using the Switch or Backplane for performance reasons is partitioning. When done wrong, it can have the opposite effect and decrease the overall throughput.

To correctly partition the system when using Switch or Backplane first cluster the endpoints based on the volume of messages exchanged. The more messages endpoint exchange, the closer they are. If all endpoints form a single cluster Switch or Backplane won't help. In a healthy system, however, there will be several clusters of endpoints of highly coupled endpoints. Assign each cluster its own instance of SQL Server. Use Switch or Backplane to connect the clusters.

Prerequisites

An instance of SQL Server Express is installed and accessible as .\SqlExpress.

At startup each endpoint will create its required SQL assets including databases, tables and schemas.

This sample automatically creates three databases: backplane_blue, backplane_red and backplane_green

Running the project

  1. Start the solution.
  2. The text Press <enter> to send a message should be displayed in the Client's console window.
  3. Hit enter several times to send some messages.

Verifying that the sample works correctly

  1. The Sales console display information about accepted orders in round-robin fashion.
  2. The Shipping endpoint displays information that orders were shipped.
  3. The Billing endpoint displays information that orders were billed.
  4. The Client endpoint displays information that orders were placed.

Code walk-through

This sample contains four endpoints, Client, Sales, Shipping and Billing. The Client endpoint sends a PlaceOrder command to Sales. When PlaceOrder is processed, Sales publishes the OrderAccepted event which is subscribed by Shipping and Billing.

In addition to the four business endpoints, the sample contains three Bridge endpoints that connect the three databases, Blue, Red and Green, to the RabbitMQ backplane.

Client

The Client endpoint is configured to use its own, Blue, database to harden the security of the solution. This database does not contain sensitive data.

In order to route messages to Sales, Client needs to configure bridge connection

var routing = transport.Routing();
var bridge = routing.ConnectToBridge("Blue-SQL");
bridge.RouteToEndpoint(typeof(PlaceOrder), "Sales");

Sales and Shipping

The Sales and Shipping endpoints are configured to use the Red database for the transport. As Sales only publishes events and sends replies, it does not need any routing or bridge configuration.

Shipping subscribes for events published by Sales and it uses the same transport database so regular logical routing is enough

var routing = transport.Routing();
routing.RegisterPublisher(typeof(OrderAccepted), "Sales");

Billing

The Billing endpoint requires even more enhanced security. It uses its own database, Green. In order to subscribe to Sales event it need to register the publisher in the bridge configuration

var routing = transport.Routing();
var bridge = routing.ConnectToBridge("Green-SQL");
bridge.RegisterPublisher(typeof(OrderAccepted), "Sales");

Bridges

Each database is connected to the backplane via a separate bridge. All three bridges share the same configuration

var bridgeConfig = Bridge
    .Between<SqlServerTransport>("Blue-SQL", t =>
    {
        t.ConnectionString(ConnectionStrings.Blue);
        t.Transactions(TransportTransactionMode.SendsAtomicWithReceive);
    })
    .And<RabbitMQTransport>("Blue-Rabbit", t =>
    {
        t.ConnectionString("host=localhost");
        t.UseConventionalRoutingTopology();
    });

bridgeConfig.AutoCreateQueues();
bridgeConfig.UseSubscriptionPersistence(new InMemorySubscriptionStorage());

They differ only with regards to forwarding configuration. The Blue bridge is configured to forward PlaceOrder messages to the Red database

bridgeConfig.Forwarding.ForwardTo("PlaceOrder", "Red-Rabbit");

And the Green bridge is configured to forward subscribe requests for OrderAccepted event to the Red database.

bridgeConfig.Forwarding.RegisterPublisher("OrderAccepted", "Red-Rabbit");

The Red bridge does not need forwarding configuration as it only routes published events and replies.

Consistency

The backplane transport (RabbitMQ) offers lower consistency guarantees than the endpoints' transport (SQL Server). The messages can get duplicated while travelling between the databases. This is simulated by the DuplicateRabbitMQMessages interceptor

var duplicate = message.TransportTransaction.TryGet<SqlConnection>(out var _);

return forward(async (messages, transaction, context) =>
{
    if (duplicate)
    {
        await dispatch(messages, transaction, context).ConfigureAwait(false);
    }
    await dispatch(messages, transaction, context).ConfigureAwait(false);
});

In order to preserver exactly-once message processing guarantees that SQL Server transport offers, messages forwarded from the backplane to the database need to be de-duplicated. Another interceptor takes care of this task

async Task DeduplicateDispatch(TransportOperations messages,
    TransportTransaction transaction, ContextBag context)
{
    using (var conn = await OpenConnection().ConfigureAwait(false))
    using (var tx = conn.BeginTransaction())
    {
        var duplicateOps = new List<UnicastTransportOperation>();

        //Detect duplicates
        foreach (var operation in messages.UnicastTransportOperations)
        {
            var messageId = operation.Message.MessageId;
            if (await WasForwarded(conn, tx, messageId).ConfigureAwait(false))
            {
                duplicateOps.Add(operation);
            }
        }

        //Remove duplicates
        foreach (var duplicateOp in duplicateOps)
        {
            messages.UnicastTransportOperations.Remove(duplicateOp);
        }

        //Set the connection and transaction for the outgoing op
        var forwardTransaction = new TransportTransaction();
        forwardTransaction.Set(conn);
        forwardTransaction.Set(tx);

        //Dispatch
        await dispatch(messages, forwardTransaction, context)
            .ConfigureAwait(false);

        //Mark as processed (atomically with dispatch)
        foreach (var operation in messages.UnicastTransportOperations)
        {
            var messageId = operation.Message.MessageId;
            await MarkAsForwarded(conn, tx, messageId).ConfigureAwait(false);
        }
        tx.Commit();
    }
}

It stores the IDs of incoming messages in the destination database, atomically with sending messages. If a message has already been sent, it is ignored.

In real worlds the ReceivedMessages table used for de-duplication would need some kind of eviction policy based on a retention period that removes old de-duplication records.

Samples

Related Articles


Last modified