SQL Server Transport Upgrade Version 3 to 3.1

SQL Server transport 3.1 introduces a native handling of delayed messages. It does so via a special table that holds the messages that are sent but not yet due. The structure of this table is shown below:

3.1 NServiceBus.SqlServer
IF EXISTS (
    SELECT *
    FROM {1}.sys.objects
    WHERE object_id = OBJECT_ID(N'{0}')
        AND type in (N'U'))
RETURN

EXEC sp_getapplock @Resource = '{0}_lock', @LockMode = 'Exclusive'

IF EXISTS (
    SELECT *
    FROM {1}.sys.objects
    WHERE object_id = OBJECT_ID(N'{0}')
        AND type in (N'U'))
BEGIN
    EXEC sp_releaseapplock @Resource = '{0}_lock'
    RETURN
END

CREATE TABLE {0} (
    Headers nvarchar(max) NOT NULL,
    Body varbinary(max),
    Due datetime NOT NULL,
    RowVersion bigint IDENTITY(1,1) NOT NULL
);

CREATE NONCLUSTERED INDEX [Index_Due] ON {0}
(
    [Due]
)

EXEC sp_releaseapplock @Resource = '{0}_lock'

SQL Server transport 3.1 by default runs the Timeout Manager using the selected persistence option to drain all the remaining delayed messages sent before upgrading to version 3.1. However, even the new delayed messages are processed using the native mechanism only even when they are sent by the endpoint using older version of the transport.

Because some delayed messages can have the due times months or even years in future it might be advisable to migrate them in order to be able to disable the Timeout Manager entirely. See the SQL Server Native Delayed Delivery article for more details.

SQL Server

If SQL Server was used as a backing store for the Timeout Manager, either via NHibernate persistence or SQL persistence, refer to this sample for details on how to migrate.

Other databases

If another database was used, use DB-specific tools to extract the Headers, State, and Destination values from the timeout records and export the result to a file.

Some persistences, e.g. NHibernate, store all the delayed messages for all the endpoints in a single table. Exporting just the ones for the endpoint that is migrated requires filtering on the Endpoint property of the timeout record.

Once exported, use the following script to insert the data into SQL Server transport's table.

3.1 NServiceBus.SqlServer
DECLARE @NOCOUNT VARCHAR(3) = 'OFF';
IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON'
SET NOCOUNT ON;

INSERT INTO {0} (
    Headers,
    Body,
    Due)
VALUES (
    @Headers,
    @Body,
    @Due);

IF(@NOCOUNT = 'ON') SET NOCOUNT ON;
IF(@NOCOUNT = 'OFF') SET NOCOUNT OFF;
By default the table used to store delayed messages has the Delayed suffix so for an endpoint called MyEndpoint the delayed messages are stored in a table called MyEndpoint.Delayed.

Last modified