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 <sup>1</sup>" 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!

Categories:
SQL Server 2008 R2

SQL Server Management Studio in 2008 R2 (and there's a standalone upgrade for SSMS) supports connecting to SQL Azure. And there were some tools on CodePlex that did too. And that's what I'd always used. But reading along in the latest docs I came across the following statement "Connecting to SQL Azure by using OLE DB is not supported". Later on, the docs talked about using SQLCMD (which is a SQL Server utility that uses OLE DB) and SSIS (which can use ADO.NET/ODBC, but uses OLE DB as API of choice) being supported.

So I decided to try an experiment using SQLCMD (which should be supported), ODBC Data Sources (which should work), and a UDL file (OLE DB, should not be supported). And leave the SSIS question for another day. This turned out to be harder than it sounded. The books online mentioned using (with SQLCMD):

SQLCMD -U {Login@ServerName} -P {password} -S {ServerName} -d master

But the ServerName in question is either a localname (e.g. foo) or DNS name (e.g. tcp:foo.database.windows.net). Turns out that the DNS name must be used for the -S operand (unless you put together a host table) and Login@ServerName MUST specify the localname (no database.windows.net suffix). So this would be:

SQLCMD -U Login@foo -P {password} -S tcp:foo.database.windows.net -d master

And the "tcp:" prefix on server name turned out to be optional if the have your client stack set up for TCP (or TCP & SharedMemory) only.

Got it. This turned out to be key in getting the connection to work in all three cases. But why is OLE DB not supported? It appears to work, the UDL file worked as well as ODBC Driver Manager. Or did it? Turned out that when I tried the dropdown list that enumerates databases in the UDL editor, I received "Connection success, but could not list databases". Trying the database name in worked fine. Hmmm...so here's my wild guess. The SQL Server OLE DB provider will on occasion use its own stored procedures to obtain metadata (like sp_columns_rowset) which retrieve the exact metadata that the OLE DB provider expects to see. Perhaps they didn't bring those along to SQL Azure. Or maybe its just the extra test cycles for an additional API.

But it would be nice to know why OLE DB appears to work but is not supported (except in some utilities). And whether using the OLE DB source and destination in SSIS is recommended.

Categories:
SQL Azure Database

Yesterday I signed up for my account to the official, live, RTM cloud. Of course it was all for the SQL Server, that is, SQL Server Azure. MSDN premium subscribers get a free 8-month trial, so I signed up for that and they transferred by CTP databases over (actually, my "server name" didn't change at all). See Roger Jennings' step-by-step walkthrough to make sure you get the MSDN plan if you're a subscriber.

I'd be playing with Windows Azure and SQL Azure since CTP1. But its all real and serious this time. I had to enter my credit card number. And this morning I got email that my bill (albeit for $0.00) was ready. Although the MSDN service is free, I did agree to pay if I used more than the allotted amount of resources. So now its time to look at where the limits come into play in earnest.

The thing that stuck out about the SQL Azure docs were the number of "thou shalt not" and "partially supported" things. I'll admit that I haven't worked on SQL Server Express (that supports a subset of features) or SQL CE (that supports a subset of the T-SQL dialect) that often, but even then you could poke at things enough to figure out how it works and why. With SQL Azure, you just can't do that. So you might hear from me about things that sound strange (from the docs) in the next few days. Now that I don't have to ask "will this limit also be in RTM?".

The first thing that was surprising (because I swear it didn't work in early CTPs, maybe I was wrong) is that the legacy TEXT, NTEXT, and IMAGE data types are supported. They've been on the deprecation list since SQL Server 2005. Of course, the neat ways you could manipulate them (like TEXTPTR) are not. But why TEXT, at this late date?

More to come. Cheers.

Categories:
SQL Azure Database

Theme design by Nukeation based on Jelle Druyts