When I downloaded SQL Server 2008 R2 CTP2 last week, I went right by (for now) the cool Gemini features and even Multi Server Management and went straight for the new SSRS Map Control. After explaining the SQL Server 2008 spatial data types, the spatial methods, and SRIDs to folks until they glazed  over, and then watch their eyes light up when I showed 'em the Spatial Results tab in SSMS, I grokked this "power of visualization" thing right away. Maybe not the most earthshattering feature in R2, the map control is perhaps the coolest.

Start by dropping a map control on a report in BIDS or ReportBuilder 3.0 and wizarding your way through a simple color analytical map of say, sales by state. And take it from there. If you're not from the US (or "you're so bored with the USA"), get base map data of more relevence to you by importing an ESRI Shapefile. Or using Bing Maps as a base map. You could even use your the geometry of your business' office Geometric layout if you have it in SQL Server Spatial format; set the map property "Coordinate System=Planar" for that type of data. And be sure to have a read through Spatial Ed's "Cartographic Adjustment of Spatial Data for SQL Server Reporting Services", series starting here for some real fun.

I'm not going to say much more for now because my next MSDN "Under The Table" column is about this beauty, as well as some other new cool spatial visualization tools for SQL Server, and I'm wary of double-dipping with the blog. Maybe some more info on advanced ways to use the control later.

So, till then...have a map!

And a CTP is available now.

I first heard of the .NET Complex Event Processing engine at this year's TechEd. Until lately, all we had to go on was Torsten Grabs' TechEd presentation and a whitepaper that gave a fairly high-level overview of its purpose and architecture. And, oh, it was to be released as part of SQL Server 2008 R2. So last week when SQL Server 2008 R2 CTP3 was released, there was also an announcement that a CTP of StreamInsight was available. The CEP engine has a name. And there's code and libraries to work with.

Downloaded it last week and installed it. It has no dependencies on SQL Server 2008 R2; in fact it doesn't even use SQL Server at this point. The metadata is stored in a SQL Server CE database, and the data it's processing as a stream isn't particularly stored anywhere unless your event target stores it. There's an example of consuming data from and storing data in SQL Server, but that's not always the point.

At a quick first glance, it is vaguely reminiscent of SQL Server Notification Services with event sources and event targets, but the similarity ends there. In the Notification Services architecture, events are stored in the database and matched against subscriptions to produce notifications. The focus in SQLNS is on storing events, matching, and formatting notifications. Until the event is stored, it's not visible to the infrastructure, only to the event provider.

In StreamInsight, the focus is on processing and querying the data in the input stream, in real-time. The stream processing architecture is lightweight, and almost all of the use cases presented in the docs name "the ability to handle up to 100000 events per second for a large number of devices" as a goal. Storing events is an option, but the interesting part is being able to query the event stream in-flight and generate output events based on aggregations, outliers, or other characteristics of the stream. In this respect, StreamInsight resembles TIBCO events much more than SQLNS.

There is an input adapter architecture and an output adapter architecture. You can write your own event server, use their server infrastructure or imbed it in an existing application. There's even a model for reporting state changes in the stream (event source) to interested parties in the event sink to implement a pub-sub application. The development model is .NET-based and at the center of it is a LINQ provider over the event stream. You register compiled LINQ queries that execute over to stream and provide selection, projection, joins, aggregation, and some additional operators like timestamp modification. You can also extend the LINQ provider's functionality with your own user-defined functions.

Rather than continue to regurgitate the documentation (I'm doing this here as an exercise to make sure I "get it", feel free to disagree with or correct me), I'll urge you to download the CTP, run the examples, get comfortable with the architecture, and try writing a simple adapter to get a feel for it. There's a team blog and a forum where the team members hang out (I recognize some of the folks' names there) if you have questions or problems.

There's also a nice event flow debugger to give you visibility over how stream flow through the processing engine, queries, and adapters. But beware...the debugger needs a later OS like Vista, Windows7, or Windows Server 2008 to work. It looks like the debugger is using event tracing for Windows (ETW) as its trace vehicle, and because it uses the "newer" ETW (ETW-vnext was known once as Crimson, and introduced in the latest OSes) it needs one of these OSes to function.

So, happy stream processing!...
Cheers 

I'm always thought that what makes or breaks any ORM layer for use in any but the simplest of applications is the underlying SQL code that's generated. In fact, I'm a proponent of using stored procedures with any ORM for best performance. So it was with great interest that I came across the blog posting "Improvements to the Generated SQL in .NET 4.0 Beta1" by the folks that are working on the Entity Framework SQL Server provider at Microsoft. These improvements, along with some others I wrote about in May, show they're serious about improving TSQL performance in the next release of Entity Framework.

I'd really like to see support in the EDM provider for SQL Server (and LINQ to SQL) for the spatial and hierarchyid data types (and UDTs in general), table-valued parameters, filestream through the streaming APIs, and better support for stored procedures (beyong the ComplexType generation that's in EDM 4.0 beta) and UDFs. This would complete and enhance the SQL Server support in future.

Today I posted the code to a projcect I've been working on, a library that creates KML documents from SQL Server 2008 geography instances or queries. It's called SQLServerToKML and is available on CodePlex here. There's no releases as such yet, just the library and a rudimentary document showing how to us it.

Hope it's useful, post comments/suggestions to the project's discussion page.

Last week, Microsoft released the MapPoint 2009 Add-In for SQL Server 2008 Spatial. You can get it here.

This add in allows you to add layers to the map from queries against SQL Server spatial geography columns. It's more of an entry level mapping program that an IT-level (like the ESRI offerings) mapping program. You can save the map and use it against without SQL Server having to be available. There's also a number of "utility" type functions including shapefile import and queries and editing against your existing layers.

One thing I especially like about it is that it's usable sans-web, because the MapPoint data is local.

I was peripherally involved with the project from a point of view of identifying general spatial query constructs that would be most performant, usually this involved using query patterns (both in T-SQL and the underlying ADO.NET code) that would be most likely to use spatial indexes. There's a few different constructs (like CTEs, intermediate temp tables, and using parameterized queries in ADO.NET) that are a help in this regard. It was a learning experience.

Check it out.