Migrating delayed messages from persistence to SQL Server transport

Component: SQL Server Transport
NuGet Package NServiceBus.SqlServer (4-pre)
Target NServiceBus Version: 7.x
This page targets a pre-release version and is subject to change prior to the final release.

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.

The database created by this sample is NsbSamplesNativeTimeoutMigration.

Running the project

  1. Start the Endpoint.NHibernate and Endpoint.SqlPersistence projects.
  2. Close them after The timeout has been requested. Press any key to exit is displayed.
  3. Open MigrateFromNHibernate.sql script in SQL Server Management Studio.
  4. Replace the endpoint name value in the script with Samples.SqlServer.NativeTimeoutMigration
  5. Run the script. The outcome should be (1 row(s) affected).
  6. Repeat the steps 3-5 for the MigrateFromSql.sql script.
  7. Start the Endpoint.Native project.
  8. Observe the message Hello from MyHandler displayed two times meaning that the delayed messages have been successfully picked up by the native handling in the SQL Server transport.

Migration scripts

It may be necessary to modify the presented scripts to introduce batching, especially when there's a large number of timeouts that need to be migrated (in the range of thousands).

The following script can be used to migrate the delayed messages from NHibernate persistence (assuming SQL Server is used as a database):

declare @endpointName nvarchar(max) = N'PUT ENDPOINT NAME HERE';

declare @endpointSchema nvarchar(max) = N'dbo';
declare @dalayedTableSuffix nvarchar(max) = N'Delayed';

declare @migrateScript nvarchar(max);
set @migrateScript = N'
    with message as (
		select *
		from TimeoutEntity with (updlock, readpast, rowlock)
		where [Endpoint] = ''' + @endpointName + '''
			and [Time] is not null)
	delete from message
	output
		left(deleted.Headers, len(deleted.Headers) - 1) + '', "NServiceBus.SqlServer.ForwardDestination": "'' + deleted.Destination + ''" }'',
		deleted.State,
		deleted.Time
	into [' + @endpointSchema + '].[' + @endpointName + '.' + @dalayedTableSuffix + ']
';
exec(@migrateScript);

The following script can be used to migrate the delayed messages from SQL persistence (assuming SQL Server is used as a database):

declare @endpointName nvarchar(max) = N'PUT ENDPOINT NAME HERE';

declare @endpointSchema nvarchar(max) = N'dbo';
declare @dalayedTableSuffix nvarchar(max) = N'Delayed';
declare @timeoutDataTable nvarchar(max) = REPLACE(@endpointName,'.','_') + '_TimeoutData';

declare @migrateScript nvarchar(max);
set @migrateScript = N'
    with message as (
		select *
		from ' + @timeoutDataTable + ' with (updlock, readpast, rowlock)
		where [Time] is not null)
	delete from message
	output
		left(deleted.Headers, LEN(deleted.Headers) - 1) + '', "NServiceBus.SqlServer.ForwardDestination": "'' + deleted.Destination + ''" }'',
		deleted.State,
		deleted.Time
	into [' + @endpointSchema + '].[' + @endpointName + '.' + @dalayedTableSuffix + ']
';
exec(@migrateScript);

Related Articles


Last modified