10/10/2023
by Marlon Ribunal
Comments Off on The Evolution of Database Administration: From Administration to Database Reliability Engineering

The Evolution of Database Administration: From Administration to Database Reliability Engineering

In the IT industry things like DevOps and Infrastructure as Code (IaC) are now becoming the dominant aspects of governance. The concepts of Reliability and Observability are now also becoming a basis for IT operations. For those of us in the field of Database Administration, these dynamics brought about a transformation in the way we work or in the way we should work.

Gone are the days when database administration was primarily about handling administrative tasks and ensuring Service Level Agreements (SLAs) were met. It has now transcended these traditional boundaries, taking on a more holistic and strategic role within the IT ecosystem.

The combination of Database Administration with the principles of DevOps, Automation, Infrastructure as Code, Engineering, Architecture, and Reliability Engineering has given rise to an all-encompassing discipline known as Database Reliability Engineering (DBRE). This discipline represents the path forward for individuals seeking to elevate their database management skills and adapt to the changing landscape of IT.

So, what exactly does this evolution mean for database professionals?

  1. DevOps Integration: DBRE seamlessly integrates with DevOps practices, fostering collaboration between database administrators and developers. This results in a more efficient and agile development cycle, where database changes are no longer bottlenecks but integral parts of the deployment pipeline.
  2. Automation: Automation has become a cornerstone of DBRE. Tasks that were once manual and time-consuming can now be automated, reducing human error and freeing up DBAs to focus on more critical aspects of database management.
  3. Infrastructure as Code: With IaC, database infrastructure is defined and managed through code. This not only ensures consistency and reproducibility but also makes it easier to scale and adapt to changing requirements.
  4. Engineering and Architecture: DBRE professionals are expected to have a deep understanding of database systems’ inner workings and architecture. They design databases for performance, scalability, and reliability, rather than simply administering them.
  5. Reliability Engineering: Ensuring high availability, fault tolerance, and disaster recovery are central to DBRE. Database systems must be resilient, and DBREs play a crucial role in achieving this.
  6. Operations: DBRE encompasses the day-to-day operational aspects of database management, but it goes beyond mere maintenance. It involves proactive monitoring, capacity planning, and continuous optimization to meet evolving business needs.

As the IT landscape continues to shift towards cloud-based solutions, the environment for Database Reliability Engineering becomes even more promising. Cloud platforms provide the flexibility, scalability, and advanced tooling needed to implement DBRE practices effectively.

The role of a Database Administrator is no longer confined to routine administrative tasks. Instead, it has evolved into Database Reliability Engineering, a multidisciplinary approach that embraces modern IT practices to ensure databases are not just reliable but also responsive to the dynamic needs of the business. For those looking to future-proof their careers in the world of IT, embracing DBRE is the path forward, and the cloud provides the ideal environment for its growth and implementation.

Nota Bene: A good resource book about the philosophy and practice of DBRE is “Database Reliability Engineering: Designing and Operating Resilient Database Systems“. Here is the amazon link.

SQL Query Design Patterns and Best Practices: A practical guide to writing readable and maintainable SQL queries using its design patterns

05/20/2023
by Marlon Ribunal
1 Comment

Book Review: SQL Query Design Patterns and Best Practices

Over the years I have been to different organizations that have their own conventions in their business practices. Codes are written and maintained in certain ways that are compatible with how they operate. These organizations would have written conventions for specific business functions while others maintain theirs through unwritten rule-of-thumbs, and some are even left to their own devices to come up with their own rules. The latter oftentimes lead to technical debt, but that’s another topic for another day. Whether documented in official artifacts or kept by oral traditions and heuristics, design patterns and best practices are ingrained in all the teams’ customs and practices.

SQL Query Design Patterns and Best Practices: A practical guide to writing readable and maintainable SQL queries using its design patterns (yes, that’s the complete title of the book) will provide you with a good foundation upon which a consistent convention for practicing your day-to-day operations can have a solid footing in your database discipline, with few exceptions. Read on.

This book is not perfect to say the least. Some glaring inconsistencies in the way the codes were written have already been talked about in Koen Verbeeck’s review of the same book (review here). I am not going to rehash those here. Let me say, though, that those points raised by Koen are all valid and should be corrected in future edition of the book.

That said, let’s take a peek at what this book has to offer.

The book is divided into 4 parts:

  • Part 1 – Refining Your Queries to Get the Results You Need
  • Part 2 – Solving Complex Business and Data Problems in Your Queries
  • Part 3 – Optimizing Your Queries to Improve Performance
  • Part 4 – Working with Your Data on the Modern Data Platform

As you can see in the list above, this book pretty much covers a wide range of topics that include data retrieval, troubleshooting, query performance, and data platform. That’s a fair coverage if you want to talk about design patterns and best practices in general; but it will take a great deal of effort to jam-pack all these great topics into a 270-page volume. The content covers the topics it presented quite fairly if you don’t set your expectations too high. It is just not possible to cover all these topics in depth within that limited amount of pages.

If you’re new to the industry, this is a good book to have in your arsenal. I would pick this as a reference if I were establishing conventions from scratch.

I love the fact that the very first chapter talks about Reducing Rows and Columns in Your Result Sets. Sometimes even the best codes can perform poorly if the volume of the data being processed is more than what the system resources can handle within a reasonable amount of time. Trust me, you don’t need a billion-row dataset in your presentation layer.

For some this book will seem like it’s a primer on TSQL. For the most part, it really looks like a primer rather than an operative manual that the title presupposes; and there are better primer materials out there for such purposes.

