SQL Server 2005, now available as a "community technology preview," is an odd beast. For those who believe their databases should be little more than simple data access routines with business logic placed at the "middle tier," the move towards application server technology integrated as part of a database is a Bad Thing. The trend is clear.
Oracle integrated the Java VM a while back. With .NET now fully integrated into SQL Server, the trend towards moving business logic into the database is enough to make the 3-tier purists wake up screaming in the dead of night.
I've never understood the purists. I tend towards long stored procedures with lots of business logic. I also tend towards transactions at the stored procedure level. Databases are a single access point, and it makes sense (at least to me) to try to keep transactions within stored procedure boundaries.
Of course, that's not always possible. When you are in an enterprise environment, databases are scattered like dandelions on a hillside, so transactions must be managed external to the database. Likewise, business logic often has cross-database aspects, which means much business logic resides of necessity outside the database. Lastly, business logic can stuff up high-volume databases, though this might be mitigated when the clustered database Holy Grail is finally reached. (Yes, implementations do exist -- among them Oracle's -- but in my experience, they have problems.)
That doesn't mean that there isn't a role for business logic close to the data. For one, performance always will be higher when your data and business logic live in the same basic location (versus separated by network boundaries). Likewise, 3-tier is more important in the fractious enterprise environment, and less important in small- to medium-sized environments. For companies with the luxury of having smaller numbers of data sources, it's easier, and less error-prone, to manage everything in one place.
Microsoft warns people not to overdo usage of the new CLR features, as it can negatively affect performance. That makes sense, as the CLR does come with some overhead. It can speed up certain operations, however, provided careful thought is applied to what operations make most sense to move into the data tier.
From a productivity standpoint, you get a lot of nifty features not possible in traditional database procedural languages, such as custom aggregation functions, or columns that use custom CLR types (classes, in other words). That's interesting, and serves as a nice middle ground between the flexibility of relational databases and the power of object technology.