What is Three tier Application ?

3 tier design refers to the following layers:

Presentation layer - this is the user interface end of our application, the controls, graphics, grid etc.
Application (middle) layer - this is the layer in which the business logic sit. The business logic of the application is the logic that decides if all conditions are met and that implement our use case scenarios. The bulk of the functionality of our program is found in the application layer.
Data layer - the data layer allows us to "persist" data for the application or in other words, store and retrieve the data required for the running of the application . The persistance mechanism could be a relational database, XML files, or some other means of storing data.

These 3 layers can be deployed in any number of ways depending on the situation. In a ASP.Net app, you could have the pages being the presentation layer, the stored procedures being the application layer and the Data tables being the Data layer. What is important of 3 tier is to spit the application and presentation layer (2 tier this is basically integrated). You may also decide to put the Applicaion layer in a WebService so that you may also create a Forms application that reuses the same application layer.

3 Tier design is important for three reasons:

Reuse - So that many applications can use the same logic to do some action.
Maintainability - So that it can easily be changed without effecting the other layers
Scalability - So that we can move the application layer to a more powerfull machine if we need better performance.

One last thought... Although stored procedures allow for implementation of the middle tier. This should be avoided for good design. Stored Procedures shoudl be used for Data Logic, to make sure all data stay consistant and complete in the database. One should rather implement the middle tier in a seperate code layer. This can still be part of the same ASP.Net App. You only have to make sure that all ASP.Net forms access a middle tier (class or service) and never the database directly. The middle layer must access the database for any data related operations. The same counts for Forms applications. You may decide to put this middle tier facade in the app or in a service.