I'm at ITForum in Barcelona this week. I'll be speaking on three topics that are:

1. Completely or almost completely new in SQL Server 2005
2. Take some DBAs a bit out of their comfort zone

If you've been avoiding these features until now, its time to take the plunge and let these features help you, rather than avoid them and do without the beneifts they provide. The topics are:

1. XML for DBAs - Showplan, DDL triggers and lots of things other things YOU use are XML.
2. Key Management - this is useful even if you don't do data encryption. Linked servers and SQL Agent use encryption keys.
3. Service Broker troubleshooting - Event Notifications and Database Mail (among others) use Service Broker.

See you there.

Last week at TechEd I was showing off Query Notifications. When I showed using the preprovisioned queue (overloads on SqlDependency.Start and SqlDependency constructor), a delegate asked about using the same queue with more than one subscriber. He repeated the question when I showed the low-level SqlNotificationRequest.

I did some tests over the weekend and the answer is no. You need a separate queue for each subscriber, otherwise the SqlDependency listener gets "confused". If you use the dynamically created queues like most people do (SqlDependency.Start with only a connection string), you get a new queue and service for each subscriber. So you won't notice this. I thought I'd have better luck with the SqlNotificationRequest because I'm reading the queue myself, but without going to the system tables (and perhaps introducing race conditions) its not possible there either.

Queue are cheap to create, so there is really no reason not to create a separate pre-provisioned queue and service for each notification subscriber. But, at least for the time being, its a requirement.

I've been doing my talk on try-catch in T-SQL for a while and whining about not being able to "rethrow" (via RAISERROR) a system error. Even went as far as writing a RegEx decoder so that I can copy the error into a user error message text and parse the text back on the client. Two weeks ago in a class, and again this week at TechEd, someone suggested the simple, relatively low-tech solution; "Just add 50000". RAISERROR to rethrow and subtract 50000 at the client. Unless you have already defined user errors in the range that can't be rethrown + 50000 (up to 13000), this should work fine. Thanks for the suggestion.

Sorry, I know that slogan has been used already. Thanks to everyone who showed up for the SMO/Powershell chalk talk at TechEd Barcelona today. Here, as promised, are the demos. Thanks especially to Jeffrey Snover, the "dad" of Powershell for showing up and showing me some neat shortcuts... as I typed. Many of the scripts were written "on the fly" and really need better argument validation and error checking, but they seem to do the job. Some of these come from the SMO chapter in Dan Sullivan and my "A Developer's Guide to SQL Server 2005 " book. Enjoy!

I'm in Barcelona in TechEd and tomorrow I'm going to be doing a chalk talk on SQL Server Management Objects (SMO). Although I'd usually done my SMO coding in C#, I decided (based on my co-author Dan Sullivan's blog post on the subject) to try out using Powershell along with SMO. So, although when most folks think of SQL "scripts" being lines of code written in T-SQL, with SMO and Powershell the term "SQL scripts" takes on a somewhat different meaning. If you're at TechEd, stop by and we'll write some new-fangled "SQL scripts". Although some things that SMO does actually generate registry changes, the "real" T-SQL equivalents for most things SMO are still available using the SMO scripter.

When I discuss separation of users and schemas in SQL Server 2005, I usually think of ownership chains (that follow the object owner which is always the schema owner unless you change it) or object resolution (which now goes: special name, then default_schema/procedure schema, then DBO). Another item that now follows schemas rather than users is query plan reuse with 1-part object names. Create two users:

-- logins already created
CREATE USER bob FOR LOGIN bob
CREATE USER mary for LOGIN mary
GRANT SELECT ON authors TO bob, mary

The query plan for the following query (executed by bob or mary)

USE pubs
GO
SELECT * FROM authors -- note the 1-part object name

would not normally be reused for both bob and mary. In SQL Server 2005, it can be reused if bob and mary have the same default schema. BTW, this is adhoc SQL, rules differ in procedural objects like stored procs. To see the reason for the query reuse, you can obtain the plan handle (reference this blog entry), but leave out querying the plan itself because you don't need it here) and pass it in to the sys.dm_exec_plan_attributes dynamic management function. This function shows the attributes of the plan and includes a second column, is_cache_key. If the cache_keys match, the plan is reused.

One of the rows produced by sys.dm_exec_plan_attributes is named user_id. Interestingly, for the query plan for bob or mary against SELECT * FROM authors, the user_id is 1. DBO. Hmmm... User_id is a misleading name for this column. It's not the user_id of the user who executed the query (and caused the plan to be created) but that user's DEFAULT_SCHEMA's owner's user_id. Looking at the CREATE USER DDL statements above, because I didn't specify a DEFAULT_SCHEMA, both users' DEFAULT_SCHEMA is DBO. So, for adhoc SQL, all other things being equal, the plan will be resued for two users as long as they have the same DEFAULT_SCHEMA. The rules for query plan reuse follow the rules for object resolution, which makes sense, once you think about it.

Of course, now that you know this, ALWAYS use 2-part names. User_id with 2-part names' plans has a special value of -2 which ensures the plan is resued regardless of DEFAULT_SCHEMA of the user who executes it.

Quite a while ago, someone (I forget who) was soliciting opinions on whether or not LINQ and ADO.NET vNext EDM should be able to run "in process", in other words should be useable in SQLCLR procedures. Here's one perspective, and a question.

LINQ and Entity SQL are QUERY languages. Although they might be able to encapsulate resultset output from stored procedures, much of their value is in the query language itself. NOT having them be useable in SQLCLR would encourage folks that want to use these languages to move AWAY from stored procedures and put their data access query code in client or middle-tier programs, rather than in the database. I don't think DBAs and database developers really want to encourage that.

On the other hand, some of the value of stored procedures is being able to restrict table permissions and find errors at compile time. SQLCLR procedures don't have either of those attributes built-in, although you can restrict table permissions if you use EXECUTE AS OWNER. And coding with LINQ/Entity SQL can allow compile-time type metadata checking. Other benefits of stored procedures, such as allowing the code to be shared among database apps, and consolidation of SQL code in database objects, do apply as well to SQLCLR.

And its a given that, with either of these languages (and with SQLCLR in procedures in general) you'll be giving away procedure execution speed for (*arguably* better, meaning you'all can argue about it) code maintainability. Unless you only use LINQ/Entity SQL to process T-SQL stored procedure resultsets.

What do YOU think?

Haven't written in a while. I've been back from Hong Kong for a few weeks (had a fantastic time, thanks folks), and, a few days later, contracted the (American) flu that's been going around here. Just getting over it. Also, I've been busy working on some whitepapers that will come out (relatively) soon. I'll announce it here when they are available.

I'm headed off to Barcelona in a few weeks to TechEd and ITForum. I'll be speaking about SQL Server 2005, on some topics that folks ask about in class often (now that they've heard of the features). But there usually isn't time to go over these in as much detail as I'd like. These talks are an effort to make the time. At TechEd, I'll be speaking on:

Plan Guides and Plan Forcing
Query Notifications / Cache Sync
Error handling on server and client
Implementing a Service Oriented Database Architecture application
MARS, transactions, and SQLCLR (for a preview of who uses MARS, read back a few blog entires)

and a chalk talk on maintaining your system with SMO. Ought to be fun.

For the IT Professional folks at ITForum, they'll be:

XML for the DBA (because the list of SQL Server DBA items in XML format is about a mile long now)
Troubleshooting Service Broker (even DBMail uses broker)
Security Account and Key Management

Hope to see you there. Stop by and say hi.

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.