What's new in SQL Server Transport 6.2

SQL Server Transport is one of our most popular transports. Customers often have deep proficiency with SQL and are able to use SQL Transport to add message queues to new or even legacy systems without needing to adopt (or get approval for) additional technology. This provides a glide path to begin using messaging, including all the advantages in loose coupling and reliability that come with it, and begin learning to design better software systems in a new way.

SQL Server Transport version 6.2.0 adds a few important features to make building complex systems even easier.

For extra secure systems, we’ve added support for SQL Server Always Encrypted so that messages can be encrypted so deeply that even SQL Server itself can’t read the messages without the correct keys. For scaled-out endpoints, you can now configure how the load balancing of batches is handled. We’ve also standardized the startup checks that endpoints do across all transaction modes.

🔗SQL Server Always Encrypted

The NServiceBus.Transport.SqlServer package now supports encrypting the columns in your queue tables using the Always Encrypted feature. Always Encrypted is supported without you needing to make any code changes.

To use Always Encrypted, you need to make sure that the keys are configured in a trusted key store so that any message endpoint that needs access can get them. Once the key management is configured correctly, you can encrypt the columns in the endpoint tables that you want to protect, likely the Headers and Body as this is where any sensitive information would be stored.

After selecting the columns to encrypt in SQL Server, the only change needed in the endpoint is to add Column Encryption Setting = Enabled; to the SQL connection string.

For details on setting up encryption, see Support for SQL Always Encrypted in our documentation.

🔗Improved load balancing control

Because the SQL Server Transport uses batching, it was possible that in a scenario with low throughput that messages were always picked up by a single instance in a single batch. If the messages had a slightly longer processing time, it was possible that one server would receive all the messages, but take awhile to process them, while the other server was sitting idle, eager to help but without any work to do.

This was due to the peek logic, which was fetching Min(100, 10 * MaxConcurrency) messages per batch. Because the default concurrency setting is 2 * LogicalCores, on an 8-core machine the batch size would be 80, meaning up to 80 messages would be fetched at once.

In version 6.2.0, we’ve added the ability to control the peek batch size and set it to any arbitrary value greater than zero:

transportConfig.QueuePeekerOptions(peekBatchSize: 10);

With this setting, if there were 60 messages available, they would be distributed to waiting message endpoint instances in batches of 10. As a result, they would be evenly distributed between two processing instances, rather than all gobbled up by one, leading to faster overall message processing.

🔗Improved startup behavior

Previously, SQL Transport endpoints would only check if the database was available when using TransactionScope mode. This led to different startup behavior depending on the selected transaction mode. This has now been changed so that the endpoint fails to start if the database is unavailable—regardless of the transaction mode specified.

If the transaction mode is TransactionScope, SqlTransport also checks whether or not the runtime supports enlisting in ambient transactions. For .NET Framework endpoints, this means that the Microsoft Distributed Transaction Coordinator (MSDTC) must be configured correctly. In .NET Core endpoints, the endpoint will warn that you are using a tranasaction mode in a runtime that does not support enlisting in distributed transactions. While .NET Core does not support distributed transactions, you can still run endpoints in TransactionScope mode in order to coordinate transport and business data operations by making sure all connections share the same connection string.

🔗Summary

Version 6.2.0 of the SQL Server Transport continues to add features that makes it a great choice for building new systems, or for integrating with existing or even legacy systems built on SQL.

For more information, check out our SQL Server Transport documentation.