Getting Started
 NServiceBus
 Transports
 Persistence
 ServiceInsight
 ServicePulse
 ServiceControl
 Monitoring
 Previews
 Samples

Migrating delayed messages from persistence to SQL Server transport

NuGet Package: NServiceBus.SqlServer (4.x)
Target Version: NServiceBus 7.x

Prerequisites

An instance of SQL Server Express, accessible as .\SqlExpress.

At startup, each endpoint creates 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. Start the Endpoint.Native project.
  4. Close it after The endpoint has started. Run the script to migrate the timeouts. is displayed.
  5. Open MigrateFromNHibernate.sql script in SQL Server Management Studio.
  6. Replace the endpoint name value in the script with Samples.SqlServer.NativeTimeoutMigration
  7. Run the script. The outcome should be (1 row(s) affected).
  8. Repeat the steps 5-7 for the MigrateFromSql.sql script.
  9. Start the Endpoint.Native project.
  10. 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):

Copy code|Copy usings|Edit
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):

Copy code|Copy usings|Edit
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

Feedback