There's another change to the SQL Server database engine in SQL Server 2008 R2. This concerns poison-message handling behavior in Service Broker applications.

Service broker's messaging is always transactional. A RECEIVE SQL statement is transactional and can be combined with other database operations as part of a transaction. For example, you can code a RECEIVE for a Service Broker queue combined with an INSERT of a database row, based on the information in the message. If the transaction fails (say the row's primary key already exists), the message that was RECEIVEd will be put back on the queue. But then, the next RECEIVE could read the same message again; if the message is never able to be processed correctly, and the RECEIVE-INSERT-ERROR-ROLLBACK statement can loop on this particular message. This is known as a poison message.

It is suggested that you write your own poison message handling strategy, a few strategies are listed here. If you do not write your own strategy, Service Broker's default strategy (which happens after five consecutive transaction rollbacks) is to disable the queue. You can receive a QUEUE_DISABLED event if you write a service broker service that handles the BROKER_QUEUE_DISABLED event notification.

Some folks have complained that this is a fairly drastic step (and limits the poison message-handling strategies you can write), but poison message loops do have the capability to waste SQL Server resources. So...

In SQL Server 2008 R2, both the CREATE QUEUE and ALTER QUEUE DDL statements allow the (new) specification POISON_MESSAGE_HANDLING(STATUS = OFF/ON). According to the description "This allows for a custom poison message handing system to be defined by the application." This does not relieve you from writing your own strategy, it just means that you can turn off disabling the queue for a 5-rollback poison message.

As long as I'm blogging about filestreams...

