• Michael Lang
• | October 22, 2013
• | Azure
• | 13 Comments
• | auto increment, azure, sequence generator, Windows Azure

SequenceId Generator for Azure or any Big Table Storage

Windows Azure and some other cloud table storage systems do not support auto-increment identity columns as Microsoft Sql Server does. Azure groups records by a dual key, the partition key and the row key. The partition key is a group of related records that should all be stored together and you usually query within a single partition at a time. The partition key should not be a unique value. However, the row key must be unique within the partition.

How do you create a unique identifier in Azure or cloud table storage systems? You have to think different than in traditional relational databases. There are no foreign keys and there are no queries with a table join. So why would you want an auto-increment primary key like meaningless value? Your partition and row key values should be a meaningful value. You may even end up storing your records duplicated in multiple tables with different keys just so that they can be searched by different partition keys. Sometimes you need to make a meaningful column value that you can give someone as a reference number. Maybe it is an order number or a confirmation number. This is the scenario the code in this article is targeted at.

Sequence Requirements

The required features in a cloud table unique identifier are

• It must be unique every time, obviously.
• A role (web or worker) crash should not allow any reuse of identities. Skipping some due to a crash or role stop is ok.
• It must not require the identity to be inserted in the order it was generated, since http requests to the table storage may not complete in order of submission.
• Ids can be short memorable values, alpha numberic, just alpha, just numeric, or a custom set of characters. 36 character GUID’s are not memorable.
• The sequence generator should not require synchronization to a central database server for each new identity value needed. You may have many nodes (web or worker) all attempting to insert records at the same time, and they should not block each other. Some synchronization is expected, but it should be minimal.
• Each node (web or worker) should not require special configuration so you can deploy your cloud application and scale up and down at will easily.

Usage Options

You can define multiple instances of a sequence generator, such as one per repository with a singleton lifetime. Or you can store a singleton shared sequence generator instance for all repositories. That decision depends on your dependency inversion architecture. Don’t define a sequence generator for each occurrence that you need an identity as that would waste the rest of the reserved block. I generally create an instance of a sequence generator once per role (web or worker) per repository type. I do this by using ProviderResolver<T> found in a previous article. You can also use a dependency injection framework of your choice.

The first time an identity is needed for a sequence, a block is reserved from the IOptimisticSyncStore given to the generator. The SequenceGenerator will reserve a block of generated sequences and callers will get those until they are used up, then it will repeat by reserving another block. Here is how you use it.

	private SequenceIdGenerator ConfirmationNumberGenerator {get {...}}
	public void SubmitOrder(Order order){
		// I pass in the name of the table the sequence is for.  You can use a single
		//	name for many tables if you don't want the same Id duplicated across them all.
		var generatedId = ConfirmationNumberGenerator.NextId(ConfirmTableProxy.TableName);
		order.ConfirmationNumber = generatedId

		//TODO: persist the order...
	}

You can create an instance of a sequence generator for a single sequence, or for all defined sequences. Here is how you define a sequence generator to work with a single sequence, with the definition of the sequence in-line. The in-line definition is only used if the azure table data store does not already have the sequence schema defined. The Sequence stores the schema separately from the last reserved value of the sequence. So updating the sequence schema during multiple application role instance starts does not hurt anything.

namespace MyApp.AzureStorageProvider
{
	public class OrderRepository : MyApp.OrderRepository  //derives from System.Configuration.Provider.ProviderBase
	{
        private SequenceIdGenerator _confirmationNumberGenerator;
        private SequenceIdGenerator ConfirmationNumberGenerator
        {	//lazy loaded property, need to wait for ConnectionName to be set before loading
            get
            {
                return _confirmationNumberGenerator ?? (
                    _confirmationNumberGenerator =
                        new SequenceIdGenerator(
                            new CloudTableSequenceIdOptimisticSyncStore { ConnectionName = ConnectionName },
                            new SequenceIdStore
                                {
                                    Schema = new SequenceIdSchema
                                        {
                                            TableName = ConfirmTableProxy.TableName,
                                            CharacterSetType =
                                                LexicalCharacterSetType.AsciiAlphaNumeric,
                                            SeedValue = "00A000"
                                        }
                                }) { IgnoreCase = true });
            }
        }

	}
}

This sample defined a ‘ConfirmTableProxy’ property definition not shown here. The type of that property and how to use it can be found in post Azure SDK Extensions – Unofficial

How do you get it?

You can get the source directly in Candor common, data namespace for the sequence generation; For the Azure implementation CloudTableSequenceIdOptimisticSyncStore from Candor common, WindowsAzure namespace

You can also find it on NuGet, using the Visual Studio package console Install-Package Candor.Core; You can install the CloudTableSequenceIdOptimisticSyncStore from package Install-Package Candor.WindowsAzure

How is it different from Snowmaker?

This idea originated from Snowmaker by Tatham Oddie and his idea is from Josh Twist on MSDN. See references below. The biggest difference in this implementation from the others is that this version supports alpha numeric or any other character set as identities. Also both of those implementations used Azure blob storage text files to store the last reserved identity per table, whereas this implementation uses Azure table storage. Both blob storage and table storage support optimistic concurrency using ‘ETag’. This implementation uses table storage instead so that it can record schema information for each sequence.

References

Sequence Id generation for. short high capacity identifiers

Sequence ID generation for short high capacity identifiers

Azure SDK Extensions – Unofficial

Azure SDK extensions – unofficial

Inspiration Sources

Tatham Oddie, July 2011: Released: SnowMaker – a unique id generator for Azure (or any other cloud hosting environment)
Released: SnowMaker – a unique id generator for Azure (or any other cloud hosting environment)

Josh Twist, Nov 2010: Synchronizing Multiple Nodes in Windows Azure
http://msdn.microsoft.com/en-us/magazine/gg309174.aspx