How do you do feature flags with stored procedures

Question

I have a system I'm working on that has almost all of its logic in SQL Server stored procedures. As part of improving the development practices we want to move to a continuous delivery model using feature flags (aka toggles) to enable functionality in production.

How can I write the stored procs so that they check the flags efficiently and don't add load to the database by hammering a config table every time the procs are called?

How many flags / features are we talking about? How often is the average stored procedure called? What is the nature of the typical change that is required by a new feature? At my previous job we were able to make almost all feature enhancements fully backward compatible - the apps didn't know about them until we wanted them to. — Aaron Bertrand, Aug 07 '12 at 01:29
Procs are called all the time - it's a pretty large and busy app. The number of flags will build up over time as part of normal development. They will get culled at regular intervals, but I'm not sure what the high water mark will be - I'm estimating over 100. — Richard Banks, Aug 07 '12 at 01:32
Can you show an example of a change in procedure behavior that depends on a flag? — Aaron Bertrand, Aug 07 '12 at 01:33
It's just a simple SQL if-else statement that wraps the parts of the proc where functionality is being changed. The check is looking at variable(s) that are read from a settings table via a simple select statement. — Richard Banks, Aug 07 '12 at 01:39
Yes I understand theoretically what you are doing. I'm suggesting that perhaps there is a way to make some of these changes forward compatible without relying on flags for every single one of them, but I can't address the how if I don't have any context other than "I use IF"... — Aaron Bertrand, Aug 07 '12 at 01:40
We're in the initial stages of trying to do this and looking for an approach that will work in the long term and can be applied consistently. If you have multiple approaches that work with or without using 'if' then please provide it as an answer. I'm all ears :-) — Richard Banks, Aug 07 '12 at 01:50
I don't have an answer to what I don't know you're trying to do, sorry. — Aaron Bertrand, Aug 07 '12 at 01:55
Instead of keeping the values in a table, you could code them in a function. Either one function per value (`fnGetZIPCodeSize()`), or pass a parameter in to select the appropriate output value (`fnConfigValue( @Param as VarChar(42) )`). Avoids table access, no contention, easy to modify. You can complicate it as much as you like with feature sets, functions calling functions, features switching on specific dates, ... . — HABO, Aug 07 '12 at 02:14
@HABO I'm not sure how much more efficient that will be than a well-indexed table that will almost certainly always be in memory. And I also find data hard-coded in a function very hard to review and maintain. YMMV. — Aaron Bertrand, Aug 07 '12 at 02:34

score 2 · Accepted Answer · answered Aug 07 '12 at 02:40

I'm not convinced you need to prematurely optimize for a performance problem you don't know is going to exist. If your table has 100 rows and it's referenced often, it will almost certainly be in memory 100% of the time and access will be a non-issue.

One way that we have made code forward-compatible is to add a parameter to the procedure, with a default value, and the app can "upgrade" when the app is ready to do so. This can be done via a config file parameter, but presumably the app would have to be re-compiled to take advantage of the new functionality anyway.

As a quick example:

CREATE PROCEDURE dbo.doStuff
  @version DECIMAL(10,2) = 1.0
AS
BEGIN
  SET NOCOUNT ON;

  IF @version >= 1.1 
  BEGIN
    PRINT 'This only executes if the app tells us it is 1.1 or newer.';
  END

  IF @version >= 2.5
  BEGIN
    PRINT 'This only executes if the app tells us it is 2.5 or newer.';
  END
END
GO

When all of the apps are up to date, you can increase the base version on the parameter. Otherwise they can all be updated at their own rates, and the schema can progress at a different rate. If you can correlate each feature to a sequential point release, this shouldn't be too difficult to manage. But again I'll insist that a 100-row table is not going to drag your performance down as much as you seem to think it will...

I like the approach, though it'd be a flag check not a version check. It made me realise the flag may be better coming from the app instead of the database - that was the approach I was thinking of simply because we're so used to doing everything in the procs. If it's from the app I can store values in a config file for use in both the app and the db. — Richard Banks, Aug 07 '12 at 03:38

score 1 · Answer 2 · answered Aug 07 '12 at 02:36

You can use CONTEXT_INFO to store 128 bytes of flags for the lifetime of your session or connection.

Create a function to retrieve the flags values:

create function dbo.GetConfigFlags() returns VarBinary(128)
  begin
  -- Retrieve the configuration flag values.
  --   This can be context sensitive, e.g. return different values based on user, server, ... .
  declare @Result as VarBinary(128)
  if Context_Info() is NULL
    set @Result = 12345 -- Get value from table or hard code here.
  else
    set @Result = Context_info()
  return @Result
  end

Start each stored procedure with code that gets the flags if they are not already loaded:

if Context_Info() is NULL
  begin
  declare @ConfigFlags as VarBinary(128) = dbo.GetConfigFlags()
  set Context_Info @ConfigFlags -- This is not allowed within a function.
  end
select Context_Info() -- Demo.

The ugly part is managing meanings for the bits.

How do you do feature flags with stored procedures

2 Answers2