Terra Mystica - 6-7 Player Variant

Number of Rounds

With 6 players, reduce the number of rounds from 6 to 5.
(With 7 players, you may want to reduce it even to 4 rounds.)

Reason: Having 6 or 7 players will make the game take too long for most people's comfort.
Note: If you do choose to play 6 rounds, you may want to also remove the variation rules for Structures.

Structures

Remove some structures in the setup. The structures you remove go in the box and cannot be built in the game, but leave empty spaces on the Player Boards, so they will generate income each round.

• With 6 players every player remove 3 Dwellings, 1 Trading Post, and 1 Temple.
• With 7 players every player remove 4 Dwellings, 2 Trading Posts, and 1 Temple.
• Since you remove a Temple during Setup, take a Favor Tile when you place your first structure. So the first player select the first Favor Tile, proceeding in turn order. Chaos Magicians do take 2 Favor Tiles, but since they always place structures last, they will choose their Favor Tiles last as well.
• In the Income Phase of each round, players receive extra income due to the buildings removed and from Favor Tile(s) selected.
• Structures removed do not give Victory Points for building, nor count as "ownership" of these structures.

Reason: More players will leave less open spaces on the map for building structures, so less structures are needed for each player.. Additionally, reducing the number of rounds from 6 to 5 reduces the overall resources acquired during the game; so increasing resources gained by removing these structures from your board offsets this.
Note: An optional rule even in the base game, with 5 players every player may remove 1 Dwelling and 1 Trading Post.

Cultist Board

If the Cultist Board has all four positions of an element occupied, you may still move your cultist to that element on the Cult Board by paying to remove one of those currently occupying a space.

• Pay a number of coins equal to the value of the space (2 or 3) to the opponent whose Priest you are removing.
• You may only remove a Priest from an element track when your Action is to move one of your Priests to the same track, and when that track already has all four positions occupied.
• You may not remove a Priest from an element that you currently have a Priest on.
• The opponent's priest is removed from the game (move it to the game box, not back to the player's Priest pool).
• Once you have paid to remove the opponent's Priest, place your Priest and advance your marker on the Cult Track as normal. The opponent's marker does not move down when their Priest is removed.
• As normal, you may still move 1 Priest to your pool to advance 1 space on the Cult Track.

Reason: To allow all players to be able to send a Priest to a Cult track.

Power Actions

Every Power Action can be used any number of times. The cost is 1 extra Power for each time the Power Action has already been used that round. Keep track of this by adding extra X tokens next to the action.

Reason: To allow more players to be able to use Power Actions.

Towns

There is no limit to the number of Towns that can be founded. If you found a Town and there are no Town Tiles left, you receive 5 Victory Points instead, as well as any other Faction bonus (Swarmlings receive 2 Workers, Witches 5 extra Victory Points). You do not receive a Town Tile, nor a Key.

Reason: The town limit is not reached in a normal game. Without these adjustments, it is very likely to reach the limit early on with 6 or 7 players, which does not make sense in the setting. It might also unbalance some factions to not be able to set up towns, especially the Chaos Magicians who are handicapped in the first round.
Comment: Normally there is a limit to 10 Towns, or 14 if you have the 4 extra Essen Town Tiles.

You may found a Town with only 3 connected structures if one of them is a Fortress, exactly like if one is a Sanctuary, but only if you have already built your Sanctuary. If you build a Sanctuary and already have a Fortress with 3 connected structures of sufficient value for a Town, you immediately found the Town under that Fortress.

Reason: Both the reduced number of Structures and the increased number of opponents makes it more difficult to gain 4 connected structures.

Scoring Tiles

With 6 players, in the Setup remove 3 Scoring Tiles of 3 different Cults.
With 7 players, in the Setup remove 4 Scoring Tiles of 4 different Cults.

ed: I need to read the rules again to see if this is worded correctly, because I'm not sure what this means as written.

Bonus Tiles

With 6 players use all the Bonus Tiles.
With 7 players use all the Bonus Tiles, and at the end of each Round place 2 Coins (instead of 1 Coin) on every Bonus Tile that was not chosen. (Or, if you have it, add the Spielbox Promo Bonus Tile instead).

Favor Tiles

With 6 players, the four 3-advance-Tiles can be taken twice. The first player that takes one of these tiles also gains 3 extra Power.
With 7 players, the four 3-advance-Tiles can be taken three times. The first player that takes one of these tiles gains 5 extra Power, the second gains 3 extra Power.
You cannot take a 3-advance-Tiles you currently own a second time (so if another player takes it from you, you can take it back).
To keep memory of the number of times that these Tiles were taken, put 1 Coin (or 1 Worker) on it each time it is taken.

Power Tokens

With 6 players, each player starts with 2 less Power tokens in Bowl I (5 tokens leftover).
With 7 players, each player starts with 3 less Power tokens in Bowl I (2 tokens leftover).

ed: I need to read the rules again to see if this is worded correctly, because I'm not sure what this means as written.

Improvise Other Limited Game Pieces

Some less important components may run out; just use improvised tokens/pieces, or adapt to not need them.

• If more than 5 players exceed 100 Victory Points, write "100 Victory Points" on cards, or just write down players with over 100 VP on a piece of paper.
• There are only 5 Rule Summary tiles, so either share 1 or 2 between two adjacent players, or copy them onto index cards.
• Treat worker cubes and coins as if they are unlimited. If they run out, use some other tokens to represent them (Monopoly money, toothpicks, whatever).
• If Terrain Tiles runs out, use something else to represent them.
• Alternately, remove Terrain Tiles that are under an established Structure. The current terrain only serves to restrict what structures may be built, and to determine what the cost is to terraform a tile to another type of terrain. Therefore, once a structure is placed, the only purpose of leaving a terraforming Terrain Tile under it is to reassure the other players that the cost of terraforming has already been paid.
• There are already enough Faction Boards, Structure pieces, and Action Markers for up to 7 players.

Credit

Originally from a post at goblins.net/downloads/terra-mystica-regolamento-italiano-faq-varianti, by Davide Malvestuto aka Principe Konrad on Jan 13th, 2013.
Translated by him into English on a post at boardgamegeek.com/article/15802476#15802476
Modified for this blog post at abamacus.blogspot.com

Universal Password Blacklist

One password-rule to rule them all: Universal Password Blacklist

When someone uses a password, it proves that it is not very "unique"; just being used once either proves it was already low-entropy, or makes it lower-entropy (because the user could have it written down, the system that accepted it might store it in the clear, etc). So no one, anywhere, ever, should be allowed to create an account with that password again.
How to you prevent a password from every being used again? Simple, create a public, universal blacklist for passwords. This one stroke, by itself, forces users to invent ever-more-entropy-laden passwords as time passes.
Of course, you need to do it securely. Which is the rest of this blog post.

Part 1:
My first thought (from a few years ago) is that when passwords are replaced (invalidated) in anyone's system, those passwords should be published, totally publicly.

When you change your password, you always have to enter your old password, and the new password. Also, when you delete an account, you usually have to enter your old password to do it. Either way, this transaction includes a "delete this password" component.
Some systems would automatically store this as a local blacklist of some kind, maybe just in the hashed form; but as far as I know, no one has tried to share these blacklists with other companies, and certainly no one publishes them to the world, which is basically what I am proposing.

If you're on board with black-listing these passwords from your system forever, why not publish them? They're no good anymore, so you could even store these now-obsolete password in the clear! (there are actually potential down-sides to storing in the clear, but since I remove this below, I won't go into them)

So send the now-blacklisted password to a giant repository in-the-sky (cloud). Get a bunch of companies to adopt this common system, and you are on your way to creating a Universal Password Blacklist. You could have a bunch of separate services implement this independently, and source extra black-listed values from each other, in any way that each one finds acceptable.

You end up with a distributed database, kindof block-chain style, but where it may be perfectly acceptable that some databases never end up agreeing with each other.

Part 2:
The "blacklist" part would necessitate checking if a new password is pre-existing. And this would always require a server-side check -- because no client-side system is going to download a terabyte of passwords just to create a new account, that'd be ka-razy. So you need a secure way to check if your new password exists in this Universal Password Blacklist. Luckily, we know how to do that.
1) Adopt a "cryptographic hash" that everyone is happy with
2) Run you tentative new password through this hash
3) Call new-service, to check if this hashed-value exists in their database of passwords
If it exists, it's a no-go, you need to input a better password. If it doesn't exist, go ahead and create the account.

Bonus: No Part 1 Necessary!
Alert security-wonks may have realized that this "read" can also function as a "write", So in Part 2, the "read" to check if that hashed value exists, can be implemented as a "write", like a SQL "insert". If the value already exists, you get a "duplicate key error", and you return to the caller that they should not allow the password that translated into this hashed value. If it did not exist, you successfully write it to the Universal Password Blacklist, and return to the client that this password has never been used before.

Side Note:
This hash should be used only for this system. That is, if someone else used exactly the same process for their internal password-storage, then everyone could "brute-force" attack the hashed values, and reverse it to find original passwords. Ideally this is still very difficult, but there is no reason to not add your own salt-and-pepper-hash to your system, that is distinct from the one adopted for the Universal Password Blacklist, and then this problem is non-existent.

Extra Risk & Mitigation
Risk:  The only real reason I can think of to not accept random inputs from all-comers is if you're worried about DOS-style flooding. Imagine someone hates security, so they just flood you with random "passwords", and you have to just soak up all this data and store it permanently, which is a burden that gives no benefit.

Migitation: I think you could force a client to solve a "hard" problem (burn a number of CPU cycles) per use. This should give a sufficient disincentive to fill your system with noise.

Thoughts?

I've run this by a few security-conscious friends of mine, and have found nothing to dissuade me that this is a great idea. I would love it if someone could find a problem with it. Or if someone, somewhere, would implement it. Either one would be great. I claim no patent, or any other ridiculous IP-right, on this idea, so please take it and use it!

In Defense of Passphrases

Ever since the XKCD comic on Password Strength became popular, I've heard more and more disparaging remarks about how passphrases are worse than more "random" passwords. I don't understand all the hating on passphrases; the basic idea of them, as I see it, is that words are easier for humans to memorize, and create associations between, than random gibberish characters.

Now it's a given that both "gibberish" passwords and long passphrases can both be done poorly -- "correct horse battery staple" is now a terrible password, because it was featured in the comic. But so is "2143658701badcfe" (even if you somehow think that string was random, the fact that it now appears in this blog post makes it a bad choice). But I think these naysayers do not understand the value that passphrases adds -- it is easier (for most people) to remember words than random characters, of the same entropy (if you aren't familiar with "entropy", think "randomness"). But let's try to prove it with some simple examples.

First, how much entropy is enough? That's a complex question, but for our purposes let's just say 80 bits; this is based on this Q&A entry. Whether 80 bits is enough or not doesn't really matter -- if you want 160 bits, just double the lengths of all the values below.

What does 80 bits of entropy look like in English? The English language allegedly has around 1,000,000 words. Now we can't use them all; for one thing, very rare words are hard to remember. So let's pick from the most common 10,000 words. I'm using the 10,000 words at the top of this github page. I made that list by taking another list, and spending just a few minutes cleaning it. But I don't think anyone would object to 10,000 being a reasonable number of words for someone to know, however you come up with the list.

Now each word has a 1 in 10,000 chance of being selected. This doesn't go quite evenly into 80 bits, but 6 words works out to be 82% of the 80 bits. (7 words would be 820000% of 80 bits, so let's stick with 6 words)

I grabbed 6 random numbers from 1 to 10,000, and got:

• 6225, 1738, 4836, 6378, 7361, 8406.

Looking up those words on my list 10,000 word list gives:

• objections, shoulders, breathe, comrade, angrily, vs

That's what 80 bits of entropy looks like in English. So how does that compare to more "conventional" randomly generated passwords?

