SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Where Logic Lives

By Mike Dillon,

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.”

Total article views: 9029 | Views in the last 30 days: 0
Related Articles

Changing SQL Server Database Logical Names

SQL Server Databases are made up of physical and logical names. This article goes over the logical n...


Logical Reads

Logical Reads


Index requirement for logical operations

Index requirement for logical operations


Three Things

So for the latest database geek meme, Paul Randal started this thing off and tagged Tom LaRock, who ...


Change file logical name for mirror database

Today I encountered a interesting issue when rename logical name for mirror database. We have a d...