T-SQL Tuesday #46: You Can’t See Me

As you might have expected, since I chose Rube Goldberg Machines as the topic for this month’s T-SQL Tuesday, I have my own story to share. It was hard to choose only one. Give five of us the same moderately difficult problem, and you will get five (or more) viable solutions. That is why our answer is almost always ‘It depends’. How much time do we have to implement the solution? Which products are already in house? How much do you want to spend? What expertise does your team already have? The list goes on…

Typically, a Rube Goldberg Machine is an over-engineered or complex solution to solve a simple task. Our jobs as software engineers can be as much art as science. The best of us will take your answers to our ‘it depends’ questions, and try to steer you down the right path. But, sometimes those answers will back us into a corner, and that’s when we need to get creative.

Asynchronous Without Service Broker (Rube Goldberg Machine)

In this case, the data team had been called on to implement some complex business logic in SQL Server. We knew it wasn’t ideal, but we had our orders. In the end, we created a solution that did the job. But it wasn’t wicked fast, and there wasn’t much more we could do to make it faster. We really wanted the end users to have a positive user experience, and there was no reason for them to wait for the process to complete. As they say, a picture is worth a thousand words. We had created the synchronous scenario on the left, and wanted the asynchronous version on the right.

We started talking about Service Broker, but we really didn’t have time to get that into the picture. Now, I can’t take credit for this ingenious idea, but this has worked as a good short term alternative. The shortcut we took was to use a table as a queue.  The QueueWork stored procedure called by the application added a row to the queue and returned quickly to the user.

The Setup

First, we need to add a user-defined message to the user database. 5556162 (the A-Team), because I love it when a plan comes together:

EXEC sp_addmessage @msgnum = 5556162, @severity = 1, @msgtext =N'RubeGoldberg';

The Ball

That QueueWork stored procedure was the ball that started the Rube Goldberg Machine. QueueWork also raised an error, like so.

RAISERROR(5556162, 1, 1);

The Fan

The ball starts the fan. If we set up a SQL Server Agent Alert to catch that particular error number, we can have an Alert start a specific @job_id:

EXEC msdb.dbo.sp_update_alert @name=N'Rube Goldberg Alert',
 @message_id=5556162, @severity=0,
 @enabled=1,
 @delay_between_responses=0,
 @include_event_description_in=5,
 @database_name=N'DataOgre',
 @notification_message=N'Time to start the fan',
 @event_description_keyword=N'',
 @performance_condition=N'',
 @wmi_namespace=N'',
 @wmi_query=N'',
 @job_id=N'D8AA5219-8010-45F7-98C0-31769E8B7190';

The Dominoes

To knock down all of the dominoes (execute the business logic), the alert starts the SQL Server Agent Job defined in the alert by @job_id. Within the business logic, that row in the queue is marked as processed so it doesn’t get reprocessed the next time QueueWork is called.

Other than Service Broker, do you have any other tricks you use to allow SQL Server to perform tasks asynchronously?