...small-scale, internal-only projects etc, and that internet-facing, or enterprise scale apps would do a more traditional n-tier thing
I mostly agree. There will be cases where you have massive systems where developers have tried to do this that will push this, though.
I can't think of many net admins that would let their databases inside the dmz with their web-servers for example.
True, most of the time, it should be considered as a disposable resource if you going to put it in the DMZ.
is that the db machines can bewcome a big scalability barrier,and are much harder to scale out than web-servers,as they are expensive, and stateful.
Right. Microsoft has decided it seems to go with a scale-up solution. Oracle with 10g seems to have gone with a scale out solution. Both have benefits and issues.
Is it database servers directly exposed the internet through web-services running c# stored procs and talking xml directly, or is there a place for the middle tier still.
Absolutely. Although you can expose CLR-based objects it doesn't mean you should. The HTTP handler in Yukon isn't IIS so its too early IMHO to predict how well or poorly it will work for such applications. Then there are a whole host of memory managment, socket
and thread (fiber) managment issues. Beta 2 should give us the opportunity to kick the tires. I'll still say that I'm relucant to do it from a security point of view, the Slammer scars are still fresh.
How are yukon ( and java / oracle etc for that matter ) changing you way you design the applications you are responsible for?
At this point, I see the CLR integration as "SQL Helper." Its good for doing things that T-SQL isn't. Its also helpful for defining new types, that's a good thing too. Much of the XML support is a function of having the CLR in-process and since XML is a good
thing, that's a good thing. But I don't see us running out and making everything on SQL server. Partially for the issues you raise here, partially that its just too soon to know how that behaves in "the real world."