SQL SERVER – StreamInsight and SQL Server 2008 R2

I was recently called into create POC (Proof of Concept) for a project which was being planned for use StreamInsight. When I was there, I was also asked to give overview of the this feature to their CTO (who had only 15 minutes to spare). Usually I do not like sudden change of plans but the dynamic nature of consultation always gives me motivation to work more. I quickly talked few things in the session. In the evening, I had received the minutes of the meeting and had brief note regarding my discussion on StreamInsight. I am copy pasting the same brief note over here.

  • StreamInsight is platform for development and deployment of Complex Event Processing (CEP) applications
  • CEP involves processing and querying of event data stream
  • Data is queried “on the fly” or rather “in flight”
  • CEP is mainly for high data rates and near-zero latency
  • CEPdeals with multiple concurrent event sources
  • CEP triggers immediate response actions to the events
  • CEP is not targeted to regular run of the mills relational database applications
  • StreamInsight has very high -throughput stream processing .NET based development and processing architecture
  • StreamInsight is highly parallel execution platform with in-memory caches
  • The “In flight” processes are done using incremental result computations
  • Due to incoming events triggers there is no polling overhead
  • The architecture can handle out-of-order events
  • StreamInsightserver hides complexity for developer who wants to focus on query logic only
  • In Short StreamInsight is the way to go to query data which arrives too fast to store.

I hope this is useful to someone who is looking for high level overview of the StreamInsight.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

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:

streamlight SQL SERVER   Brief Note about StreamInsight   What is StreamInsight

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 (http://blog.SQLAuthority.com)