Microsoft SQL Server 2008 R2 StreamInsight Overview

By: Tim Cullen | Comments | Related: More > StreamInsight

Problem

I was reviewing some of the new features in SQL Server 2008 R2 and ran into a technology named StreamInsight. The company I work for is a large manufacturing and distribution company and I think this technology could benefit the company. What is StreamInsight and what benefits can the company reap if this technology is incorporated? Check out this tip to get these questions answered.

Solution

Microsoft introduced the StreamInsight technology as part of SQL Server 2008 R2. In the past companies have depended upon lengthy processes to see Key Performance Indicators (KPI's). In traditional applications a data set was obtained and manipulated to find the KPI's, sometimes waiting hours or even days. Companies have been looking for ways to monitor and make decisions based on these indicators in as near real-time as possible. Some industries require immediate information for making critical decisions-like a financial company dealing with stock markets or a utility company monitoring for outages. In this day and age data is streamed to us in many ways-from sensors in manufacturing and weather stations to financial markets and device controllers.

Microsoft StreamInsight takes advantage of this streaming data, analyzing and correlating it "on the fly", allowing for near real-time evaluation of KPI's and other measures, as well as quicker decision-making. There are essentially five sections of the Microsoft StreamInsight technology: event sources, input adapters, the StreamInsight engine, output adapters, and event targets. When discussing the five sections of StreamInsight there are four actions taken, one between each section: Read, Enqueue, Dequeue, and Write.

Streams and Event Sources

A stream is a sequence of events that can occur randomly, at a certain rate, or continuously. The basic unit of a stream is an event. Events within a stream have two main parts-a header and a payload. The header includes metadata about the event type and includes a timestamp which is of the DateTimeOffset data type, meaning it is timezone aware. The StreamInsight server normalizes all times to the Coordinated Universal Time (UTC). Events can arrive at the input adapter in chronological order ("in order") or out of chronological order ("out of order"). The payload is a .NET data structure that holds the actual data for that event. The fields in the payload are user-defined. An event source is any device that can stream data to the input adapters. Examples of event sources include web servers, device sensors, news and other data feeds.

Input Adapters

Input adapters are functions or sub-routines that read stream events. As mentioned earlier, streams are provided via files, databases, and news or device sensor feeds. Streams may either be pushed or pulled from the source to the Input Adapter. Mechanisms used to access the stream events are READ events, although the ability to write to the sources may be necessary. Events are added to a queue, or ENQUEUED, on the StreamInsight server.

The StreamInsight Server/Engine

The StreamInsight Engine is responsible for processing and analyzing events using standing queries written in the Language-Integrated Query, or LINQ. There are a number of analytics available, like filtering, grouping, and windows over time. Once the event has been processed and analyzed it is removed from the queue, or DEQUEUED.

Output Adapters

Output adapters are functions or sub-routines that receive the events that have been processed and analyzed by the StreamInsight server, transform them into a format that is expected by event targets, and send the information to the event targets. This is the WRITE action mentioned earlier.

Event Targets

Event targets are devices or systems that either provide the user with data from the output adapters or change conditions based on information received from the output adapters. Examples include smartphones, dashboards, databases, and other computer information systems.

Microsoft StreamInsight Editions

When planning for a StreamInsight instance, there are two main considerations to ensure you have the correct edition of Microsoft Insight: the event rate and latency tolerance. If your requirements indicate either a latency tolerance of less than five seconds or processing more than 5000 transactions per second then the Premium edition is recommended by Microsoft; otherwise the Standard edition will perform well. The edition available to you is derived from the SQL Server 2008 R2 Product Key. The Standard Edition is available with the Standard, Enterprise, and Web editions. The Premium Edition is available with the Datacenter, Developer, and Evaluation editions.

Next Steps

In future tips we will discuss the planning for, and installation of, a StreamInsight instance, as well as how to create adapters and queries within the StreamInsight Engine.
Become more familiar with the terminology associated with StreamInsight.
Download the latest version of StreamInsight
Download A Hitchhiker's Guide to Microsoft StreamInsight Queries
Review and create an end-to-end StreamInsight application