What are the basic dba skills a developer should learn?

Question

Creating objects such as tables and indexes is quite important, even if the code needs to be resolved or dba created. What other areas commonly performed by dbas should an experienced developer know?

Answer 1

The full capabilities of storing and optimizing the database are enormous. Knowing how to index and partition tables well is invaluable knowledge.

Also, how to read the query execution plan. SQL is such a cool language that it will tell you exactly how it will run your code if you ask it well. This is absolutely necessary to optimize your code and find bottlenecks.

Database maintenance (backup, file compression, etc.) is always important for the smooth operation of your server. This is something that is often ignored.

Developers need to 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 process all aspects of the application when they really need to process what they think in the sets.

Which leads me to the most important point, database developers need to think in sets. Often I hear: "For every line I want ...", and this is usually an alarm in my head. You need to think about set interactions and the actions you want to use for entire columns.

Answer 2

The developer is responsible for doing everything that makes his code a) correct and b) fast.

This, of course, includes creating indexes and tables.

Making DBA accountable is a bad idea. What if the code is slow? Who is to blame: a developer with bad code or a DBA with a bad index?

A DBA needs to transfer database support operations such as creating backups, creating infrastructure, etc. and report a lack of resources.

He or she should not be the only person to make decisions that affect the operation of the entire database system.

Relational databases are not yet in this state, which would allow sharing the responsibility so that developers can make queries on the right and DBA can make them fast . It is a myth.

If there is a lack of resources (for example, the index makes the query fast, if the operation of some DML slow), this should be reported by the database administrator, and not fixed .

Now is the time to make a decision. What do we need more, quick request or quick insert?

This decision must be made by the program manager (not the DBA or the developer).

And when the decision is made, the developer should be given a new task: "Make the SELECT query as fast as possible, given that you do not have this index." Or, "make the INSERT query as fast as possible, taking into account that you will have this index."

The developer should know all about how the database works when it works fine .

A DBA needs to know all about how to make a database work fine .

The latter includes the ability to backup, the ability to recover from a backup, and the ability to detect and report resource conflicts.

Answer 3

Optimization. Your code should always use as few resources as possible.

Answer 4

I would recommend developing an understanding of the security architecture for the respective DBMS.

This can facilitate the development of secure code.

Specifically, in SQL Server:

• Understand why your “managed code” (such as the .NET CLR) should not receive elevated privileges. What would be the implications for this?
• What is a database binding chain? How it works?
• Understanding the context of execution.
• How does SQL Server Native Encryption work?
• How can you sign a stored procedure? Why do you even want to do this?
• Etc.

As a rule, the more you understand about the engine you are working with, the more performance you can squeeze out of it.

Answer 5

One thing that currently comes to mind is how to navigate and understand the information that the database “system” tables provide you with. For example. in sql server - views that are under the main database. These views contain information, such as current logins, lists of tables and sections, etc., which is a useful tool for finding things like logins hang up or current user connections, etc.

Answer 6

Relationships of your tables. You should always have a recent listing and a soft copy of your database. You need to know the primary keys, foreign keys, necessary and automatically filled columns, without this I think that you cannot write efficient queries or make sure that your database carries only what it needs.

I think everyone else has taken it.

Answer 7

A good understanding of the architecture of your database system will certainly be helpful. Can you draw a diagram by heart to show the components of your DBMS and their interactions?