This project is read-only.

DBAs & Devs: Brothas from anotha motha

We've had a lot of discussion with the advisors on the project about the need for guidance to help devs and dba's get along better on projects. To address this we would like to have a topic that gives these two groups the ability to leverage each of their strengths to, which should make the project and the ultimate solution more successful.
  • What mutual understandings have you seen work on projects?
  • What type of guidance is needed for each of these groups?

Please leave a note, and we'll incorporate them as we begin this topic.

Last edited Jun 30, 2009 at 11:26 PM by donsmith, version 1

Comments

tymberwyld Jul 21, 2009 at 3:34 PM 
I've been both a DBA and a DEV for a number of years. Currently I work in a company as a developer using ASP.NET. Unfortunately we're working with some very junior level DBA's and one Senior level DBA with antiquated design techniques (this goes both ways, DBA's who "think" they are Data Model Architects just because they are DBAs). So, we've had our hands completely tied in what our applications are "allowed" to do: No dynamic SQL, no Data Paging allowed, no Xml data types, no Xml processing, etc. This makes it extremely hard to leverage future technologies such as LINQ and not to mention the performance of the applications suffer becuase we're doing "pseudo" data-paging on the web server and cannot take advantage of the speed or SQL Server 2005. So basically, I feel like I'm running a Ferrari web app on top of a Yugo engine. Is there anyway to get DBA's to understand that if dynamic sql is written correctly (parameterized and etc), that there is no harm in this and that data-paging in this manner is completely acceptable?

larrywa Jul 2, 2009 at 7:18 PM 
From my experience I have found that there are developers that are good at their T-SQL and relational database design skills, and then there are those that just believe they are good because whatever they have done in the past didn't seem to break anything.

So what happens is after an app is designed, built and deployed (note there that some DBAs are left out of these loops), then reports start coming in about poor performance and they point to database issues. Lesson learned, bring in the DBA at the beginning, but even further than that, if the devs are going to be writing their own stored procs, they should be reviewed by an DBA.

Most DBAs know that their skills have been relevant for a really long time because how much has T-SQL and relational design changed in the last couple of years? Now look at how development tools have changed and those technologies and you'll see developers wanting to move ahead with new things whereas the DBAs are just not inclined to jump to these new technologies. It is out of their comfort band, and more importantly it is out of band as far as best practices and expected results.

In regard to mutual understanding a dba must be given the functional specs and be allowed to design the relational portion of the database. The developers should be allowed to write their own stored procs if the dba gets to approve them. Generally DBAs need to know 'what' you want to get back, don't go telling them 'how' you want to get it back.

In regard to guidance, for the DBA, they need to understand the relationship of the new technologies with what they are used to, TSQL, performance, tuning.

paulgielens Jul 2, 2009 at 9:25 AM 
There’s no such thing as Dynamic SQL.

DBAs want explicit control over access paths. For this purpose they review stored procedures and monitor database access while applications move through the different staging areas (development, test, acceptance, and production/maintenance). DBAs are good at this!

Query execution with LINQ to Entities is, in the end, not much different from regular SQL. A LINQ query is converted to a command tree and than executed against the database. This leaves room for developers and DBAs to, in a joint effort, stay in control regarding access paths.

Security.

DBAs want to control applications and/or role based access to the database and or tables through the use of stored procedure and configuration management. This is relevant in an enterprise where multiple applications integrate by storing their data in a single shared database. For this scenario stored procedures make perfect sense.

In scenarios where your applications has exclusive access to a database (SOA) this level of security can also be achieved through restricting access for other applications. Still this requires your application to be able to query your database directly using SELECT/INSERT/UPDATE/DELETE statements.

It might be a bit more nuanced. The Entity Framework uses the stored procedure sp_executesql instead of the EXECUTE statement for executing T-SQL strings. Some DBAs have no objection to execute this stored procedure.