Creation of objects like tables and indexes are fairly essential, even if the code has to be authorized or created by the dba. What other areas normally carried out by dbas should the accomplished developer be aware of?
+1
A:
Optimization. Your code allways should use as little resources as you can achieve.
Pawka
2009-08-24 10:44:38
I like this answer! (although I can think of scenarios where resource usage isn't an overriding issue)
DBMarcos99
2009-08-24 10:48:26
+7
A:
A developer is responsible for doing everything that makes his code a) correct and b) fast.
This of course includes creating indexes and tables.
Making a DBA responsible for indexes is a bad idea. What if the code runs slowly? Who is to be blamed: a developer with bad code or a DBA with a bad index?
A DBA should convey database supporting operations like making backups, building the infrastructure etc, and report the lack of resources.
He or she should not be a sole person for making the decicions that affect the performance of the whole database system.
Relational databases, as for now, are not yet in that state that would allow splitting of responsibility so that developers could make the queries right and the DBA could make them fast. That's a myth.
If there is a lack of resources (say, an index makes some query fast at the expence of some DML operation being slow), this should be reported by a DBA, not fixed.
Now, it is a decision making time. What do we need more, fast query or a fast insert?
This decision should be made by the program manager (and not the DBA or developer).
And when the decision is made, the developer should be given the new task: "make the SELECT query to be as fast as possible, taking in account that you don't have this index". Or "make an INSERT query to be as fast as possible, taking in account that you will have this index".
A developer should know everything about how a database works, when it works normally.
A DBA should know everything about how to make a database to work normally.
The latter includes ability to make a backup, ability to restore from a backup and ability to detect and report a resource contention.
Quassnoi
2009-08-24 10:48:00
Thx for your input. I can think of many scenarios where the dba is responsible for query optimization - the developer leaves, the db structure changes, functionality is changed, new indexes are required etc.
DBMarcos99
2009-08-24 10:56:06
`DBMarcos99`: everything you just described (except when developer leaves) involves decision making. This is not `DBA`'s responsibility, this is program manager's responsibility.
Quassnoi
2009-08-24 11:09:45
@Quassnoi: I disagree; being a DBA is “ALL” about decision making and responsibility. You cannot run every single minute decision by a program manager, nothing would ever get done. Business, which is what we are all in after all, is about delivering results and this is what the role of DBA facilitates.
John Sansom
2009-08-24 11:51:37
`@John`: assume a situation. A database report, which relies on a certain query, is slow. This report can be made faster by creating an index, but this will make `DML` on the involved table slower and the program will be less responsive for the users that fill it with data. Is it a `DBA`'s task to make this decision?
Quassnoi
2009-08-24 12:01:37
Unfortunately I cannot provide you with a definitive answer because of the bizarrely shaped example. In my opinion, the proposed choice would not even arise, given that there would potentially be a host of viable and alternate solutions.
John Sansom
2009-08-24 12:28:11
`@John`: what decision should `DBA` make then, in your opinion? Could you please provide your own example of a "single minute decision" **that affects the end users** (that line is important) but should be made by a `DBA` rather than a developer or a program manager?
Quassnoi
2009-08-24 12:38:16
Sounds like the Project Manager determines the responsibilities of the programmer and the dba and then becomes the referee when they can't get along?
Jeff O
2009-08-24 12:59:40
`@GuinnessFan`: In `Joel Spolsky`'s opinion (that I share), program manager should, alongs with other things, "coordinate teams": http://joelonsoftware.com/items/2009/03/09.html Unfortunately, in current world the `UI` applications (which he mostly writes about) can usually be developed in absolutely optimal way, while the databases can only be developed in Pareto optimal way (when you cannot improve one thing without worsening another one). I think that Pareto optimizing the system is certainly a program manager's task. This can be done by both determining the responsibilities and ...
Quassnoi
2009-08-24 13:27:32
Developers should state logical aspects of indexes (what columns are indexed, uniqueness, structure if appropriate). DBAs should be responsible for physical aspects (eg file/disk location, growth factors etc).
Gary
2009-08-25 00:42:21
+5
A:
The ins and outs of database storage and optimization are huge. Knowing how to index and partition tables well is invaluable knowledge.
Also, how to read a query execution plan. SQL is such a cool language in that it will tell you exactly how it's going to run your code, so long as you ask nicely. This is absolutely essential in optimizing your code and finding bottlenecks.
Database maintenance (backups, shrinking files, etc) is always important to keep your server running smoothly. It's something that's often overlooked, too.
Developers should know all about triggers and stored procedures--getting the database to work for you. These things can help automate so many tasks, and often developers overlook them and try to handle it all app side, when they should really be handled by something that thinks in sets.
Which brings me to the most important point, database developers need to think in sets. To often I hear, "For each row, I want to..." and this is generally an alarm in my head. You should be thinking about how the set interacts and the actions you want to take on entire columns.
Eric
2009-08-24 10:49:59
+1
A:
One thing that currently springs to mind is how to navigate and understand the information that database "system" tables/views gives to you. E.g. in sql server the views that are under the master database. These views hold information such as current logins, lists of tables and partitions etc. which is all useful stuff in trying to track down things such as hung logins or whether users are currently connected etc.
Calanus
2009-08-24 10:50:28
+1
A:
Relationships of your tables. You should always have a recent printout and soft copy of your database. You need to know the primary keys, foreign keys, required and auto filled columns, without that I think you can't write efficient queries or make sure your database is carrying only what it needs.
I think everyone else covered it.
mcauthorn
2009-08-24 11:05:12
+2
A:
I would recommend developing an understanding of the security architecture for the relevant DBMS.
Doing so could facilitate your development of secure code.
With SQL Server specifically in mind for example:
- Understand why your “managed code” (such as .NET CLR) should not be granted elevated privileges. What would be the implications of doing so?
- What is Cross-Database ownership chaining? How does it work?
- Understand execution context.
- How does native SQL Server encryption work?
- How can you sign a stored procedure? Why would you even want to do this?
- Etc.
As a general rule, the more you understand about the engine you are working with, the more performance you can squeeze from it.
John Sansom
2009-08-24 12:50:45
This is a nice example of what I consider to be *really* a `DBA`'s task which developers should understand too. +1.
Quassnoi
2009-08-24 12:58:05