Here is why I believe the BLL/DAL concept is sound in principle, but unworkable for anyone not part of an enterprise development team that codes their own DAL. You've gone to all the trouble to write a DAL that can be swapped out when needed, but all your BLL is written to the API in the DAL. The powers that be say, \"OK we need to switch from SQL Server to MySQL.\" If you've got the developers to make your DAL work with MySQL, using the identical API, you're fine.
But, most of us do not want to hand code our own architecture, when so many proven ones are available. We start with NHibernate, EasyObjects, EntitySpaces, take your pick. If the new back-end isn't supported by your choice, not only do you have to swap out the DAL, but you have to re-write your BLL to conform to the new API. In truth, you are committing yourself to a BLL/DAL combination. Any separation provided by the architecture between BLL and DAL is smoke, if you are not willing to code, test, debug, and maintain a new DAL that conforms to the API used in the BLL. It's called vendor lock-in. And, for small to mid-size development shops, it is serious factor to consider before deciding on an architectural choice.
With code generation, and a well designed architecture, you can gain the benefit of the BLL/DAL principle, without having a strict physical separation of those two tiers. With EntitySpaces, you can swap out back-end databases with a code regen and changing the EntitySpaces provider. Your custom business logic is not touched, even though it is compiled into the same assembly. It is logically separated, not physically separated. In fact, with EntitySpaces, if the db schema are the same, you do not even have to chose one supported db over another. You can read from one and write to the other at runtime. Your presentation layer is of course separate, so that all you have to distribute are the newly compiled MyCompany.BusinessObjects.dll and the EntitySpaces.XxxxxxProvider.dll. But, even here, do not kid yourself. Your presentation layer is written to the API in your BusinessObjects assembly.
So, if you find yourself in a position to have to change vendors, because your current one does not support the newly required database, you are not going to be plopping in a new DAL. It is going to be a major conversion and re-write of the business logic and, probably, the presentation layer as well. You have a devil's choice that only you can answer. What is the likely-hood that a back-end change will force a vendor change? Am I better off coding everything from the get-go so I only have to re-write the DAL? Or, is it more economical to chose an architectural vendor so I can concentrate on business rules and presentation? If the vendor has BLL/DAL separation, is it practical for me to think I am going to write a new DAL, or am I kidding myself? What's more fun, re-writing the same old CRUD routines for a new db's SQL peculiarities, or having the time to get creative with a unique UI?