Voice of the DBA

Category: Editorial

  • Analyzing Breached Data

    A few of you out there might be data scientists who profile data regularly. Probably a fair number of you do import/export work and learn to check data values, perhaps with counts, distincts, or other aggregates. I don’t know if the performance tuners out there look at the skew of data or the details of what is in a query that needs improvement. However, all of you are likely familiar with data and trying to query it for some type of meaning.

    One of the largest data breaches occurred with National Public Data. Troy Hunt analyzed the breach as a part of his work with haveIbeenpwned. The piece is an interesting analysis of the data, trying to determine both it’s legitimacy as well as what is actually included in the breach. It’s a fascinating read and I encourage you to look at it not just from the data analysis side, but also to be aware of what data about you is being aggregated and sold by companies.

    The read is interesting as it is a bit of a detective story, digging through data in a folder, which is something I’ve had to do. I’ve had people in previous jobs just dump a bunch of data on me and ask me to load it into a database. Or a table. Often without them knowing what type of data it is, what formats, do files relate to each other? Are there multiple tables worth of data in a file? All questions I’ve had to ask myself (and answer), and similar to what Troy did to analyze the breach.

    Data is very important to many of us, in different ways, but I’m often amazed at how few people actually understand how to organize data and ensure others can track the metadata about their data (what their data represents). I’m guessing this is why every person that gets an extract of data to load into Excel formats it in different ways.

    In many cases, people want the ability to query data, but they prefer to just focus on one table that contains a lot of information. They don’t want to know how to “join” data together. I think this might be the reason we see so many views in databases, and why we have views built on views. Each new client of the database needs their own view structure.

    The world of data is a mess, even inside an organization. Once we start moving data between organizations, it’s truly a mess. We might bemoan all the inefficiencies and work we do to move, change, and re-load data as custom, human ETL machines, but there is one great thing about this tangled web. It provides for steady, secure jobs for many of us with no end of work in sight.

    Steve Jones

  • What Do You Drop?

    Many years ago I was training for a triathlon. I had competed in the Sandman Triathlon the previous year in Virginia Beach and wanted to do it again. I had a young child, work was busy, and I was struggling to find time to swim, bike, and run every week. One night, I was at a work event with a customer who was also a triathlete. He was much more competitive and successful than I was at competing in triathlons, and he told me I should just get up earlier and find time to train or ensure I spent time after work on training before I went home to ensure I was meeting my goals.

    That sounded fine. Want to be better at something, then spend time on it. Certainly, that’s what I often advocate for your career. Spend time on your career.

    However, if I get up earlier, then that means I’m more tired at the end of the day. I’ll go to sleep, or more likely fall asleep, earlier and miss time with my wife. She won’t like that. If I try to ensure we get the same amount of time, I’d likely shortcut time with my kid. There’s no magic way to find more time. If I take time to do one thing, I’m taking time away from something else.

    The same thing happens at work. Our Chief Marketing Officer noted this at our global meetup recently, saying that too many people are adding new tasks or projects and letting other work fall away. They don’t mean to let other work drop, and sometimes that’s a problem, but the reality is that we can only get a certain amount of work done as a group, and if we add new work, old work gets lost. The same thing applies to coding software. We might get more work from a developer in the short term, but that falls apart long term, and it can be bad for retention.

    Time is one of the most valuable resources you have in life. I see this more and more as I age, and you must recognize that it’s a limited resource. For a short time, you might be able to get more time by sleeping less (or working more), but those things mean you are dropping other things in your life. That often isn’t good for our health, relationships, or happiness over time.

    If you want to do something more, or new, then you should consciously decide what to drop. You have to make decisions and choose what is a priority and what is not. The things that are not a priority might get dropped (or their time reduced). That’s a big part of growing and maturing, as well as one of the worst parts. Making choices is hard.

    Decisions you make are rarely permanent. They are often choices you make for a period of time. You’ll make some great choices and those might be long-term or permanent. You might make bad choices, which hopefully are short-term, and then decide to make a new choice. Whether this is at work or in your personal life, make the choices that drive you forward, towards your goals, but with an eye on keeping a balance across all parts of your life.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Prevent SQL Injection

    I would hope most of you reading this know what SQL Injection (SQi) is and how you can prevent it. Or at least what patterns cause problems. If not, here’s a short explanation that is worth reading. If you have more questions, ask in our forums.

    SQL Injection has been, and continues to be, a problem in many systems. In fact, I chatted with Mike Walsh recently after he’d published this post on an attack for one of his clients. He has some notes that explain how your database server might be vulnerable, as well as a description of a recent attack example. He also notes that many of you are responsible for protecting data, which is separate from other security mechanisms. You need to be sure you are protecting your data, even in vendor applications.

    I’ve seen similar issues in the past, both in homegrown and purchased applications, where text fields aren’t checked and SQL is built by concatenating user input with code. I’ve complained to vendors, though often a short repro helps them see the problem and I’ve found many companies will patch systems, albeit sometimes slowly.

    There are application firewalls that can help, and certainly limiting access to those users who need access is always good, but that’s not helpful when the application is something that many clients use.

    The best protection is education. If you don’t know what to do, or your developers don’t listen to you, perhaps engaging a consultant like Mike will help. I’m amazed at how often people listen to an outsider when they ignore the same advice from someone they work with. That might be especially true for managers who are more concerned with doing more new work rather than fixing something that’s not quite working well.

    Security is becoming a bigger issue in many organizations. Not because we might get fined, but often because our customers might decide to choose another service if we can’t protect their data. There are other choices these days for most of the services we provide, and many organizations are finding customers increasingly fickle and quick to leave. This might not be the case in business-to-business work, but it does happen.

    We often won’t be perfect in our security and even if we are, our systems will change and new vulnerabilities or attack vectors will appear. We can work on the problems we know and improve security over time. SQL Injection is fairly simple to prevent, but it takes some education, some practice, and some code review.

    All things good database professionals should be doing.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • A Checklist for Learning

    One of the challenges many people have is focusing their learning efforts along some path. The best way to move forward is with steady effort that guides you through steps to build knowledge or skill. However, with so much information out there on the Internet, how do you decide where you focus your efforts?

    Lots of people choose a random method, but the world is full of those people, many of whom never develop strong skills. That might be fine if you are an hobby guitarist or piano player, but it’s not the best way to approach your career.

    Choosing what you want to learn is hard, but if you were to try and become a better Database Administrator, what do you think of this list: the ultimate checklist for Jr DBAs. This is a long list of topics in various areas, such as basic concepts, indexing, backups, security, objects, etc.

    I think it’s not bad, but it leaves you a lot of work to do on your own. You have to find places that explain these items, which can be a chore. If you find a link, how do you know it’s good? You can’t judge because, well, you don’t know. You’re learning here.

    I wish there were more guidance in posts like this. Not necessarily more information in this post, but with links that might help someone know where to look for good information. If you know these things, then what do you think is a good source of information? Maybe on your own blog or maybe an article that taught you something. I’m sure I don’t always provide background links, but I try to ensure I include links to help someone learn more.

    Inside a company, I might include internal links to our policies or documentation. As an example, maybe we write down how often we think stats should be updated as a standard and why or what types of backup schedules are appropriate. This would be an easy way to help someone learn why we approach our jobs in certain ways, which is a very specific type of learning we need. Other more general learning is important and would drive these decisions.

    Would you make a list like this one for yourself? If you’re a junior DBA, you might find this helpful, though I’d suggest you ask others for input on where you learn about these concepts. For people looking to learn something else, such as Fabric or Snowflake, is this the type of list that would be helpful, assuming there are some links on where to learn more? Let us know today.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.