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