Voice of the DBA

Category: Editorial

  • A Dearth of Data

    Most of us feel that our data volumes are constantly growing. A lack of data isn’t something we worry about, at least not in production. In development, far too many people use sensitive data for developers, which certainly ensures there is enough data, but at a potential increase in liability if the data is mishandled.

    If we require developers to create their own data, often there is a problem with them not having enough, or certainly not a representative set that helps ensure the software meets enough of the specifications. This is a challenge, and while random data helps, it doesn’t always work well. This problem might be hard to solve with OLTP systems, but what about an AI or ML scenario, where we might need lots of data to train a system?

    Microsoft Research is working on ways to solve this problem. They presented a paper on Icebreaker, a technique that uses minimal data to train a model. I’m not sure I completely understand how this works, but the idea is to be able to work with very little training data and somehow still train the model. I’m guessing there is some ML inside of the process itself.

    There are all sorts of downsides with using existing data to train models. Sometimes we have inherent bias, or otherwise skewed data. Allowing a model to work with less data, and perhaps then working to change, or even skew, the data to meet our goals might help. Certainly this likely requires input and feedback from a data scientist of some sort, but that might be where we take advantage of the skills and knowledge of that staff.

    There will be more use of the AI/ML technologies in the future, if for no other reason than people are very interested in how this can improve the way that systems can help analyze data. Of course, techniques like this might help us deal with the challenges of doing so when we don’t have all the data we would like to have while building the model.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • 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.