November 27, 2008 at 12:35 pm
Comments posted to this topic are about the item The Wild Fringes of SQL Server Development
November 27, 2008 at 4:49 pm
Hurray Tony!
If the database is to be nothing more than a data dump then the data dump crowd could (should?) use a simple structure such as the dBase file structure. Why would you want to buy a Ferrari if you only plan to drive it at 20 mph (that's 30 kph for you continental types).
The only rule that a developer should always follow is that you should always keep your options open. Do the right work where the most benefit can be achieved.
--Paul Hunter
December 1, 2008 at 2:29 am
Well... great editorial, I'm surprised it didn't spark off more comments!!
From a personal point of view, I use Nhibernate/Active Record an awful lot, so would fall under the 'Dumb Database' brigade. At the same time however, there are significant portions of my application which would just require too much querying/coding only using NHibernate/ActiveRecord, so I tend to implement those as either stored procs or views which I then access through my middle layer.
The great benefit is that front-end developers don't need to know the data model, and I can make changed to the data model (such as splitting tables, etc) without any major repercussions to anyone.
The downside is of course that the middle layer code needs direct table access, and performance is not as good as a pure stored proc/ado .net solution would be. However, for most of our applications these are things I can get around with auditing, proper permission setting and a streamlined middle layer.
In conclusion, I have to agree with Tony's comment:
I am wary of this sort of "best practice" advice. It is usually a device to surreptitiously hide irrational dogma, or else just a vapid reflection of "what Microsoft says". It's the sort of received wisdom that can stifle creativity and innovation.
There is always room for improvement, and both camps will argue that their way is better - I am quite happy treading a nice easy [path down the middle 😛
Regards,
S Armondi
December 1, 2008 at 2:50 am
I have not been around that much to notice the two camps that the author is talking about but I can see the points.
Where I have been for the last two years, we use sql a lot and .net a lot. We use each platform where we think it is best and we do not really have any sides of sql peeps or .net peeps. Most here does both sql jobs and .net jobs at the same time and everyone has some responsibility areas. Then we have network admins etc responsible for the hardware.
December 1, 2008 at 5:15 am
From my experience, a lot of the 'data dump' brigade are developers (particularly web) who have written front ends for other databases (e.g. MySQL) and hence adopt a 'lowest common denominator' approach. That is, they assume that the database server is unlikely to be able to do something, so if you can do it in the application then do so.
Derek
December 1, 2008 at 5:22 am
I like the comment about a solution needing to be better than the orthodox solution. Too often an alternative is adopted without baking the whole cake.
As for the dumb database, I'd hate to see us go back to dBase, or as more likely, MySQL. As a DBA I'd like to see developers use the platform, but if all they want is a data store that's ok too. I suspect over time they are going to want to use those other 'extra' features than seem frivolous in the beginning.
I blame MS for a good bit of this confusion. It's a combination of not explaining loudly enough that best practices aren't always best and not tiering their recommendations (ie, for 1-10 users do this, for 10-100 users do that, or something along those lines).
It's part our fault too. Too much dogma, too much rigidity, not enough thinking, and few DBA's have written data access code to understand the work involved. Equally it's the fault of developers, who think that any time spent on data access is wasted, when the data is arguably the only thing out of their effort that matters?
It's a subject that both intrigues, infuriates, and exasperates me! It goes back to whether we agree that the orthodox solution is the minimum requirement, and I don't think we (DBA vs Developer) agree on that at all.
December 1, 2008 at 6:10 am
I suppose its my age and just having been in this business way too long, but I never thought I would work long enough, let alone live long enough, to hear tech people talking about the data layer removed from the application, or vice-versa. I suppose this is also why there are larger numbers of developers I have not hired over this last decade - mainly because I have been stunned at how many know only one side of that coin. Still, I find this really bizarre to even listen to!
The age of dBase (and on to Visual FoxPro) was a good one. You had to know code, and you had to know data and how to make the two work together. Later, as VFP became more useful with SQL backends, it was a rather simple and smooth transition to be able to stay with a familiar application product, and get the muscle of a rich and powerful data backend.
Now this editorial, these times, suggest what? Do developers sit around pondering great applications without any clue how the data backend might work? Do SQL DBAs think about great interfaces to work with their data, if only they knew how to code?
Yikes... This seems like a giant step backwards! Worse, its like going to a baker who will make a cake, and then tell you you need to find a frosting specialist to finish it. Hardly progess. The more we break up what used to be "a developers knowledge base", the more we cripple ourselves - thats my take.
December 1, 2008 at 6:16 am
I think you've hit the nail on the head here Tony. I don't agree with either of the wild fringe groups. I think that well designed apps take advantage of strengths in the areas of development that best benefit the business problem, not simply use tool X or paradigm Y to develop the system.
Unfortunately, I'm now working on a pretty major undertaking that is adopting, whole-heartedly, one of the wild fringe group approaches. We no longer have a database. We have an information persistance layer. Funny thing is, it's still on a relational database management system, SQL Server. I don't understand how ignoring the database will make any issues with it go away?
You mentioned how Microsoft is causing the problem. Part of the issue coming out of MS are the apps they're developing. One of the VP's in charge of about half our development teams keeps pointing at how MS developed their CRM application. It's largely, but not completely, a dumb database. He just keeps saying that if MS does it, it must be OK. It's hard to argue with that except to point out that the system, while it doesn't use stored procedures, has tons of code on the database in encrypted views that do a lot of the work and it has a clearly defined data model that drives the engine... It's all falling on deaf ears because there is a hefty percentage of developers that believe the days of having to deal with all that messy TSQL stuff and confusing normalization are behind them.
I just don't see it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 1, 2008 at 6:21 am
I agree with Blandry - to be considered anything more that an averagely good developer more is needed that knowledge about your subject area. There is a need for a wider appreciation of how things work together - different technologies, front, middle and back end, etc... decisions should be taken based on 'this is the best tool for the job' rather than 'this is the tool I know hence it will be better than everything else'.
In my experience, far too many developers think they are great because they know the .Net framework inside out, or know c++ inside out but have absolutely no knowledge (or indeed wish to have any) of the technologies for the back end, how they work, how to use them, which are best suited for what, etc. Technology is moving too fast to only know one side of it!
December 1, 2008 at 6:51 am
I agree with Tony, and others, that you need to evaluate all options and choose those which create the best application. I used to be a do it all in the DB guy, and frankly, that is still easier for me, but I have learned that many times complex business logic is better handled in a Business layer.
Of course, in my mind, stored procedures are the way to go for data access for security purposes. Not because you can't secure an application, but because exposing tables directly means users can use Access, Excel, etc..., to read and manipulate data. This is also why I use triggers for logging changes to data, even if just to protect me from an ad-hoc update/delete that I might run.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 1, 2008 at 6:54 am
Re Jack's point on triggers - watch out for a forthcoming article on generic triggers, which will (hopefully) address some of the business layer/database logging debate.
PS - this will be my first article for SSC, so please be gentle with me 😉
Regards,
SAM
December 1, 2008 at 7:06 am
Having developed using both of these extreme paradigms, I can honestly tell you that having your business logic primarily implemented in the database is usually the best way to go. If not completely in the database, then surely within some web service logic that separates the data access and the client.
The reason for this is simple, as a developer one must always assume that someone down the line is going to want to talk to your database. Not implementing your business logic at a layer that lends its self to being opened up to other developers (IE, either stored procedures, views, functions, etc. in the database or at the very least with a robust web service api that implements the business logic correctly).
One problem I have run into is when developers do not structure their databases correctly (for whatever reason, the concept of a relational database seems to elude many developers). They may make a very complex database with weirdly named columns, without constraints, and of course without any kind of business logic implemented at the database level. When someone comes later and tries to interact with this database (as often will happen) the second developer wreaks havoc on the database because they are not putting the correct data in the places where the original application assumes that they are going to be. What's worse, is that without a good set of database API's for the secondary applications to interact with, the database becomes corrupt because of missed, unimplemented business logic in the secondary or tertiary applications that come down the road.
Telling someone -- HEY if you want to create a sales order, call this Web Service, or HEY if you want to create a PO, just call this stored procedure makes it much easier for future applications to use your database. If your application doesn't require this kind of code reusability, then don't use SQL server. Store all the information in a lightweight database (like SQLite or FirebirdSQL) and leave the real database stuff to the pros. Using SQL Server (or Oracle) insinuates that you are making an Enterprise (or at worst professional business level) database driven application. I think if you are going to play with the big boys, then you ought to program like the big boys do.
Again, anyone can disagree with me, but I think that we need to jump into the 21st century and understand that our database engines today have the ability to do many things, the best of which is that these new database engines allow us to abstract business logic from within our applications, creating a truly extensible framework with which to create our own applications that can live on for years to come.
Aleksei
December 1, 2008 at 7:30 am
I have seen both sides, and its fair to say the answer is in the middle. And as many have said, the real key is to understand both your business objectives and the strengths and weaknesses of the entire application stack.
Developers don’t really like to deal with database stuff, and out of this came the object abstraction tools like MyGeneration, EntitySpace, NHibernate, Hibernate, etc. The modern day developers like to think in terms of objects, and using these tools to add an object layer to the database makes development much easier. And using the UML approach, they like to have the database tables support the higher level objects.
By the way, EntitySpace, which is a very nice product (builds on top of MyGeneration) will generate stored procedures for the typical CRUD processes – very nice. [much better than NHibernate]
From an architecture approach at the database level, typically you created the db schema based on the business objectives. Now, with the database object abstraction tools, developers come in, use these tools to actually create the db schema, and literally take the stand that they don’t need database architects/DBA’s. The JBOT approach (just a bunch of tables). The problem is that the database schema tends to get ugly, grows in an ugly manner, and is difficult to optimize. (I just finished a project to try to clean up such a mess).
I have found the best approach is to derive the db schema first, from the business objectives, and then use the Object abstraction tools to access the database - but not create it. The business rules/logic can be in either place, based on the business driven goals.
The object tools are great, if used wisely. But they need to be managed, and not used blindly. And realize that they have limits. Like any tool, you need to read the manual.
The more you are prepared, the less you need it.
December 1, 2008 at 8:12 am
Good article.
I've done a relatively extreme version of "all the rules and logic in the database", and a middle-of-the-road version, where some of the logic and rules are in the db and some are elsewhere. Of the two I've worked with, the first was by far the easiest to develop for. And not just for me, but also for the team that was doing the web pages and reports and such.
I haven't been in a situation where all the rules and logic were in the "business layer", so I can't speak for that model.
The major advantage to having all of it in the database was that all applications had the same rules consistently (we had multiple dev environments and several applications). Another significant advantage was that we had one place to look if we needed to debug or change a rule. Didn't have to worry about, "is it in the database or in the applications?" Had a dozen applications, but could change the rules for all of them by changing the rules in one place.
If I ever work in a business where the business rules never change, that won't matter much. But I don't think there's ever been such a business, or, if there was, it probably lasted all of 1 week.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2008 at 8:20 am
Good article. I've found this case to be true in purchase applications and not just in-house development. I've gone to blows with more than one vendor after their finding that their security scheme involved granting DBO rights to every user in the database, and then "handling security" in the app.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply