The other day, I got an opportunity to be a part of a conversation with a group of young professionals currently working in the field of data, specifically working on Microsoft stack. The conversation veered towards the role of a DBA and how the role has been changing at a fast pace. There were questions around the requirements to keep oneself relevant and at the same time important areas to focus upon while preparing for an interview.
While I did share my understanding in the discussion, but later felt this would be a great topic for my own blog. Below I share the different areas within SQL Server and related technologies that as a DBA one must focus upon to remain in vogue and to prepare for an interview.
Microsoft has been investing heavily in SQL Server and the entire data platform. There are so many aspects to the data platform and there’s just an endless list of things to learn. However, in my opinion, better understanding in the areas given below should serve well.
- Backups, Recovery Model and Transaction Log
- With the rise of cloud and various DBaaS (database as a service) offerings one may want to look at backups as a thing of the past, but I strongly believe that having a good understanding on backups, recovery model and transaction log is a must for anyone looking at doing well being an SQL Server DBA.
- Transaction log is the foundation of one of the pillars of the ACID principles – “durability”. Not just that, a lot of critical components like Always On Availability Groups, Transactional Replication, Log Shipping (and more) within SQL Server rely heavily on the transaction log. Knowing how the transaction log works, understanding VLFs, crash recovery etc. are critical to having a good understanding of the SQL Server.
- RPO, RTO – As a DBA one must have a good understanding on the RPO and RTO for the critical databases. Without having an understanding around these principles, it would be difficult to set up an effective backup strategy.
- Performance Tuning
- While performance tuning in itself is a massive area, being a DBA, you must have the basic ability to investigate and identify performance bottlenecks on a given database server.
- As a production DBA, you may not be expected to resolve or fix code related issues, however you will certainly need the ability to identify the code/stored proc/ad-hoc statement which is causing a lot of grief.
- You must have a fair idea on the area where a certain issue would warrant tuning – is issue emanating out of a server config, or whether this would need better indexing or does the query need to be re-written? The learning in this area seems to be never ending.
- HADR
- Data is the lifeline of a business today. Data needs to remain available – (almost) all the time. Data lives in the database. Essentially your database also needs to remain available (almost) all the time. As a DBA, not having a good handle on HADR is not an option. Hone your skills on HADR – get to know HA and get to know DR. Both are not the same. Availability Groups, Log Shipping, Database Mirroring (though deprecated now) are a must to learn upon.
- Concurrency – Locking, Blocking, Deadlocks
- Locking and blocking is a fundamental concept which is ubiquitous across all the modern relational database management systems. It plays a crucial role in isolating concurrent transactions and ensures that data remains consistent. Get a basic understanding on the isolation levels supported within SQL Server and how those impact locking and blocking.
- Investigation Tools
- Look at learning on Extended Events, get familiar with DMVs (Dynamic Management Views) and work towards understanding server level bottlenecks with the help of wait stats. As a DBA, it’s almost a must to have a good handle on these tools to be able to understand, resolve or at least give a good explanation on the situation.
- TSQL
- Yes, a fair bit of understanding on T-SQL is a must. It would get very difficult to make sense of a query if as a DBA you don’t understand the difference between a Left Join and an Inner Join. Makes a world of difference when you understand what the query is trying to achieve before you get into a conversation with the Devs. This I would rate as a good to have skill, though it may not be required until pretty late in your role.
- PowerShell
- PowerShell really amazes when it comes to information gathering, automation and managing servers at scale. While to some this may not really categorize as a core SQL Server DBA skill, but it certainly can enhance your appeal to a prospective employer and can increase your chances of getting through that interview.
- Azure/AWS/GCP – Cloud Service Providers
- It would be extremely beneficial to acquaint (actually not just acquaint, learn it really) yourself with the database/data related services being offered by different cloud service providers like Azure, AWS and GCP. At least pick one of them and learn through the basics. Understand the terminologies and the more you know the better it is. Being an SQL Server DBA, I personally would recommend going ahead with Azure. There’s a great community around SQL Server and Azure Data Platform and there’s incredible support available in the form of blogs, articles, trainings, coaching both from Microsoft and from individuals working in the industry.
Finally – the role of a DBA is changing rapidly, I would say for the better, it’s just that we need to keep up with the learning.
There’s a lot of quality content out there and some of the resources that I have always relied upon are given below –
- https://learn.microsoft.com
- https://www.sqlskills.com (look for articles from Paul Randal and Kimberly’s team) – probably the best information out there for anything SQL Server
- https://www.brentozar.com (he posts a lot of free content on YouTube and has paid trainings as well – I have been a subscriber of his trainings – worth every penny!)
- https://www.sql.kiwi Read articles from Paul White
- https://www.sommarskog.se Read articles from Erland Sommarskog
- https://www.scarydba.com Read articles from Grant Fritchey
- https://sqlha.com/blog/ – Allan Hirt for HADR
- https://learnsqlserverhadr.com/blog/ – Edwin Sarmiento for HADR
- https://sqlmaestros.com – Amit Bansal
- https://www.mssqltips.com
- https://dba.stackexchange.com
- https://sqlperformance.com
- https://blog.sqlauthority.com – Pinal Dave
