Introduction

In this article I will introduce you to the Boomerang Notification Framework and show how it can help you to quickly build dynamic and powerful notification solutions using a minimum of lines of T-SQL.

Since this is an introduction I have chosen to keep the examples fairly generic, however leave you with some ideas about how this framework can offer help under your circumstances. 

Why Boomerang?

Boomerang address several areas of business IT and can be summarized to the following topics:

• Improve Communication with Customers, Suppliers and Employees - Send tailored notifications with relevant and accurate information for improved and timely communication.

• Streamline Business Processes - Remove unnecessary steps and streamline your business processes.

• Automate BI Distribution - Dynamic Distribution of Business Intelligence reports (SSRS) and KPI’s to stakeholders.

• Integration - Quick and easy integration to cloud services (like Twitter, FaxLogic, Dropbox) and on premises infrastructure (like printers, file/ftp servers).

How does Boomerang Work?

The Boomerang Notification Framework consists of a number of tables in a SQL database. Each one of these tables represents a notification function for example sending e-mails (EMAIL_OUT) and printing documents (PRINT_OUT). Boomerang services use a pull technique, i.e. database tables are polled on specific time interval set independently for each delivery service. So when inserting into any of these tables e-mails are sent and a documents are printed.

To monitor activity and connect existing infrastructure in your environment the Boomerang Administration Console make it an easy and quick task.

Below illustration depicts the architecture of the Boomerang Notification Framework:

Working with Boomerang

Interactions from your application with Boomerang services are done via database interface. This implies that any programming language can be used as long as it provides support to MS SQL Server. You will communicate with Boomerang in exactly the same way whether is it with ASP.Net application, bar-code device running a Mobile OS, or TomCat application connecting over Ruby on Rails, such as Confluence.

Boomerang Objects

The unit of work implemented by Boomerang is called an Event. You can think of an Event as an application task. The Event is represented by a single record in the EVENT_MASTER table. These Event records do not carry delivery data, however they may carry application specific keys used to tie events to sources which generated them. Examples of such keys may include a helpdesk id, an application source id, a customer order number, a client lead unique identifier, etc.

Each Event comprises one or more jobs. A job represents an actual unit of delivery, such as e-mail, or print. Each job declares delivery data of its own kind. For example, a print job will define at the minimum an applicable printer path. Although several jobs make up a single event, no specific order is guaranteed in which the jobs will fire. Because of the multi-threaded nature of Boomerang services, chances are they will fire all at once as soon as the corresponding event record is marked "ready".

Some jobs may extend data to one or more auxiliary tables. For example e-mail jobs list the target recipients in a separate table called OUT_EMAIL_RECIPIENT.

Many jobs will have content or attachments for which a common storage table is designed and called EVENT_CONTENT. For example, a fax job may be set to deliver a MS SQL Reporting Services report by means of facsimile transmission. In this case the event content table would feature corresponding URL path, where a RDL report has been deployed.

Each job maintains its own status and repetition schedule in a table called EVENT_STATUS. Such schedule defines whether or not a job should fire immediately or wait a certain time delay. Also it defines whether or not the job should be retried or not in the case of failure and if so, then also the number of times and interval.

As jobs are processed an event log is being generated regardless of the completion outcome. Some jobs establish a feedback mechanism with target device. For instance, the fax service will alternate job status based on notifications it receives from MS Fax Server. The e-mail service will "listen" to the delivery reports from SMTP relay agent and will log them in association with the original e-mail job.

The illustration below depicts how various Boomerang objects (with tables they are hosted by) fit together.

You may find the full description of Boomerang database at Fuel9 website, which is provided at the bottom of this article.

Basic Process

The process below demonstrates how to create a Boomerang distribution in 4 simple steps.

Create Event | Step 1

In the first step you create a new task record. Simply insert a new row in the table EVENT_MASTER. The only required field in EVENT_MASTER is the gKey (uniqueidentifier). This record will tie output jobs together as we will see in further steps. There are several columns in EVENT_MASTER that you may use to help you organize and manage different tasks. In this example we will use Created_By and Str1

E.g.:

-------------------- Step 1 -------------------------------------------
declare @gKey uniqueidentifier; set @gKey = newid();
Insert EVENT_MASTER (gKey, Created_By, Str1)
values (@gKey, 'domain\username', 'Weekly Sales Report')

Another useful field would be Source. If you maintain a list of applications in your environment with unique integer id for each, the Source column is an ideal candidate to reference your applications.

In the above example we declared a local T-SQL variable @gKey of certain type and assigned it a value by means of the built-in T-SQL newid() function. We will need this variable in the steps below. If you are a beginner with T-SQL, you may find a lot of useful articles explaining T-SQL variables by searching this website.

Create Job | Step 2

In the second step you will select the destination.For example, e-mail, or printing, or faxing. As discussed above this will be referred to as a job. Depending on the job destination you will insert the new job into an appropriate table. The following are your options: OUT_EMAIL, OUT_FAX, OUT_FILE or OUT_PRINT. While fields vary slightly depending on the destination nature, the job key jKey and the event key gKey are common. In this example we have chosen to send an e-mail.

E.g.:

-------------------- Step 2 -------------------------------------------
declare @jKey uniqueidentifier; set @jKey = newid();
insert OUT_EMAIL (gKey, jKey, [ReplyTo], [From], Subject, Body)
values (@gKey, @jKey, 'Boomerang_account@domain.com', '"e-mail display name" <Boomerang_account@domain.com','Weekly Sales Report', 'Message body test test test' )

Since this is an e-mail job we also need to add recipients to it. You will do this by adding one or more rows to OUT_EMAIL_RECIPIENT. An optional Type column designates recipient as either To (default), Cc, or Bcc. E-mails can be represented either by simple SMTP address or full SMTP notation, i.e. "name" <email@domain.com>. Do not forget to pass along the job reference key jKey.

E.g.:

insert OUT_EMAIL_RECIPIENT (jKey, [Email])
values (@jKey, 'info@fuel9.com')

Add Content | Step 3

Albeit optional, a SQL Reporting Services report can be easily attached to your e-mail. You will do this by adding one or more rows to EVENT_CONTENT. Multiple attachments may be added. Akin jobs, events and attachments are also identified by the key aKey. Should you add multiple attachments consider passing along the same gKey and jKey you have used in the above steps. EVENT_CONTENT is designed to accommodate the different kinds of jobs, i.e. e-mail, fax, print and file out.

E.g.:

-------------------- Step 3 -------------------------------------------
declare @aKey uniqueidentifier; set @aKey = newid();
insert EVENT_CONTENT (gKey, jKey, aKey, Src_Type, Path, [Format])
values (@gKey, @jKey, @aKey, 2, 'SALES/Sales_YTD_Demo', 'PDF' ) 

By default the report will attach itself as a file to your e-mail. To render a report in-line with HTML body, pass 0 (zero) to an optional IsAttachment column of EVENT_CONTENT table.

Should the report require input parameters to control its content, add these into CONTENT_PARAMETER.

E.g.:

insert CONTENT_PARAMETER (aKey, [Name], [Value]) 
values (@aKey, 'Week', '12')

Release task | Step 4

The last step is to release our task for processing. This is done simply by updating the flag in EVENT_MASTER. When the record was inserted in step 1, the Status column defaulted to -1 (negative one). This indicated to Boomerang services that our event is a work in progress and hence should be ignored for the time being. It is now time to set Status value to 0 (zero).

E.g.:

-------------------- Step 4 -------------------------------------------
update EVENT_MASTER set Status=0 where gKey=@gKey 

Entire Process

The whole transaction put together would look something like this:

---- Declare keys -----------------------------------------------------
declare @gKey uniqueidentifier; set @gKey = newid();
declare @jKey uniqueidentifier; set @jKey = newid();
declare @aKey uniqueidentifier; set @aKey = newid();
---- New task ---------------------------------------------------------
Insert EVENT_MASTER (gKey, Created_By, Str1)
values (@gKey, 'domain\username', 'Weekly Sales Report');
---- Create e-mail job ------------------------------------------------
insert OUT_EMAIL (gKey, jKey, [ReplyTo], [From], Subject, Body)
values(@gKey, @jKey, 'Boomerang_account@servername.domain.com','"e-mail display name" <Boomerang_account@domain.com>','Weekly Sales Report', 'Message body test test test');
---- Add recipients to e-mail -----------------------------------------
insert OUT_EMAIL_RECIPIENT (jKey, Email)
values (@jKey, 'info@fuel9.com');
---- Add report attachment as a PDF to e-mail -------------------------
insert EVENT_CONTENT (gKey, jKey, aKey, Src_Type, Path, [Format])
values (@gKey, @jKey, @aKey, 2, 'SALES/Sales_YTD_Demo', 'PDF');
---- Add parameters to report -----------------------------------------
insert CONTENT_PARAMETER (aKey, [Name], [Value])
values (@aKey, 'Week', '12');
---- Release task to be processed -------------------------------------
update EVENT_MASTER set Status=0 where gKey=@gKey;

Result:

Additional Options

To give the file attachment a dynamic name add the field EVENT_CONTENT.Name to the syntax:

---- Add report attachment as a PDF to e-mail -------------------------
insert EVENT_CONTENT (gKey, jKey, aKey, Src_Type, Path, [Format], Name)
values (@gKey, @jKey, @aKey, 2, 'Boomerang_Demo/Sales_YTD_Demo', 'PDF', 'Sales_Info_' + convert(varchar (20), GETDATE(), 110))

Result:

To render the SSRS report inline add the field EVENT_CONTENT.IsAttachment to the syntax:

---- Add report Inline to e-mail -------------------------
insert EVENT_CONTENT (gKey, jKey, aKey, Src_Type, Path, Name, IsAttachment)
values (@gKey, @jKey, @aKey, 2, 'Boomerang_Demo/Sales_YTD_Demo', 'Sales_Info_' + convert(varchar (20), GETDATE(), 110), 0)

Result:

For more code examples with explanation to various options please visit the documentation section at www.fuel9.com.

Stored Procedures

Although not required by Boomerang, stored procedures are known as a convenient way to encapsulate business logic. They may reside in Boomerang database or other databases of choice. They can either be invoked by database triggers, or run on SQL Agent schedule, or called directly from your application. Please refer to the MS SQL Server documentation, or articles on this site on how to manage stored procedures.

Event Scheduling

Boomerang jobs may be set to delay their processing in time or to retry upon an unsuccessful attempt. This behavior defaults to the service settings, however it may be individually tuned for each job via EVENT_STATUS table.

Alternatively you may use any available scheduling tool, such as MS SQL Server Agent. For more information on the MS SQL Agent please refer to the MS SQL Server documentation.

Boomerang Administration

Boomerang services ship with Microsoft Management Console snap-in. You can use snap-in to configure important service parameters, as well as Boomerang database. You may also manage Boomerang printers and attached fax servers. 

The Activity node will show past usage for all or any of the available services. Activity may be monitored by hour, day, 30 days or 365 days.

Helpers and Event Handlers

The Boomerang Notification Framework comes with a number of helpers and event handlers. These objects are made to help you speed up and simplify development and to extend hooks on which you can build additional functionality.

Following are examples of Helpers and Event Handlers:

• fn_Get_Reply_Text; Returns reply portion of an incoming e-mail
• sp_After_Print_Job; Triggered upon completion of a print job
• sp_On_Email_In; Triggered after the new incoming message is received

A complete list of availbile Helpers and Event Handlers.

Application Examples

With Boomerang’s pre-built components you can assemble powerful notification solutions in hours. The notification framework support both outgoing, Fax Out and Print Out, and incoming communication like E-mail In and Twitter In.

For inspiration here are some examples from the Boomerang community:

• Data Driven Subscriptions
• Automate Meeting Requests using iCalendar and SQL
• Automatic Excel to SQL Import
• Bill of Material Notification
• Collect Business Intelligence from Twitter Feed
• Sales Order Confirmation 

Conclusion

Boomerang takes away the chore of writing complex code to interface with notification infrastructure like e-mail, twitter, printers, file and fax servers. By means of the Boomerang Notification Framework IT Professionals can create robust and dynamic notification solution with a minimum amount of T-SQL code. 

Links

• Download a Free Trial
• Feature Overview
• Video - Boomerang Architecture
• Application Scenarios
• Documentation

About the Author

Michael is a co-founder of Fuel9 and the Boomerang Notification Framework.