Process Delegation Workbench

Robyn Page and Phil Factor show a useful technique for delegating SQL Server processes to a 'Back-Office', by using 'user-defined Alerts'.

Here is a way of designing part of a SQL Server Database application so it seems faster to the user.

Often, when you are designing a database-intensive application or website, you will often merely want the application layer to signal that a job should be started, and return back immediately without blocking. How nice to have a secure system that buffers up routines that aren’t time-critical and irons out peaks in demand, all without having to use Service Broker. This is done by delegating processes that aren’t time-critical to another database connection. As a by-product, you can then tighten up security, so that back-office functions are never done by user logins.

The technique is to delegate as much processing as possible on to another ‘back-office’ process under a different connection login via a queue. This is quite a commonly-used technique, and uses the Alerts and Jobs within SQL Server Agent.

In this Workshop, Phil and I will show how it is done, using user-defined alerts in either SQL Server 2000 or SQL Server 2005. It is, of course, more convenient to use SSMS interactively to set up alerts and jobs but here we’ll script them so you can try it out more quickly and easily.

For the technical background to User-Defined Alerts, you might like to read SQL Server 2000: User Settable Object (Catchy title?) or The SQL 2005 version.

Here is a skeletal system that is designed for the dispatch of email information from a database-driven website. It can be used for almost any time-intensive or security-sensitive process that does not require the immediate return of the results to the user, such as processing an invoice, printing out a dispatch note, doing a trade confirmation or trade settlement, resizing an uploaded image, or indexing text.

The first thing we must do is to create the log, queue and queue parameter table. The log is necessary to tell us how the queue is getting on, and to contain errors and warnings. we need a queue parmeter table because each process that we add will have different parameters.

The steps in the process:

  • The user calls a function to send an email
  • The function puts an entry in the queue table to say he wants an email sent
  • The procedure puts the parameters into a parameter table.
  • The procedure sets a user-counter to 1 to fire an alert
  • The user returns
  • The alert fires and runs a job
  • The job step runs a procedure that empties the queue, running every task that has been put in the queue, including sending the email

Firstly, you must create a database called BackOfficeDemo.

Then run this script….(should run in either SQL 2000 or SQL 2005)

We can enable or disable this alert very simply with this procedure:

Now we log out, log back in as TypicalUser and run this procedure to send an email (it should just write to the log in this test mode).

If we then look at the log:

We’ll see something like this…

401-BackOfficeWorkbench.jpg

If all has gone well, the spSendAnEmail procedure will have put a request in the queue which will have been picked up and executed by the agent after a short period. Notice that the execution of the task is done by DBO (we set it that way) rather than the TypicalUser (who will have very few privileges).

You will notice a latency before the Agent picks up the job, up to twenty seconds. This is because, in SQL Server 2000, the performance condition counters are only polled every twenty seconds. As a belt-and-braces approach, one might want to have a scheduled task the same stored procedure that fires every minute or so, as it will do no harm.

If it doesn’t work:

  • Make sure that the SQLServerAgent is running
  • Check that you are using the latest SQL Server service pack
  • Check the account the SQLServerAgent services runs under
  • Make sure the event is firing. If not, is it enabled?
  • Check the alert properties to see the last time that the alert worked
  • Check the SQL Server error log, and job history log

You can have up to ten of these mechanisms working independently, but we’ve never thought of a use for more than one per database.

However, user-defined counters are great for application-based figures that need to be monitored as part of performance logging (such as people logged onto a site simultaneously, or average views per minute, so don’t use them up unnecessarily: they are too valuable.