About a week or so ago, I read a request for information about a SQL Server database management feature known as Data-Tier Applications, abbreviated as DAC (apparently someone realized that the DTA abbreviation was already "taken" in SQL Server (Database Tuning Advisor) but not that DAC was too (Dedicated Admin Connection)). Data-Tier Applications is a feature that was introduced as part of Visual Studio 2010 and is supported in SQL Server Management Studio for SQL Server 2008 R2. It is a fairly (understatment here) controversial feature, that provides a file (.dacpac) that uses an XML format that encapsulates descriptions of database objects, suitable for deployment.

The .dacpac could be thought of as the ".msi file" of database deployment. The basic premise is that you can produce a .dacpac from an existing database or create one with Visual Studio. Then you can point at the instance node in Object Explorer pane of SSMS and choose "Deploy Data-Tier Application". The underlying infrastructure produces and runs DDL to set up the database and underlying database objects. You can also automate all the use cases using PowerShell, as its based on SMO.

DAC-deployed databases keep deployment history information in MSDB (in master if you're using SQL Azure, as Azure has no MSDB). DAC-deployed databases also integrate with SQL Server 2008 R2's Utility Control Point feature.

Most (well, all that I've seen) articles cover version 1.0 of the DAC feature, which has the following limitations:
1. Didn't support all database objects, not even all the ones in SQL Azure Database.
2. It didn't address server-level objects (except for logins) like SQL Agent jobs, at all.
3. The "upgrade a database" action didn't upgrade in place; instead, it made a copy of the database set up the new DDL and copied the data in existing tables "the best it could". Then renamed the current database to old and new database to current. The "best it could" could result in data loss, and reading the docs (and runtime warnings) informed you of where this could occur. You would (naturally) need to back up your database after a DAC "upgrade", as your transaction log recovery chain (and anything else in the old database would be renamed away. Although they do keep the old database around for those instances where you need to (and are able to) scavenge from it.

Folks correctly target this as a SQL Azure Database-centric (but not only) feature. You can support other objects with manual pre-and-post deployment scripts. Moving just a database and logins directly relate to the SQL Azure product structure. But there is an upcoming Denali feature, Contained Databases, that also go by the concept that everything should be contained inside the database, to simpify moving a database from one SQL Server instance to another. This would another place where DAC will likely come into play.

Back to my original premise...since the original DAC annoncement and general upsetness by the SQL Server DBA community, the folks at DAC have listened to your "suggestions" (not the "off with their head" ones, just the constructive ones). And DAC has already released a 1.1 version. And a 2.0 beta version is also available. Because this posting is getting a bit long, I'll cover them in detail in the next entry. But for now, realize that if you're reading an article about DAC, its most likely about DAC 1.0. And things have changed. You can get version 1.1 here. It fixes the "upgrade" scenario, point number 3 in the list above.

@bobbeauch

I've recently completed an update to the SQL Server 2005 Security Best Practices whitepaper. It's available on the security and compliance website under whitepapers or the direct link is here. Unsurprisingly, its called "SQL Server 2008 R2 Security Best Practices - Operational and Administrative Tasks". Thanks to all the folks who reviewed it. Enjoy.

@bobbeauch

I usually have a number of different versions of SQL Server around, installed on Virtual PCs. Usually works fine. So I was a bit surprised when installing the RTM version of SQL Server 2008 R2 from the downloaded .iso file to receive error message 2337 in the middle of the install. Thinking it was something wrong with my download (IIRC 2337 is a some kind of IO error during install), I re-downloaded the .iso. Same error. I then tried installing SP2 on my virtual Windows Server 2008 OS, then installing SQL Server again. No luck. Moved the .iso from the portable Passport drive to the C: drive on the host OS. Nope. Since this error occured during install of SQL Server database itself, there was no possibility of "guess I'll do without that component". Hmmm....

Finally I explored the possibility that this might be a problem with VPC2007SP1 .iso reader, the one that allows you to mount an ISO file as a virtual DVD. That's what I always use when dealing with ISOs on a VPC, (usually) works great. To eliminate this as a variable, I burned a physical DVD from the ISO, and mounted it on a remote computer, connecting to it as a network share. I could have tried physical DVD on the host, but was too lazy to swap the ATA disk for the DVD (it was my laptop). This worked fine, no IO error, everything installed great.

Since then I've also installed SQL Server successfully on a physical 64-bit laptop (no virtualization, but 64-bit version of SQL Server vs 32-bit on VPC, likely different installed files) from the exact same ISO, on my Passport drive, mounted using a free program, Virtual Clone Drive. No problem. But... I've heard of the exact same error from others on install to Windows Virtual PC (ie the one that you use with Windows7 host).

There's too many combinations to test them all out, but should you receive install error 2337 when installing SQL Server 2008 R2 on a VPC, you can likely benefit by the workaround. Of course, the nice thing about installing (especially betas, but even released software like this) is if you have a problem during install, you just delete the install-failed copy of the VPC image and try again.

Categories:
SQL Server 2008 R2

This posting is a journey through StreamInsight 1.0 APIs, undertaking by porting the CTP3 samples. Part 3.

In the StreamInsight CTPs, there existed three methods to code applications. My original post describes the current state of Observable. That leaves us with "Explicit Server" and "Implicit Server". Coding using the "Implicit Server" method,  you'd make (basically) two main calls. You'd create a CepStream with input provider information. Then use stream.ToQuery(...) specifying output provider information. This call would not only bind the query, but create a Server and Application object under the covers. Since (I'm not sure this is the reasoning) your Server object must specify the correct StreamInsight instance you named at install time, this would be more difficult to abstract.

So....even using the CepStream.Create() and ToQuery method for binding a query to a query template, you still must instanciate your own Server and Application instance. That's what the error message was telling me, unless I missed something obvious. So,

In ImplicitServer.cs:

1. Added these lines at the beginning. Remember "default" is my StreamInsight instance name from installation. Use your own instance name.

Server server = Server.Create("default");
Application application = server.CreateApplication("ImplicitServer");

2. Pass the application instance into the ConsumeQuery method.

3. In ConsumeQuery, use the overload of ToQuery(...) that specifies the application as the first parameter. Just like the error message said to.

And voila, ImplicitServer works too! To conclude this series, I need to also address the SQLApp example, the one that uses the StreamInsight sample input and output providers over SQL Server. This example (in SQLApp.cs) uses the "implicit server" method of coding query binding, so you'll need to make analogous changes like you did to ImplicitServer.cs (above).

And you have working example code with StreamInsight 1.0 RTM. Now I'm "caught up", time to look at the new stuff. And (maybe) determine how to use Reactive Framework for Observable providers and how this all fits together. Maybe starting with my good friend Bart De Smet's blog for that.

Cheers.

This posting is a journey through StreamInsight 1.0 APIs, undertaking by porting the CTP3 samples. Part 2.

So let's update the SimulatedDataInputFactory provider, now. The problem seems to be the replacement of the interface that allows you to configure the CTI frequency. Sure enough, ITypedDeclareAdvanceTimePolicy has been replaced by ITypedDeclareAdvanceTimeProperties.

In SimulatedDataInputFactory.cs

1. Change the interface declaration to the new name (above) ITypedDeclareAdvanceTimeProperties.

2. This interface has one method that now returns a AdapterAdvanceTimeSettings instead of an AdvanceTimeSetting. So change the code declaration at the end of the module to AdapterAdvanceTimeSettings. Now, to make an AdapterAdvanceTimeSettings....

3. Change the code in the DeclareAdvanceTimePolicy to the following:

return new AdapterAdvanceTimeSettings(
           new AdvanceTimeGenerationSettings(configInfo.CtiFrequency, TimeSpan.FromSeconds(0)),
           AdvanceTimePolicy.Drop);

It's the same information we provided before, just refactored a bit. So far, so good. if you've made all the changes so far, including the multiple Snapshot() -> SnapshotWindow(...) instances. The code should compile. Let me know of there's any changes I've missed writing about; my code compiles at this point.

First, to test the ExplicitServer sample. It runs fine....OK! Now the ImplicitServer sample. That's a different story. Although it compiled, running it produces the following error on the Stream.ToQuery(...) statement.

System.InvalidOperationException: The query application must be specified explicitly because none of query inputs are bound to queries. Use an overload that explicitly provides the target application for the query.

That's the subject for the next blog post...

This posting is a journey through StreamInsight 1.0 APIs, undertaking by porting the CTP3 samples.

The first look at the errors produced by recompiling the CTP3 examples is the realization that the IObservable model for coding adapters doesn't exist in the RTM version. This is quickly confirmed with a look at a StreamInsight forum thread; it may return in future. Meanwhile, use the reactive framework. So I removed the Observable and PatternDetector projects for the samples solution.

Concentrated on making the projects compile, first off. So, here's a list of the code that had to be changed for that purpose.

In ExplicitServer.cs

1. Server.Create() now requires an instance name. That's the instance you named when you installed the project. In my case, that's "default". Note that this doesn't mean you're using the service, you'd still need Server.Connect for that.

2. CreateQueryTemplate needs two extra arguments, template name and template description. Call them whatever you want and describe them as you'd like.

3. application.CreateQuery has the arguments in a different order (at least in the 3-arg version the samples use). It's now, 'name, description, and query binder instance. There's a few of these in other samples.

4. The Snapshot() method in LINQ queries is replaced by SnapshotWindow(SnapshotWinderOutputPolicy.Clip). Clip is the only policy supported, so there's no real choice here. There's a few uses the Snapshot() in the samples.

5. While we're on Windowing code in LINQ queries, the TumblingWindow constructor has one new required parameter (in UserAggregateQuery.cs), that's HoppingWindowOutputPolicy.ClipToWindowEnd. Again, ClipToWindowEnd is the only policy currently supported.

Now, although most of the adapters seem to build just fine, as is, there's a little problem with the SimulatedDataInputFactory for that set of providers. That's covered in the next blog entry.

StreamInsight V1.0 RTM'd a few weeks ago. Although I worked with it, looking especially at the infrastructure and programming models, I just installed the RTM version a few days ago.

One thing I noticed immediately upon install is that it requires a license key. This is because there are different versions of StreamInsight based upon which SQL Server SKU you'd installed. See the licensing explanation, versioning, and SKUs information here. Because I hadn't yet installed SQL Server 2008 R2, I chose to enter no license key, which meant that I installed the 180-day trial version. Now that SQL Server 2008 R2 is available via MSDN, I'll need to revisit this.

Next, I was prompted for a StreamInsight instance name. Being the SQL Server and PowerShell user I am, and having no imagination, I chose the name "default" for my default instance. After installation, I noticed a shiny new StreamInsight (default) Service listed under Windows Services. This service is meant to run the standalone StreamInsight host, if you chose to run a separate host rather than imbed StreamInsight in your application. Much easier than starting up the service from the command line.

There's some neat new features since CTP3, namely support for composing queries at runtime, count windows, and left-anti-semi joins in the LINQ provider. Check the updated documentation. Those will be interesting to look at, but first I wanted to get my bearings by looking at the samples. Interestingly, no samples come with the RTM. Although there will be a codeplex project devoted to them, it's not active yet. Time to recompile the CTP3 samples.

Although there's a few changes noted to the APIs in the documentation, there are many more than documented. A naive 'recompile against the 1.0 libraries' netted 30 or so compile errors. Time to "up-port" the existing samples. This turns out to be a process that yields a lot of insight into the programming model of the 1.0 product, and I'll discuss this in the next few blog posts. I have the CTP3 samples working, but can't post the code, as it's not my code to post (ie, I don't "own" the code). So I'll describe the process.

 

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, EMC2 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 <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

Theme design by Nukeation based on Jelle Druyts