Voice of the DBA

Category: Editorial

  • The DBA’s Twelve Days of Christmas

    On the first day of Christmas my new DBA sent to me
    A table with a primary key

    On the second day of Christmas my new DBA sent to me
    Two Foreign Keys, and
    A table with a primary key

    On the third day of Christmas my new DBA sent to me
    Three stored procs
    Two Foreign Keys, and
    A table with a primary key

    On the fourth day of Christmas my new DBA sent to me
    Four database roles
    Three stored procs
    Two Foreign Keys, and
    A table with a primary key

    On the fifth day of Christmas my new DBA sent to me
    Five execution plans
    Four database roles
    Three stored procs
    Two Foreign Keys, and
    A table with a primary key

    On the sixth day of Christmas my new DBA sent to me
    Six unnested views
    Five execution plans
    Four database roles
    Three stored procs
    Two Foreign Keys, and
    A table with a primary key

    On the seventh day of Christmas my new DBA sent to me
    Seven maintenance jobs
    Six unnested views
    Five execution plans
    Four database roles
    Three stored procs
    Two Foreign Keys, and
    A table with a primary key

    On the eighth day of Christmas my new DBA sent to me
    Eight tables to remove
    Seven maintenance jobs
    Six unnested views
    Five execution plans
    Four database roles
    Three stored procs
    Two Foreign Keys, and
    A table with a primary key

    On the ninth day of Christmas my new DBA sent to me
    Nine unit tests
    Eight tables to remove
    Seven maintenance jobs
    Six unnested views
    Five execution plans
    Four database roles
    Three stored procs
    Two Foreign Keys, and
    A table with a primary key

    On the 10th day of Christmas my new DBA sent to me
    Ten database containers
    Nine unit tests
    Eight tables to remove
    Seven maintenance jobs
    Six unnested views
    Five execution plans
    Four database roles
    Three stored procs
    Two Foreign Keys, and
    A table with a primary key

    On the 11th day of Christmas my new DBA sent to me
    Eleven Extended Events
    Ten database containers
    Nine unit tests
    Eight tables to remove
    Seven maintenance jobs
    Six unnested views
    Five execution plans
    Four database roles
    Three stored procs
    Two Foreign Keys, and
    A table with a primary key

    On the 12th day of Christmas my new DBA sent to me
    Twelve tickets already closed
    Eleven Extended Events
    Ten database containers
    Nine unit tests
    Eight tables to remove
    Seven maintenance jobs
    Six unnested views
    Five execution plans
    Four database roles
    Three stored procs
    Two Foreign Keys, and
    A table with a primary key

  • Double Check Your Math

    Performing calculations in our applications is important. In fact, if we do any sort of math, we really want to ensure that our results are what we expect. That seems obvious, but I’ve run into applications where the code deployed didn’t quite calculate things as expected. In fact, there are all sorts of cases where someone was using a type of rounding that didn’t work well. You can read about a number of stories in this article, some of which were very expensive.

    While much of the work of calculations is done in application software, there are powerful capabilities in a database platform that are useful, especially when working with more than a row of data. It seems that even when developers want to treat an RDBMS as a storage location, they still will manipulate data with SQL functions to get results from a query that are easier to work with in their application.

    Some of us might use FLOOR(), CEILING() , ROUND(), or other mathematical functions in our work. We should be sure this is what the actual specification calls for, and that we are performing the calculations correctly. After all, mathematics in computers are often not as precise as we’d like them to be, or perhaps, not as we’d expect them to be.

    This is one reason I think you should have database testing. Clients have had the wrong calculations in production, sometimes for years. When we make switches based on values, or we perform a calculation designed to somehow round or trim a number, we may do so incorrectly. I’ve seen incorrect discount rates, tax calculations, and more in production systems.

    Even if you don’t want to write tests for most of your database query code, you ought to at least ensure that any math calculations have some testing around them that documents the expected behavior with easy to use numbers. This might not prevent mistakes, but it does give you a way to explain what you expect to happen in the code, verify it works, and show the input and output to a client. Perhaps their set of eyes on your test and test data will prevent silly mistakes in your math algorithms.

    Steve Jones

     

  • Better Protection from Hardware

    Microsoft is working on ways to protect your system at an even lower level than the bootloader. Many modern computers have UEFI protection, which allows for security in the boot process, and prevents untrusted code from running. This is intended to ensure that some other boot process cannot run and then invoke your Windows bootloader

    This doesn’t protect from firmware issues, but Microsoft may have a solution. Working with hardware companies (Intel, AMD), they have a System Guard Secure Launch that provides a way to secure your OS. There’s a light explanation at Ars Technica of what this means for you. Essentially, this allows the OS to reset the CPU and ensure untrusted firmware code isn’t running

    I won’t pretend to know how this works in depth, or what additional levels of security this provides, but I do recognize the problem being worked on here. I also think that criminals (and rogue nation state actors) are making deeper and more complex attacks on systems. We know that compromised code can be a major problem for our servers, and we need better mechanisms to ensure we can trust the platform on which we run our database servers

    Just recently there was an alert about a backdoor in SQL Server. This was noted as being in SQL Server 2012 and 2014. At first I was surprised this didn’t get more play, then I realized this was an issue with the Windows OS being compromised and then a patch installed on the SQL Server service to allow attackers to log into the database server. Disconcerting, but if someone gains control of the Windows OS, I’d expect they can get into SQL Server.

    Security is a problem with database servers, and the number and variety of attacks continues to grow. It pays to be diligent, and certainly, use whatever tools are available. Strong passwords, access controls and low privileged accounts, UEFI, SGSL, set up every security feature you can. After all, data is your organization’s most important asset.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • The Evolving DBA

    The world is changing. Plenty of people have written about the changes in the job of the DBA (DBAKevlar, Brent Ozar), including myself. While lots of vendors might postulate their database removes the need for a DBA, and some might believe that, I think that’s disingenuous. Microsoft doesn’t hire DBAs, but they hide that job under other titles. We still need DBAs, though the job changes a little in the future.

    Recently I saw a post from new PASS Board director, Melody Zacharias, where she notes that DBAs are more important than ever. She gives us five reasons, and I tend to agree with most of them.  There is a need to somehow keep track of and manage data, much of which will continue to live in relational stores. As much as there is grown for key-value, graph, streaming, document, and other styles of data stores, we still use relational databases for many tasks. I don’t see that going away anytime soon, especially for legacy software that will continue to live for some time.

    There is also the aspect of a DBA that involves the cleaning, checking, and verifying that data is in the form and format we expect, not to mention the need to ensure the data is intact and protected, and not changed inappropriately (or corrupt). Every system needs people that can dig through data and help decode its meaning as well as ensuring that business users can get it in to a report of some sort. Those tasks don’t go away. While end users might have more self-service query tools, that doesn’t ensure that they will actually know where the data is or how to reformat it. No end user tool is going to fix mm/dd/yyyy for a user expecting dd/mm/yyyy.

    There is also the need to just manage large amounts of data. I know data scientists have struggled to work with large scales of data in the past, and not just because of hardware limitations. They spend an inordinate amount of time gathering and organizing data that is used for analysis or AI/ML operations. Future organizations won’t want to waste their time on those tasks and turn to some sort of DBA to help manage the large data needs of those people performing high level analysis.

    The DBA isn’t going away, though the job is changing. Checking logs and backups is likely something you’ll do less of in the future if you work in Operations. Instead, you’ll manage more data, and you’ll certainly need some coding skills to ensure you can perform in GitOps environments, where we don’t click on things, but rather use code to define our systems and the configuration that we expect.

    The evolution can be daunting, but it can also be exciting. You have time to learn and change and build new skills. Embrace the opportunity to grow and change in your career as a DBA.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.