Development DBA II

With the last post, we began talking about the Development DBA and the skills needed at the beginning of this career path. Now it’s time for you to ponder what the next level looks like.

Development DBA II – 2 to 4 Years of Experience

1. All competencies from level 1
2. Develop basic to intermediately complex stored procedures, triggers, views and other database objects.
3. Will have some experience with SSRS, SSIS, and/or SSAS development, deployment and possibly administration.
4. May administer source control systems for the environment.
5. Promotes SQL changes from Dev up through all environments, including production.
6. Contributes to automation, particularly using native SQL Server methods, but could also leverage PowerShell or other languages for automating tasks (Python, C#, etc.)
7. Designs pre-prod testing of SQL code prior to production upgrades or migrations. This includes performance and regression testing.
8. Basic to mid-level understanding of execution plans, indexes, SQL Server statistics, and query tuning.
9. Investigate basic to moderately complex data integrity/repair issues within SQL Server
10. May begin leading in knowledge sharing in some capacity in one or more areas from level I or II.
11. May take an active interest in leadership and in development of leadership skills, including emotional intelligence.

In years 2 through 4 the Dev DBA will work with more complex T-SQL such as recursive CTE’s, and error handling with TRY CATCH or the THROW syntax will likely be introduced at this stage. You may begin writing database views during this part of your career and leveraging them to make query writing a bit simpler for yourself and others. You are likely to be exposed to T-SQL Triggers and you’ll need to understand them and even maybe write a few triggers of your own.

You will likely start to get some sort of regular exposure to SSRS, SSIS or SSAS at this point. This will of course be based partly on your own interest in learning the tools of the trade and what is in use already at your employer.

Source control and the process of promoting changes from source control up into QA, and even production, may be something that begins at this level. After all, as a person who is writing a fair amount of code, you will have an interest in ways to manage code changes.

Automation

Let me ask you a question. Would you rather wash clothes manually with a washboard and tub or use a washing machine? It seems like a silly question. You would want to use a washing machine. You probably can’t imagine washing clothes without one.

In a similar thought process, there are things that you and others at your job will not want to do over and over in a manual way.  Accordingly, you will make more significant contributions to automation of tasks at this level in your career. SQL Agent will become your friend and learning to script out processes to make them easily repeatable, rather than doing everything in the SSMS GUI by hand, will occur more frequently. You may automate tasks with PowerShell for sure, especially using DBATools, but Python may also be an option. In case you didn’t know, Python does integrate with SQL Server via a specific driver.

Testing Code and Performance Tuning

An important part of the Dev DBA’s work is testing SQL code. You’re going to be doing more of that at this level in your career. You will need to verify query results and probably automate repeated testing of code as well so that you can test not only results but performance. Maybe the query returned the results you expected but it took too long to do it. This is where learning how to read execution plans will come in.  Execution plan operators will give you clues about what is happening with your T-SQL and those clues can help you with re-writes or with indexing.

Aaah yes. Indexes.  You’ll need to begin understanding indexes as well.  You’ll need to know what they are, the different types of indexes and when to use each type. Types of Indexes are as follows:

1. Clustered indexes, which may be the result of creating a primary key. These may be clustered or non-clustered primary keys.
2. Non-clustered row-store indexes.
3. Non-clustered columnstore indexes.
4. Clustered columnstore indexes.
5. Filtered indexes

As a corollary, you’ll need to look into the concept of SQL Server statistics as well.

Next Steps to Take

1. If you’ve read the other parts of this series, then you know I’m going to tell you to copy the skills list above to a Word doc and place an “X” next to the things you need to work on.
2. Use those “X” marks to build a training plan. Put the training plan somewhere that you will see every day. One thing that should be on your training plan at this stage is learning more T-SQL.
3. Work the plan. I have a post that will help you find resources for your learning plan.
4. Contact me here for questions about this post or the skills listed in it. You can also reach out to me via Twitter using the handle @leemarkum.