I received an interesting question today that I started to answer in email, then realized it would make a good blog post. It went something like..."Do you know if EF4 addresses the Bob Beauchemin’s criticism in his “LINQ to SQL and Entity Framework: Panacea or evil incarnate?” blog entries?"

First off, the series was not a blanket criticism of LINQ to SQL or Entity Framework, but a set of concerns, purely from a "database programmer's" point of view. That is, I'm comfortable with programming languages that are SQL-based and also languages that "are not SQL", like C#, and also comfortable with LINQ (which can use a syntax that's almost a dead-ringer for SQL). After examining the concerns, I dismissed some of them as unwarrented. I also didn't attempt to critique object-orientation or object-relational mapping in general, though some of the concerns are intrinsic to mappers in general (e.g. the existing of a code generator that generates SQL from LINQ or ESQL code). While we're on the subject of code generators, bear in mind that both L2S and EF support stored procedures and "store queries" and so use of the L2S or L2E/ESQL language is, techincally, optional.

So how does EF4/L2S4 change the game?

1. EF4 has fixed some of the “plan cache pollution” problems I referred to, but, in order to avoid plan cache pollution you must use parameterized queries in you L2E or ESQL code. There were other SQL generator related enhancements too. See these two blog entries by the team.
2. The best practice of “always get an anonymous type with only the columns you need” unless you REALLY need a whole object instance still holds.
3. There’s still no way to do bulk updates or deletes, but there  are various workarounds for this.
4. It's still a best practice to limit the number of levels of "Included" objects in your L2E code; the code generator does best when you limit the number of levels of objects returned in a single database call. In EF4, lazy loading is the default, but you can override this by using the ObjectContext.ContextOptions.LazyLoadingEnabled property.
5. It’s easier to use stored procedures in EF4 than EF1 (i.e. the added designer support).
6. There’s more ways to write functions in the EDMX file in EF4. Functions can be written in the CSDL, MSL, or SSDL.

However, EF4 devoted a significant number of cycles to pleasing the traditional O/R mapping folks, providing persistance ignorance, foreign keys in the model, support for test-driven development, and alternate development styles like POCO, code first, etc. So some of the database afficianado features like:

1. SQL Server table-valued function support in the SqlClient provider that supports EF.
2. UDT (including types like SQL Server's spatial and hierarchyid types, that are built on the SQLCLR UDT architecture) support.
3. Exposing database defaults and constraints in the model.

didn't make it in EF4. Maybe next time.

As always, MHO.

In my blog entry from about a month and a half ago, I summarized my finding on using Visual Studio 2010 with SQLCLR, and the partial support of SQL Server 2008 new features. One feature (with the disclaimer that's it's not preferred/supported) that I thought I'd lost was "attach to process" style debugging with SQLCLR. And that I hadn't discovered the "secret sauce" that makes this work. Here's the secret sauce.

Robert Bruckner's blog entry (about debugging SSRS add-ins) mentions that "there are now two debug engines inside Visual Studio 2010, one for .NET 2.0/3.0/3.5, and the other for 4.0 only". To debug SQLCLR  (which uses .NET 2.0/3.5) with "attach to process" we need a config file like the one Robert posted for reporting services. Except that we're attaching to sqlservr.exe. So the file must be called sqlservr.exe.config and be in the same directory as sqlservr.exe.

Then "attach to process" works with SQLCLR code (as I said, I started using this for SQLCLR broker activation procedures). The only odd thing is that I had to "attach to process", run program that hits SQLCLR, it hangs. Detach from process, cancel program, try attach and run program that hits the SQLCLR again. Then it worked a treat. Thanks, Robert. Do read the warning that appears when you debug this way. On occasion, if things get really hosed in debugging (or I do something strange in VS), I've been able to knock down sqlservr.exe debugging like this. So I only use this on the private copy of SQL Server at my own desk.

Last week I was at TechEd in New Orleans, presenting, manning the SQL Server Security and Managability station, meeting with friends and business associates, and, of course, partying. The heat and humidity took a little getting used to, but otherwise it was a great time. Thanks to everyone who attended the sessions; the demos are posted on the SQLskills website as promised. To all of my old friends, as well as the new friends I met in New Orleans for the first time, I hope you enjoyed it as much as I did.

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.

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 a special kind of project for SQLCLR development in Visual Studio. It's been around since Visual Studio 2005, when SQLCLR was introduced in SQL Server 2005. When SQL Server 2008 was released, there were a bunch of new features introduced in the SQLCLR arena. Unfortunately, Visual Studio 2008 supported none of them. We were told that support would be forthcoming in the next version of Visual Studio for these features.

The problem with features not working with autodeploy or not being supported in "SQLCLR projects" is that programmers don't think the feature actually exists. I'd get blank stares (or arguments) when I talked about SQLCLR using nullable types or System.Core (LINQ to objects) being supported in SQL Server 2008. Because VS projects didn't support these things.

Visual Studio 2010 was released a few weeks ago, and I decided to revisit the SQLCLR code I'd written (all written as "ClassLibrary Projects" with DDL-based deployment) to see how they did with support of new features. Here's the list.

System.Core and System.Xml.Linq should appear in the "supported" library list (add reference) - works
Large User-Defined Aggregates and User-Defined Types (ie, MaxSize=-1 in SqlAggregate/SqlUserDefinedType attribute) - works
SqlGeometry/SqlGeography/SqlHierarchyId recognized as parameters/return values - works
SQLCLR support of .NET Nullable Types as parameters/return values - fails deployment
Multi-parameter user-defined aggregates - fails deployment
Ordered TVF - no SqlFunction property to generate correct DDL for these - can't be specified

That's not a great record. Guess I'll still be explaining that some SQLCLR features really can't be built in autodeploy projects.

My friend Adam Mechanic has a post about his experiences (in general) when doing development for SQLCLR in VS2010, which spurred me to test these things out. They are similar to mine, except that I don't think that post-deployment scripts make up for proper autodeploy support, and in most cases aren't enough to recover a failed autodeploy. Another irritation is the general error you get with some of the autodeploy features the don't work (like multi-parameter UDAggs) is sort of misleading... "The assembly failed verification". "Failed verification" is a specific error in SQLCLR, meaning that the assembly is using the wrong version of .NET or the PEHeader is hosed or you're using undefined .NET opcodes. Using this terminology threw me for a while, until I tried using CREATE ASSEMBLY DDL and noticed the assembly passed verification just fine.

I noticed one additional change. This is something that's never been officially supported (that I'm aware of) but worked in VS2005 and VS2008. It's the ability to do "Attach To Process" type debugging in SQLCLR (that is, attach to sqlservr.exe, run a script in SSMS and have the debugger hit the breakpoint). This doesn't seem to work in VS2010, and was my debugging method of last resort when dealing with complex scripts (that VS "Test Scripts" couldn't handle). Or debugging Service Broker activaton programs written in SQLCLR (when "F5 debugging" didn't work). Sigh...maybe I haven't yet discovered the secret sauce that enables this in VS2010.

One thing I haven't tried yet is targeting a SQL Server 2005 (where the new features aren't supported) with a VS2010 project. I'd hope, for example, that System.Core and System.Linq.Xml won't appear in the list of supported assemblies. But that's a test for another day.

This blog posting is meant to bring attention to the fact that I'm doing a preconference talk, "A Day of SQL Server Security" at TechEd 2010 in New Orleans in June. OK, the TechEd folks asked me to publicize it. I'm also doing two breakout sessions, one on "Entity Framework and LINQ2SQL vs. Stored Procedures", and the other on "Integrating Microsoft SQL Server Event Tracing with OS-Level Events and Database Client Events".

Although I've done individual topics of SQL Server Security before (e.g. Auditing, at TechEd Europe 2009) you might be saying to yourself, "this Beauchemin guy is known for database development, what's his background in security anyhow?". Well, I did write the "SQL Server Security Best Practices Whitepaper" for SQL Server 2005. But there's a better story.

In 1992, I made my one-and-only foray into the world of startup companies, when I joined (as employee #2) a company called Open Computing Security Group (OCSG). This company eventually grew and changed its name to CyberSafe, and it's still around today. At "the beginning" we concentrated on Kerberos software, releasing commercial versions of Kerberos for 5-6 Unix variants and Kerberos clients for Windows (3.1) and Mac. This included SDKs, like the GSSAPI, and clients like klogin/klogind. My first Kerberos port was targeted at the NeXT computer.

The new company was going strong and, in addition to the products, we did security audits and taught classes on Kerberos protocol and implementation. The very first class I ever taught was on Kerberos; students seemed to like it, although I immediately went back to being "that geek in the corner who wrote code, and spoke to no one". ;-) Other classes came along post-OCSG.

When Kerberos R5 was released, I was asked to brainstorm a list of products where Kerberos could be integrated. I came up with about 25 ideas (probably not new ideas, but they were new to me) including using Kerberos for database authentication/authorization and using a database as a repository for the KDC. Eventually, I split with the company as it grew.

I've always had a fondness for computer security ever since. And I've been implementing, studying, and teaching anything to do with SQL Server security. Hence, the preconference talk. Be you DBA, developer, architect, or anything in between, I think it will be worth your while.