Whats the best paradigm or design pattern for exception handling in a mission critical DB driven web application?

I want to design a bullet-proof, fault tolerant, DB driven web application and was wondering on how to architect it.

the system will have a asp.net UI, web services middle tier and SQL2005 back end. The UI and Services will communicate using JSON calls.

I was wonderin开发者_运维百科g on how to ensure transactions are committed and if not for any error to bubble up and be logged. ideally the action to be retried a couple times after 5 minute intervals, like an email app does.

I was planing to use try catch blocks in SQL and was wondering what the interface (or contract if you will) would look like between the SQL stored procs and the Services that call them would look/ function. this interface will play 2 roles one is to pass params for the proc to function and return expected results. the next will be for the proc to return error information. maybe somethign like error number and error message.

my quagmire is how to i structure this intelligently so that the services expect and react accordingly to both data and error info returned from procs and handle each accordingly?

is there a framework for this because it seems very boiler plate?

---

You might consider looking into SQL Server Service Broker:

http://msdn.microsoft.com/en-us/library/ms345108%28v=sql.90%29.aspx

The unique features of Service Broker and its deep database integration make it an ideal platform for building a new class of loosely coupled services for database applications. Service Broker not only brings asynchronous, queued messaging to database applications but significantly expands the state of the art for reliable messaging.