Will there be a giant sucking sound as architects and developers rush to host their business components in SQL Server 2005?  I don’t necessarily think so.  I’d like to explore why I think this way…

Yes, SQL Server is going to host the CLR in process and allow you to author and run your sprocs, user functions, and triggers in any compatible .NET language.  But what does this really mean?  Are you going to port all your T-SQL code to C#?

Here’re some reasons that I think you might consider it:

The manner in which you are manipulating your data makes it unbearably onerous to do in T-SQL.  Let’s face it, as a general purpose programming language, T-SQL, and every other SQL based language out there, have a long way to go to get to where C# and VB are today.  SQL shines brightly in manipulating data in sets derived from expressions.  When it comes down to working with individual records in those sets, such as performing complex calculations, performing field level transformations, and applying business rules, things get awkward really fast.  For example, if you have sprocs that are opening up cursors, running through the data, and applying some business logic, you’ll want to think about porting them to a language better suited to those activities.  (Then again, you might also think about moving that code out of the database entirely and re-hosting it in your business logic layer.)

Heavy math.  Fast Fourier transforms in T-SQL?  Yikes!

Security.  Your threat models indicate that you absolutely can not trust any client code with your data.

Now, to take it a step further and take the idea to its radical extreme and move all your middle tier code to the DB?  I think the number of desirable reasons diminishes still further.

Experience has shown me that databases are almost always the first to feel the scalability squeeze in a web farm environment (if the app is even half way well written).  It’s a simple fact that your database is one box which supports N boxes which usually hit it pretty hard.  Given this, scalability improvements in your app are almost always achieved by leveraging some of the following ideas:

• If at all possible, don’t go to the database to begin with.  Cache!  Cache!  Cache!
• Get in and get out of the database as quickly as possible.  Database threads are sacred.   Let them service as many request as possible without the database having to spawn new ones.
• Don’t consume ANY resources on the database server that you don’t absolutely need to.  Don’t open up large record sets that chew up RAM and IO.
• Watch your locks!  Locks in your database can kill your performance and scalability.

Now, taken these measures, how does the presence of managed code execution in the database improve matters?  Frankly, I don’t see it helping much at all unless you’re doing some of the things I mention above as a possible reason to consider porting from T-SQL.  On the other side of the fence, tossing your entire business logic layer into the database makes many of the positive improvements you could make to scalability all the more challenging.

• You’re going to the DB box more often
• You’re chewing up more threads for greater periods of time.
• You’re running more code that’ll consume more resources.  Just think about all the temporary objects and associated GC pressure that box is going to have to endure.
• You’ve got additional locking scenarios to worry about.  A deadlock on a single app server is real trouble while in a distributed environment you’re just taking one of N boxes down with the deadlock.