SQL Server Transport Upgrade - Supporting Unicode in headers

Summary

This document explains how to patch a system using the SQL Server transport to allow message headers to contain characters not supported by the current SQL Server collation. The issue may cause data loss when such characters are used in header names or values.

Compatibility

This issue has been resolved in the following patch versions of the SQL Server transport as defined in the NServiceBus support policy:

Any of the supported affected minor versions (3.1.x, 3.0.x, or 2.2.x) should be updated to the latest patch release. Older (unsupported) affected versions should be updated to a newest supported minor (2.1.x or 2.0.x) or major version (1.x).

Upgrade steps

To upgrade an existing endpoint:

Check at startup

The SQL Server transport detects incorrect definition of the Headers column and logs a warning:

Table [dbo].[SampleEndpoint] stores headers in a non Unicode-compatible column (varchar).

This may lead to data loss when sending non-ASCII characters in headers. SQL Server transport 3.1 and newer can take advantage of the nvarchar column type for headers. Please change the column type in the database.

If this log event is written to the log file then read the following guidance on how to upgrade the queue table schema.

Queue table schema upgrade

The incorrect Headers column definition on existing queue tables needs to be updated manually using the following SQL statement for every queue table managed by a given endpoint:

This procedure does not require any downtime and it can be executed when affected endpoints are processing messages.
Run this script on a testing or staging environment first to verify that it works as expected.
declare @queueTableName nvarchar(max) = N'...'
declare @sql nvarchar(max) = N'alter table ' + @queueTableName + N' alter column Headers nvarchar(max) not null';

exec sp_executesql @sql;

Last modified