Author: way0utwest

The 7th T-SQL Tuesday is upon us and you can read about it on SQL Chicken’s blog. He’s hosting this month, with a few rule changes (like the picture above) and his call is for posts on new features in SQL Server 2008 or SQL Server 2008 R2.

T-SQL Tuesday is a blog party where we encourage a large number of people to write on a single theme or topic. It’s fun, and I encourage all of you to participate.

StreamInsight

A new feature introduced in SQL Server 2008 R2 is StreamInsight. It’s a complex event processing technology that I think is one of the more amazing things to be added to SQL Server in quite some time. Of course I thought the Service Broker was cool in SQL Server 2005, so that shows what I know.

This is based on the work at Streambase, and the work of Michael Stonebreaker. It involves building a query engine, processing that can handle large volumes of data, things that come in streams: price quotes, sensor data, auditing data, any number of continuous data elements.

We commissioned a Stairway Series on this topic, since I think it’s a game changer. The introductory article, What is StreamInsight and why does it matter?, does a great job showing what the technology is and what it can do. It’s a way to filter, process and gather data, find patterns, etc. from a large volume of data, and then get it into another database.

Why does that matter to us? I think this image presents a great view of why:

As much as I have said BI isn’t the next big thing because it’s too complex, I think there’s great value in BI systems that can distill data down to a point where we can work with it. I think StreamInsight can help with this, especially as we get larger and larger volumes of data, more than our SQL Server instances can handle.

I believe the ability to gather information from a variety of streams, in a more timely manner than you might get with something like Integration Services (SSIS), will greatly aid businesses in analyzing the information that can help them.

This is a technology that I can see lots of uses for. Real-time auditing data, going through a stream of information about activity on a server or application and flagging patterns. Learning what is normal, and then raising flags when there’s a deviation from that.

It could be great for monitoring a large volume of data from some source like Twitter, searching for references to your company or product, categorizing them, counting retweets, analyzing those that never get a response, or might need a response. The same thing could be done with a stream of blog entries from a search engine like Google or Bing.

I think the possibilities could be very cool. I look forward to seeing some innovative uses of this new feature.

StreamInsight is available in SQL Server 2008 R2, in two editions. There’s a Standard edition that works with Standard, Enterprise, and Web editions. There’s also a  Premium Edition that is available for Enterprise, Developer, and Evaluation editions. The difference is in the rate of processing that occurs.

If you exceed these levels of stream data, you need the Premium edition:

• More than 5000 events a second
• You need to process them inside of 5 seconds (low latency)

These are the basic differences in the editions, and if you can tolerate a delay of more than five seconds (most companies/applications) and you don’t have an extremely high rate of data (5000 events/sec), you can use this technology in Standard Edition.

This is one of my favorite technologies for SQL Server 2008 R2. This session was from Torsten Grabs, the program manager.

It starts looking at the value  of timely analysis. Being able to view, and use data in a timely manner. A trend line of analysis with time. Data that is months and years old is for historical analysis. Days and hours is primarily used in forecasting. The data that is minutes and seconds old, can be very valuable. This is stuff from web analytics, etc. More and more verticals are seeing value in real time information, and they want to query it. Health case, financial services, utilities, etc.

Many companies have built home grown solutions for this, but they are expensive and require lots of development resources as the number of events grows.

MS decided to build this into SQL Server since it was a missing part of their stack. Where does this fit into SQL Server?

Operational Intelligence as a platform comes from multiple sources of data being cached, processed, distributed, and then visualized in multiple ways.

From the slide, you want data from the left to move to the right as quickly as possible for the top, push items. The pull items, reports, mining, etc, you also want to be able to refresh each other quickly.

How do event driven applications differ from standard database applications? There is a good set of contrasts. The query paradigm is from adhoc queries. For event driven apps, there are continuous standing queries.

For latency, a standard db looks for days, minutes, seconds. The event driven app wants ms or less.

For the data rate, standard db wants hundreds of events per sec. For event driven, there are tens of thousands per second.

For the query semantics, both use relational queries, but event driven items need temporal queries as well.

The target applications are for operational analytics apps, web analytics, manufacturing apps.

For an overview, there are development with VS 2010 using .NET, C#, LINQ.