• Hex: 63485AE5638C1EDCC61E
• 20 hex digits is exactly 80 bits of entropy.
• Decimal: 236663118018716201382515
• 24 digits is ~80% of the entropy of 80 bits; close enough
• Base64: kiydPJHQh4jL7
• A lot of systems try to use all the number, upper and lower case letters, and sometimes other characters thrown in. This is pretty awful for humans, both because it takes longer to type in, and it's often hard to tell a 1 from I from l, 0 from O, etc. But including here for comparison. The math works out that 13 characters in base64 is 2^78; that is only 25% of 2^80, it's close enough.
• Passphrase: objections shoulders breathe comrade angrily vs

So now, you be the judge. You have to memorize one of these 5 choices; if you succeed, you will live a long and safe life, if you fail, your identity will be stolen and you will be miserable. Which one do you choose?

Or maybe you're trying to be "practical"; which one is easier to type in? Well I just timed myself typing in each one, on my normal keyboard, and my times were: 10 seconds, 8 seconds, 5 seconds, 7 seconds. So the terrible base64 was the fastest, presumably only due to the very low character count; but typing the long passphrase was second in speed. But I certainly wouldn't choose the base64 option, especially if you consider what it's like to type that into a phone/tablet; all the numbers and capital letters require multiple taps, it's terrible. Whereas the whole words could be swiped-in, since they are all recognizable, common words. I'm too lazy to try timing myself on a phone right now, but I would speculate that I can swipe-typing 6 words much faster than I can enter any of the other three random sets of characters above.

Now a real scientific test would be to formalize this a bit more, and run real memory tests on humans. But I think I have proved my point.

Just for fun/reference, here's the javascript code I used to help with the above:

var base64 = function(n){return '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz+/'[n];}
var rnd = function(max){return Math.floor(Math.random() * max)};
var rndDigits = function(base, digits){var s = '';
 for (var i = 0; i < digits; i++)
  s += base64(rnd(base));
 return s;}
console.log('Binary: ' + rndDigits(2, 80));
console.log('Decimal: ' + rndDigits(10, 24));
console.log('Hex: ' + rndDigits(16, 20));
console.log('Base64: ' + rndDigits(64, 13));
console.log(rnd(10000) + ', ' + rnd(10000) + ', ' + rnd(10000) + ', ' + rnd(10000) + ', ' + rnd(10000) + ', ' + rnd(10000));

// for marking times, I just pressed enter before and after each combination
document.addEventListener('keydown', function (e) { if (13 == e.keyCode) { console.log(new Date()); } }, false);

letItSnow() // javascript

// NOTE: This script does not work in some browsers, due to lack of support for SVG effects/animation.

