Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Two-tier vs Three-tier

    Hey all.

    I've just been doing some surfing on the pros and cons of multi-tiered environments. I see a lot of conflicting information out there.

    I'd like to get everybody's opinion of the advantages and disadvantages of two-tiered vs three-tiered applications, specifically with a SQL Server back-end and all the functionality that comes with it (TSQL, triggers, user-defined functions, etc...).

    Also, some definitions or principles regarding what tasks should be handled in which layers (especially business logic).

    Brett? Pat? RJabarov? Rudi? Others?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you might start by explaining what 2-tier and 3-tier means

    isn't it how many servers you can stack up on a rack? how do blade servers figure in?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    I've actually had this question for awhile as well.

    When you talk about things like business logic, where is the best place to run that to optimize the system?

    The way I understand it, you could put that type of thing either on the client or on in an App server that could be located on the same machine as the database server, which would slow stuff down

    Or, you could just build the business logic into the db server with stored procedures and the like.

    What have you guys seen in your experience, and what works the best?

  4. #4
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    I prefer n-tier. (n) = the right number of tiers to get the job done.

    Web Application (n = 3) or more whether you like it or not. However, be careful here, because web applications are about to morph into WinForms applications, and it will be nearly impossible to discern the two.

    Web Services - (3 =< n <= infiniti) : Web Services offer some incredible opportunities to integrate disparate systems. Unfortunately, we can't get everyone to upgrade to the latest/greatest technology, but Web Services can work wonders here. Seen Microsoft's internal project? It provides tight integration with legacy (old as me) systems.

    WinForms Applications / Avalon / Longhorn / WinFS (1 =< n <= infiniti) Again, I'm a Microsoft bigot, but WinForms is da bomb.

    Communications / Indigo - (n = n) Communications are more important than database transactions. The ease with which we communicate will continue to dramatically improve. ipTV, anyone?

    Business Logic - put it in the place where it is the easiest to maintain. If database tier = n, put it in the database.
    Last edited by MaxA; 02-10-05 at 23:49.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm a fan of N-tier computing too.

    I think that you need to define at least four separate computing components, which can be implemented on 1 or more machines. The compnents are:

    1. Database (application semantic storage)
    2. Business logic
    3. Application deployment (where the code lives)
    4. Presentation (user interface)

    People often blend these in varying ways, and some people make further distinctions than I do which result in more components (tiers) in their model.

    The database tier is something that pretty much everyone agrees on, but they tend to disagree on the specifics of what goes into that tier. To me, the database stores data for the application. This is where you define rules that describe what is permitted to be stored, things like referential integrity, row level constraints, etc. I think that any code that handles translation between the database storage and the rest of the universe belongs here at the very least.

    Business logic describes the broad rules about what can be done with the data. Can this client apply for a loan, can that account be closed, do multiple people need to agree before that contract can be considered valid, etc. These rules are the hard and fast rules defined by the business itself, that either have no exceptions at all or have well defined and generally understood exceptions that are de facto part of the rule itself.

    Application deployment describes the parts of the program that makes things "go" from the user's perspective. This is code that executes to create that invoice, process the loan, or complete the users call. This is what decides the "what" about the user experience: which screens need to appear for the user, what data goes on them, and what logical choices are acceptable for the user to make.

    The presentation is the part that the user actually sees. This manages what GUI components appear, what keys are monitored, which devices are usable (mouse, etc). This tier determines the "how" of the user experience, how they see things that the application layer determines are relevant, and what responses the user can make to that presentation.

    All of these components can reside on one machine. In development they often do reside on one machine. This is fine for convenience when performance needs are relatively low.

    At the opposite end of the spectrum, each component (tier) can be implemented on a cluster of beefy machines. This is exactly how most of the major web sites are actually implemented, especailly the ones that need five nines (99.999% up time) or better. While this appears as a single application to the user, it is really many machines that are often in more than one place around the world.

    In between the two is where most of the computing universe actually sits. One machine might contain the database and the business logic (implemented as stored procedures). Another machine might contain the application (say IIS with ASP). A small hoard of machines could then provide the presentation (web browser) that was the user's interface to the system.

    The really nice thing about this model for the application is that it allows for easy (and relatively cheap) scaling of the application. You can scale down to a single laptop for the developer or salesperson that needs to demo a product. You can scale up to one server for a workgroup. You can then scale up to a few relatively beefy servers for a fortune 500 company. You can then scale up to three or more clusters to serve the known universe.

    While I'm not trying to imply that this is cheap by any sense of the word, compared to even the most scalable options available ten years ago, this design is more flexible at about ten percent of the cost.

    -PatP

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    But the $64,000 question is "Where should the business logic reside?"

    I think there is pretty common agreement that it should not reside in the presentation layer, but DotNet coders want to put it in the middle tier, while DBAs favor placing immutable rules in the data layer.

    I have just never understood the arguments for putting business logic in the middle tier. It seems to me that if the rules apply to data, they belong with the data. But I keep getting arguments that coding in the database layer does not scale well, and that processing load in the middle tier is more easily balanced. But it seems that this balancing would incur considerable cost in terms of transfering unnecessary data to the middle tier, and the coding that would be needed to maintain data integrity.

    Does anybody here understand these arguments?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    <Rant>
    The argument that coding in the database layer does not scale well is pure bunk. It is a method used by DBA-outcasts to create Fear, Uncertainty and Doubt.

    Give me numbers that dispute this or sit down and be quiet and go back to writing a data-independent interface.

    This argument is synonymous with caching - with .NET, developers are implementing uber-cached middle tiers and then wondering why they're running out of memory or why the stored procedures that cache the sites are running so frequently, blocking the world and consuming the SQL Server CPU. Cache as little as possible in the middle-tier(s).

    It is all about the data - the biggest potential bottleneck for an n-tier solution is in the data architecture and the associated DML. Get it right, and everything else will seem simple and arguments about nanosecond differences b/w a ProperCase function written for the CLR vs. using a SQL function go away. WHO CARES?

    Bottom Line - you want to rewrite, recompile (I understand JIT compiling, so don't waste the bytes) and redeploy everytime you need to change business logic or do you prefer a simple stored procedure modification? Maintainability, maintainability and maintainability are the three deciding factors.

    JIT is a great concept, but try using it in a highly-scaled web farm with thousands of sites. The kernel memory (non paged) can take a bodacious hit. Beauty of IIS 6.0 for these situations is the ability to gracefully cycle the application. Anybody who relies on it is in for deep trouble.
    </Rant>
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  8. #8
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    I think we understand the arguments, and it's probably comes down to what is the most logical place for the business logic. I've been thinking about it some since my last post and usually the most logical place for the "applications" I've worked with is in the database.

    Of course that assumes that the business logic relies only on data stored in the database and does not require any sources outside of such (i.e., text files, etc.). For instance, I have an ETL process that moves data from a csv file into the database and through some transforms ultimately ending up in an OLAP database. I have been asked to audit that process on a regular basis to ensure that all data received in the csv file is actually making it into the database as it was received.

    While I can do most of that audit in the database with stored procedures, at some point I have to have some fairly detailed statistics from the csv file, which can only be obtained by logic outside of the database.

    In this case, I think that all of the business logic will reside in the database except for that one component for the csv validation. So, I guess the answer to the $64,000 question is "It depends."

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I use a couple of rules to try to decide where to place a given snippet of code. Most of the time my rules make it easy to determine where in my hierarchy a given piece of code should go. The short answer boils down to keeping the code declarative and as close to the data that it operates on, with a few exceptions for maintainability/performance.

    If you expect less than 10,000 simultaneous users, you typically implement my business rule layer in the database. They are effectively the same thing until the load gets relatively high.

    If a piece of code can be done via declarative logic (constraints, views, etc), it belongs in the database. An application of this rule that developers rarely see without help is that any problem that can be solved using set-based solutions also belongs in the database instead of being implemented in code anywhere.

    If a piece of code affects the display/input of data, the degree of interactivity decides where it goes. Interactive code (in other words, UI implementation) belongs in my presentation layer, everything else belongs in my deployment layer.

    If a piece of code requires iteration, especially if that involves dynamic data selection, it almost always belongs in the deployment layer.

    The project manager sometimes needs to make some alterations depending on implementation specific details. One good example of this is a system that had varying speed links between layers. During high load periods, the application layer was implmeneted on a cluster in the data center with the database. When the load decreased, half of the databases when down for maintenance, and after they came back up half of the applicaiton servers went down. This meant that at high usage times, we had 100 Mb between the database and app server, but at other times we might only have T1 between the database and application servers. That drastically changes your perspective, pushing more stuff into the database!

    -PatP

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, I read it all.

    Stored Procedures (and packages for Oracle) contain ALL of the business logic. And I'm starting to think Views for Access Paths to the data.

    Presentation layer only has access to EXEC procs

    Architecture has a lot to do with performance as well...

    Did an Annual Enrollment for Benefits one year...60,000 people had a 4 week window...we stressed the hell out of NT and Oracle before we went live...

    NT Couldn't handle it...Unix and Oracle didn't blink...now this might have a lot to do with what Pat's talking about...but Unix and Oracle was rock solid.

    Oh, and they all waited to the last day to enroll...and it didn't blink.

    Now they outsourced HR to Tillinghast....and rewrote the app...crashed and burned...had no idea how to handle the load...

    Is Googles database properitary? I think Ebay is Oracle as well...say an architecture diagram once...very impressive...

    Anyway..why?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's an excerpt from the article "Keys to the Database" by Joe Celko --
    When the host languages were outside the RDBMS, you had ANSI/ISO standards that defined those interfaces. But the host language wasn't inside the RDBMS, firing triggers or executing stored procedures in multiple proprietary languages. Now all bets are off. The code will be such a mess that you'll never leave Microsoft or interface with any other data source.

    ... Destroying tiered architecture is fundamentally wrong. It endangers data integrity and returns us to the worst of file processing.

    Sure, Little Johnny with his MS certificate now thinks he's a DBA and has the keys to get inside the RDBMS, but can anyone begin to imagine any advantage to the enterprise?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Google's database is proprietary. Their operating system isn't - unless you consider the fact they've probably modified it and it is doubtful they would 'release' their version.

    Now we're getting into Oracle vs. SQL Server ... oh my! Never met an Oracle database that couldn't be outperformed by MSSQL... unless, of course, silent transactions are your bag of tea - who needs the ability to rollback a mistake/constraint violation? Not the people who get irritated with pesky foreign keys.

    And, eBay was clueless with their implementation. Their original architects were database-outcasts. Interestingly, they made two changes at once - got real DBAs, and switched to Oracle. The former is what created success, not the latter.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's not to agree with that article...aren't we constantly battling with developers?

    I had once ask me why I couldn't handle a constraint violation...why should the have to handle the error....she got upset because I made her feel stupid....

    And besides the "short comings" we have now...I find there is very little that I can't do with T-SQL, PL/SQL, etc.

    Now DB2 OS/390 7.2 has no real procedural SQL extensions...so they claim to use "External" Stored procedures....basically COBOL that gets loaded to the DB2 Address space...ok...so I'll live with it...

    It's all the same sheet

    I have no plans to use CLR
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Does Microsoft have recommend any "best practices" about where to put business logic? After all, they make both SQL Server, XP, and .Net, so presumably they have some sort of overall vision as to how the pieces should interact. I've seen hints on their sites that putting the logic in SQL Server, but nothing definitive (http://msdn.microsoft.com/library/de...er_applica.asp).

    I would love to be able to cite Microsoft in response to some of our developer's claims...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    I would love to be able to cite Microsoft in response to some of our developer's claims...
    You talk to developers?

    Tell them they get EXEC authority...period

    They need ANYTHING..they need to go through you....



    How'd they get all the way up to Olympus anyway?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •