SQL11UPD04-TSCRIPT-07

This wiki is a transcript of a previously recorded video.

Related content assets:

  • Presentation: Introducing SQL Server  StreamInsight 2012 Improvements (SQL11UPD04-DECK-04)
  • Video: Introducing SQL Server  StreamInsight 2012 Improvements (SQL11UPD04-REC-07)

Introducing SQL Server StreamInsight 2012 Improvements

Hi, I’m Greg Low. I’m a SQL Server MVP and part of the Microsoft Regional Director Program. In this section, I want to take a quick look at what’s new in StreamInsight 1.2. But I understand that many of you may not have had a chance to look at StreamInsight in the past, and so I want to spend a few minutes just reviewing what it is StreamInsight is all about.

So Microsoft StreamInsight is a platform, so it’s important to understand that it’s not just a product that you use out of the box. It’s a platform that we use to develop what are known as Complex Event Processing applications. So these are high-throughput applications, and what we’re able to do is construct, using .NET, an application that then processes streams of events at high speed.

Now a lot of people ask, isn’t this just a database application? And the answer is, no, it’s not. So, with a database, what we’re doing is taking data that’s received, storing it in a database, and typically we’re receiving data at maybe hundreds per second – data elements. Thousands per second does happen, but it’s not so common. And then typically the queries we’re running, we’re running on the stored data, and we’re doing that often with the latency of seconds is not untypical for that sort of thing, or atypical.

With StreamInsight, this is very different, because the issue is: what do you do if the data rate keeps getting higher to the point where you can’t store the data in the database before you run the queries? And this is what StreamInsight’s for. So we can have a query running all the time, we pass a series of events through it, and then, we’re processing that on the fly before we stick it in a database. We might not even put it in a database in the end; it may be passed on to something else that then processes the events.

So the goals of this are being able to pick some patterns that occur in the data, maybe relationships between things that occur in different event sources, but it can also be looking for a gap. Maybe something is meant to occur, and hasn’t occurred. And so I could imagine all sorts of events where I’m expecting various things to occur. Maybe two things can’t occur without something occurring in between. And that sort of gap I can pick up using StreamInsight as well. I need to be able to also perform abstractions, so perhaps from this set of events, and maybe combining with data from somewhere else, I also need to then perform calculations before I push it back out again. Another thing I might need to be able to do is trigger a response. So if this event occurs in conjunction with something else, then here’s some action I then need to take.

So it is important to understand the basic event flow here. Lots of external devices and systems generate a constant flow of events. If you’ve done any work with industrial-type applications, these things generate events nonstop. But it could also be things like the network, it could be telecommunications systems, utility systems, and so on. There are a huge number of events that are generated by systems every day.

What we construct here are a set of input adaptors. Some are provided with the product as well, but generally you will go off and create the required input adaptors. And this allows you to build – bring a stream of events into StreamInsight. StreamInsight provides a framework where queries run, but we call these standing queries. These are queries that are running all the time. They might make reference to reference data as well so it could combine with data from a database, but it doesn’t have to be. And then once this is processed, we may then push this out, concurrently to multiple output sources. On the input side, we might also have a wide variety of input sources also providing data concurrently.

Now in 2012, a number of enhancements have been made. First up, for critical applications, there’s resiliency support. So this allows the applications to become much more tolerant, to most common network and system-related failures. In earlier versions, we also had the ability to provide monitoring and via some diagnostic views that were exposed from inside the objects directly. And there was a client application that we could use to couple up to that. But for many people, they were trying to integrate these as services that were running in the background inside Windows systems, and they wanted to use exactly the same management as they do for any other applications or services in the system. So in this version, StreamInsight now supports providing performance counters out to performance monitor, or Windows reliability in performance monitor, depending upon the version as to what that’s called, and in addition, events can be pushed out to the event log. The development has become a lot easier than in the past. Previously, if you looked at the types of objects we had in the payload, they had to be simple data types. We could have numbers of them, but they needed to be simple data types. In this version we can now nest data types within each other. There are a wider variety of LINQ expressions that are available, and for doing basic testing, there’s an updated LINQPad driver and a whole series of samples that now make it easier to work with. So this allows us to fire up LINQPad to be able to execute some of our queries, see the results of this in an interactive way, and particularly this is useful if you’re trying to learn to use it in the first place.

Another substantial change is the addition of a user-defined stream operator. So perhaps if I’m processing an audio stream, I could create myself some form of new audio algorithm or smoothing operator or something that processes the stream in a totally different way than anything that’s been built before.

Finally, there are lots of .NET data sources that I might want to couple up without having to build complex input adaptors. And so, observable and enumerable .NET collections can be brought in as a series of events into StreamInsight. This was actually added back in StreamInsight 1.1 and that was released since SQL Server 2008 R2 came out, so there was an interim release. Now, the idea here, the observable pattern is a good example. This was part of what they called a reactive extension to .NET, and there’s a whole lot of series you’ll find if you look online with webcasts from Eric Meyer and so on. There are descriptions there about how the reactive framework works, or the reactive extensions. But one of the things that it provided was this design pattern where I could create an observable object and then different subscribers could connect up to it and they would get data supplied to them from this observable object. Now this lends itself very, very nicely to being used in StreamInsight, where we are often after a series of events coming out the other end.

The other type of collection in .NET that is of interest here is anything that’s enumerable. So if we have a collection that can be enumerated via an enumerator, then we’re able to use that as a source of events as well. And so in this example I’ve listed here, I’ve got an intList as an enumerable range of values, then I’ve said, look, create a PointStream, as we have different types of events in StreamInsight, and a PointStream is one type of stream. And I’ve said, look, create that input list as a stream of points, and I’ve shown here how to apply the dates and so on to that, because events in StreamInsight are sort of time and date related. And then once I’ve converted that to a stream, I’m able to save from the results in the input stream, go off and process that. I can also take results from a query and turn them back into an enumerable collection as well. This just dramatically simplifies the type of code that’s required for dealing with collections and objects that may already exist in your .NET code.

In an upcoming demonstration, we’ll take a look at just what’s involved in creating a standing query over .NET collections using StreamInsight 1.2.

Return to SQL Server 2012 Developer Training Kit BOM (en-US)