'use strict';
function letItSnow(options) { // <3 CC0 
    var SVG_NS = 'http://www.w3.org/2000/svg';

    var getSnowflakeArm = function (x, y, radius) {
        // TODO: this could be enhanced to make much more intersting snowflake shapes, with input parameters and/or randomized values
        // round all numbers to a max of 2 decimal places
        var x0 = Math.round(x * 100) / 100;
        var y0 = Math.round(y * 100) / 100;
        radius = Math.round(radius * 100) / 100;
        var radiusOffset1 = radius / 5;
        var xArm1 = radius / 5;
        var yArm1 = radius / 10;
        var radiusOffset2 = radiusOffset1 * 2;
        var xArm2 = xArm1 * 2;
        var yArm2 = yArm1 * 2;

        var armPoints = [];
        // draw from center to tip
        armPoints.push({ a: 'M', x: x0, y: y0 });
        armPoints.push({ a: 'L', x: x0, y: y0 - radius });

        // draw first spoke -- from tip, to center, to tip
        armPoints.push({ a: 'M', x: x0 + xArm1, y: y0 - radius + yArm1 });
        armPoints.push({ a: 'L', x: x0, y: y0 - radius + radiusOffset1 });
        armPoints.push({ a: 'L', x: x0 - xArm1, y: y0 - radius + yArm1 });

        // draw second spoke
        armPoints.push({ a: 'M', x: x0 + xArm2, y: y0 - radius + yArm2 });
        armPoints.push({ a: 'L', x: x0, y: y0 - radius + radiusOffset2 });
        armPoints.push({ a: 'L', x: x0 - xArm2, y: y0 - radius + yArm2 });

        var d = '';
        for (var j = 0; j < armPoints.length; j++) {
            var p = armPoints[j];
            d += '\n' + p.a + p.x + ',' + p.y;
        }
        return d;
    }

    var getSnowflake = function (x, y, radius, speed, spin, clockwise) {
        var secondsSpin = (11 - spin) / 4; // spin should be from 1 to 10
        var sign = clockwise ? 1 : -1;

        var g = document.createElementNS(SVG_NS, 'g');

        // the bigger the snowflake, the thicker you need the lines to be
        var strokeWidth = Math.round(radius / 15 * 100) / 100;
        var d = getSnowflakeArm(x, 0, radius);
        for (var i = 0; i < 6; i++) {
            var path = document.createElementNS(SVG_NS, 'path');
            //path.setAttribute('id','Mine' + i);
            path.setAttribute('d', d);
            path.setAttribute('fill', 'none');
            path.setAttribute('stroke-width', strokeWidth);
            path.setAttribute('stroke', '#ffffff');

            var animate = document.createElementNS(SVG_NS, 'animateTransform');
            animate.setAttribute('attributeName', 'transform');
            animate.setAttribute('type', 'rotate');
            animate.setAttribute('from', (i + 0) * 60 * sign + ' ' + x + ' ' + 0);
            animate.setAttribute('to', (i + 1) * 60 * sign + ' ' + x + ' ' + 0);
            animate.setAttribute('dur', secondsSpin + 's');
            animate.setAttribute('repeatCount', 'indefinite');
            path.appendChild(animate);

            g.appendChild(path);
        }

        var h = window.innerHeight;
        var secondsFall = h / speed;
        var fallenBeginSeconds = secondsFall * y / h;
        var animate = document.createElementNS(SVG_NS, 'animateTransform');
        animate.setAttribute('attributeName', 'transform');
        animate.setAttribute('type', 'translate');
        animate.setAttribute('from', '0 ' + (0 - radius));
        animate.setAttribute('to', '0 ' + (h + radius));
        animate.setAttribute('dur', secondsFall + 's');
        animate.setAttribute('begin', -fallenBeginSeconds + 's');
        animate.setAttribute('repeatCount', 'indefinite');
        g.appendChild(animate);

        return g;
    }

    { // validation/defaults for all used option values
        if (typeof options == 'undefined') options = {};
        var validOrDefault = function (value, min, max, defaultValue) {
            if (isNaN(value))
                return defaultValue;
            value = Number(value);
            if (value < min || value > max)
                return defaultValue;
            return value;
        }
        options.numFlakes = validOrDefault(options.numFlakes, 1, 500, 75);
        options.minSize = validOrDefault(options.minSize, 5, 50, 5);
        options.maxSize = validOrDefault(options.maxSize, 5, 50, 20);
        options.minSpin = validOrDefault(options.minSpin, 0, 10, 3);
        options.maxSpin = validOrDefault(options.maxSpin, 0, 10, 7);
        options.minSpeed = validOrDefault(options.minSpeed, 0, 100, 10);
        options.maxSpeed = validOrDefault(options.maxSpeed, 0, 100, 90);
        if (isNaN(options.zIndex)) options.zIndex = -1;
    }

    // create <svg>, set to size of page, add animated snowflakes
    var svg = document.createElementNS(SVG_NS, 'svg');
    svg.setAttribute('width', window.innerWidth);
    svg.setAttribute('height', window.innerHeight);
    svg.style.position = 'fixed';
    svg.style.left = 0;
    svg.style.top = 0;
    svg.style.zIndex = options.zIndex;

    for (var i = 0; i < options.numFlakes; i++) {
        var x = Math.random() * window.innerWidth;
        var y = Math.random() * window.innerHeight;
        var radius = Math.random() * (options.maxSize - options.minSize) + options.minSize;
        var spin = Math.random() * (options.maxSpin - options.minSpin) + options.minSpin;
        var clockwise = (Math.random() > 0.5);
        var speed = Math.random() * (options.maxSpeed - options.minSpeed) + options.minSpeed;
        var snowflake = getSnowflake(x, y, radius, speed, spin, clockwise);
        svg.appendChild(snowflake);
    }
    if (isNaN(options.delay) == false) {
        var delaySeconds = Number(options.delay);
        if (delaySeconds > 0) {
            svg.style.display = 'none';
            window.setTimeout(function () { svg.style.display = ''; },
             delaySeconds * 1000);
        }
    }
    document.body.appendChild(svg);
}

javascript - getRGBA()

/* getRGBA:
  Get the RGBA values of a color.
  If input is not a color, returns NULL, else returns an array of 4 values:
   red (0-255), green (0-255), blue (0-255), alpha (0-1)
*/
function getRGBA(value) {
  // get/create a 0 pixel element at the end of the document, to use to test properties against the client browser
  var e = document.getElementById('test_style_element');
  if (e == null) {
    e = document.createElement('span');
    e.id = 'test_style_element';
    e.style.width = 0;
    e.style.height = 0;
    e.style.borderWidth = 0;
    document.body.appendChild(e);
  }

  // use the browser to get the computed value of the input
  e.style.borderColor = '';
  e.style.borderColor = value;
  if (e.style.borderColor == '') return null;
  var computedStyle = window.getComputedStyle(e);
  var c
  if (typeof computedStyle.borderBottomColor != 'undefined') {
    // as always, MSIE has to make life difficult
    c = window.getComputedStyle(e).borderBottomColor;
  } else {
    c = window.getComputedStyle(e).borderColor;
  }
  var numbersAndCommas = c.replace(new RegExp('[^0-9.,]+','g'),'');
  var values = numbersAndCommas.split(',');
  for (var i = 0; i < values.length; i++)
    values[i] = Number(values[i]);
  if (values.length == 3) values.push(1);
  return values;
}

Simple Creationary

Draw a card and build what you drew, using ten Legos or less, in one minute. Guess others' creations or have yours guessed to get points.

Details:
1) Everyone take ten Legos.
2) Everyone draw a card, and think of how to build a Lego creation that looks like that for a few seconds.
3) Start a timer for 60 seconds.
4) Everyone build at the same time. You may trade any block(s) with unused blocks in the center
5) Stop working when the time runs out.
6) Decide who will guess first, and go around once clockwise. Each player will choose a creation that they think is recognizable and make one guess. If the guess is correct, give one point to the guesser and one point to the builder.
7) First player(s) to 7 points wins!

SQL Server "Asynchronous" Stored Procedure (insert only)

So you have a SQL server stored procedure for logging, and its running a little slower than you like. There is no output, you just need to make sure the log message is put into the system. It would be really nice if there were a way to say "run this, and the client doesn't need to wait for a response", but unfortunately that's not a simple built-in feature. So how do you do it, with a minimum of headache?

Much of the credit for this goes to http://rusanu.com/2009/08/05/asynchronous-procedure-execution/. That post solves a slightly different problem - you want to execute a slow stored procedure that has no inputs, and check back later for the output -- without leaving a SQL connection open. But it was the basis of my solution here.

First, we setup a trivial example -- the destination table for the log message, and the stored procedure, which is slow but eventually does the insert. We will want to see what happens if the stored procedure fails, so we'll have a simple check to force an error.

CREATE TABLE [LogDestination]([LogValue] [varchar](max));

GO

CREATE PROCEDURE [usp_SlowProcedure]

  @message VARCHAR(MAX) AS

BEGIN

WAITFOR DELAY '0:00:00.5' -- wait 1/2 second

IF @message LIKE '%ERROR%'

  raiserror(@message, 16, 10);

ELSE

  INSERT INTO [LogDestination]([LogValue]) VALUES(@message);

END

GO

The solution will involve creating a QUEUE and a SERVICE, so you need to make sure your database has the Service Broker turned on. You can do that with this command:

ALTER DATABASE [MyDatabase] SET ENABLE_BROKER;

It sometimes seems to run forever -- it won't want to finish if there are any connections open on the database. You can force those to be closed with this option:

ALTER DATABASE [MyDatabase] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Now to start creating the new objects. For reasons of circular dependencies, you have to CREATE one of these first, and then ALTER it later. That's just the breaks. Let's put down the fundamental parts:

CREATE PROCEDURE [usp_AsyncExecActivated] AS

  PRINT 'placeholder'

GO

CREATE QUEUE [AsyncExecQueue]

 WITH ACTIVATION(STATUS = ON

                ,PROCEDURE_NAME = [usp_AsyncExecActivated]

                ,MAX_QUEUE_READERS = 1

                ,EXECUTE AS OWNER)

GO

CREATE SERVICE [AsyncExecService] ON QUEUE [AsyncExecQueue] ([DEFAULT]);

GO

CREATE PROCEDURE [usp_SlowProcedureAsync]

   @message VARCHAR(MAX)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @h UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @h

        FROM SERVICE [AsyncExecService]

        TO SERVICE 'AsyncExecService'

        WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @h (@message);

END CONVERSATION @h;

END

GO

Now we have:

• [AsyncExecQueue] - the queue to hold the messages on
• [AsyncExecService] - because only a service can write to a queue
• [usp_AsyncExecActivated] - stored procedure that will run in the background whenever the queue is written to
• [usp_SlowProcedureAsync] - to wrap all this up with, hiding the mess from the users. It has the same signature as [usp_SlowProcedure], but will return immediately.

One more consideration -- since this is completely asynchronous, the only errors the client can get are if the database is offline, out of space, etc. But if [usp_SlowProcedure] has an error, we want a table to just shove that input into, without leaving it on the queue. Or I guess you could omit this part, and the code that writes to it.

CREATE TABLE [AsyncErrored]([LogValue] [varbinary](max), [ErrorTime] DATETIME2 DEFAULT SYSDATETIME());

GO

Now we ALTER the background stored procedure, which is still just a placeholder, to actually do the processing.

ALTER PROCEDURE usp_AsyncExecActivated

AS

BEGIN

SET NOCOUNT ON;

DECLARE @h UNIQUEIDENTIFIER = NULL

      , @messageTypeName SYSNAME = NULL

      , @messageBody VARBINARY(MAX) = NULL;

RECEIVE TOP(1)

      @h = [conversation_handle]

    , @messageTypeName = [message_type_name]

    , @messageBody = [message_body]

FROM [AsyncExecQueue];

WHILE (@h IS NOT NULL)

  BEGIN

    BEGIN TRY

      DECLARE @message VARCHAR(MAX) = CAST(@messageBody AS VARCHAR(MAX));

      EXECUTE [usp_SlowProcedure] @MESSAGE

    END TRY

    BEGIN CATCH

      INSERT INTO [AsyncErrored]([LogValue]) VALUES(@messageBody);

    END CATCH

    END CONVERSATION @h WITH CLEANUP;

    SET @h = NULL;

    RECEIVE TOP(1)

          @h = [conversation_handle]

        , @messageTypeName = [message_type_name]

        , @messageBody = [message_body]

    FROM [AsyncExecQueue];

  END -- WHILE

END -- PROCEDURE usp_AsyncExecActivated

GO

That's all there is to it. But what good is a bunch of tables without running some tests to see it in action?

DECLARE @countQueue VARCHAR(10), @countDestination VARCHAR(10), @countErrored VARCHAR(10)

PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ' Test 1: invoke [usp_SlowProcedureAsync] one time with simple input.'

EXECUTE [usp_SlowProcedureAsync] 'Test input message';

SET @countQueue = (SELECT COUNT(1) FROM [AsyncExecQueue])

SET @countDestination = (SELECT COUNT(1) FROM [LogDestination])

SET @countErrored = (SELECT COUNT(1) FROM [AsyncErrored])

PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ' Queue=' + @countQueue + ' Destination=' + @countDestination + ' Errored=' + @countErrored

WHILE (@countQueue <> '0')

 BEGIN

  WAITFOR DELAY '0:00:00.2'

  SET @countQueue = (SELECT COUNT(1) FROM [AsyncExecQueue])

  SET @countDestination = (SELECT COUNT(1) FROM [LogDestination])

  SET @countErrored = (SELECT COUNT(1) FROM [AsyncErrored])

  PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ' Queue=' + @countQueue + ' Destination=' + @countDestination + ' Errored=' + @countErrored

 END

DELETE FROM [LogDestination];

DELETE FROM [AsyncErrored];

PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ' Test 2: invoke [usp_SlowProcedureAsync] five times, with fourth causing an error in [usp_SlowProcedure].'

EXECUTE [usp_SlowProcedureAsync] 'Test message 1';

EXECUTE [usp_SlowProcedureAsync] 'Test message 2';

EXECUTE [usp_SlowProcedureAsync] 'Test message 3';

EXECUTE [usp_SlowProcedureAsync] 'Test ERROR 4';

EXECUTE [usp_SlowProcedureAsync] 'Test message 5';

SET @countQueue = (SELECT COUNT(1) FROM [AsyncExecQueue])

SET @countDestination = (SELECT COUNT(1) FROM [LogDestination])

SET @countErrored = (SELECT COUNT(1) FROM [AsyncErrored])

PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ' Queue=' + @countQueue + ' Destination=' + @countDestination + ' Errored=' + @countErrored

WHILE (@countQueue <> '0')

 BEGIN

  WAITFOR DELAY '0:00:00.2'

  SET @countQueue = (SELECT COUNT(1) FROM [AsyncExecQueue])

  SET @countDestination = (SELECT COUNT(1) FROM [LogDestination])

  SET @countErrored = (SELECT COUNT(1) FROM [AsyncErrored])

  PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ' Queue=' + @countQueue + ' Destination=' + @countDestination + ' Errored=' + @countErrored

END

Here's some output I got from the above test run. You can see the 1/2 second pauses between each insert being processed.

15:59:16.62 Test 1: invoke [usp_SlowProcedureAsync] one time with simple input.

15:59:16.85 Queue=2 Destination=0 Errored=0

15:59:17.07 Queue=1 Destination=0 Errored=0

15:59:17.28 Queue=1 Destination=0 Errored=0

15:59:17.48 Queue=0 Destination=1 Errored=0

(1 row(s) affected)

(0 row(s) affected)

15:59:17.48 Test 2: invoke [usp_SlowProcedureAsync] five times, with fourth causing an error in [usp_SlowProcedure].

15:59:17.48 Queue=9 Destination=0 Errored=0

15:59:17.70 Queue=9 Destination=0 Errored=0

15:59:17.90 Queue=9 Destination=0 Errored=0

15:59:18.10 Queue=7 Destination=1 Errored=0

15:59:18.30 Queue=7 Destination=1 Errored=0

15:59:18.51 Queue=7 Destination=2 Errored=0

15:59:18.71 Queue=5 Destination=2 Errored=0

15:59:18.91 Queue=5 Destination=2 Errored=0

15:59:19.12 Queue=3 Destination=3 Errored=0

15:59:19.32 Queue=3 Destination=3 Errored=0

15:59:19.52 Queue=3 Destination=3 Errored=0

15:59:19.72 Queue=1 Destination=3 Errored=1

15:59:19.93 Queue=1 Destination=3 Errored=1

15:59:20.13 Queue=0 Destination=4 Errored=1

And if you run all of that SQL, and are done playing with it, here's the easy clean-up:

DROP TABLE [LogDestination]

DROP PROCEDURE [usp_SlowProcedure]

DROP TABLE [AsyncErrored]

DROP PROCEDURE [usp_AsyncExecActivated]

DROP SERVICE [AsyncExecService]

DROP PROCEDURE [usp_SlowProcedureAsync]

DROP QUEUE [AsyncExecQueue]

I hope someone eventually finds this useful; but if not, I will hopefully remember that I put this out here =-]