I did a talk yesterday at TechEd Hong Kong on ADO.NET vNext. Most of them just wanted to know what ADO.NET vNext was about. Short and sweet. Here goes...

There are two main new features:
1. Entity data model (EDM) - a mapping of objects over relational database to bring data access to a higher level of abstraction. No longer do you need to code to the DBMS schema, only to the entity schema. Think of it as a new data source.
2. Entity SQL - a new query language, SQL plus extensions to query against the entity data model (EDM). A bit richer than SQL, but SQL-like.

And some integration pieces:
1. An ADO.NET provider over the EDM (the MapProvider). Uses ESQL queries.
2. An ObjectServices layer over EDM. Reminiscent of ObjectSpaces (at least to me). Uses Query<T> and an ObjectContext.
3. An integration piece that allows LINQ over the EDM. "LINQ to Entities".

LINQ for Entities joins:
  LINQ to Objects
  LINQ to SQL (was: DLINQ)
  LINQ to XML (was: XLINQ)
  LINQ to DataSets
  PLINQ (parallel LINQ)

That's all folks.

When installing previous versions of SQL Server, I'd always keep a list of the exact privileges that a SQL Server service account would need. I'd make up a new user and give them exactly these rights. The SQL Server installation program would grant the appropriate permissions during the install, and I'd be set. Principle of least privilege, run SQL Server and associated as the minimum privilege account needed. If I thought about changing the service account, I'd call up knowledge base article Q283811 and sigh at the list of work items. And make sure that I used Enterprise Manager, as the article recommends.

