harvey_melfi

How to Event-Enable any Database Table

Blog Post created by harvey_melfi Employee on Oct 23, 2017

In this article, I will explain why an event driven architecture is valuable to the business, discuss different ways to event-enable an application, and provide a step-by-step solution for how to event-enable any database table.

 

 Get the processes discussed below from the Process Library here.

         

 

 

Why to Event Enable the Enterprise

The Real-Time Enterprise responds to business and data events in real-time or near real-time. The traditional way of processing events is in batch mode once a day usually during off hours. Being able to process events quickly can be very valuable to an enterprise. Richard Hackathorn wrote a very informative article on Real-Time event processing called: The BI Watch Real-Time to Real-Value. Richard talks about the net value of processing an event and how the value decreases the longer it takes to react to that event. Here is a graph that he shows:

 

 

Therefore, the faster you can process an event after it happens, the more value you can get. This business event could be a request for a quote from a prospect or changing an opportunity to Closed/Won. The business event can be an opportunity or a threat like someone creating a support ticket to report a bug in your product. Being able to capture the event and react to it quickly can provide tremendous value to the business.

 

How to Event Enable your Enterprise

Now that I have discussed the business value of processing events in real-time, lets cover the different ways this can be done. The best way to capture events is to utilize the capabilities of the system that generates these events. Many on-premise and cloud based platforms provide this capability OOTB. You should always utilize the 'front door' of an application to use the published API, if one exists. For example, Salesforce has outbound messages that are very easy to configure, NetSuite has SuiteScripts that can emit real-time outbound messages to events that happen. These events could be when someone creates a new account in Salesforce, or when a user updates a Sales Order in NetSuite. On-premises applications like SAP can send outbound IDocs in response to events and Oracle EBS can emit events via Oracle Advanced Queuing. To event enable these applications, leverage their native event architecture.

 

Event Enable Any Database Table Framework

This article will discuss how you can event enable an application that doesn't have native event processing built in. We will leverage the 'back door' of this application and create an event-enabled architecture using the back end database. There are at least three ways of getting events (inserts, updates, and deletes) from a database table: using the Last Modified Date field, using the database audit log, and using a Change Data Capture (CDC) table with triggers. The Last Modified Date field implementation assumes that there is such a field and you have to query the entire table for updates. Also, getting deletes would be difficult unless the application did a logical delete because a physical delete would entirely remove the record. This may not perform well in a high transaction environment, especially if the table has many rows. While the database audit logging monitoring solution may work, not all databases support this out-of-the-box and you may have to license another product from the database vendor or purchase a third party product. This article will focus on using a CDC table. With this architecture, you don't have to continually query the table for modified rows. It will add a little more time to inserts, updates and deletes to the base table while the triggers execute, but this should be negligible. This CDC implementation can be done in two parts: Design Time (one-time) configuration and Run Time configuration.

 

Design-Time Configuration

This is a diagram of the design time configuration. Note that a stored procedure will facilitate the creation of the CDC table and the database triggers on the base table. This asset and a Boomi integration process will be provided below. The process will accept one input: the name of the base table. Therefore, the database assets can be created automatically or manually. A DBA could also manually create the CDC table and the triggers.

 Design-Time Configuration Diagram

 

This solution to event-enable a database table is based upon a base table, a CDC table, some database triggers, some Boomi integration processes, and an Apache ActiveMQ Topic (you could use a Boomi Atom Queue Topic if you have this capability). For example, let's say you want to 'watch' a database table named Person for inserts, updates, and/or deletes and get notified when these events happen with all of the event data. What needs to happen is to create a CDC table (boomi_Person) that has the same columns as the base table (Person) plus a few extra columns to hold contextual information about the event. This will be discussed later in detail. Database triggers then have to be made on the base table (Person) On Insert, On Update, and On Delete. These database triggers would just handle inserting these events into the CDC table (boomi_Person). This will complete the database side of the design time configuration. At this point, whenever any application runs any DML SQL statements against the base table, they will be automatically inserted into the CDC table.

 

Run-Time Configuration

Now for the run-time configuration. Firstly, download, install, and configure Apache ActiveMQ on a system that the Boomi runtime can access. A Boomi integration process will get all event data in the CDC table ordered by event date, publish each record to an Apache ActiveMQ topic, and delete all the processed records from the CDC table. This process should be deployed and a scheduled job created to automatically execute as frequently as every minute. Another Boomi integration process will subscribe to the ActiveMQ topic and process the event. You could implement an ETL scenario, synchronize the data to another table or database or notify someone about this event. You can implement any type of event processing logic you require. Because we are leveraging the ActiveMQ Topic, other processes can be created and subscribe to the same topic to implement another type of event handling. This is why the ActiveMQ topic is being used. It is possible to implement the event handling logic in the process that pulls the event data, but this is not as decoupled nor as extendable. I would only suggest not using the ActiveMQ topic if you don't want this other system in your architecture or you already have a JMS compliant message bus to leverage. Here is a diagram of how the run-time solution works:

 

                                                   

 

Synchronize to Database Table ETL Implementation

The event processing implementation for this article will synchronize the data changes in the base table to another table. This is a common ETL pattern that can be modified for your specific ETL requirements. The ETL table is called PersonETL and after the configuration of the solution will always look exactly like the base table in the number of rows and the exact same data. Here is a graphical representation of this implementation:

 

 

Solution Limitations

This solution was developed on MS SQL Server Express 10, although it should run on most other versions of MS SQL Server. While this event driven architecture can be manually implemented (CDC table and base table triggers) by a DBA for other database types, the stored procedure that creates the CDC table and the base table triggers is implemented in TransactSQL and therefore only runs on SQL Server. These are the SQL Server data types that have been tested and are supported:

 

