SQL SERVER – Brief Note about StreamInsight – What is StreamInsight

StreamInsight is a new event processing platform introduced in upcoming version SQL Server 2008 R2. Similar to other components such as SSIS, SSAS or Service Broker, it also needs to be installed along with the SQL Server.

Up to SQL Server 2005, Microsoft’s main focus on SQL Server was to build a platform to efficiently store, manage, and retrieve data. However, now, Microsoft enhanced SQL Server to accept, monitor, and respond to complex and high number of events in near zero latency. For this, Microsoft introduced StreamInsight using the following approaches:

1. Continuous and incremental processing of unending sequences of events.
2. Lightweight streaming architecture that supports highly parallel execution of continuous queries over high-speed data.
3. The use of in-memory caches and incremental result computation provide excellent performance with high data throughout and low latency.
4. Low latency is achieved because the events are processed without costly data load or storage operations in the critical processing path.
5. All processing is automatically triggered by incoming events. In particular, applications do not have to incur any overhead for event polling.
6. Static reference or historical data can be accessed and included in the low-latency analysis.

In StreamInsight, the events are processed by a CEP (complex event processing) Server. CEP Server has its own optimizer and standing queries to handle events optimally in real time. In simple terms, we can understand it as given below:

This architecture is designed to process up to 100,000 or even more events per second.

Following are few areas where this new platform would help in monitoring, mining, and analyzing the data in motion and in providing significantly more informed business decisions in real-time:

• Manufacturing process monitoring and control
• Clickstream analysis
• Financial services
• Power utilities
• Health care
• IT monitoring
• Logistics
• Telecom

Another important thing about StreamInsight is that the developers do not have to learn any new language to implement it. Its CEP Server (standing queries) can be created in LINQ (Language Integrated Queries), while the event Source application and Target application can be written in Microsoft .Net language. By using LINQ, developers familiar with SQL will be able to quickly write queries in a declarative fashion. Its deployment is also simple with flexible methods such as integrating in an application as a hosted DLL or running as a stand alone server through a wrapper such as an executable or windows service.

Reference: Pinal Dave (https://blog.sqlauthority.com)