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.

There's an interesting article that caught my attention in this month's (July 2006) MSDN magazine by John Mollman about building the MSDN aggregation system. The system uses SQL Server 2005 Service Broker for reliable messaging, activation procedural written in T-SQL, SQLCLR for interactions with the world outside the database and the XML data type and schema support built in to SQL Server.

I found the use of SQL Server Service Broker because of its scalability potential, the data dependent routing implementation, and the plug-in object and interface based provider model most interesting. Check it out.

In helping out with a project involving ASP.NET 2.0's cache sync feature, which has best performance when built on SQL Server 2005's Query Notifications, I was asked if there was a way to "pre-provision" the queue, service, and stored procedure that cache sync needs. The concern was that the connection string to start up the listener (in Application.OnStart()) used the same security principal as the remainder of the application. That is, the app data access and listener start connection strings were both

"server=myserver;integrated security=sspi;database=mydb".

This meant that the entire application had create queue, create service, and create procedure privilege. That's too much privilege. Using a separate SQL login for SqlDependency.Start() was not an option.

ADO.NET's SqlDependency has an override for the static Start method that takes the name of a pre-provisioned queue. This can be combined with use of the constructor of SqlDependency that uses an Options parameter. In my test, the queue name had to be a one-part SQL identifier; a two-part name (schema.object) didn't seem to work. So the queue, service, and procedure must live in the SQL principal's default schema. The options parameter is a string that names the Service and Database (Broker Instance) that the depdendency should use. The options string would look like this:

"service=myservice;local database=mydb" or
"service=myservice;broker instance={GUID}" //where GUID is the Service Broker GUID.

As far as a stored procedure to do the same type of processing that SqlDepedency's listener does, you can base your procedure on the one SqlDependency dynamically generates, changing the name of the queue and service, of course.

Unfortunately, ASP.NET's SqlCacheDependency doesn't allow the options string to be specified. And the override of SqlDepedency.Start() doesn't work without the corresponding options on SqlDependency. So the only way to use a preconfigured service, queue, and is to build you own cache, using the ASP.NET Cache class as a "template". It's not as simple as being able to use the OutputCache directive on the Page class, made things OK as far as the security folks concerns.

Perhaps ASP.NET will support this in future, or there's a workaround that I hadn't thought of (and I did go as far as to read the code for SqlCacheDependency). I'll have more to say about query notifications at the upcoming SQLskills scale-out events. It's quite an interesting area. If you have lots of read-mostly lookup tables, its well worth the time.

SQL Server 2005 implements quite a few technology pieces to help database developers and administrators acheive scale-out while acheiving the appearance of (or actually accomplishing) application or server federation. The information is scattered throughout the books online, but you can read a nice whitepaper by Roger Wolter on how to choose among the features here. I'll be doing some events with SQLskills about scale-out applications in Chicago and NYC, and partners in Reading UK, and I didn't want to miss anything. One technology piece that was a big hit in SQL Server 2000 stayed mostly the same in SQL Server 2005, but I wanted to qualify the word "mostly". That feature is distributed partitioned views and distributed queries.

Distributed queries and distributed partitioned views use OLE DB as a communication mechanism. OLE DB access is built-into the query engine, data can be retrieved by the same basic mechanism from the SQL Server storage engine or from a "remote storage engine". When you're talking to another SQL Server, the OLE DB provider used in SQL Server 2000 is SQLOLEDB. In SQL Server 2005 the OLE DB provider changed to SQLNCLI (or SQL Native Client). There are some subtle differences (remote errors, ability to run out of process) between providers, and, if you upgraded to SQL Server 2005, all your Linked Servers now use SQLNCLI.

SQL Server 2005 does, however provide nice tracing information in SQLProfiler regarding which OLE DB calls the engine makes to the linked server. Because OLE DB is interface based, you can even see the query interface calls, if you want to go to that level of abstraction. You can trace these on either or both sides of a SQL Server linked server; outgoing trace works regardless of the destination provider. You do have to grok the OLE DB interfaces to understand the events, but it's like the query plan, there are various levels of understanding.

Distributed partitioned views remote part of the query tree to the remote database. In SQL Server 2000 the portions of the query would never execute in parallel. In SQL Server 2005, the startup commands can execute in parallel and accomodations are made for caching the remote connections and using overlapped I/O to optimize things. And SQL Server's OLE DB providers have always been able to remote "statistics rowsets" to the other side to help the query processor out.

For distributed query, there's a version of the SQL EXECUTE statement that implements passthrough queries using an AT keyword. You could execute passthrough queries in previous versions by using OPENQUERY, but this required the results to be treated as a rowset (SELECT * FROM OPENQUERY(...)). I've gotten EXECUTE AT to support remote queries that use the "MAX" data types and CLR data types (the assembly must be catalogued to both remote and local database), but the XML data type isn't yet supported for remote queries even though the BOL says so. So cast it to a MAX type and cast back when you get it back on the local server.

That's enhancing one piece of the puzzle, real federated servers. Although this won a TPC benchmark (in the SQL Server 2000 release timeframe, with 32 federated servers) there's LOTS more. As always, the devil is in the details, that's where I usually come in.

It was a fine time at TechEd. I enjoyed catching up with a lot of old friends and making some new friends. Thanks to all of you who dropped by my chalktalks or attended my error handling breakout session. It was fun hanging around the Data/SQL Server area and talking with all the folks who came by. Thanks also to:

- Data Direct and Jonathan Bruce, who were kind enough to invite me to their ADO.NET panel.
- New England user group, who were kind enough to invite me to sit on their panel.
- All the folks who attended the DLINQ BOF, including Dinesh and Luca. Much appreciated.
- John and Eric of A/W who were nice enough to provide me with some giveaway books, as well as interviews and book signings so that we could sell more books.
- The organizers, who overcame unfinished hotels, bus strikes, and more to make this a good time.

I heard some good things about Service Broker Extensions (TwoConnect and their sister site AdapterWorx), ADO.NET vNext (ADO.NET team) and the Visual Studio Team System for Database Pros (thanks for the disk, Gert). And heard how Oracle 10gR2 supports query notifications (they call them change notifications).

Only negatives were the flight to and fro, where I acquired a nice sinus infection, helped out by the amazing changes in cabin pressure during landing (yuk). Hence the lateness of this post.

See you'all next year, or at the Immersion events soon.

It's raining here today, starting to feel like home.

Many thanks to the folks that told me they had an excellent time delving into error handling in SQL Server 2005 server and clients during yesterday's talk. The demos are already posted on the SQLskills website; look under “Current Events” for the link.

Today's talk (to a smaller audience, only the hard core perf folks were there) was on plan guides and plan forcing in SQL Server 2005. The demos for this one will be up shortly, same general place.

My talk on error handling in SQL Server and clients starts in another hour. Error handling in SQL has been more of an art than a science in previous versions, and some folks just pass every error back to the client directly. With TRY-CATCH in Transact-SQL you don't have to do "passthrough" error handling now, but its not as "remove @@ERROR, plug in TRY-CATCH" as people first thought. Some folks wondered how I could do over an hour on errors alone; there's actually even more to it than that. I'm sure glad they have unit testing in Team System for DB Pros; it will sure get people past the "oh, that error will never happen" mentality.

The DLINQ BOF last night was amazingly well attended. I'm surprised that there were very few "this should all be done in stored procedures" afficianados in attendence. It would make things more interesting if DLINQ worked in SQLCLR. Hmmm... Then this would be IN stored procedures.

Started my part of the festivities today with a chalktalk mentioning SQL Server DDL triggers and Event Notifications. But explaining Event Notifications meant explaining Service Broker services, queues, and contracts. And the fact that both Event Notifications and DDL Triggers use a XML format. That you can change to a rowset format using the XML.nodes method. Which meant talking about XML.nodes. Showed a DDL trigger for CREATE ASSEMBLY that had to be written in SQLCLR.

