Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Using SQL Server Sequences in Integration

Dan Field

The Challenge

An integration scenario requires a unique incrementing numeric identifier to be sent with each message (or each record in a message).  These identifiers cannot be reused (or cannot be reused over certain ranges, or cannot be reused over certain periods of time). A GUID is not suitable because it will not be sequential (not to mention that many legacy systems and data formats may have trouble handling a 128 bit number!).

The Solution

Integration platforms will have a hard time meeting this on their own – GUIDs work well because they guarantee uniqueness on the fly without needing to worry about history.  Messaging platforms typically deal in terms of short executions, and BizTalk is no exception. While persistence of a message might be handled (such as BizTalk does with the MessageBox), persistence of the entire execution process is usually not guaranteed.  Deployments, updates, or even system resets may bring the integration platform down temporarily, and building a singleton instance that knows how to keep track of such things and compensate can become a major task.

However, if you’re using SQL Server 2012+, you have the option of creating a sequence and having the database guarantee the uniqueness and incremental nature for you.  Sequences in SQL Server work somewhat like IDENTITY Columns, but are not tied to a particular column in the database and can be used for many other purposes.  Creating one is fairly simple:

CREATE SEQUENCE dbo.CountBy1
    AS int
    START WITH 1
    INCREMENT BY 1 ;

There are several other options documented here, which include specifying the return type, whether to recycle when the sequence hits a certain number, etc. Sequences make it possible to either get the next available number

NEXT VALUE FOR Test.CountBy1

or a range of available values starting with the first one:

CREATE FUNCTION fReserve100()
RETURNS int
AS
BEGIN
  DECLARE @first int;
  -- reserve the next hundred numbers from the sequence, and tell us what one to start with
  sp_sequence_get_range @sequence_name = N'dbo.CountBy1', @range_size = 100, @range_first_value = @first OUTPUT;
  RETURN @first;
END

BizTalk applications can leverage this functionality by either directly calling one of these functions and using the return value, or by calling some other stored procedure that leverages a sequence and allows the integration to pick the message back up. If you’re simply using a sequence to seed a number in a map, an inline ADO.NET call in a pipeline component or orchestration is probably your best bet (it’s generally best to avoid this kind of logic in a script or external assembly call from the map itself though – exception handling in maps gets pretty hairy pretty quickly, and it’s very easy to lose track of the scope of how often such a function would get called in a map). For example:

int NextSeqNum()
{
  using (SqlConnection conn = new SqlConnection(Config.ConnectionString)
  {
    string sql = "SELECT NEXT VALUE FOR dbo.CountBy1";
    using (SqlCommand cmd = conn.CreateCommand())
    {
      cmd.CommandText = sql;
      int result = (int)cmd.ExecuteScalar();
      return result;
    }
  }
}

Obviously, this could be expanded to allow you to call the function we defined earlier, or a function that allows you to reserve a specific range from the sequence, etc.

Caveats

There are a few things to be aware of. Much like IDENTITY specs, SQL Server will not allow you to return used sequence numbers that you decide you don’t want to use (i.e. if your transaction fails and you’re trying to compensate down the line); if you’re using a sequence generated number for multiple destination systems and one fails in a way that the same number should not be used again for that system, you should rollback the transaction for all systems that have received the message. Destination systems that expect no gaps in sequencing will either have to be configured to allow reuse of failed numbers or tolerance for gaps that get created by failure.

Sequences can also be reset or recreated in the database independently of the integration use. However, they can also be manually set to start with a certain number if this happens.

If you use a sequence value in a column that doesn’t have a unique constraint in SQL Server, repetitions will be allowed (unlike an identity column). While any call

NEXT VALUE FOR

should give you a unique incrementing number (assuming the sequence hasn’t recycled or been manually reset), there’s some sacrifice here in terms of uniqueness. If the requirement really is for a unique identifier, you may have to sacrifice some of the ability to have it be sequential.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>