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.

I usually show folks the SQLCLR streaming table-valued functions and using SqlDataRecord with SqlPipe.SendResultsStart/SendResultsRow/SendResultsEnd on the same day. This sometimes brings up the question "which is better"? Since you can synthesize rowsets with both mechanisms and they both stream rows back to the client one buffer-worth at a time, its an interesting choice. However, with SqlPipe/SqlDataRecord, you can synthesize multiple rowsets in the same stored procedure. Just cruft up two different SqlDataRecord With multiple SendStart/SendRow/SendEnd blocks. Can't do that with any kind of table-valued function. You must "finish" one rowset (SendResultsEnd) before "starting" the next (SendResultsStart), though, you can't have interleaved output any more than you would with an ordinary cursorless rowset. Example follows:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void MultiRowset()
{
SqlMetaData[] m = new SqlMetaData[1] {new SqlMetaData("colname", SqlDbType.NVarChar, 5) };
SqlDataRecord rec = new SqlDataRecord(m);
rec.SetSqlString(0, "Hello");

SqlMetaData[] m2 = new SqlMetaData[2] { new SqlMetaData("another", SqlDbType.NVarChar, 5), new SqlMetaData("number", SqlDbType.Int) };
SqlDataRecord rec2 = new SqlDataRecord(m2);
rec2.SetSqlString(0, "Goodbye");
rec2.SetSqlInt32(1, 42);

SqlPipe p = SqlContext.Pipe;
p.SendResultsStart(rec);        // first rowset
for (int i=0;i<10;i++)
 p.SendResultsRow(rec);
p.SendResultsEnd();

p.SendResultsStart(rec2);       // next rowset
for (int i = 0; i < 10; i++)
    p.SendResultsRow(rec2);
p.SendResultsEnd();
}

People often ask about the set of base class library assemblies that can be safely used in SQLCLR. It usually takes the form "can I use assembly System.XYZ.dll in SQLCLR procedural code" or "why do I get "assembly System.XYZ.dll is not found" when I try and catalog my own assembly that calls this one? The ones is hear mentioned most frequently is System.DirectoryServices.dll (Active Directory support) or System.Management.dll (WMI support) or System.Remoting.dll et al. The only way you use these is to run CREATE ASSEMBLY on them yourself, which involves using PERMISSION_SET = UNSAFE. And cataloging all the dependencies. Not for the faint of heart.

I explain that there is a hardcoded list of assemblies that SQL Server will load from the file system and these assemblies have undergone an extensive code review to ensure they don't destablize SQL Server when they are running in process. Since some assemblies contain both crucial and "unsafe" (to SQL Server) classes (mscorlib.dll is an example that comes to mind) these libraries must also be decorated with instances of HostProtectionAttribute. This attribute gives the host (SQL Server 2005, in this case) the final say over whether to run a method (e.g. Thread.Start in mscorlib.dll). SQL Server is very picky about when it will run an HPA decorated method, its only if the calling assembly is CREATEd with PERMISSION_SET = UNSAFE. There's more about this in chapter 2 of A Developer's Guide to SQL Server 2005.

I refer to this list of assemblies as "the approved list". You can see most of the "approved list" assemblies by creating a Visual Studio 2005 Database/SQL Server project and choosing "Add Reference". This is the entire list of approved assemblies with one exception. Right before RTM "System.Configuration" was added to the list. I'm not entirely sure why, because although you can successfully use a .NET config file with SQL Server 2005 currently, it neither supported or encouraged (see my blog articles about this).

Why the long story? Because, in SQL Server 2005 SP1 another assembly made the list: System.Deployment.dll. I'm rather puzzled by this one too, because it has to do almost entirely with click-once deployment. Not sure why someone would want a sproc to do this, unless it was used to tie SQL Server to .NET project deployment in conjunction with Visual Studio Team System. But, its in there. Hmmm...

The libraries that I'd hoped would make the list eventually, perhaps in a SQL Server service pack when Windows Vista and WinFX are released, are the Windows Communication Foundation (System.ServiceModel.dll) and Windows Workflow Foundation (System.Workflow.Runtime.dll et al). I've heard a lot of folks ask the same question, but so far there's been silence on this. Since System.WebServices (but not WSE) is aleady "in there", there might be some uses going forward. As a look toward "intent", I ran ILDASM against the just-released May CTP version of these. Not a HostProtectionAttribute in sight. Oh well.

SQL Server 2005 doesn't support assembly versioning in SQLCLR. Recently in a talk I restated this as "SQL Server 2005 will not allow two versions of the same assembly to be cataloged into the same database using CREATE ASSEMBLY". Someone promptly pointed out that they could indeed run CREATE ASSEMBLY on two instances of the same code, if one was strong-named and the other was not strong-named. That is the case, but that is not support of assembly versioning. The .NET Framework Developer's Guide states "Versioning is only done on assemblies with strong names".

As an example, if I run CREATE ASSEMBLY on an assembly without a strong name, the query "SELECT name, clr_name FROM sys.assemblies" produces the following output:

notsigned_test versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil

Note the version in clr_name is "0.0.0.0" regardless of the version number that is stored in the [assembly:AssemblyVersion] attribute in the file AssemblyInfo.cs. Attempting to catalog another version of the unsigned assembly, code or AssemblyVersion attribute change or not, produces the error:

Msg 10326, Level 16, State 2, Line 1
Two versions of assembly 'versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil' cannot coexist in database 'Northwind'. Keep one version and drop the other.

If I have strong named assemblies, I can only have one strong named version that differs by version. Running create assembly (on a strong-named "version 1") gives the following output using "SELECT name, clr_name FROM sys.assemblies"

notsigned_test versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil
v1                  versioningtest, version=1.0.0.0, culture=neutral, publickeytoken=3e0cd30f6da0843f, processorarchitecture=msil   

So I DO have "two copies of the same" assembly in the database at a time. However attempting to run CREATE ASSEMBLY on a strongly typed version 2.0.0.0 yields the following error.

Msg 10326, Level 16, State 2, Line 1
Two versions of assembly 'versioningtest, version=2.0.0.0, culture=neutral, publickeytoken=3e0cd30f6da0843f, processorarchitecture=msil' cannot coexist in database 'Northwind'. Keep one version and drop the other.

So assembly versioning is not supported. Note that you can have assemblies with different 4-part names that differ only by culture. Assemblies that include a culture other than "neutral"; these are resource-only assemblies such as localized strings for different cultures. How does SQL Server 2005 decide which culture to use? SQL Server itself really doesn't decide, but that's another story for another day.