Migrating delayed messages from persistence to SQL Server transport

Prerequisites

Ensure an instance of SQL Server (Version 2016 or above for custom saga finders sample, or Version 2012 or above for other samples) is installed and accessible on localhost and port 1433. A Docker image can be used to accomplish this by running docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d mcr.microsoft.com/mssql/server:latest in a terminal.

Alternatively, change the connection string to point to different SQL Server instance.

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. 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

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);