Ok,
I have almost a daily argument about this topic at work, and I have a pretty passionate opinion on the subject.
Before I share, a little bit about me. I've been working on multi-tier enterprise applications, both big and small, for over eleven years now. I am both a front/end and a back/end guy. I've built web-based, data driven applications from Server Side Java Script on Netscape platform, to classic ASP, and all versions of .NET. I've worked with SQL Server 6.5 - 2008, and have done some Oracle.
First of all, THERE IS NO ONE WAY TO DO THINGS THAT IS "RIGHT"! Each project presents a set of it's own constraints, whether it's development resources, technologies, or simply small budgets.
As programmers, we some times get carried away with being adamant about doing thigs the "right" way at the expense of delivering working code quickly. A lot of times, the "right" way is code that WORKS, and is delivered QUiCKLY, and is fixed QUICKLY if there is a bug. The business customer usually could care less if you have one or 4 tiers in your application.
With that said, if I'm building a small to medium application, where my team is 1-5 people, and quick and working is the key, I'm using two tiers....the front end, and stored procedures, with most of my business rules in the stored procedures, and some UI type rules in the code behind. I might utilize Microsoft Application Blocks to talk to the DB, but that's about as far as I would go.
If I'm building a big system like and ERP or something like that, and I have a big budget and a big development team where I can abstract objects, tiers, and DB roles to different development groups, and I have a strong process for unit, and integration testing, I'm putting business rules into int's own layer, entities into it's own layer, my sprocs are doing minimum complex work, etc.
Also, we have to consider what kind of an application we are building. If our application is simply for reporting, then just use sprocs for delivering your data. It's a lot easier to do a complex join in a stored procedure than it is joining objects in code, even with LINQ. On the other hand if you are doing a lot of inserts, updates, single object manupulations, then it's probably better to do it in the business layer.
To continue my point, if I'm doing batch data processes that need to be scheduled, data migrations, extractions, transformations, I'm using SQL Server for that, whether it's sprocs and jobs, or SSIS and jobs, etc. I don't want to be writing my own services to do this when Microsoft has already done those for me, and they are already running.
Here is my pet peeve.....developers that create 4 tiers just for the sake of creating 4 tiers. Usually their code looks like this: The database has just the tables, straight insert, update, select and delete stored procedures. A Data Access Layer that just calls the sprocs, A Data Layer that just calls the Data Access Layer, and a business layer that just calls the Data layer, and 99 % of all real business logic is done in the code behind of an aspx page! WHY!!!!!????? Now every time you need to add a column to the database, you have to regenerate 4 layers, and still change your code behind! What a waste of time!
So as a rule of thumb, there is no rule of thumb! Every situation has to be analysed for what it is, and procede accordingly.