Recent Posts

Archive

Tags

Event-Based SQL Server Integration

As companies continue to depend on SQL Server to run their business operations, the ability to turn a database engine into an event-driven platform has never been greater. That’s because workflow automation is front and center to all businesses, such as customer engagement, B2B integration, employee management and operational reports.

Modern software developers depend primarily on two main architectural components to implement workflow automation: webhooks and messaging architectures. In this article I will discuss the benefits of extending SQL Server so that it can participate in the webhook and messaging ecosystem, and introduce a no-code PaaS solution for achieving this easily: Enzo Pipelines.

Webhooks and Messaging Architecture

Webhooks are gaining traction because they simplify the overly complex API landscape. Calling HTTP endpoints is far simpler than building middle-tier services that need to be smart about the end system being called. In fact, we could argue that webhooks, when understood by all parties involved, are the simplest possible integration mechanism that allows virtually any system to communicate to virtually any other, regardless of operating system, programming language or device. In essence, webhooks provide a ubiquitous connectivity architecture, and works well when the source system knows which destination system(s) need to be called.

Messaging architectures (such as message queues or messaging bus) are also critical for many systems as they enable certain asynchronous consumption patterns that webhooks are not designed to offer, such as workload distribution, fire and forget and eventual consistency. Messaging systems require all parties to agree on message content and meaning; in other words they require more integration design work than webhooks. Messaging architectures provide an asynchronous integration model that works well when the source system does not (or should not) know which destination systems will be consuming its events.

Emulating SQL Server Events

Unfortunately SQL Server offers no event-based integration capability with other external systems, such as a messaging platform or webhooks; this essentially isolates SQL Server and prevents it from becoming part of a larger integration ecosystem.

To fill this gap, I am proposing Enzo Pipelines, an integration platform that emulates events by leveraging SQL Server’s own internal change tracking tables. This architecture essentially monitors SQL Server data changes as needed, transforms them into JSON messages containing the source data as its payload, and forwards them to one or more destination systems, such as another SQL Server database, Twilio, Azure Message Bus, webhooks and more.

Enzo Pipelines requires no coding, and allows organizations to connect SQL Server to a large number of external systems within minutes.

Enzo Pipelines solves several integration problems, including:

  • Data synchronization: copying data out of SQL Server into another system, directly, and keeping that system in sync (usually for reporting purposes, or to keep a secondary database in sync)

  • Event-Driven Integration: push messages in a messaging platform for other consumers to consume, such as a message queue or a message bus

  • External Log: keep an external log of changes in JSON format for historical purposes and auditing, and replay the log at any time as needed

  • Monitoring: monitor changes in the database and forward critical events to administrators through Twilio or other platform

To learn more contact me at info@bluesyntax.net, or better yet, you can try it out yourself at https://portal.enzounified.com and find out within just a few minutes how the technology works.