Folks always want to know if the filestream information can be stored on a remote share. They look kind of disappointed when they hear that the filestream filegroup must exist on a local (to the SQL Server instance) drive. However... (isn't there always a however?)

For SQL Server 2008, the SQL Server team released a companion feature to filestream called remote blob storage (RBS). RBS consists of a set a stored procedures and an SDK that allows you to store your blob on a remote blob store. The SDK works differently than the filestream storage feature. And, to use the feature, you need a RBS blob store provider. Looking around, EMC² has an RBS provider for the Centera product line. And there's a sample provider and code to use it up on CodePlex.

The SQL Server team released the first version of RBS as part of the SQL Server 2008 Feature Pack.

They'd always said they'd be bridging the gap between filestream storage and RBS in future. We'll...the future is soon (or now, depending on how much you like CTPs). The November CTP of the SQL Server 2008 R2 Feature Pack comes with an RBS provider for Filestream. You still have to use the RBS APIs, but you can have your (filestream) cake and eat it too. Remoting. There's little info right now, but there are a number of postings on the RBS team's blog.  Including a comparison of filestream storage and RBS. In addition, RBS & filestream appears to hook into SharePoint 2010, but I'll leave the description of that to the SharePoint 2010 documentation (which has just been updated today).

And, BTW, if you were going to ask "Are features that appear in the SQL Server Feature Packs supported?", the answer is yes. I asked.

I was under the impression that the database engine/programming model changes in SQL Server 2008 R2 were minimal and could be counted on one hand. Today I was running an old demo that I had, having to do with filestreams on the R2 Nov CTP. Usually I run this one line-by-line, but I was in a hurry and ran the whole thing. Funny...I didn't remember this few errors caused by limitations when using filestream. Hmmm...

In SQL Server 2008 R2, filestream storage now support snapshots transaction isolation levels. Both flavors, read-committed snapshot and snapshot. This should expand the possibilities for using this feature because, in SQL Server 2008 (non-R2), you can't even enable either of these isolation levels at the database level if you have a filestream filegroup/column. Now, you can not only enable the levels, but the filestream goes exhibits the transactional semantics.

This is actually doc'd in the SQL Server 2008 R2 BOL, right here.  BTW, although the chart in the BOL lists the streaming access doesn't support ReadUncommitted, RepeatableRead, or Serializable iso levels, in my experiments, you don't use an error using these iso levels with streaming. The stream just doesn't exhibit the expected transactional semantics.

So you can count 'em on one hand, 'eh? Between this enhancement and the perf improvements blogged about recently on the SQL Server Storage Engine blog, maybe I'll need another hand.

In the last installment about the object model, let's the at the third development style, using .NET's IObservable/IObserver. IObservable/IObserver is a new interface in .NET 4.0, the "mirror image" (to use a less controversial term) of IEnumerable/IEnumerator. A really simplistic way of thinking about it is the Enumerable/Enumarator is allowing you to pull, IObservable/IObserver is watching someone push. So supporting IObservable/IObserver makes it possible to use StreamInsight with .NET classes.

What they've done in this case is write a generic StreamInsight adapter set over the interfaces. It's called ObservableXXXInputAdapter and ObservableXXXOutputAdapter, where XXX stands for the three StreamInsight event shapes (Point, Edge, and Interval). So there's six classes plus a Factories and Config class for each adapter. Similar in concept to some of the generic text providers they use in the samples, but shipped in a supported library.

To encapsulate the rest of the model there are some ExtensionMethods for the .NET classes that implement IObservables (and a hook for IEnumerables). The main ones are ToObservable and ToCepStream.  They work with other extension methods and helper classes to provide for a generic transformation of .NET classes that implement the right interfaces to StreamInsight objects from the familiar Object Model. These extension methods create the (same) StreamInsight object model along the way. Although there is one method currently that may allow you to inject/use your own Server instance, therefore, being able to navigate the whole Object Model.

So at the end of the day, you work with IObservables/IObservers (and maybe IEnumerables/IEnumerators). And the same object model. For a code-assisted review of the main components of the object model, you may now return the the Explicit Server sample. Or for a data-assisted review, a CE database that's been "used" to register CEP objects. ;-)

Now that you know the StreamInsight metadata (see inside the metadata), its fairly easy to take apart the implicit model because you already know which objects are being created for you. In the implicit development model, you:

1. Create a CepStream for input - specifying
   EventType as a template parameter
   Event Shape (Point, Edge, or Interval)
   Factory class of the input adapter
   Config info for the adapter
2. Build your LINQ query over the stream
3. Call Stream.ToQuery on input stream - specifying
   Factory class of output adapter
   Output configuration
   Output event shape
   A stream event order

When calling Stream.ToQuery in step 3, the internals have all of the information to build the objects you need. That one call:
   Implicitly creates Server and Application
   Registers and configures Adapters, QueryTemplate (from step 2), EventType info.
   Binds the adapters to the QueryTemplate producing the Query.

All you need to do is start the query.  Just to prove that the entire object model is in place, you can use the references to navigate from the Query instance back to the Server, and retrieve the diagnostic information, just like in the Explicit Model. Using the ImplicitServer sample...

Query query = ConsumeQuery(top);  // step 3 - see above, now add the following code.

//Now, get the Server
Server server = query.Application.Server;

At the end of the run, you can use your server instance to get the diagnostic views. Cribbing a little code from the ExplicitServer...

RetrieveDiagnostics(server.GetDiagnosticView(new Uri("cep:/Server/EventManager")), tracer);
RetrieveDiagnostics(server.GetDiagnosticView(new Uri("cep:/Server/PlanManager")), tracer);

// View for your query from Stream.ToQuery
RetrieveDiagnostics(server.GetDiagnosticView(query.Name), tracer);

The main differences between this model and the explicit server model are:
  Because you're not instanciating the server, you don't specify server options or use Server.Connect
  Because you didn't name some of the objects (because you didn't register them yourself), it would be more difficult to reuse them in a robust manner. If you're going to go the trouble of trundling through collections or depending on registration order (if this would work), you might as well use the explicit model. If you look at the query.Name, for example, you can see that the implicit model has made the application name "default" and the query name "DefaultQuery".

But its still the same object model.

