Business Logic in SQL Server 2008

Question

If there are any DBAs out there, I'm making a fairly large piece of software and one of the biggest issues presently is where to put the business logic. While Stored Procedures would be easier to fix on the fly, the processing requirements would probably slow the DB down tremendously. I also don't want to have all of the business logic handled by the application because I want it to be a "self-sustaining entity" that doesn't require the user-front end to operate.

My idea, is to create a service to run on a central server somewhere, and have the clients connect through that. The service would maintain all the business logic and serve as a front-end for all the database operations.

Ideas? Yes? No?

I'm willing to accept that I'm also missing some key concepts and need to read some literature.

Answer 1

Having all business logic on the server side is fine.
Not having it on the server side is fine, too.

In fact, it's up to you.
If a stored procedure tends to look not sql-ish, you can make a CLR stored procedure.

Here's a similar question.

Answer 2

What do you mean by "business logic"?

I've seen cases where aggregations and other set-based operations have been done in client code, as well as horrible RBAR operations in SQL that should be somewhere else.

SQL is one tool that has it's place: if you're working through large datasets, JOINs, aggregations etc then SQL is the place to do it. Anything else is slavish obedience to an SOA ideal.

My approach is to consider what the stored proc or SQL is doing: is it part of the middle tier to avoid set based operations in procedural code, or is it lower as pure data integrity/persistence?

If your business logic is 100% set based then you don't need a middle tier (edit: client code based) arguably, unless it's very thin.

Answer 3

I'd highly recommend a traditional n-layer approach, where you have at least UI layer, business layer (like a C# assembly or Java equivolent), and data access. See: http://en.wikipedia.org/wiki/Multitier_architecture.

I worked for a company where all the business logic was in the procs, and maintence costs are much higher than they had to be, it limited us to a specific version of sql server, it wasn't scalable, etc. In short, unless your application is a simple throw away kind of thing, I'd not put any business logic in the database.

Answer 4

i would suggest that you keep a keen eye on the difference between what you think of as business logic, and what are the referential integrity constraints.

Make sure all constraints that keep the data meaningfully related are in place at the database layer. i.e. if you need to cascade some deletes, or inserts - and when you need to validate some basic data values in order to have everything make sense... these should all be in the database.

Then decide if the Client, or the middle layer server, or the database is appropriate for any additional business logic.

Answer 5

Over the years, I've seen client applications come and go, but the database is still there.

So nowadays I use stored procedures for most of the business logic. Three big advantages:

• Bug fix deployment takes an instant, with no downtime
• Multi-user by default
• Far less plumbing code (no data access layer)