Who needs DBA Skills? [#TSQL2sDay]

T-SQL Tuesday for November 2010

T-SQL Tuesday for November 2010

Databases are doing just fine. If an issue is encountered add more hardware to the mix. If that does not work then add more servers and move databases to it. No DBAs on site, just developers and a business owner that wants to save money.

What? With that first paragraph I can pinpoint at least 3 main reasons why DBA skills are required:

  1. Adding more hardware is just a tactical approach, never strategical as the new one will encounter the same issue the old one did in a matter of months.
  2. Adding more servers can be complex as databases will reside on different boxes. Messing around with linked servers and executing joins across boxes are not the best approach.
  3. Licensing costs go through the roof! Yes. I have dealt with a solution where the Lead Developer decided to add 8 Servers running SQL Server 2005 Enterprise Edition each. The tab was running over $300K just in licensing costs.

Ok, so what else will a good DBA perform to add value to any company? These come to mind:

  1. To plan a long term solution properly. Architect a database structure in order to be flexible to extensions, following 3rd and 4th normalization forms, with proper relationships, keys and indexes.
  2. To understand the business requirements and design a database layout that will approach them. Which tables will be transactional? Which ones will be historical? Which ones will receive bulk insert operations?
  3. To implement proper security.
  4. If data will grow too large, to prepare the underlying tables to support sliding windows to swap out old data.
  5. To make the business owners aware that a separate entity would be beneficial if reporting is crucial. Nothing better than replicating data to another instance and allowing it be queried all day long.
  6. To make developers understand that business logic shall not run in the database tier. Data validation, cleansing, and any process that will require scalar UDFs are bad. If there is no way to add an app tier, then CLRs can help, but these kind of solutions need to have proper justifications.
  7. To see if filtered and/or include indexes can benefit a solution.
  8. To justify if data compression can be a good trade off with CPU cycles.
  9. To suggest when mirroring, log shipping, replication and/or clustering will benefit.
  10. To understand if I/O will require additional spindles and how to prove it.
  11. To understand if an edition downgrade, release upgrade, virtualization or X64 implementation will add value to any solution.
  12. To have an up to date checklist for production database implementations
  13. To follow a good process: Unit Test, QA, UAT, Production
  14. To learn from the best and to share his knowledge with their team.

DBA skills are not just a nice-to-have. They are a requirement. Do it right the first time and avoid the hassle of fixing in the future.

Once I was approached by a developer and he literally told me:

Every night you should have a job that truncates the logs. Also, you need to shrink the databases to make them faster.

I am not making that up! It is a true story. Imagine if that developer was taking care of the databases. I believe that most developers have no business in the DB world.

Most Developers Have No Business In The Database World

I have been around superb developers that understand the concept of approaching efficient code that needs to be ran on the database, and will seek help from database developers/DBAs to ensure that is the case. Unfortunately, most do not really understand the need and approach a solution to certain requirement with one thing in mind: effectiveness.

Yes, If business folks request a page that will show up all kinds of fancy data and drop downs, they will do it. It does not matter if the code behind the scenes goes through an expensive loop to render each record one-by-one.

The database is the most difficult and expensive Tier to scale, and code needs to be written in a way so it does the less amount of reads to get the answer to certain request. Developers need to understand that effectiveness needs to be tied with efficiency whenever they are coding.

If they do not understand/do not know how to make efficient code then seek help from a Database Developer/DBA. For example, why code a process that updates one row at a time instead of performing a bulk operation that updates all at once? Didn’t they know that a nested loop can be the worst route instead of hash or merge join to perform an update? Reducing trips to the I/O subsystem really makes a difference.

Now, there are other type of developers that think they know the answer for improving performance to any database without even looking at their code. I have experienced situations where I was told “we need more indexes” or “the database is timing out” when the issue was another heavy process taking long I/O requests and the developer had an uncommitted transaction respectively.

If developers have a problem, work as a team. Ask for help to someone that really knows their business so they can perform a health check. Imagine if a DBA goes and tells a developer how to write their application tier code.

I have not seen any DBA that has morphed onto being a developer. Most cases are the opposite, where developers become DBAs. I have experienced it myself as a former developer (yeah the procedural days) and DBA. If DBAs become developers this will be a perfect world, as the efficiency techniques will be considered while coding application and web tier code.

My final thoughts are, DBA’s own the database; it does not matter what the developer says. If a developer has an idea, then express it as it; do not demand what needs to be done to a database as they are most likely wrong.

And no, creating a single index on each field will not make the database faster. Let the DBA collect statistical facts and go from there.