The StreamInsight object model seems fairly complex at first. There's a series of choices as you progress down the development path which make things appear more complex than they are. One thing to keep in mind is, no matter how you populate it, there is only one object model that encapsulates the metadata.

One way to get a handle of the model is to look into the metadata. The easiest way to do this is to use SQL Server CE to register your metadata. If you don't specify SQL Server CE at runtime, the default behavior is to store the information in memory, making it more difficult to see.

Note that the included standalone server host application, StreamInsight.exe, can use either SQL Server CE or inprocess metadata based on the presence of a configuration file setting.

To use SQL Server CE, use the Explicit Server Development Model (StreamInsight Books Online has nice information comparing and contrasting the development models), but instead of using:

Server s = Server.Create();

these additional lines will use SQL Server CE.

SqlCeMetadataProviderConfiguration config = new SqlCeMetadataProviderConfiguration();
config.DataSource = "MyMetabase.sdf";  // these should be in a config file
config.CreateDataSourceIfMissing = true;
Server s = Server.Create(config);

When you register the rest of the StreamInsight objects (Application, EventTypes, Adapters, etc), the metadata will be written into the CE database. Because the database information does not disappear when the application completes (unless you specifically delete it), you can browse the StreamInsight metadata afterwards.

You can also go a step further than that. Because the metadata is stored in the database, its possible to pre-provision the database with the registration information by running the program once. With a pre-provisioned database your program would consist of creating the Server (as in the code above) and pulling out the configuration like this:

Application a = s.Applications["TheApplication"]; // the Application name from original run
if (a == null)
  throw new Exception("Application Metadata Not Found");

It's possible to run your entire application this way, because all of the metadata you provided has been stored with full fidelity in the CE database. If you run, say, the sample ExplicitServer app this way, you need only fetch the Application and Query objects and start your Query. The other objects (Adapters, EventTypes, Bindings, Streams, and QueryTemplate) are already in place from the database where you populated the metadata originally.

I thought it was curious that in a DACPAC you can specify required version and edition of SQL Server as a deployment option. But DAC (Data-Tier Applications) is a new feature of SQL Server 2008 R2 and VS2010 data tools. So what versions and editions does it support? (or will it support?). The somewhat surprising answer came in today, as an answer to a forum question.

Check this thread out... If I'm reading this correctly, and "No ¹" means "No with a superscript of 1", the functionality may be being backported (see note 1 below the chart). Very cool!

Visual Studio 2010 B2 can make DACs and deploy them as of Nov 2008 CTP of R2. Take a look at Sanjay's video if you missed this announcement.

Note that DAC is a separate concept from SQL Server Utility, although Utility will keep per-DAC stats (one diagram in BOL insinuates non-DAC databases too, but I think I might be misreading the diagram) on the SQL Servers it manages. Utility can only manage 2008 R2 instances at present but maybe (big maybe, move along, nothing to see here) they might backport the support to 2008 some day. And remember, the SQL Server edition for a UCP (Utility Control Point) must be SQL Server 2008 R2 Data Center, Developer, or Enterprise Evaluation. That's Data Center-only for "real" installations.

Glad to see this functionality taking shape.

Folks that are on early betas of products (especially private betas) are not surprised when projects are not updatable between beta versions. These limits are usually listed in the release notes, sometimes you'll just stumble across them.

I'm getting back into SQL Server 2008 R2 and related features in earnest (since my presentation in Portland in September) after a busy November and December and noticed early on that PowerPivot workbooks weren't updateable between CTP2 and CTP3.

Lately I found that maps from BI Dev Studio Report Projects from the August CTP don't upgrade to the November CTP. Although it says the project upgrades, the maps are unusable. However, there is a workaround (I bet you wondered when I'd start including useful information in this post ;-). Just create a new report project in BI Dev Studio November CTP. Then, move the .rdl, .rdl.data, and .mds files from the old to new project. Voila! Maps!