This, then, begs the question: who is this book for? If you are just starting out in the database industry and SQL Server is your primary tool of the trade, this is a good book for refreshing your TSQL skills. If you are a well-established data professional who already built your company’s database conventions from the ground up, you can skip this book.

Don’t get me wrong. I’d recommend this book if you need a starter in TSQL. This is a good companion to our well-beloved volumes such as Itzik Ben-Gan’s T-SQL Fundamentals (Developer Reference) or Pedro Lopes and Pam Lahoud’s Learn T-SQL Querying: A guide to developing efficient and elegant T-SQL code (these two books should already be in your tech library).

09/19/2022
by Marlon Ribunal
1 Comment

Book Review: SQL Server Query Tuning & Optimization

SQL Server 2022 Release To Manufacturing is just around the corner. The current release as of this writing is SQL Server 2022 Release Candidate 0. This SQL Server edition is the most “Azure-enabled release yet.” What that means, according to Microsoft, is that it “provides true resilience by connecting SQL server to Azure through seamless disaster recovery to Azure SQL Managed Instance.” There are tons of resources that you can find online to learn more about the features of the latest iteration of SQL Server.

SQL Server Query Tuning and Optimization Book
SQL Server Query Tuning and Optimization

If you are like me and still prefer the physical book as your primary reading material, Benjamin Nevarez’s book from Packt Publishing is a great book to add to your bookshelf (it’s available in digital format too).

I have reviewed the last two editions of Benjamin’s other book on “High-Performance SQL Server” (here & here).

And just like how I recommended those to friends, I highly recommend SQL Server Query Tuning and Optimization: Optimize SQL Server 2022 Queries and Applications to anyone interested in making their SQL Server performant; more so if you are a SQL Database Administrator or SQL Developer.

TL;DR – I highly recommend this book.

Marlon Ribunal

I like how the table of contents is arranged in a two-column format which allows easier scanning when you are looking for a specific feature or section in the book. I know this is a minor feature but it’s kinda a big deal to me 🙂

SQL Server Query Tuning and Optimization Table of Content Example
SQL Server Query Tuning and Optimization Table of Content

As the book title indicates, you don’t expect to see all the new features introduced in SQL Server 2022 here (e.g, Ledger, Azure Synapse Link for SQL, etc). If you are looking for a great resource for SQL Server query tuning and optimization, this is your book. And if you are not planning to implement SQL Server 2022 in the near future, need not worry because it also covers older versions.

It is just proper that the book starts with Introduction to Query Tuning and Optimization in Chapter 1, which covers how the query optimizer works and how it produces efficient execution plans. The first chapter takes you from the fundamental of query tuning to the basics of an execution plan.

I like the cadence of this book. It’s kinda fast-paced when it comes to the progression of discussion. By Chapter 2, you are lunging at Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs), SQL Trace, Extended Events, and Data Collector. This chapter covers the foundation of query and performance tuning with the use of DMVs and DMFs. In this early part of the book, you’re already shown how to find expensive queries, how to investigate bottlenecks such as blocking and waits, and how to work with SQL trace and extended events to investigate further.

Chapters 3 to 5 cover the Query Optimizer, Execution Engine, and Working with Indexes. I feel like Chapter 3 – Query Optimizer – should have been the second chapter but that’s no big deal. Chapter 4 covers the Execution Engine, which can help you find what is happening under the hood while queries are being executed. Although this isn’t the first time I heard about Halloween Protection, I like the fact that Benjamin dedicated a whole section about this Update operation behavior. Some of the books out there don’t even mention Halloween Protection. Chapter 5 covers Indexes. Working with Indexes is a big deal and this book covers the topic quite well considering that extensive discussion about Indexes merits its own book.

Chapter 6 covers Statistics which includes a section about the “new” Cardinality Estimator introduced in SQL Server 2014 and a little background on estimation models. Cardinality Estimation is a big aspect of an execution plan and I like that this chapter recommends ways of detecting and fixing cardinality estimation errors. This is the chapter that I might often refer to in my day-to-day job as a Database Administrator.

Chapter 7 covers In-Memory OLTP. I don’t work with this feature and didn’t delve into it, to be honest. It’s in this book if you need it.

Now onto the biggest features and enhancements of existing features in SQL Server 2022, Chapters 8 to 10 cover Understanding Plan Caching, Query Store, and Intelligent Query Processing. The discussion of the execution plan in the previous chapters focuses on how the query optimization process produces them. Chapter 8 – Understanding Plan Caching – focuses on what happens to those plans. This is basically an extension of Chapter 1. If you are planning to read this in phases, you could probably sew them together as a contiguous chapter. Chapter 8 is capped with a discussion of a SQL Server 2022 feature, the Parameter-sensitive plan. The Query Store has become a de facto method of gathering performance-related information. In fact, Quey Store is on by default in SQL Server 2022. Chapter 9 covers the enhancements to Query Store. Chapter 10 covers an overview of Intelligent Query Processing (set of features) which was intended to improve the workload performance without the need for application changes. SQL Server 2022 further enhanced some of the features within the Intelligent Query Processing family.

Chapter 11 covers an Introduction to Datawarehouse and the common performance problems and how to solve them. Again, this is a big topic and probably requires another book for its extensive discussion. This chapter is here if and when you need it.

I understand why the author decided to place the chapter on Query Hints at the end of the book, Chapter 12. Do you need Hints? Probably not. Do you always “not need” Hints? Probably not, either. There are instances where Hints are “necessary” depending on your needs. This chapter covers some of those scenarios.