The application at runtime takes input adapters, perhaps some static reference data from SQL Server, and it sends the data through output adapters to consumers. These can be devices, or they can be event or data stores.

Inside, StreamInsight takes your LINQ queries and compiles them to be used against the streams of data. They become triggers against events.

Out of the box, this is an in memory query processing engine. You do not need to persist any data to work with it in StreamInsight. Persisting data is expensive, and introduces latency. You have the choice of where, and if, you want to deal with this latency.

StreamInsight query is very composable, so you can take out smaller portions of the queries and reuse them, or build on them for other queries.

This is needed because data acquisition costs are falling, and with low storage requirements of StreamInsight, you can now process data while it is in flight and then avoid loading huge amounts of data.

Utility Scenario: households can be instrumented with smart grid power meter. This provides continuous insight into usage, and/or demand. StreamInsight scales to millions of meters, with 100,000s meter readings per second. You can then write validation, editing, estimation rules in LINQ, that might allow them to see a constant view of their grid, and perhaps help prevent power outages.

Financial Services scenario: You can get continuous low latency market waching, and managing risk better. You can actually detect and notify users in real time of actual risk. You can also take the same models or LINQ queries and run them against historical data.

Web Analytics: StreamInsight allows continuous analysis of online behavior. This can identify relevant content before the next click, and perhaps define that new content based on the click behavior. It also allows analysis of web logs online, in near real time instead of offline in a batch.

Event Types – basic structure of what you are writing queries against. Structure, using the .NET type “system” and have a series of fields. The SI engine provides timestamp fields to capture all temporal event characteristics. All calculations are based on business time.

Event streams can be continuous, or  bursty. They can go on indefinitely, and potentially arrive out of order.

There are a number of operators that provide all sorts of things you might expect. JOINs, existence, filtering, grouping, APPLY operations, sum, count, ranking, and more. There are a number of temporal operations. Four are provided out of the box: hopping window, sliding window, tumbling window, and a count window.

The demo shows some real time data management. In this case, it is a financial application, examining MSFT and IBM as stock tickets. Incoming data is partitioned with a temporal function by stock, and data is pushed to some applications, and the stored in SQL Server as well. We saw the stream start coming into an application, which could then display the data as a stream.

By enabling various output adapters, we could see graphs updating in real time, some data being stored in SQL Server, and even Excel pulling refreshed data for someone. It’s very cool.

There is a Linqpad like application that can connect to a stream and allow you to write some LNQ queries and see results from the stream.

We also saw the debugger. You can see what is happening in your application, and even record a stream. If you pick an output row, you can do a root cause analysis, and see at each stop of the query processing, what rows contributed to that output row. That would be amazing for T-SQL as well. If you could see a complex query and find out which rows “contributed” to that final result.

This is an extensible framework, allowing you to build your own operators or extensions into the StreamInsight applications. These can be temporal or non-temporal operators.

There are different deployment scenarios. You can process close to the data source, or you can deploy further downstream and capture events from multiple types of sources.

StreamInsight is being deployed as an embedded engine, a few DLLs inside your application. This gives a low footprint and very little overhead. It can run as a Windows Service as well.

The packaging is something I’ve noted for tomorrow’s blog, and the latency and event size varies according to the edition.

There are a number of partners that are working with MS on solutions. Check the StreamInsight site for more information. Lab 49, OSISoft, Matrikon, and more can help you.

This stuff is cool. Mostly high end, but I can see lots of smaller sites thinking of using this for more real time web analytic analysis.

You need consistent, reliable data. You want data that you can trust.

This is usually data about your transactions, slowly changing data that multiple stakeholders need. This is not necessarily the traditional definition of MDM, but MS sees as master data as something that is important. This isn’t a great session in terms of presentation, but it’s an interesting idea to examine and consider.

Why do we need an MDM solution? I’ve never felt I had a need in the operational world. Often it’s because you have simple apps that help you handle data, but it might not work, or your ETL solutions are expensive. They don’t work well. Or you can’t actually get the data from your warehouse. Or maybe you can’t alter your line of business solutions (ERP, CRM, etc.) can’t change to add new attributes or sections of data.

You are probably using MDM right now, but perhaps it’s distributed into multiple solutions. Sharepoint lists, Excel, Access, etc. The MDM services here are not built into a single technology, and you will need other technologies to work with the master data and ensure that it moves to the places where it is needed.

