Getting Started
Architecture
NServiceBus
Transports
ServiceInsight
ServicePulse
ServiceControl
Monitoring
Samples

SQL Persistence Upgrade Version 1.0.0 to 1.0.1

Component: Sql Persistence

Convert Outbox Index to Nonclustered

As the MessageId is not guaranteed to be sequential a nonclustered index gives better performance. Applying this change results in the table being treated as a heap.

Performing the upgrade

Perform the steps described in this section for all endpoints that that are using Microsoft SQL Server, Outbox and Sql Persistence Version 1.0.0.

Stop endpoint

Stop the affected endpoint.

Convert to Nonclustered

Run the following upgrade script

declare @table nvarchar(max) = @tablePrefix + 'OutboxData';
declare @index nvarchar(max)
declare @dropSql nvarchar(max)
declare @createSql nvarchar(max)

select @index = si.name
from sys.tables st
  join sys.indexes si on st.object_id = si.object_id
where st.name = @table
  and si.is_primary_key = 1

select @dropSql = 'alter table ' + @table + ' drop constraint ' + @index
exec sp_executeSQL @dropSql;

select @createSql = 'alter table ' + @table + ' add constraint ' + @index + ' primary key nonclustered (MessageId)'
exec sp_executeSQL @createSql;

This script takes a tablePrefix as a parameter and then performs the following actions:

This script can be executed as part of a deployment using the following code:

using (var connection = new SqlConnection("ConnectionString"))
{
    connection.Open();
    using (var command = connection.CreateCommand())
    {
        command.CommandText = File.ReadAllText("PathToConvertOutboxToNonclustered.sql");
        var parameter = command.CreateParameter();
        parameter.ParameterName = "tablePrefix";
        parameter.Value = tablePrefix;
        command.Parameters.Add(parameter);
        command.ExecuteNonQuery();
    }
}

Start endpoint

Start the effected endpoint.