Went on from there to setup a cross-database event notification, which (the way that I did it) entailed using the TRUSTWORTHY database property. And what TRUSTWORTHY means (I'll do a blog entry on that in future). And because cross-database service broker uses session keys (BEGIN DIALOG CONVERSATION uses encryption by default), each database must have a database master key. Explained what database master keys are.

Finally, I cross-instance event notification. Needed to explain what ENDPOINTs were and the ENDPOINT abstraction. And explain service broker ROUTEs. A good portion of the new "infrastructure type features" (I don't think they're only for developers any more) in one talk. That's some (fairly useful, I think) scope creep.

On to the WMI provider for events, and how this works with SQLAgent. Watching it use WAITFOR (a queue) and RECEIVE. Uh, oh...

If you haven't already seen the good news on our website, SQLskills welcomes Elizebeth Vitt. Elizebeth is well-known in the business intelligence community and will be our Director of Business Intelligence Skills. We're looking forward to even more synergy among members of the organization; Business Intelligence may be the most earthshattering part of SQL Server 2005. Welcome Elizabeth.

We're also "pre-announcing" a series of Immersion events, featuring in-depth architectural guidence. The events will be smaller to focus on personal attention. Check out the website for more details.

The week before TechEd, everyone's reminded to blog about what they'll be up to, to draw folks to sessions. The sheer number of presentations and topics this year, including breakouts, chalktalks, BOFs is overwhelming. The show is sold out, but some of the talks are being "simulcast" as well. So I'll be there talking about SQL Server and ADO.NET (surprise,surprise) but I thought I'd begin with the "extracurricular activities". And no, I don't mean the late-night parties in the evenings.

First off, SQLskills might have some exciting announcements coming that we've saved for the occasion. You'll have to find out about this one as it happens...hope you can stand the suspense.

Monday night, I'm participating in a mega-meeting of the New England User Groups on the TechEd premises.

Tuesday afternoon/evening, I'm participating in DataDirect Technology's ADO.NET get-togther hosted by Jonathan Bruce, with some of the ADO.NET team members. Followed by a DLINQ BOF with Sahil Malik. Oh, and I'm at the Addison-Wesley booth signing books at lunchtime. I may even have some books to give away at the events above...

Thursday, I'll be up bright and early (well, early, especially with the timezone difference) to answer questions of the broadcast during Greg Low's (Mr SQLDownUnder) simulcast session on SQLCLR for DBAs. Wonder what time it is in Brisbane then.

The chalktalks break down as follows. I'll try and leave lots of time at the end for your questions:
Monday 10:45-12 - DDL triggers, Event Notifications, and WMI in SQL Server 2005
Tuesday 8:30-9:45 - Security Controlling the new SQL Server 2005 features for DBAs: Service Broker, XML, and SQLCLR
Thursday 9:45-11 - Plan Guides in SQL Server 2005

Whew, some of the rest of the time, I should be hanging out in the "Data" breakout area. Oh, and my breakout session is Wednesday June 14 - Everything you always wanted to know about error handling in SQL Server 2005, server and client but were afraid to ask. The official blurb is:

DAT424  SQL Server Error Handling: T-SQL, SQLCLR and Clients
Day/Time: Wednesday, June 14 2:00 PM - 3:15 PM  Room: 157 ABC
Speaker(s): Bob Beauchemin
In SQL Server 2005, both T-SQL and SQLCLR procedures can use Try-Catch constructs to intercept and process exceptional conditions. This is a big change for both server and client/middle-tier processing, as client and middle-tier received and processed all errors directly from SQL Server. In addition, both T-SQL and SQLCLR error handling don't catch every type of error or allow the ability to directly rethrow all errors to reproduce SQL Server 2005 behavior. This session looks at the mechanism in-depth and provides a comprehensive and consistant error handling strategy that works for all procedural code and clients.

What a slacker, eh? So be sure to stop by and say hi. I'll also try to blog some during the "activities" going on, especially the buzz surrounding Visual Studio Team System for Database Professionals (aka DataDude).

Did I remember everything...? (hope so)

I've been looking at the DLINQ syntax a bit more (I'm participating in a BOF at TechEd next week), and its always struck me how, at its most basic, its reminiscent of XQuery FWOR. Yes I did leave out the L (Let) on purpose. I guess its the relative placement of the SELECT part of the query:

FWOR:

for $x in /customers/customer/address
where $x/city = "Portland"
order by $x/zipcode
return $x

DLINQ:

from a in db.Address
where a.City == "Portland"
orderby a.Zipcode
select a

I've done the FLWOR/SQL comparison quite a bit. Just replace "for" by "from" and "return" by "select". The ordering of the keywords in DLINQ just makes it a bit more obvious. Or maybe its the "in" keyword in the from/for clause. The superficial similarity kind of ends there, however.