One key thing to keep in mind is that building an MDM solution ought to be a multi-pass solution. Don’t try to solve everything at once. Implement it in phases, and grow it as you can.

How does MDS work?

There is nothing magic here, and it can be solved with a SQL database. This MDS has it’s own database, with security to allow you to segregate data to certain users. It has all the features of SQL Server, so it can easily integrate with other SQL applications, and there is an WCF API.

It helps you enforce process with a UI for business users as well as handling simple business rules among the data. It also allows you to send notifications/alerts or start Sharepoint workflows.

The demo in this session essentially showed a basic model that is almost like building a small database yourself. You built entities, link them, add attributes, just like you might for any other database model. In some sense, what you’re doing here, apparently, is building the model for your lookup data, and then making sure that all other systems/applications work with this “master data” for themselves.

It seems like it might be something you can use, but I wonder whether this provides a better framework than what you might build. There are definitely some time saving features in here that make it easy to enforce some business rules.

It’s a zoo.

Once again wifi sucks, sitting in the keynote room, unable to join the wireless network and get a connection. Likely this will be posted later in the day once I find a stable connection.

Opening this morning was (Syncopation?) a jazz band from New Orleans, playing live on stage before the keynote as we waited for the Microsoft talk. It’s nice to have something going on since TechNet is truly a zoo.

I didn’t need to read signs to find breakfast, I could just follow the hundreds of people flowing through the convention center. After a muffin and some coffee I headed to the main auditorium, filling up my water bottle on the way. One great thing about TechEd is that there is always a stand nearby with coffee, water, or some type of beverage to enjoy.

This place really is a zoo. If you want a really large conference experience, nothing like the PASS Summit, come to TechEd. The keynote auditorium has 5 large screens flanking the stage, each one has to be at least 20ft high and 30ft wide. Then in the rear of the room are 4 more large screens, each one at least 15ft across since there are likely 8,000-10,000 people in here. And in the New Orleans heat, I’m sure AC is working overtime.

Cloud Computing

That’s the focus of Bob Muglia’s keynote. Dynamic IT, a 10 year vision MS presented in 2003 at TechEd then. Seven years in, they feel they’re delivering. Do you? It’s hard to look back to 2003 and think about how things have changed. In some sense I know things have changed in seven years, but is it a lot better?

I saw a demo with System Center and VS2010. A bug filed from Test Manager, showing lots of information, including video, about the bug that occurred. An error popped in the video, but they switched windows quickly. So was it a VS bug or an app bug the demo is showing. These demos are cool, but they sometimes seem a little contrived. I somehow doubt that all of this will be created, even automated, by testers  working through a large test suite that they are trying to get through.

The very cool thing I did see was walking backwards through VS from the point of the bug. Going forward backward would be cool for developers. ?Not sure in T-SQL, especially as we’re moving lots of data around, but walking back to see what happened right before the bug, the environment as it’s getting set up, that’s cool.

“look ma, no hands”

The demo girl raised her hands during the automated test, which was something I was wondering about. It’s hard to tell sometimes what’s real and what isn’t. Hopefully it’s not someone in the background that’s actually managing the demo. This is a case where I’d rather see video of this edited so that it works smoothly.

Dynamic IT, showed live, automating our environments. That’s cool, but what about some real failure scenarios. Errors in deploying to an environment, seeing changes partially deployed, connectivity issues, bugs found immediately and rollbacks. Those are the demos and scenarios I’d like to see. Maybe they’ll put some of those out on TechNet.

“The cloud is a major transformation.”

Is it? I wonder

Bob says it’s about delivering IT as a standardized service. I think they’d like that, especially the standardized (on Windows) part.

JIT provisioning and scaling of services

That is a big thing about Virtualization and cloud computing. This is, in many ways, better than hot-add/hot-subtract that I’ve seen in IBM/AIX servers. I do like this part of cloud computing, but I think that we ought to be thinking more private clouds initially, rather than public clouds for these services.

One true fact that Bob mentioned is that when you are dealing with thousands of servers, you cannot deploy/manage/or even build them in the way you used to. I am sure of that. Having a few guys unpacking and imaging servers does not work well. Just trying to get them to understand where the storage is, the names are, etc., is complex. You need a way to actually have the junior architect handle stuff like that and automatically have names, IPs, etc. pushed to images in an automated fashion.

But how many people, even here at TechEd, would have to manage and deploy thousands of servers? Even with VMs, in smaller companies, I’d think it would be rare that you would need to deploy 100 servers at a time. You might have that many servers, but not likely that you are dramatically renaming/ reconfiguring them at the same time. This is more larger scale stuff for a service provider, like Microsoft or Rackspace. So are we seeing technology here designed to make us more comfortable with MS as a provider?

It is cool to see System Center allowing you to provision a new application and set min/max limits for the number of servers that are being used. Useful for the web, or middle tier, not not necessarily for the db.

Yet.

Once we get some more scale out features, this will be very cool. And it will be cool for those running parallel data warehouse. All 4 of you.

Hearing Bob say they patch offline and deploy/reimage reminds me of a discussion from SQL Saturday #22. Someone asked why can’t we patch without any downtime. I wasn’t sure I wanted it, seeing the pressure to deliver patches, and the lack of testing, and the potential for errors. I think this helps, but in some sense, patching is one of the issues I have with Cloud Computing. I don’t have control over patching at all, which can be an issue when I have applications that aren’t standard.

The applications they’re doing in the  demos do look much better. It appears that they are getting more designers and UI/UX people to help build them.

This demo seems to show AD information, name, login, address, etc. flowing up to the cloud through App Fabric. That is both cool and scary. Will more liability be handled in the cloud? Security becomes more important. Are we then going to see less information stored in AD?

EF 4.0 is a “premier ORM framework”.

Seeing the VS designer be able to designate VM sizes is an issue. As smart as many developers are, should they be architecting at that level? I’m not sure.

Using local SSMS to talk to SQL Azure clients. Not surprised, but I wonder how many things inside of SSMS don’t work, or are limited based on SQL Azure limitations.

Lots of focus on System Center. I wonder how many people use it, and drill through diagrams, how many people just have the images on their desktop to look busy, and how much margin there is in that product. It feels like a push to sell this particular product.

Lots of companies building clouds. Makes me wonder is they are really building, reselling from someone like MS, and how reliable, and liable, they’ll be. That is a major concern on the cloud platform.

However if you can use the cloud as a CDN, replicating or transferring some database data to have them run more powerfully, that is interesting. That works well if the web servers, or front ends, can easily switch dbs, or they can scale a single cloud db behind the primary one.

But how many people need that. I’m sure hundreds, lots of content providers, but SQLServerCentral, StackOverflow, some very, very busy sites don’t need that. We run on 1-2 servers. I do have some friends that do a lot of virtualization work and they said after the session that they would really like better tools, and System Manager seems like it would work well for them.

Bandwidth in the demo, which you would think would be dedicated, didn’t work. There were delays, issues, and app sharing of PPT didn’t work well.

The cloud wants smarter devices. I agree, but they can’t all be WinMo phones. Or BBs. They need to be all types of devices, which means standards, and collaboration.

Sharepoint collaboration on Windows Mobile 7. I think this is useful for business people, especially since people will forget to finish things or make mistakes in the office. They can make corrections and see documents away from the desk. That’s good, but I’d like to see that on Android, BB, and iPHone as well.

The cloud can bring with it sources of information you don’t have on your desktop.

With Azure you can build applications in a fraction of a time? I call BS on that. Good development just takes time and it’s not the platform. Unless you infrastructure sucks.

40M people paying for Exchange and Sharepoint. I can believe that. But it’s a licensing issue, especially if I can buy small numbers of mailboxes/accounts for people to use.

Communications Server 14, the next version. New features that show communication and collaboration. How can they say that when they’ve probably say that over and over in the past.

74000 soft-phones in MS. That is pretty impressive. And about time. I think hardlines are dying faster than I had expected.

An ecosystem of cheap, HD cameras. Good for porn, and for grandparents. I can see big bandwidth issues coming with all the video work being done. And with people getting stuck in more meetings, even when they work remotely, or are out of the office. How much more task switching will come from video calls?

It wasn’t a great keynote, nothing major shown, nothing incredible or wow’ing, but a good demonstration of how much Microsoft wants to push the cloud.