October 15, 2015 at 9:26 pm
Comments posted to this topic are about the item Where should middle tier business logic be stored?
October 16, 2015 at 1:44 am
That is a constant debate in our DEV shop. Since deploying DB artifacts is a bit harder in our environment, DEVs argue that it should be moved to application code, since it is easier to deploy the application code.
So, I would say that it depends. 🙂
Thanks...Chris
October 16, 2015 at 2:38 am
Why do you not use a procedure call to dummy-procedure that calls a CLR-procedure for your heavy-calcualtion-stuff?
In this case you would only need to replace the assembly in MS SQL only instead of deploying the (changed) dll to several places. Furthermore this would be transparent to the application because it would still call it's old procedure while you as dba decide if it does T-SQL or CLR calls.
October 16, 2015 at 2:40 am
I've programmed through phases and styles that have been everything from all in stored procs through to all in code. In general you cannot get away from some logic in the db even if it is just the relationships and constraints, but you can switch most out easily enough.
For my circumstances certainly at the moment, the correct answer is the logic should be in the most appropriate place, which will be a mixture of stored procs and code logic. It makes little difference in terms of difficulty to modify in our shop, so we really do try and choose the best place. Anything with moderate set based complexity is certainly likely to be in a proc. Anything which requires more serial processing (if you like, I think that a good way of putting it) stays in a middle tier library. Simple retrieval and updating is also done in the library. Either way the results populate DTO models for front end consumption.
In general we'll have about 10 - 30% of the logic database side.
October 16, 2015 at 4:16 am
We use the stored procedures route. But I am thinking that this could be incorrect. When we deploy a new version of an application, we deploy the website and also change the SP's. From a testing point of view, you are implementing untested code. If it was in a DLL, then that will have been tested as part of the change.
October 16, 2015 at 5:06 am
While the final answer may depend on the skills that are available in-house. Also, if the application will never be large, it probably doesn't matter. But if you are building a large application that needs to scale well, then the business logic absolutely belongs in the middle-tier.
The middle-tier can have lots of servers with copies of the code. But ultimately they have to point to a single database. If the logic is in the database and the middle tier simply serves as a way station to pass requests, it will fast reach a choke point. If the logic is on the middle tier, the real work can be spread among the various servers who do the heavy lifting and reduce the final answer to an insert/update/delete.
I've seen the gain in performance when the logic is moved from the database (mostly in stored procs) to a middle tier with multiple servers. As a DBA who can also program, each has its place.
I'm not advocating that the middle-tier replace the PK and FK checks, though. That is not only properly done at the database, it's vital for the integrity of the data that it's there.
October 16, 2015 at 5:37 am
For me it comes down to two things: testing and money.
I have heard people do unit databases but there isn't any good tooling out there. Unit testing a well designed c# app is easy.
On the money side, I can buy a BUNCH of web servers for the cost of one more core's licensing on SQL server. I am actually starting to get uptight about seeing a sort by in queries and sprocs. You should use the cheap CPU cycles for that.
October 16, 2015 at 5:42 am
To add on to the posting, I have seen another value in keeping a lot of the core business logic in the database. End users like the newest and shiniest interfaces...on a side note my cat is entranced by metallic shiny objects.
From my observance, .Net patterns seem to evolve much more frequently. From a web perspective alone, look at the varieties from WebForms to Silverlight to MVC/MVVM (or any other variety thereof). Data architecture is more stable, so placing your business logic in the database allows you more flexibility to keep the business user entertained with the latest UI bling.
Just my thoughts on the matter.
October 16, 2015 at 5:54 am
Over the past few years I have been working on an application that has a large database and 5,000 + daily users. The business logic is in the middle tier with significant logic in stored procedures. What I have seen is that the stored procedures have tended to lock database object causing performance issues. I have for most of the past two and one-half years spent my time working on database issues surrounding the problems caused by these stored procedures.
That being said, the power of Sql Server is often neglected by developers using Entity Framework and other ORMs. Application processes using algorithms such as needed in insurance underwriting are often more effective and efficient in stored procedures rather than in the business layer. This is where data/information and software architecture come into play. The software architect who presents a balanced approach to development can identify the processes most suitable for business layer processing and those that are more suitable for completing in the data layer.
In my experience the database server has more computing power than the server supporting the business layer; this is where the consideration of options should begin.
October 16, 2015 at 6:17 am
It's kind of a broad question but ultimately depends on what constitutes "business logic". I generally fall on the side that says databases should be stupid and do what they're meant to do, i.e. be an engine for storing and retrieving data.
If I'm creating an invoice where a fairly complex set of logic associated with discounts, allowances etc is required to determine the invoice amount I'm going to fall on the side of the middle tier and when I've performed those calculations use a stored proc to insert the invoice row.
If I have a set of logic for "Create a new customer" I might create a stored procedure to determine which rows should be created for each table associated with a customer based on a dataset passed to the proc. This logic might be complicated based on customer type, new or existing account etc but ultimately the app already captured all of the data and this is really a CRUD process and should live on the database.
"Beliefs" get in the way of learning.
October 16, 2015 at 6:25 am
Implementing business logic in the database is not the best approach. If you follow that through, why not implement UI logic there too? I'm sure you technically could, but just because you can...
The real argument is that by taking a layered approach (best practices) to designing and developing your application, the net result is a more scalable and testable application (google layered design/separation-of-concerns/SOLID principals etc).
Another way of looking at it is core competency. What is the purpose of an RDBMS? Its core purpose as a tool is to ensure the integrity of data while being read and updated my numerous users across an application. The procedural language of SQL is designed for and does a great job at data-access. CRUD. By keeping procedures small and data-focused you encourage less contention and greater throughput. Leave the job of data access/integrity to the DB and business logic to the middle-tier. Now granted business logic is inherent in the normalized design of the DB, but these usually take the form of relational and column level constraints (but even column level constraints can be over-done).
And lastly, having seen numerous production systems (Oracle on big 'Nix boxes and SQL Server) where business logic was implemented in procedural code, the DB inevitably becomes the bottleneck for the system.
October 16, 2015 at 6:50 am
Business logic should reside in a location best suited for the solution. A solution that presents a single point of failure sounds bad. It is possible to deploy simple,distributed, testable, and maintainable. Net code without resorting to the use of 20 year old practices. Separation of concerns helps to sort out lots of situations where there are questions surrounding code. The data store should hold application state and nothing else.
October 16, 2015 at 6:54 am
Interesting topic.
Personally, I favor putting business logic in the database. As someone already pointed out changing business logic (which is tragically seldom logical or stable) is easier to do safely in SPs.
Likewise I view business logic as part of the database integrity. Taking a simple example if a customer is only allowed a maximum of X dollars in their order but Y dollars for "preferred" customers this is clearly an integrity issue.
On the other hand, if the calculation is horribly complex (insurance or banking spring to mind) you may not have a choice. Performance issues demand SQL CLR at least.
But in that case it's still *database* logic. There won't be a synchronization issue like there is with middle tier stuff.
Happily I don't have to worry about scaling. 🙂 However, from a security standpoint I would think the database engine is always where you want your business logic.
October 16, 2015 at 6:54 am
The real argument is that by taking a layered approach (best practices) to designing and developing your application, the net result is a more scalable and testable application (google layered design/separation-of-concerns/SOLID principals etc).
Interesting someone in the DB world knows about the SOLID principles.
For answers about this topic, thorough reading of this book will shed some serious light on the subject
This suppose a perfect world, and we all knows that we're not in a perfect world. Now this come down to your judgement about knowing what "should" be done and the constraints you're having on the project (software purpose, time to market, longevity, resources, money, people, technologies, subjectivity, fears of changes, etc)
Finally, to my knowledge (I may be wrong here), we (as mankind) didn't yet find any modeling design that covers every quality software pattern aka: no flaws. Each one has some. But some have fewer flaws than others like the DDD and the anemic one seems which to be the "top" modeling design as of now. Each one deals with different way to handle the middle tier business logic.
Oh if you want top TTM (fastest possible way on the market) but scrapping your software as soon as it gets out on the market, try the SMART UI pattern. It has it's own way of dealing with the "middle tier business logic"
Have fun, happy friday 🙂
October 16, 2015 at 7:00 am
The answer *so* depends on your development environment. If I was starting a brand new system or application today, all middle-tier business logic would be in a DLL with a matching .NET DAL (database access layer) and most stored procedures would be fairly simple and used for atomic CRUD operations.
Having said that, I have inherited a suite of applications (a system if you will) with a SQL Server back-end. The "BLL" is implemented poorly and inconsistently between the form-class code and stored procs, leaning heavily toward the procs -- no BLL or DAL existed to start. There was no DLL when I started on this and no business objects whatsoever in sight in any of the ten or so apps. Code repetition everywhere with logic conflicts between the form code and the procs, magic numbers galore, with everything else being convoluted because of three-letter variable names, and so forth. Cursors thrown into the procs wherever it was convenient. Changes to this suite of applications take considerable analysis and testing time because of the scattered nature of the logic, vague SQL column names (with no documentation to start with), procs that are thousands of lines long, things already mentioned, et al. The most important SQL table in the system has at least four different "business objects" stored there, and would greatly benefit all involved if it was normalized and split accordingly. I think you get an adequate picture.
I have slowly started to convert over to a .NET DLL for the middle-tier business logic + XML documentation, simply for the sake of understanding the system first of all (and documenting business-processes), let alone rewriting it. As I am able, I am replicating ALL business logic from the form code and procs into .NET BLL classes, and then deprecating both the form code and procs where I can. I'll take a system I can quickly understand and modify any day over a system that is mostly implemented in SQL, with documentation non-existent, very little consideration for normalization, business-rule conflicts, etc.
I love T-SQL as a beast of burden, but I use it for that which it was designed: set-based data processing, data-rules, data-security, FK/PK constraints, CRUD operations, et all. While I have seen BUSINESS-rules successfully implemented in SQL, it's not the path I would choose for many reasons.
-----------------------
Scripted
SQL Server user and admin since v2000
"It takes a village to raise a SQL Server."
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply