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.