Voice of the DBA

Category: Editorial

  • Aiming for Better Data Governance

    At Redgate, we have customers all over the world. I’m lucky that I get to work with, and sometimes meet, people dealing with all sorts of situations, environments, and even regional challenges. As a dev and DBA, I’ve never worried about anything other than varchar(). Now I have customers that have lots of languages and other issues that need to be accounted for in their designs.

    One of the areas that has become more important in the last few years is Data Privacy and Protection. Between the GDPR and other legislation, and growing concern from customers, many organizations are starting to pay more attention to this area. Less so in the US, where we seem to be behind most of the rest of the world.

    I saw a piece recently on the reasons why we might need more data governance in companies. Actually, the piece talks about needing more money for the Chief Data Officer’s budget, though I’m not sure most organizations have anyone in that role. A few do, and certainly I’ve seen some growth in companies worried about this, but it’s still a minority.

    While regulations might change, and there could be a quick need to improve our processes in this area, I think the bigger issue is competition. More consumers are fickle, easily changing services whenever they are unhappy. More companies are building competitive services, and if you cannot satisfy your customers, they’ll go elsewhere. Increasingly consumers are considering data security and data privacy to be more important than in the past.

    I’d urge you to think about these issues inside your organization, and try to be better in new projects. Ensure you could comply with the CCPA or the GDPR if necessary. That need could come quicker than you expect.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • The Challenges of Splitting a Table

    I ran across a discussion on Reddit about splitting a table. In this case, the original post had to do with a vertical partition of data, which is a technique that can help you better manage data in your database. However, I haven’t often seen this technique employed in the real world.

    I wonder how many of you have considered a vertical partition when you are modeling data. Often we may not think about this early in the lifecycle of an entity, but as it grows, you might think about reducing the amount of data you often query in some way, and a vertical partition can help.

    Is there some criteria that you might use in deciding this? Or how you can evaluate if there is a need? I once worked on a system with a very hot table, lots of queries, lots of updates against this table from our online system. In response to some requests, the developers wanted to add some columns to the table. This was important, and we needed to capture the data.

    These were valid columns, but they were large in terms of data size, and not every one would always be used. This was before the option of sparse columns, so that wasn’t an option. I had no interest in an EAV table, despite the fact that it might have worked well at this scale. Instead, this was a situation where I thought a vertical partition would work. In fact, I thought a few of the other columns in this entity could be moved as well, as they were rarely queried and contained significant data.

    We split the table, and performance actually improved for the main table, as it had less data. Just like an index, we had more rows on every page and less IO for range queries, and even key lookups for data that wasn’t already indexed.

    There are lots of good techniques in database development for dealing with the challenges we face in data modelling and with performance. I’d urge you to learn about some of them and understand when they can be useful. I would also practice implementing them, making changes to existing tables, and learning how you can deploy them if the need arises.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Choosing a VCS

    As someone that speaks and promotes DevOps, I get asked for recommendations and specifics all the time for tooling. One of the questions I’ll get asked regularly is about version control. First, use it. There’s no excuse for not using version control these days, especially as most of the software out there is free.

    My view is that Git is really the choice these days. Most IDEs and software tools work with git, and if they don’t, likely they don’t support version control. While there are lots of choices out there, and I’ve used a lot in my career, it seems that Git has really won and is the default choice for so many organizations. What’s interesting is so many of the surveys and tracking of version control systems tend to rank the most often used hosting services, all of which use Git.

    However, does that mean you should abandon your existing TFVC, SVN, or other system for Git? I wouldn’t necessarily recommend that, but I would start learning Git and considering it for new projects. Some people love the change, others see TFVC with more complexity, and many people recommend moving away from TFS. I see similar thoughts about SVN and other VCS systems. Even this svn v git site that links to repo stats shows the stated stats of 47% of projects on SVN v 38% for git is outdated. As I write this, it’s 71% on git. I think that’s a testament to the growth from 2016 to now in Git’s popularity.

    What would I choose today? Git, hands down, for any project at any company. I might live with the existing system in the short term, but I’d be thinking git, if for no other reason than future hires and staff will likely be more familiar with git than anything else. I’d move in that direction. I don’t know I’d spend time converting existing repositories to git, but if the need arose, I’d be ready to do so.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Automation is a Key Skill for the Modern DBA

    This month we had T-SQL Tuesday #130, hosted by Elizabeth Noble. Elizabeth and I had some good talks about database development and DevOps last year, and I managed to convince her to host one of the blog parties. I had expected that people might focus on the software development side of automation, but many of the posts cover administrative topics.

    The recap is coming next week, and I look forward to it, but I shouldn’t have been surprised. Good DBAs, as well as many sysadmins and Operations staff, have known that automation is important for years. It helps to ensure a smooth running environment and helps us cope with the volume of work that is thrust upon us.

    There were a few interesting posts. Greg Dodd talks about the advantages for his employer when he automates things, which is important to think about. Spending time automating things can slow down the initial closing of tickets, but it pays dividends in the future. It’s an investment, which is something to think about when you try to reduce repetitive work. Especially if your boss is concerned about the time taken to solve some tickets.

    One of the big advantages of DevOps, as well as general automation, is consistency. Taoib Ali explains how he enforces trace flags with automation, and Kevin Chant talks about SQL Server updates. Deepthi Goguri explains how to handle DBA work at scale. These are all situations where a little automation is not only useful, but perhaps essentially to reducing mistakes and human error.

    As we move to a larger number of versions to support, a great variety of platforms, including the cloud, it’s critical that a DBA not be required to click around in SSMS or connect to lots of systems to manage them. Learning to automate can produce some great blog posts for your brand, give you interesting conversation ice breakers at events (or on social media), and generate some stories that will impress interviewers.

    If you aren’t sure how to get started, consider reading Eitan’s Laws of Automation. It’s a look at what to automate, why, and a few ideas on implementing changes.