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.

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.