Whose job is it anyway?

Well, today has definitely been enlightening. I've been around and around with a few folks (mostly Tim Huckaby and Richard Campbell – who are BOTH great friends and people whose opinions I greatly respect!!!) and I've come a conclusion… Paul started down this line of commentary here: https://www.sqlskills.com/blogs/PAUL/post/Mandatory-SQL-breathalyzer-test.aspx and this was in reference to something I was saying in our long thread…

But, where I'm at is somewhere that I'm not sure entirely understand AND I need your help. I need to understand where and with whom do you think each role should be…. Here's what I think:

The DBA has to be able to trust the code that a developer submits – plain and simple (yes, this is absolutely true). But, (IMO) it’s still the database developer that writes the code and they’re the ones that need to know what goes on IN the db and the best practices of database design/development. The database developer should know how to create scalable schemas, they should know best practices, they should have a fundamental understanding about their specific platform as well (whether it's SQL Server or DB2 or even that O one :)). At a minimum, they should have a basic understanding of things to avoid… But, I'll give you this – it's not easy and it's not like the best practices really jump out at you. I'll even argue that some of the defaults INSIDE of SQL Server itself aren't ideal (ah, let me give a list: database size and autogrowth settings have HORRIBLE defaults, the PRIMARY KEY of a table is – by default – enforced with a UNIQUE CLUSTERED INDEX (and, well, there are many cases when that's not the best CLUSTERING KEY even though it might be a completely legitimate PRIMARY KEY), databases default to the FULL recovery model but run in a pseudo simple mode where log truncation on checkpoint occurs automatically (ah, until you do your first backup…)). Ugh, many of these things can catch you out and can completely cause you grief… and some are horribly complex to change after the fact (like what is your PRIMARY KEY and/or what is the index type of a key). So, it's definitely NOT easy. BUT, back to my original point – someone needs to know this and I need to find out who YOU think should? I think it's the database developer and NOT the DBA. I think there should be a somewhat blurred line between the database developer and the DBA. IMO, the DBA needs to know operational stuff (backup/restore, availability, security, maintenance… and, this is a HUGE list of things to really understand in and of itself – especially when you're managing this for many servers), it’s not their job (IMO) to know TSQL coding, error handling, stored procedure recompilations (although I can debate both sides of this last one), I would even argue in many cases it's not the DBA that should make the primary decisions on base indexing – I *REALLY* feel that it is the database developer – they should know the database schema, know the general access patterns AND know how the platform uses those objects so that they can make better design decisions EARLY. My favorite line is "the sooner you begin to code, the longer it's going to take" and it's really true.

However, with all of the discussions I've had today (and, I'm debating on posted an edited version of each of our rants… it's colorful in places :)), it feels to me like (and what I’m seeing more and more and more) is something that got mentioned early on in the thread – the database is “just plumbing” and the real application is in the client. Is this true… are there really only two players:

The Application Developer (who doesn't touch (because they're not allowed) OR possibly because they don't want to) the database
The DBA (who doesn't allow the AppDev to touch the database BUT doesn't touch it for them either because they're too busy and/or too protective over changes??)

Seriously, IS there a database developer in this formula above? And, is it a distinct person? I don't think so (personally)… I think the Application Developer SHOULD be the database developer. I think the AppDev should do EVERYTHING in terms of schema, keys, indexes, procedures, etc. AND once in production the two should work together to handle efficient and effective change control policies and procedures. There is a way to make this work. But, when good design doesn't exist – the database performance/scalability suffers, bad practices fester and then everyone blames SQL Server and no one really makes a change. In some cases, people just accept the poor performance as "how it works".

Thoughts??
kt

24 thoughts on “Whose job is it anyway?

  1. Great article, Kimberly (as usual). I’m of the opinion that a database developer is worth their weight in gold for all but the smallest projects. Here’s why:
    – Writing code that not only runs, but that runs efficiently, scalably, and integrally on SQL Server takes an awful lot of expertise. If SQL Server isn’t your primary focus, it’s easy to get lost
    – Windows or Web programmers live in an entirely different world. This isn’t a criticism – it’s the nature of their job. They need to know as much as possible about collecting and displaying data and interacting with the user. They don’t, and in most cases can’t, understand the underlying principles of efficient data access and schema design.
    – The inability of [i]most[/i] developers to properly interface with SQL Server isn’t a new problem. Same goes for the inability of database administrators to interface with developers. A database developer understands both sides. They don’t understand database administration as well as a DBA, and they don’t understand the client tier as well as the application programmer. But they understand what a DBA means when they talk about index optimization. And they can write pseudocode, or skelton code, for the application developer that just can’t see how to make it happen, and save both DBA and development teams a whole lot of trouble trying to bridge the gap.

    What do you think?

  2. Definitely. However, I’m hearing more and more that people just "expect SQL to work" and they don’t really want to learn what’s going on… like it’s just "plumbing" and this is what’s shocking/confusing me. There are some things (like you mention – for the smallest of projects) where design might not be as critical BUT, for anything that’s going to be public facing OR even important for a number of users internally – it just takes some good design to make it scale. I knew we had issues with developers/DBAs getting along but I’m confused by the "who should know what" question and people pointing at each other (in some cases no one seems to be saying THEY own it). In fact, I got an email from someone who listened to my Indexing podcast (they’re a reluctant DBA that’s just managing SQL as a part-time thing) and they decided to go poking around to look at their indexes and found that they had absolutely NONE. They said their developers had told them "SQL did all of that for them". I’m not saying it was intentional but it’s clearly indicating a problem in terms of what people think SQL is doing and/or how much people think they need to know about what’s going on "in the backend".

    I guess the problem is even bigger than I thought… I’m really intrigued by this.

    I wonder what the solution(s) are? I keep focusing on best practices… but, a lot of people don’t realize that they need them until they’re in production. Then, it’s often too late. Even worse (especially in this economy) the cost of re-architecting and/or fixing the problem is so high that it might not get done. Everyone suffers.

    It’s almost like the breathalyzer comment I made yesterday (Paul posted on this here: http://www.sqlskills.com/…/…-breathalyzer-test.aspx). Before someone creates a table (because it’s just so darn easy to do it), they should be forced to take a "SQL breathalyzer" test to make sure that they don’t hurt themselves. Which I say in jest, but the repercussions of bad design are COSTLY – which really does hurt….

    I think this discussion is going to continue. Thoughts? Suggestions? Solutions? Thanks Aaron!!!
    kt

  3. Good topic .. :)

    But question on Indexes, you think developers should be allowed to handle this? The Developers I have ran into don’t try to figure out indexes and just create index for each query they have and everything works great until they start running into update/insert issues.

    You also mentioned that it’s not DBA job to know TSQL, aa my co-worker would love your right now :). But as DBA we are always writing scripts that help to better manage SQL Server and such. So I think even thought DBA shouldn’t have to know what is in a store proc; they should know TSQL. Maybe know every little detail. But it shouldn’t be I don’t know because it’s not my responsibility.

    I agree with you Application Developers should be Database Developer; but problem I see with that is most AppDev have a hard time making a transition from Application Development to DB Development. What I mean is I had a developer saying this one procedure is running painfully slow; so I told her lets look at what you are doing it turned out the application had seven or eight nested loops with in store procs updating information in tables. When I asked the developer why it wasn’t done using sets, her answer because this works so why should I worry about it? It’s not my code it’s SQL …

    Other issue I keep running into, Developers come to me for advice on how to do stuff. As soon as I recommend something I get told "how about you write it, your the DBA".

    Best I have heard is "SQL is set it forget it technology", so I am always seeing poor planning, poor deployments, and horrible database development where I work. I think people have it in their mind SQL Server isn’t good so don’t care, most laugh I gotten is "SQL Server is just a fancy spreadsheet" heh.

  4. OK, this is great. I hadn’t heard your phrase "set it forget it technology" but, you’re right this is more and more what I’m hearing…

    And, I definitely agree that DBAs need to know some TSQL – definitely administrative stuff and I’d probably argue (as you do) that a good DBA probably knows quite a bit. I was trying to focus more on complex joins, CTEs, error handling and, in re-reading my post, I may have over-generalized my separation. But, I definitely agree that the lines are blurred (TSQL being a big blurry line there :)).

    Now – set-oriented programming and AppDevs. I’m not sure I want to open up this can of worms but, you’re right… this might be a hard transition. I (personally) have an xbase background so I *was* of the mindset "goto top for x = 1 to EOF do begin…" and today’s programmers really need to combine set-oriented with pagination techniques (to allow a rowset to be displayed on multiple pages).

    Finally, indexes… I knew I was going to spark a few things with this one but, YES I think good indexing starts with database design/development and for many of the core/base indexes (the CL index, the PK, the UKs, manually indexing the FKs and creating indexes on some of the most selective/critial SARGs, yes). From there, it can get more complex. In fact, there’s a SCIENCE to indexing A QUERY (for SURE – it’s to create a nonclustered covering seekable index) but in the end, it’s an ART to properly indexing a server. So, I think the developer should have something to do with setting the database up better… But, ultimately, the DBA will need to have some involvement with adding/removing indexes and possibly creating better indexes. There again, the line is horribly blurred.

    So……….. keep it coming! This is excellent! I’m waiting for [even more] horror stories as well! ;)
    kt

  5. I think the number of roles depends on the way the company is set up.

    Like most smaller development companies, our DBA is responsible for other jobs too. Our DBA is also the Network guy. This was the case at my previous jobs too.

    For these amaller development companies, there are three roles:
    – the DBA, who takes care of backups and the like, but does not know any detail of the applications being developed. Because his role is not just DBA, he does not have time to know the databases in detail.
    – The Application Developer, who has no role in modifying the tables, indexes, etc. They do not access the database directly, but via code written by other developers (e.g. by using entity framework). Application Developers are often the more junior developers.
    – The Database Developer, who is responsible for the general design of the application, and designs the database structure to suit. They need to either know how to determine table structures, indexes and like, or must follow a set of guidelines defined by someone who does.

    I am in the Database Developer role. I recently been working through a speed issue with our SQL Server. This has involved working through what is happening on the server with our DBA. He extracts the server statistics and together we analyse what they mean. I combine this information with the results of execution plans, and then adjust the indexes and stored procs to reduce the problem. The DBA does not understand the context of what is trying to be achieved by the application – this is information only known to the Database Developer.

    As it happens, the DBA believes the problem is not due to my own database, but is due to SQL Server configuration issues and excessive activity on other databases on the SQL Server. Our DBA can see this activity and can interpret it (to a degree) – but those databases are not ones that I manage. They are mostly databases that I don’t even have access to.

    I think the worst databases occur when an application developer is allowed to design the database. It should be a job for someone who knows something about how to design a database.
    As a Database Developer, I have restructured my tables so that queries can be executed more easily by SQL Server. I think that a DBA alone cannot do that – I don’t think that they get a detailed enough picture of the requirements of the application.

  6. Hey Kimberly .. I came from xbasic too .. and always will be basic guy LOL. I never got over VB6; have done Java, .NET etc. But VBScript = FTW :). Since I have a developer background I am also aiming at knowing the developer side of TSQL not because I want to go back to development. But I wrote an article on SQL Server Central last Nov; and my T-SQL was correct few times when I was demonstrating an idea on Normalization. They showed me many ways to write more efficient SQL that I have not run into while doing just DBA tasks. So I like the idea of splitting the DBA and DB Developer roles apart to split the responsibility but I think of both DBA/DB Developer know whats in each others basket it helps just like that Movie you/Paul posted hehe. *rant off* .. Thanks for great post again :).

  7. As a development manager/dba during the dotcom boom who is now a web applications consultant for small to medium size businesses with small IT departments, the setup i prefer is as follows:

    App Developer builds schema and codes SQL in dev env with advise and consent of DBA. DBA deploys to production. Thus DBA and App Dev must know SQL. DBA has final say of course and is responsible for enforcing best practices since they must regulate entire server. App Dev does not have rights to prod. Ideally, DBA knows some SQLCLR as well or at least knows how to monitor performance.

  8. Actually, I think the last comment (n2itiveus) sums it up fairly well – and I concur (for the most part). The line is blurred – a DBA does need to know what’s going on in the database and I think they have ultimate say over what can go in it. But, I still stand by (and agree) that it’s the AppDev that actually implements it. It’s true that at smaller shops it might be the same person that does all three but as you approach the enterprise the roles are most distinct and there’s just no way that a full time DBA can also design/develop all of the databases that they need to manage. Management, automation, security, disaster recovery (relatively consistent testing of backups too) is a full time job and is a lot to know. And, the application developer is the one who REALLY knows what the application needs and is going to be doing. They’re the ones that have worked with the users (as painful as that may be :)) to really get to the bottom of the requirements, etc. They’re really the only one that can do a good design. So, I’m back to thinking that there are really three sets of KNOWLEDGE areas but now I’m not so sure that too many people actually focus on the knowledge required of a database developer…

    the DBA
    Database Developer
    Application Developer

    But, now the question shifts – how much do you actually think a "Database Developer" (whomever actually does it given that not all companies have all three) *NEEDS* to know about SQL Server to create a good database? Is SQL Server just plumbing… OK, I think I need to create a survey too (Paul just created one and it’s pretty cool). Easier to respond to. But, keep the comments coming. This is a great discussion!

    Thanks for reading – and especially THANKS TO ALL OF YOU for commenting! I’m really enjoying this thread. Oh, and if anyone has a lot of experience with the EF (Entity Framework) check out my last post… I’m really interested in some comments there too. I really can’t believe that default!!

    Thanks again,
    kt

  9. I think it all depends on the IT culture within the company. As you have mentioned, sometimes the DBA doesn’t allow the AppDev to touch the database. What to do in these cases? The time constraints are still there for the application, so the TSQL coding must be done and the DBA must find time to do it. However, if he’s too busy, either hires someone to help him or gives up and allow the developer to do the work.

    In cases when the DBA doesn’t mind allowing the developer to do the database coding, a best option would be to allow the developer to do it. The developer knows exactly what the application needs and can get it done a lot faster and without waiting for the DBA to do the work. However, the issue with this approach is that, even though developers usually know how to do the database coding, the main objective for them is to hit the database and get the task done without worrying too much about efficiency or best practices in database programming.

    Either way, I think that DBAs should be the ones creating the databases, tables, and so on. This way, the DBA controls security, normalization, organization, and objects identities. Developers should have access just to the minimum necessary to get the job done, what also prevents unauthorized attacks via the application.

    By Sam Moreira

  10. I don’t see why one person can’t do it all. There isn’t one good reason why a dba shouldn’t know TSQL inside and out. Here’s why…

    EF’s and various other ORM shenanigans all have downfalls. Read Ted Neward’s blog on The Vietnam of Computer Science for a biting critique. But these technologies all exist for a reason. They are the manna that falls out of the sky when software architects pray for that mythical "database developer".

    A dba who puts up defensive walls and makes life difficult for the rest of the organization is asking for this fate to happen to them. A dba who strives to be a good communicator and a team player can do much better. And the number one priority of a dba should be to instill good SQL coding practices throughout the organization. This will prevent developers from getting frustrated, hating databases, and turning to ORM and code generation as their solution. If the dba doesn’t know SQL then all is lost.

  11. I don’t disagree that one person can’t do it all BUT, the more databases/servers that a DBA manages along with more and more apps – just means that things won’t get done. Good database design isn’t something that a full-time DBA really has time to do (for a whole bunch of apps) ALONG with management/operations of the existing apps. But, that’s more of an enterprise view… If the DBA is required to write all of the SQL as well as manage the database then this is probably what leads some DBAs to create these defensive walls (because they just don’t have time to do it right AND they know what happens when it’s not done right).

    As for the "mythical database developer"… I’m not saying that this HAS to be a separate person but my argument is really just that someone needs to have the knowledge of the inner workings of the database and, more importantly, someone needs to care. (this is where I think things are failing)

    What I think is happening is that people are trying to get around the DBA, not wanting to learn SQL (or not knowing that they NEED to know more SQL to make things truly scale) and [b]some of these methods are actually making things worse[/b].

    Still these are all great points. I’m still not sure of the solution but I love these posts! It’s definitely an interesting topic/debate.

    THANKS!!
    kt

  12. I started out as a app developer who liked to work with databases. Over the year I started to specialize and I find myself in a pure database developer role. I don’t touch c# anymore and we’ve got a team of DBAs to handle the servers.

    One thing I’ve learned about my days as an application developer – I thought I knew a lot because I could write fancy T-SQL, but in hindsight, I knew absolutely nothing because I didn’t understand the internals. I didn’t know the hows and whys of performance. I probably would have thought it was perfectly ideal to use a GUID as a CI key.

    No offense to the application developer, but I’ve found this is quite typical – and for good reason. To dive deeply into SQL Server is a full time job. The .Net developer has enough on their plate to deal with. This is where a professional DB developer comes in. It’s not the DBA’s job. They’ve already got a full-time job trying to keep the servers ticking.

    The name of the game (or one of the games) is scalability and you need to understand what’s going on before you can achieve it.

  13. I read all of the comments here because I was too frustrated over with a DBA yesterday. I used to work for a oil and gas MNC and the developer has almost full rights to the database except remote login SA rights, that said app developer has dbo rights to the database.

    Now that I am working for a public company , all of a sudden app developer doesnt have dbo rights, and it took me 1 DAY and a half just to alter 1 column from identity to non identity and update 1 record 1 column in PRODUCTION just because the DBA refused to change the identity on production using SQL designer interface.

    Now….is this necessary? In the end it’s who will be accountable for ? If DBA is just a mere administrator they should give the app/database developer the dbo rights since they wouldnt do the job and wouldnt be held accountable for ? No ?

  14. To add things to worst…the app just rolled into production its a fresh database, 1 user with no impact and risk.

    the dbas are over controlled sometime…and even with the risk and impact assessment with my team lead approval, they still refused to make the change manually using the designer and demanded a script is a must…why????????

  15. This isn’t my first time to your site Kimberly, but it’s the first time I’m leaving a reply. I really enjoy reading your posts and have learned so much from visiting here.

    I manage a database team at a small company. We have good relationships with the application developers. Even though I have a full time database developer on my team, we still leave it up to the application developers to create or modify stored procedures, tables, functions, etc for the application. We do this mostly because of what someone mentioned above – they are the most in tune with what the end user wants. My team could be more heavily involved in that too but we choose to let the application developers give us the run down upon delivery of the objects to us. It generally speeds things up and when you’re working in an agile environment, it’s important to keep things quick while maintaining high quality.

    Before any SQL objects move to anything other than development servers, my team reviews the work. We take care of the performance tuning, index usage, statistics, etc to make sure the query won’t bring down the production servers. We give feedback to the application developers and tell them what we tweaked, which generally gives them a better sense of how useful it is to know SQL at a deeper level. There’s a mutual respect between the teams. They handle figuring out the requirements and creating the initial code and we make it as efficient as possible for them. I think it’s a good setup and one that’s worked well for us. In the process, both sides learn something.

    I’m happy to say that in the years that I’ve been here, the education paid off. The application developers give us much more complicated SQL code now. I love it because it means we can dig even deeper into other things – either tuning things more or other developer work – plus we’re sharing the knowledge about SQL. Our last bit of involvement is always in the mock that we perform before a release goes to production. We watch the SQL servers for activity on the mock server environment, just to make sure we made the right calls. At that level of analysis, we’re the best team for that. Depending on the changes, either a database developer or DBA will participate in that.

    Hope this adds some spice to the mix!

  16. As soon as I read "the sooner you begin to code, the longer it’s going to take" comment, I couldn’t stop laughing, because it reminded me the "good ol day" as being a developer for 12+ years (back in the 80s, we called us "programmers"), which many time, my lead often told us "You guys started coding, I go to the Users meeting to find out what they wanted!".
    Your point is 100% corrected, but nowadays, many big blockhead developers think they know everything, since "the codes work perfect in prototype!", and in the race for beating the deadline, for being the first to come up with the idea & prototype that "works", they often ignored the rest of the interfaces – one of those is the database (regardless whether it’s an old "clunker" like the IMS DB/DC, CICS, DB2) , the so call O-Larry, or SQL Server. After 22+ years in the IT, I know this formula is almost nail on the head for many problem existed: 75% is due to poor coding, 15% is due to the hardwares, and 10% is the databases (which more than half of it related to the management itself).

    Sam

  17. I’ve actually worked as both a db developer and DBA. I started as a developer and moved into the DBA role at different companies. I’ve found that having a development background makes me a much better DBA. I also find that when problems do occur, I can look at the issues from the developer’s point of view too. I view education as a large part of the DBA’s role and I am a firm believer that the more you know, the better work you can do. I try to educate the developers – if there is something I’d like them to change, I explain why. We are also set up so that the developers will routinely run things by me, asking for my input on database design, data types, fields, etc. I have been lucky to work at a place where there is such good communication between the DBA (I’m the only one) and developers. Additionally, I have learned several things from them as well, so the education process is definitely a two way street. Tomorrow is my last day with this company and one of the developers just sent me an email saying I have been of tremendous value. That’s probably the best compliment I could ask for.

  18. Kimberly,

    This is an older post but I couldn’t help throwing a reply on it because a) one of the Dev managers where I work IM’d me a link to it as a means of pointing out an area of dev lifecycle we are still missing (SQL developer) and b) because I think it is still extremely relevant.

    In the 12 years of working with SQL Server so far I’ve learned without question that I could make a career out of doing nothing but fixing problems resulting from a combination of bad database architecture and bad code. In other words, from people creating databases and writing code that had but ancillary training with it, and believed whatever they didn’t know didn’t matter because using all the defaults seems to work fine. After all, you can solve literally any problem that comes up with Google, right? I once worked for a company who pointed to a SQL reference book and said, “That’s the DBA you’re replacing!”

    But it may surprise you to know that over the years of encountering case after case of data files on the C:\ drive and million-row table scans and triggers and cursors a-plenty, I’ve mellowed a bit on my approach to these blunders. It seems that the natural evolution of a company – any company – that begins using SQL server as a platform for their data tier almost always has no subject matter expert on hand when that decision is made. And let’s face it, there’s no deterrent; nothing is stopping you from going ahead with that installation with all the defaults, bad as they are, and no ominous popup message warning of impending doom if they are left where they are. Nothing stops you from creating composite clustered keys with 7 columns (which I’ve seen) or omitting a clustered key entirely on a multi-million row table (seen dozens of times). Why should I hire an expensive SQL DBA when everything works and my C# developers seem to be handling this SQL thing just fine?

    In fact, for literally years, “works” is the only criteria for TSQL code making its way into production. And then one day, after emergency meetings are called when a huge data import from a new client has just caused SQL to crash (read performs like molasses in wintertime and/or throws lots of deadlocks), somebody like me is called in to figure out why. After a few days the consultant presents his report and everybody suddenly realizes what a mess they have on their hands and how deep the problems are.

    Truth is, a scenario similar to this has played out in almost every company I’ve worked for. In the company I’m working for right now we have a team of some 30 developers, and exactly one SQL DBA/Developer: me. I add the “Developer” to my role only because I’m the only one who *should* be writing TSQL code but not because I am. No, the Dev guys still write 90% of the code and it would be impractical for me to believe that’s going to change. There’s no way I can write it all and there’s no way I could even *check* it all, so what to do? The only thing I can. With the patience of a glacier I must educate, gradually enlightening Devs to the wonders of set-based vs. iterative loops, cached, quality execution plans, skinny index keys, triggers are definitely evil and for goodness sake, leave SQL Server setup and database creation to the DBA. Essentially, over a long period of time, I hope to win hearts and minds and make the Devs more SQL-savvy to improve the quality of new code and try to correct some of the deeply embedded problems in existing stuff myself.

    So whose job is it? In light of my experiences I think it’s more important that a company simply recognize that that role *should belong to somebody*, regardless of what actual title the person carries. SOMEBODY needs to own and be responsible for the quality of the SQL database design and code. Its just as important as quality and design checks anywhere else in the application development lifecycle. If a company can simply acknowledge the need for this role in their dev process, and early on, I think they’ve demonstrated great foresight and will most likely bypass a lot of the SQL “growing pains” I’ve had to deal with. The problem is, it’s very hard for companies to see the need for that role when they just start using SQL Server, which is why I think there’s so much bad code and bad architecture out there.

    1. Hey there Bob – All very valid points. Thanks for your message. You’re right – admitting that there is a problem (that the role belongs somewhere and to someone) is always the first step, right? ;-)

      Thanks!
      kt

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.