Things are a lot simpler in SQL Server 2005. When you install SQL Server, the process creates Windows groups with exactly the minimum privlege that you need for the appropriate services. The user you specify during the install (which should be a "normal", low privilege user and NOT administrator or LocalSystem, is plunked into the appropriate group. There is a Windows Group for each SQL Server-related service, all nicely decked out with least privileges (file system DACLs, security policies, registry key access, etc). NO more privilege lists to keep around, although they are doc'd in BOL should you need to refer to them.

If you should need to change the service account, things are even better. Just put your new Service Account user in the appropriate Windows groups, and voila, no long list of tasks. Very cool. Or use Configuration Manager.

Twas' not all sweetness and light, however. an FAQ during the early days was "why does SQL Server setup "clutter" my directory with groups?" That's why. First time you need to change service accounts, you'll appreciate the "clutter".

There was a newsgroup discussion recently about the intricacies of Multiple Active Resultsets in SQL Server 2005, and one of the questions that came up was "Who actually needs/uses MARS anyway"? I've been taking a much closer look at ADO.NET vNext lately, one reason being that I'm doing a talk on it at TechEd Hong Kong the week after next. Guess what? Both the supplied ObjectServices and LINQ for Entities samples have "multipleactiveresultsets=true" in their connection strings. Why? Without taking MARS out and seeing where the samples fail (I'll get around to it...), I'd bet they use both the "multiple resultsets on the same connection" and "issue an UPDATE statement while reading a rowset on the same connection" patterns. That's what MARS is supposed to address (the very precise ODBC error message for this is "Statement in Use").

BTW, MARS has some interesting effects on transactions and session settings, too. I'll be doing a talk on this at TechEd Europe in Barcelona in Novemeber. Don't forget to stop by and say hi if you're at either of these excellent events.

Thanks to those who came to my webcast today on SQL Server 2005. Between this webcast and the other upgrading webcasts that are available on the Technet event website and the MSDN event website, you should have a pretty good idea about why you need to upgrade to SQL Server 2005. If you haven't done that already.

As promised, here are the demos and scripts from the session, on the SQLskills website. I'll post a Q&A blog entry and a pointer to the webcast as soon as I have the information.

I've noticed that, as more companies want to run SQL Server applications 24x7x365 interest of clustering has increased dramatically. One of the most asked-for papers about SQL Server 2005 is an update to the clustering whitepaper. Well, its available NOW; an over-100 page treatise by Allan Hirt and Dave Whitney. It covers not only clustering SQL Server 2005 but also Analysis Services 2005. A MUST read. Look for "Failover Clustering for Microsoft SQL Server 2005 and SQL Server 2005 Analysis Services" at Microsoft download site.

I've been honing my slides and polishing up my demos for TechEd 2006 Hong Kong at the end of September. I'll be doing some breakouts there on SQL Server 2005 and ADO.NET vNext, but there will also be a "Day of SQL Server 2005 for Developers", as a preconference presentation. There's so much that's new in SQL Server 2005 that it's impossible to fit it all in a single day, but I think that even if you were at last year's TechEd, you'll be pleasently surprised at some at the newer in-depth material from a slightly different perspective. It will be a FULL day.

By the looks of the speaker list, I'll be in good company in the SQL Server space, with experts such as Paul Randal (check out his latest amazing entries on the SQL Server Storage Engine blog) and Thierry D'Hers. And my old cohort Jon Flanders will be there too, captivating attendees with Windows Workflow Foundation and Biztalk, no doubt.

It will be good to see old friends again, too. Stop by and say hi. See you in Hong Kong!

You've read the specs. Now get the bits. Here. Note that "vNext" isn't V3.0, which doesn't include these new ADO.NET items. vNext is the release after V3.0. Maybe V4?

When I first heard about it, I didn't think the DEFAULT contract in Service Broker had the potential to cause confusion. Goes to show what I know. The problem comes up when you assume "no specification" means "use DEFAULT". Not always...

Service Broker contracts indicate which message types can be sent from the conversation initiator, which message types from the conversation target, and which message types from both/either. Service Broker contains a DEFAULT message type (Validation=None) which is a real message type. The DEFAULT contract indicates that the DEFAULT message type (but no other message types) can be sent by ALL.

Here's the possibly confusing bit. On the target side (ie the receiver of the initial message in a conversation), the contract is specified on CREATE SERVICE.

CREATE SERVICE mytarget ON QUEUE myqueue
 (contract1, -- list of supported contracts
  contract2,
  contract3,
  [DEFAULT]  -- we need this, if we want to use DEFAULT
 )

CREATE SERVICE mytarget2 ON QUEUE myqueue2 -- no contract at all supported 
                                                                   -- not even DEFAULT

Leaving out the [DEFAULT] contract here means the target can't use it. You need to specify it.

For the initiator side (ie the sender of the initial message in a conversation) the contract is specified on BEGIN DIALOG.

CREATE SERVICE myinitiator ON QUEUE myqueue_init -- no contract needed here

BEGIN DIALOG @handle
 FROM SERVICE myinitiator
 TO SERVICE 'mytarget'
-- ON CONTRACT [DEFAULT] -- not needed

However, in BEGIN DIALOG you CAN leave out the contract, and this does mean we're using the DEFAULT contract.

Also, sending a message using a message type, leaving out the message type means you're using the [DEFAULT] message.

SEND ON CONVERSATION @handle
-- MESSAGE TYPE [DEFAULT] -- not needed
  ('Hello World')

Got it? DEFAULT message type and contract are the default, except on the target CREATE SERVICE, where its not a default.

I got email today notifying me that Dan Sullivan and my new book on SQL Server, "A Programmer's Guide to SQL Server 2005" is on sale on Bookpol.com at a deep discount for a limited time only. Check out http://www.bookpool.com/ct/214. I just did and, geez, its better than my *author* discount when I buy 'em direct from A/W. How DO they do it?

I came across a new article, "Data Access Tracing In SQL Server 2005" at MSDN and I'm listed as one of the authors. That's interesting, as I didn't remember writing it but upon closer inspection, they give me credit because they leveraged my original article "Tracing Data Access" from the ADO.NET 2.0 series. Thanks Jennifer and Acey, now I remember you asking if that was OK. I did notice they still used an old bio for me though, just ignore that part.

Anyhow, it's an excellent paper. They added one-third more pages and even included MOF files for tracing JDBC/XA, BCP, SQL Browser, and more. Very cool. Be sure to give it a read and practice your tracing BEFORE you need to use it. 

Sometimes I'll have these interesting juxtapositions of two or more projects I'm working on at the same time. So what does Joe Celko have to do with the Visual Studio "Data Dude" (VSTSDD for short, I don't know what the official acronym is yet) product? Well...

The first time I generated test data with VSTSDD, I was kind of surprised because it was SO random. Odd conglomerations of characters covering every bit of a data type's value space. I was used to more "regular" test data, like...

I'd once worked on a VISA card system in the 80s that had about 100 or so completely made up test customers with real-sounding names. We'd run system tests (15 cycles if I remember correctly) that generated a few boxes of green-bar reports. BUT. The made up customers *had histories* so real that our users (who actually read the test reports) knew them better than our real customers. They could tell, for example that if "Fred Anderson" didn't go over his credit-limit on cycle 12, there was a problem with our latest change on the test system. REALLY.

I couldn't see how any users would remember test names that consisted of 25 "random edge-of-the-value-range characters". I know you can write your own test data generator, but, I was a bit disappointed. At first.

So I'm reading Joe's book "SQL Programming Style". Rules 3.8.1 and 3.8.2: "Consider range constraints for numeric values." And. "Consider LIKE and SIMILAR TO constraints for character values." Hmmm...

So, although the test "names" aren't particularly memorable, they exercise the ENTIRE allowed value space, preventing surprises in the case where someone has a name containing edge cases of characters, or numeric values at the end of the range. Forcing the needed constraints or procedure corrections. Great!

Because... (back to the book) "Look at the actual DDL and see how often you find this constraint. Programmers are lazy and do not bother with this level of details".

Not any more, I guess.

I'd like to see how our users would have pronounced those character combinations and numeric values. Or they'd never encounter them because of proper constraints. Very cool.

Just got back from the SQLskills Immersion event in Chicago. There were a couple of bonus sessions, with Kimberly doing a high-availability talk for Microsoft customers and our students one evening, and Joe Celko making an appearance on Wedsnesday. Joe spoke on elements of SQL style, and I was able to acquire a signed copy of his new (but not his latest) book "Joe Celko's SQL Programming Style". He's also just released a book on analytics since the style book.

With all the weary smiles at dinner on Thursday, I got the idea that everyone in attendence had a good time and was full to overflowing with the topics we presented. If this sounds interesting to you, there are some spaces in our October event in NYC.

An interesting question that came up at dinner was "how do you motivate use of the Service Broker in SQL Server and how mainstream are the use cases"? Here's how.

If you've ever purchased anything on the web, you'll notice that no matter how popular the website, once you navigate through the catalog, fill out the forms with your personal, shipping, and credit info, the actual order screen is quite quick. You get back an acknowledgement very quickly, but it usually only consists of an "echo" of a subset of the data you entered and an order ID. Also, there is likely a hyperlink where you can check the status of your order. Perhaps an email is sent.

Bet they didn't do a credit check, set up billing, shipping, check inventory, and consult the MRP system for a manufacturing schedule while you were waiting, did they? Most or all of this is done asynchronously, probably with queued messages of some sort. Otherwise, it would be quite a transaction, and if all subsystems weren't on the same instance, a (slower) distributed transaction at that.

Generating an order number, saving the order details (maybe in XML format, for later decomposition into the relevent relational tables), and updating/checking the customer table is much faster. And, if the queueing system is inside the database, your queued messages and database updates will be a fast *local* transaction. If you need to save state, you're already in the "state machine/DBMS", rather than one tier or more away. BTW, if you're using SQL Server's Database Mail feature, the email is also sent asynchronously using Service Broker. Gotta save on that synchronous distributed processing. Else you'll get impatient and push "Buy" again. Or perhaps not return next purchase. 

That's the motivation.

We had some very talented technical reviewers for our "Programmer's Guide to SQL Server 2005" book. Some of them, like Greg Low and Roger Wolter, are well known in the blogging community.

I was happy to see that John Huschka and Jens Süßmeyer are both blogging. Jens has been blogging for a few months and has been posting screencasts too; John just started this week. These guys should have some interesting insights into SQL Server 2005 and related subjects. Subscribed!

After having a go at the information on the Entity Framework and other ADO.NET 3.0 papers that were posted about a month ago, I was trying to come up with a good example of relational tables or views that are designed with entities in mind. Or perhaps from entities. Concepts like inheritence, for example. Unlike my good friend Ted Neward, I'm not about to make a blanket statement like "entities good" or "entities bad" (I believe Ted has a Political Science degree of some sort in his background), just looking for examples of an entity based design "in nature".

Then, someone sent me, out of the blue, a request for a diagram of the new system metadata views in SQL Server 2005. There's a PDF of a E/R diagram for them out on the web, and the more I looked at it, the more "interesting" the design looked. I happen to be doing some work with the system views for an upcoming paper, and came across the word "inherited" in SQL Server Books online description of them. Here's some excerpts:

-For a list of columns that this view inherits, see sys.objects (Transact-SQL).

-<inherited columns> - inherits from sys.xml_schema_components

-Some catalog views inherit rows from other catalog views. For example, the sys.tables catalog view inherits from the sys.objects catalog view. The sys.objects catalog view is referred to as the base view, and the sys.tables view is called the derived view. [base view and derived view? really?]  

-This can be summarized as follows:

-The base view contains a subset of columns and a superset of rows. The derived view contains a superset of columns and a subset of rows.

It's almost as though some of these follow one of the traditional patterns for mapping object/entity models to relational. But I'll leave this to you to decide. Entities are never a subject without controversy.

I wonder how I'd query these in EntitySQL. Or DLINQ.

Even now, although SQL Server 2005 has been for about 3/4 of a year, when I teach people about the new developer features, I'm still asked about the zen of the new features. Do they follow a common theme? Is there a usage pattern that emcompasses all of the things like Service Broker, SQLCLR, XML data type and query, Query Notifications, Event Notifications, HTTP Endpoints?

Here's an academic paper by Dave Campbell that explains at least one usage: towards a Service-Oriented, Active database. This paper, released in 2005, ties together most of it.

I've been just pointing out the paper when folks ask me the "why" question for some time now. Making a database Service and asynchrony-capable is one of the ways to build a scale-out solution. I'll never have to ask "what happened to my application state when [mid-tier host of choice here] was temporarily offline" again.

If you hadn't noticed, I've stopped taking comments and trackbacks for a while. I figured if you'all like video poker, you know where to find it. I'll try and put them back soon, because you and I lose the valuable feedback along with the valueless drivel. For example...

I'd mentioned in yesterday's blog entry about scale out being a "last resort". Today, someone from a consultancy/solution design group at Microsoft said "but we don't recommend scale-out only as a last resort any more." The new database technology features along with advances in hardware make it accessible to all.

All? So there you have it. I'm used to thinking in terms of buying myself a 1gig or more personal music player to replace the 64meg one I bought last year. For the same price or less. But the commoditization of once high-end only software solution designs? Or approachable (to all) incremental design evolution towards the high-end without a major rewrite? Hmmm...

Good. I was having trouble figuring out how to max out the database system from the design meeting room, anyway.

Reading some papers on scale-out, I came across this in the SQL Server 2000 resource kit: "Scaling up is the first approach you should consider, ... scaling out should be the final consideration." Other papers mention "in order to best implement a scale-up architecture, it has to be planned in advance." and "it is easier to add scale-out and partitioning to an application designed with partitioning in mind". Hmmm... 

These statements seemed to be in opposition (at least to me). And then I ran them past Mary (who is back to being part-time DBA, but working from home) and she came up with:

"I've yet to see an architect who can max out a database box from the confines of the meeting room during a design meeting."

Great. The picture of this meeting in my head just cracked me up. "It's like, that insurance you never know you need until you really need it."

BTW, SQL Server 2005 has some scale-out features, SQL Server 2000 didn't have. And allows scaling-out in stages, one service at a time, if you want.

A few weeks ago, I told you about Elizabeth Vitt joining us at SQLskills. She's also be bitten by the blogging bug and is blogging now. Liz is a Business Intelligence specialist and author. Her lately paper "Integration Services: Performance Tuning Techniques" is out on Microsoft Technet, this week. Check out her latest blog entry on Influencing Aggregation Candidates. And, oh yes, she'll be doing (what else) the Business Intellegence track of our Immersion series, beginning with a day of “Exploring the Power of Business Intelligence” in Chicago.

At TechEd, a couple of folks came up to me and asked "we see around at conferences and read your blog, but when are you going to offer any...ahem... *public* classes, already?" I see you were a "special guest" at a training event back in March, but we couldn't afford to go to Amsterdam. Well...SQLskills will be giving a series of public training events starting in July. I'm participating in the Chicago and New York city events, and a 1-day'er in Reading. And we're scheduling 2007. Check it out on the website. We enabled signup on the website just recently. And there are specials.

My topic/track for the event is "Building Scale-Out Applications with SQL Server 2005". Reason for the sudden focus on ends (apps) rather than means (features) is the revelation that all the "nice features" of SQL Server, that is T-SQL enhancements, SQLCLR, and XML are really "enabling technologies" for scale out applications that can handle all types of data.

If you liked these as nice features in a vacuum, they're even better when implemented in concert. The glue that ties all these together is the Service Broker feature. In conjunction with replication and distributed query/transactions (used sparingly), scale-out requires (cries out for) asynchrony and data-directed routing (aka data-dependent routing) and this is where broker comes in.

But isn't broker only between SQL Servers? That's where using SQL Server's HTTP endpoint as a gateway comes in. But, we won't forget the XML data type and queries, SQLCLR, T-SQL, or related technologies. After all, your service has to do something besides add 2 numbers together. And it must be fast, in messages/sec.

The other thing that's exciting about the SQLskills immersion idea (see Kim's blog post) is that we invite folks to "bring their own problem". That's cool, as a consultant, I'm always the one who "makes things work". That's (making things work) is part of what I've been doing lately. Mostly, I took I year-long "hiatus" from public classes; my last official class for [previous company] was July 2005. For the two years before July 2005, I was mostly "Mr Yukon Ascend", not a lot of public there either. Since August 2005, I've worked on SQL Server 2005 launch, finished the book, and did a few private offerings. Not exactly retirement, but...

Hiatus over.