A few weeks ago i was surfing the web and come across this article Kiss the Middle-tier Goodbye with SQL-Server with SQL Server Yukon,i didn't had the time to read it but save it for reading it later. The title was intriguing and after reading nothing more than title, i thought it might give a nice post. And this after having a few thoughts about it while taking my morning shower, i envisioned the structure of this post.

Now that that i've read the article it has nothing to do what i've envisioned, but since no thoughts (even if they aren't worth much) should be wasted i decided to write it anyway. The article summarizes how we can store and manipulate XML directly in yukon, interesting but not what i expected. :-)

During my professional life, i've encountered two type of schools of thought when dealing with databases. (in no particular order):

1. The ones that consider that the database is is just a data store, a simple silo where you can store,delete and fetch data. No more, no less. All business logic should be kept somewhere else. Normally data access it written using either code generation (during compile time, written automatically during run-time using some kind of abstraction,using some O/R mapper or some kind of similar technology). Their reasoning:
• By using the data store as data storage and keeping the business logic independent from it, we are independent from it, and later on we can switch to another database incurring no costs (apart from the licensing ones and the data migratiom :-))
• It easier to contain all business logic on one place. Easier to write, easier maintain and easier to understand.
• More scalable systems, the processing is split across systems. No need to overload the already most strained system the database (unless the database is distributed, it is probably the most used single point and a biggest point for contention)
2. The ones that believe that not only the database is no only suitable for storing data, but is also the most appropriate place to put business logic. Their arguments (probably some other i don't know or don't remember)
• Reduce the number of roundtrips while executing queries
• By controlling transactions manually on the database, you can make optimal lock management (no hold on locks while business logic is doing other things)
• By controlling manually the queries that are written (inside SP's for sure) you can micro optimize them. From my experience (YMMV) This is all fine and dandy, and you can extract every bit of performance from the database, but at a certain point it becomes unmanageable unless you functionalities are contained and orthogonal. (almost no reuse of SP's)
• The more close you are to the data the more performant you will be.
• Since business validation is made near the date it will be impossible to introduce incorrect data into the database (assuming there are no bugs in the validation code off course).

I understand both sides, i've been on both sides of the fence and i try to try to take a pragmatic approach and normally try to combine the best of both worlds.

But from what i've seen people tend to be fundamentalist about it. They seem to be binary, it's either one or the other. I've seen some pretty convulated machine states machine implemented in T-SQL in the name of performance. Apart from one or two people, trying to change something meaned almost invariable to system breakage.

From my experience, on most systems the database (data centric systems that is. On my business i would say 100% of them :-)) seems to be the bottleneck, and i confess, that when i hear stories about people putting more things into the database i shriver,since it's something that is very difficult to scale (vertically scaling is expensive and horizontally scaling is either architected up front or it's a nightmare to perform later).

For example this post claims that yukon will make the business logic tier irrelevant (i'm yukon of Borg, resistance is irrelevant?), can we afford it? until we can scale and distribute a database into multiple machines (transparently, and with a query optimizer engine that can write plans for distributed queries automatically) i think we can wait a little before putting even more things into the database.

Don't get me wrong, exposing web services directly from Yukon will probably cut a lot of craft and will speed up development, but for that MS has a wonderfull tool sqlxml, which for simple tasks is great (not to mention it can alleviate some sqlserver CPU load). But i probably still use .Net web services for more complicated things.

I think with yukon things will remain pretty much the same. People from one side of the camp, will now load CLR into their database, the other side will continue it's way (either with manually written code,objectspace or something else) but continue it's way.

I know i will continue my quest to find a nice db access code generator, i will use CLR on the database when appropriate, and will continue waiting and sighing for the theory of everything)