SQLTransport to MSMQ transport bridge solution

1

I’ve had a need to create a bridge from the SQL transport over to the MSMQ transport. There was one issue that came up and i’m not sure if it is something that can be done through the bridge.

Everything we implement that uses sql (MSSQL) we create custom database schemas for instead of using dbo. When doing the SQL transport to MSMQ bridge, the bridge table is always created under the dbo schema. The issues is when the SQL transport sends the message over to the bridge it is sending it to the bridge table under the custom schema while the bridge is setup to listen on the dbo schema. Is there a way to specify the bridge to use a different schema?

Example:

[Schema].[database table name]
Sql Transport table
CustomSchema.mwa.ITOps.TPI.MemberWorkflow.Endpoints.WorkItemForwarder.Sql

SQL Transport Bridge Table created when endpoint starts
CustomSchema.mwa.ITOps.TPI.MemberWorkflow.Endpoints.WorkItemForwarder.Sql

Bridge Table
dbo.mwa.ITOps.TPI.MemberWorkflow.Endpoints.WorkItemForwarder.Sql

When this code runs and routes the message to the bridge it sends the message the queue table with the schema while the bridge is listening on the dbo schema

Capture
Capture.PNG718×116 6.57 KB

Here is the bridge code:

image
image.png1585×280 20.7 KB

2

Hi @mick24

The transport extensions callback you pass to Bridge.Between allows you to customize the transport. You should be able to set the default schema for the bridge using that extensions object.

Also, consider switching from NSB.Bridge to NSB.Router. It has similar basic features but has also some more advanced features. The NSB.Bridge is no longer actively developed. You can continue to use Bridge.Connector in the endpoint – it is compatible with Router.

3

I’ll give the NSB.Router a look as i’m sure it will meet our needs. When i was looking at the Bridge.Between transport extensions for the SqlServerTransport I didn’t see anything there with the ability to override the schema name. Maybe i’m missing something?

4

DefaultSchema should do the trick:

var bridgeConfiguration = Bridge
    .Between<MsmqTransport>(endpointName: "Left")
    .And<SqlTransport>(
        endpointName: "Right",
        customization: transportExtensions =>
        {
            transportExtensions.DefaultSchema("mySchema");
        });
5

When i’m looking at it i’m not seeing a SqlTransport option (like you mentioned) and the SqlServerTransport which I’m using doesn’t seem to have a method for setting the DefaultSchema. Am i using the wrong NuGet package for this? We are referencing the NserviceBus.SqlServer 4.1.1 package.

var bridgeConfiguration = Bridge.Between<SqlServerTransport>(endpointName: sqlBridgeName, customization: transportExtensions => {
            transportExtensions.ConnectionString(connectionString);
            transportExtensions.Defa

        }).And<MsmqTransport>(endpointName: msmqBridgeName);
6

Hi @mick24

Do you have an import for namespace NServiceBus.Transport.SQLServer? Can you push the code somewhere to github so I can take a look? The extension method is there in SQL Server Transport since version 2 I believe.

Szymon

7

I’ve pushed the code to my github.

https://github.com/mick-anderson24/NSB-SQLTransport-MSMQTransport

8

Hi

I’ve changed the code to:

var bridgeConfiguration = Bridge.Between<SqlServerTransport>(endpointName: sqlBridgeName, customization: transportExtensions => {
    transportExtensions.ConnectionString(connectionString);
    transportExtensions.DefaultSchema("MySchema");
}).And<MsmqTransport>(endpointName: msmqBridgeName);

and it is fine. Apart from missing references to your packages it would compile just fine.