Add Streams on top of relational database

Implementing an Orleans Stream Provider on top of a relational backend has come up as a subject a few times in Gitter, in https://github.com/dotnet/orleans/issues/623 and is connected to https://github.com/dotnet/orleans/issues/255. We could open an official issue about it as queuing, and streams, is a common scenario and especially on-premises development happens largely on top of relational backends. The following ideas are applicable to all relational databases, but to keep this simple, let’s assume SQL Server for the time being.

As mentioned in https://github.com/dotnet/orleans/issues/623 already, there’s an interesting article Using tables as Queues by Remus Rusanu. The ideas presented about FIFO queues look like sound plan to implement Orleans Streams of interest are the ordering guarantees. However, it might be prudent to explore the use of ANSI SQL 2003 SEQUENCE. This feature is widely available in various relational databases and also on SQL Server 2012 and later.

As of myself, I don't have a more concrete plan or schedule on starting this (though I've expressed interest), but a rough idea for the SEQUENCE part could be to mimick a ring buffer on a table. The table could something like following (might be inaccurate with regard to the needs of Orleans Streams, but the gist of it)

CREATE TABLE StreamRingBuffer
(
    [SequenceToken] BIGINT NOT NULL,
    [Slot] BIGINT NOT NULL,
    [StreamId] NVARCHAR(256),
    [EnqueuedOn] DATETIME2(7) NOT NULL,
    [ReferenceCount] TINYINT NOT NULL,
    [Payload] VARBINARY(MAX)
);

Here sequence token would be generated like

CREATE SEQUENCE EnqueueSequence AS BIGINT START WITH 1 INCREMENT BY 1 CACHE 500000;
SET @sequenceToken = NEXT VALUE FOR EnqueueSequence;
SET @slot = @sequenceToken % 500000;

This table could, or better perhaps should, be prefilled to avoid constant CPU churn on statistics, index updating, page allocation churn etc. If a variable size is needed, perhaps a feasible strategy would be to add a trigger that allocates a bigger table (as a note, perhaps doubling always the size, cf. amortization).

If I were to implement this, I would continue on the path of adding a few new queries to the database and use the same idea in code as with other tables.