Voice of the DBA

Author: way0utwest

  • Cloud Native

    The cloud is an amorphous thing that isn’t well defined, but we do know that there are various types of services (IaaS, PaaS, Saas) that many of the cloud vendors offer. While IaaS is the easiest platform on which to move our software applications, it is fundamentally limited and lacks the scalability of the other services.
    PaaS systems offer the largest opportunity for systems that scale up and down, allowing the ability to meet workloads while ensuring cost savings by reducing resources when times are slower. To use PaaS, we often need to rewrite code to be cloud native as most on-premises applications do not run smoothly in the cloud. This often entails different code structure, connection handling, and other technical details in the software development.
    There is continued pressure to move both application code and databases to the cloud, often because of financial advantages. There can be a cost savings by reducing resource loads, as well as changing from capital to operational costs. Today I’m curious how many of you are moving to the cloud, whether wholesale or in a limited fashion.
    If you are moving any systems to the cloud, are you rewriting software to be cloud native? Or are you trying to lift and shift systems into an IaaS infrastructure? Do your teams think there is an advantage in rewriting code to take advantage of the cloud or is it too much work and effort? If you are moving to the cloud, are you doing any work to change and improve your database code. After all, a more efficient database will matter in the pay-for-use model.
    Steve Jones
    Listen to the podcast at Libsyn

  • Computer Disruptions

    I travel a fair amount for work to speak at various events around the world. Traveling can be quite a disruption to my life, and I do work to limit the amount of time that I’m gone. As my kids have gotten older, I’m have less commitments at home and an extra night during a trip isn’t as much of a burden. It’s even enjoyable when my wife can accompany me to enjoy a few days visiting another part of the world.
    I still get on airplanes enough that the scheduling matters. I often plan trips to limit the number of hours I spend in transit, which means I depend on the airlines to keep to their timetable. I’ve been lucky that I haven’t experienced many delays, usually localized to a specific airplane, but I do worry when I see reports like this about computer glitches for the airlines.
    There are numerous computer systems that airlines share, some of which are still running on large mainframe systems. As the travel industry has grown, the software managing it has changed. From a single system (and company) coordinating all flights to a distributed set of applications that must interact and share data.
    Do we think this is going to happen more frequently in the future? Is the state of software development improving enough to prevent large scale issues and disruptions? As much as I’m pleased by the advancements in software development and the higher quality of code, I still think that companies often take too many shortcuts, without enough testing and evaluation in their quest to build systems quicker.
    I hope nothing goes too wrong as I’m traveling this year. I’m sure I’ll have a few delays, but if I do, hopefully I don’t miss any of the speaking engagements.
    Steve Jones

  • Testing as another user–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    This is one of those things I do often and thought I should write a short blog on the topic. Often I want to check how another user can interact with some object. I could certainly open a new window or change my connection string, but that can be disruptive. Not to mention I sometimes get confused about which user is in which window in SSMS or ADS.

    There’s a better way. I can use the EXECUTE AS USER statement.

    Testing Access

    Here’s a quick example. I create a new table with Dynamic Data Masking. I want to see if another user sees masked content. Here’s my table:

    CREATE TABLE dbo.Subscriptions (
        UserID int, 
        SubscriptionName VARCHAR(200), 
        SubscriptionValue MONEY MASKED WITH (FUNCTION ='random(100,1000)')
    
);
    
GO
    
INSERT dbo.Subscriptions
         (
             UserID
           , SubscriptionName
           , SubscriptionValue
         )
    
VALUES
         (1, 'My first sub', 50.99),
         (1, 'Time', 24.99),
         (1, 'ESPN Mag', 19.99),
         (1, 'Popular Mechanics', 19.99),
         (1, 'The Guardian', 24.99)
    
GO

    When I access this, I see this data:

    2019-04-08 11_23_41-Window

    What does SallyDev see? I could log in as this user, but this is easier:

    GRANT SELECT ON dbo.Subscriptions TO SallyDev
    
EXECUTE AS USER = 'SallyDev'
    
SELECT top 10
      *
      FROM dbo.Subscriptions AS s
    
GO
    
REVERT

    Now I see this:

    2019-04-08 11_24_49-Window

    The EXECUTE AS USER allows me to simulate another user. The REVERT brings me back to my context.

    Use this to make testing easier.

    SQLNewBlogger

    I was using this technique recently and realized the I hadn’t blogged about it. I spent about 5 minutes creating a scenario and 5 minutes putting this together. You could do this, show some knowledge, and explain how this helps your scenario.

  • Understanding Your Database

    I ran across a neat post from Michael Swart recently. In doing some spring cleaning, he was looking to remove unused, or maybe unnecessary, tables from his database. He published a script that looks through the plan cache to determine what connections exist between tables. He joked that if he doesn’t find any joy when examining a table, he drops it. At least I hope he was joking. If not, I suspect we’ll see a note on LinkedIn soon.

    Actually, I’m sure he’s joking, and he makes a good point at the end of the his post. His script isn’t used to make decisions, but rather it provides a place to begin to investigate more about what the table might be used for in an application. It provides a starting point for more questions, such as is there value in removing this table. His company is embracing AWS, and they are becoming more cost conscious. This means keeping less data, and possibly moving cheaper data stores where possible to reduce license and/or hardware costs.

    I suspect that other organizations will start to embrace similar attitudes as more move to the pay per month model of the cloud. Many of us rent hardware, and when we do, the recurring costs become an issue. Suddenly we might rethink the amount of data we keep and archive or remove older data, either to reduce costs, or risks. The GDPR brings with it a push to not keep all data in perpetuity.

    The goal of better understanding our databases, and specifically tables, makes a lot of sense to me. Far too often I’ve inherited some system and only understood portions of the database. I’ve performed hours of investigation to try and better comprehend how data is stored, retrieved, and manipulated. I’ve found my share of unused tables, often renaming them for months and eventually deleting them.

    I do think that we poorly document databases, almost treating them like a file share where we drop a new item when we need it, without thinking through the usage, ensuring others know about the entity, and often forgetting it exists if we don’t regularly use it. A RDBMS isn’t a file share, or at least it’s not an inexpensive one, so we ought to be cognizant of the data we keep and trim unnecessary waste over time.

    I empathize with Michael and would relish the challenge to review and trim old tables where I could. However, I also know that often there are tables that won’t “make a difference”, either in cost or any other savings and aren’t necessarily worth the time to investigate and remove. Spending even an hour to decide if I should remove a 10 row table whose purpose is unclear doesn’t seem like a good use of time. Unfortunately, I’ve run into plenty of those, which nag me, but really shouldn’t be something I spend time on, and I have to leave them be.

    Steve Jones

    You can hear the podcast here: http://traffic.libsyn.com/voiceofthedba/understanddb_50_v2068.mp3