Prerequisites
An instance of SQL Server Express, accessible as .
.
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
- Start the Endpoint.NHibernate and Endpoint.SqlPersistence projects.
- Close them after
The timeout has been requested.
is displayed.Press any key to exit - Start the Endpoint.Native project.
- Close it after
The endpoint has started.
is displayed.Run the script to migrate the timeouts. - Open
MigrateFromNHibernate.
script in SQL Server Management Studio.sql - Replace the endpoint name value in the script with
Samples.
SqlServer. NativeTimeoutMigration - Run the script. The outcome should be
(1 row(s) affected)
. - Repeat the steps 5-7 for the
MigrateFromSql.
script.sql - Start the Endpoint.Native project.
- 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);