Convincing a die hard DBA to use an ORM for the majority of CRUD vs Stored Procedures, View, and Functions

Question

I have been working with NHibernate, LINQ to SQL, and Entity Framework for quite some time. And while I see the benefits to using an ORM to keep the development effort moving quickly, the code simple, and the object relational impedance mismatch to a minimum, I still find it very difficult to convince a die hard SQL dba of an ORM's strengths. From my point of view an ORM can be used for at least 90-95% of all of your data access leaving those really hairy things to be done in procedures or functions where appropriate. I am by no means the guy that says we must do everything in the ORM!

Question: What are some of the better arguments for convincing an old school dba that the use of an ORM is not the absolute worst idea ever conceived by a programmer!

Answer 1

Explain to them that creating a stored procedure for every action taken by an application is unmaintainable on several levels.

1. If the schema changes it's difficult to track down all the stored procedures that are affected.
2. It's impossible ensure that multiple stored procedures aren't created to do the same thing, or if slightly altering an existing stored procedure is going to have serious ramifications.
3. It's difficult to make sure that the application and database are in sync after a deploy.

Dynamic SQL has all these issues and more.

Answer 2

If you want to convince him, first you need to understand what his problem is with use of an ORM. Giving you a list of generic benefits is unlikely to help if it does not address the issues he has.

However, my first guess as to his issue would be that it prevents him from doing any optimisation because you're accessing tables directly so he has no layer of abstraction behind which to work, so if a table needs altering or (de)normalizing then he can't do it without breaking your application.

If you're wondering why a DBA would feel like this, and how to respond to it, then it's roughly the same as him coming up to you and saying he wants you to make all the private fields in your classes public, and that you can't change any of them without asking him first. Imagine what it would take for him to convince you that's a good idea, and then use the same argument on him.

Answer 3

Procedures used to be more efficient because of predictable caching mechanisms. However, many DBA's overkill the procedures, introducing lots of branching logic with IF commands, resulting in an scenarios where they become uncacheable.

Next, procedures are only useful if you plan to span data logic across multiple platforms; a website and separate client application, for example. If you're only making a web application, the procedures introduce an unnecessary level of abstraction and more things to juggle. Having to adjust a table, then a procedure, then a data model is a lot of work when adjusting a single model via the ORM would suffice.

Lastly, procedures couple your code to your database very tightly. If you want to migrate to a different database you have to migrate all the procedures, some of which may need to be heavily rewritten. This sort of migration is significantly easier with an ORM since you can yank out the backend and install a new one without the frontend application knowing the difference.