NumericDate and TimeString and BinaryOthers

1. bigint

2. decimal

3. float

4. int

5. money

6. numeric

7. real

8. smallint

9. smallmoney

10. tinyint

1. date

2. datetime

3. datetime2

4. datetimeoffset

5. smalldatetime

6. time(7)

1. binary

2. bit

3. char

4. nchar

5. nvarchar

6. varbinary

7. varchar

1. geography

2. geometry

3. hierarchyid

4. uniqueidentifier

5. xml

 

The following MS SQL Server data types are not supported, mostly because they can't be included in database triggers: image, ntext, nvarchar(MAX), sql_variant, text, timestamp, varbinary(MAX), and varchar(MAX).

 

Also, this solution doesn't have any error handling nor try/catch shapes. To production-ize the solution, you may want to put some error handling in the Process Person Events from CDC Table and Subscribe to Person Events Topic Boomi integration processes.

 

Important: This solution is provided as an example and is not intended for production use as-is. It is not delivered nor maintained by Dell Boomi. You should thoroughly evaluate and test for your own scenarios before considering for production use. Use this framework at your own risk.

     

 

Steps to Setup this ETL Implementation

  1. Download the file link at the bottom of this article called Event Enable Database Table Files.zip. Extract the contents on your computer.
  2. From the contents of the Event Enable Database Table Files.zip, find and execute the Create Person Table.sql, Create PersonETL Table.sql, and CreateBoomiEventTableAndTriggers_SP.sql scripts in your instance of MS SQL Server.  You may have to include a 'Use <database name>;' at the top of each script to make sure the assets get created in the proper database.
  3. From your Dell Boomi AtomSphere account, install the Database: Event Enable any Database Table Process Library into your account. This "container" process contains three processes.
  4. Read the process descriptions for each of the three Boomi Integration Processes contained in the Process Library: Event Enable Table by TableNameProcess Person Events from CDC Table, and Subscribe to Person Events Topic. The instructions on how to configure each process to your environment is included in the process description. Basically, you have to enter appropriate information in the SQL Server DB Connection to point to your instance of SQL Server and walk through the wizard for each Database profile.
  5. Open the Event Enable Table by TableName Boomi integration process in your Boomi account and Test it on a Boomi runtime Atom/Molecule/Cloud that has access to your SQL Server instance. Confirm that the CDC table, boomi_Person and 3 triggers got created for the base table: Person.
  6. Make sure you deploy the following Boomi integration processes (as noted in the process descriptions) to your Boomi runtime that has connectivity to your SQL Server instance: Process Person Events from CDC Table and Subscribe to Person Events Topic. Also, be sure to setup a scheduled process to automatically run the Process Person Events from CDC Table process every minute or any other time interval.
  7. Create a topic on ActiveMQ called DatabaseEvents.
  8. This should complete your configuration. You should now have a working implementation of the Event Enable Any Database Table framework.

 

Testing your Solution

  1. Open the BoomiEventDrivenPersonTableQueries.sql file included in the zipfile below in your favorite MS SQL Server database client.
  2. Run the following SQL statements in the BoomiEventDrivenPersonTableQueries.sql file. They should all return 0 rows.
    1. select * from Person
    2. select * from boomi_Person
    3. select * from PersonETL
  3. Execute the following SQL statements in the BoomiEventDrivenPersonTableQueries.sql file. They should insert 3 records in the Person table.
    1. insert into Person (FirstName, LastName) values ('John', 'Doe')
    2. insert into Person (FirstName, LastName) values ('Jane', 'Smith')
    3. insert into Person (FirstName, LastName) values ('Dell', 'Boomi')
  4. Now run the following 3 queries quickly, before the scheduled job kicks off to process the records in the CDC table: boomi_Person:
    SQL QueryResults
    select * from Person
    select * from boomi_Person
    select * from PersonETLThis query should return 0 rows
  5. After the scheduled job kicks off the Process Person Events from CDC Table process, and you will know this from the Process Reporting page, you will see the following from running the queries again:
    SQL QueryResults
    select * from Person
    select * from boomi_PersonThis query should return 0 rows
    select * from PersonETL
  6. Now run the following SQL statements
    1. update Person SET LastName = 'Stamos' WHERE FirstName = 'John'
    2. update Person SET LastName = 'Johnson' WHERE FirstName = 'Jane'
    3. update Person SET FirstName = 'Go' WHERE LastName = 'Boomi'
  7. Now run the following 3 queries quickly, before the scheduled job kicks off to process the records in the CDC table: boomi_Person:
    SQL QueryResults
    select * from Person
    select * from boomi_Person
    select * from PersonETL
  8. After the scheduled job kicks off the Process Person Events from CDC Table process, you will see the following from running the queries again:
    SQL QueryResults
    select * from Person
    select * from boomi_PersonThis query should return 0 rows
    select * from PersonETL
  9. Run the following SQL Statement
    1. delete from Person
  10. Run the following 3 queries quickly, before the scheduled job kicks off to process the records in the CDC table: boomi_Person:
    SQL QueryResults
    select * from Person0 Rows Returned
    select * from boomi_Person
    select * from PersonETL
  11. After the scheduled job kicks off the Process Person Events from CDC Table process, you will see the following from running the queries again. There should now be 0 rows in all 3 tables.
    1. select * from Person
    2. select * from boomi_Person
    3. select * from PersonETL

 

User Guide Articles

Here are some links to our User Guide, which you may find useful when using and configuring this event enabled database table.

 

I would like to thank my former colleague, Steven Kimbleton for creating the stored procedure that is used in this solution.

 

Harvey Melfi is a Solutions Architect with Dell Boomi.

Outcomes