SQL Persistence Upgrade Version 1.0.0 to 1.0.1

Convert Outbox Index to Nonclustered

This upgrade is only required by Endpoints that are using both Microsoft SQL Server and Outbox.

This is a optional performance optimization that is only necessary for high throughput endpoints. All new endpoints created with Version 1.0.1 and above will have this optimization applied.

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.

Since Version 1.0.1 does not require the nonclustered index to function, the conversion of indexes over to nonclustered can be done before or after the upgrade to 1.0.1.

Stop endpoint

Stop the affected endpoint.

This process can be done on a per-endpoint basis or in bulk for all affected endpoints.

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:

Find the index name by querying sys.tables and sys.indexes.
Execute a dynamic DROP CONSTRAINT command.
Execute a dynamic ADD CONSTRAINT command.

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.