A Response to: Ken North’s SOA, Multi-Tier Architectures and

Logic in the Database

I have read several articles over the last several months on the topic of

business logic and where it should live. In a recent article by Ken North (http://www.sqlsummit.com/Articles/LogicInTheDatabase.HTM), he made a case for

placing a majority of the business rules and logic in the database as a means of

simplicity, consistency and data integrity. Ken makes what I think most would

agree are very good points. I can also tell you from experience that over time

Ken’s theories on the advantages of this type of model will be proven wrong or

at best cumbersome and clumsy.

While I agree that developers are under more pressure than ever to deliver new

features, new applications, and even new products to management in less time

than ever before. It is precisely this reason that I disagree with Ken on every

level possible.

A database can be a lot of things as we know and now more than ever the types of

thing a database can store is mind blowing. Thinking about storing images and

XML documents several years ago was unheard of. I, much like many DBA’s and

developers, can think of many situations in the past where that would have been

much easier to place the logic in the database as opposed to doing the

programming required to accomplish the same thing. It is at this point, however,

that I am reminded of something my father Kevin told me a long time ago. “Son,

just because you can do something doesn’t mean you should” and “The easy way is

almost always the wrong way” and finally “all things in moderation” or was that

Ben Franklin. Now I will acknowledge that Dad didn’t intend for these sayings

to be a guide for me in my future database endeavors (Dad didn’t have database

classes in the one room school house which he walked 20 miles to in six feet of

snow, uphill both ways….but I digress). But allowing for the possibility that

dear old dad knew what he was talking about I would like to present a few

reasons as to why placing logic in the database is a bad idea.

I will acknowledge how tempting putting logic in the database is. Having a

central location, consistent logic, data integrity and the like are all good

arguments if you are building a database that is going to be used by a known

number of users and the content will be largely static. As we all would agree,

both of these ideas requires that we live on some planet other than this one.

The fact of the matter is that in most cases databases are designed to do one

thing and then asked to do several others. Any DBA who has been around for more

than a week has experienced this. As painful as it could be from time to time

it was still just adding data, new columns, new tables, and even new databases.

Now we have stored procedures and indexes and views and functions and stored

procedures that call functions that reference indexed views. How great is that?

We can add this here and that there and before you know it, all of your logic

ends up in the database.

It is later on in this process that the things begin to sway the other way.

What was once an easy place to put some logic has become a tangled web of

different logic for different things that are so specific that they can’t be

used for anything else. Now adding something means adding something specific.

Every new release or update requires dozens of new stored procedures on new

tables and new views with new indexes and although the developers may be working

on the same “project”, the reality is they are not by nature good at sharing or

thinking about ways in which they could leverage something that is already

there. If I had a quarter for every time I heard “I can’t use that because it

returns A and I need B" that, well you know the rest.

The main problem arises is performance degradation. Good luck optimizing a

server that contains oodles of business logic. I am often asked to tune a

database in this environment and I always tell them the same thing. I will do

what I can and you will be better than you were, but in a couple of –insert time

frame- you will be calling me again. That’s because you will always bump up

against server side limits that application servers just don’t have. In the

simple fact that you do all of you calculations or applying rules or stripping

and formatting data on the fly requires overhead and resources that database

servers are not created to do efficiently. Can it do them? Of course it can

and SQL Server is the best at it, but it still isn’t what is designed to do and

because of that it will always be more inefficient than doing the same things

programmatically.

Flexibility decreases as logic increases. If you are doing all or your

formatting in the database, then when you need to add features that allow you to

do business in another country or you are asked to perform internationalization

work, you have to go and duplicate almost everything that you need in each

language that you need it. Again, more data and more overhead to process that

data along with formatting and making sure it is used in the specific instance

it was logically designed for is going to result in a resource shortage at some

point. A database shouldn’t care about language or format or any of that. It

should return data that developers can use to format and display in any way they

like. By placing logic in the database you take away the developers ability to

do just what you are asking them to do, be flexible and fast.

Lastly and most importantly, databases don’t scale. Once you reach the limit,

you are done. You can only add so much RAM or so much processing power. You

can only tune a stored procedure so much when it is asked to perform logical

operations to return specific data. Say it with me “Databases don’t scale,

application servers do.” You can split and divide and add to application

servers from now until the end of time. Not so much with a database. You can

do lots of things many more than I have mentioned above, but I promise you the

day will come when you will hit the wall. Maybe it will be when you’re asked to

do the same stuff you do on MSDE on SQL Express with a larger database limit but

a smaller RAM limit which is right around the corner. As a database architect I

am looking very much forward to the release of Express as I know may customers

who have been tuned to the utmost, by me or others, that are at limits they

shouldn’t be at because of logic in the database. Losing a gig of cache is

going to be something that pushes their performance meter over the edge.

In closing, I understand that some of this may seem counter intuitive at first,

but give it some thought. I also understand that this would seem to fly in the

face of what Microsoft is trying to accomplish with the integration of the .NET

CLR and SQL Server 2005, but I haven’t seen one thing that leads me to believe

that there intent is to have logic live in the database. I could be wrong as

the jury is still out on that one and no one seems to have a clear picture of

the cost/benefit of such a model. I can tell you from experience that some

logic in the database is ok, but to intentionally put it there is asking for

trouble. And you know what Dad said about trouble “Son, you never have to go

out looking for trouble, it will find you on its own.”