Voice of the DBA

Category: Editorial

  • Customize Software or Process

    Many of us work with software and often wish that we something was designed differently. I’m sure many of us feel the same way about our database schemas, which is usually even harder to change. In any case, we often want to mold software to fit our thought process. We’re often asked to alter software to meet the expectations of our customers as well, trying to alter visuals or workflows to match some method of working that our customer desires.

    We often try to do this with COTS (Commercial off the shelf) software, notably ERP systems. SAP and Oracle applications, and similar software packages, have made many consultants rich and required millions (maybe billions) of dollars of investment by companies that look to ensure their logos, their workflow, the specific process is implemented in software. Even many of us that purchase some infrastructure software, or even get open source applications, may spend lots of resources on getting the software to fit our organization.

    The thought that most people have had is that it’s cheaper to make the software fit the system, or the people, than vice versa. However, is that the best way to proceed? Do we want to customize software to work in a way different from the way in which is was designed? Or should we learn to alter some of our processes to better fit with how the tools work? Do we think that we really have some special secret in our process that makes us more efficient? Or are we resistant to change?

    For some software, like SAP, the system is designed to morph and fit an organization. Certainly when there are hundreds, or thousands of users, it might be worth the cost of customizing the software to fit the users. However when we have more specific software, such as that used to monitor infrastructure or deploy software or track features, with dozens of users, do we want to spend a lot of time changing the way things work? I sometimes wonder if we should instead focus on getting our people to learn a new way of working that flows with the software. After all, upgrades, patches, and other minor changes are less disruptive.

    I don’t think there’s just one answer here, and certainly there are valid reasons to spend time and money on changing a system. I just think it’s worth some thought to be sure that changing software is a better decision that adapting our process to work with the application.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.9MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Disable or Drop

    When I started working with SQL Server and Windows, it seemed that the administrative side of many actions was limited. We could add and drop many items, but that was it. Relatively few tools allowed setting limits or disabling them, which was a pain. After all, I’d gotten used to setting up accounts for vendors and contractors in Active Directory, often disabling them when they weren’t in use. I couldn’t do this in SQL Server for many objects, which was a pain and an administrative burden to reset them up when troubleshooting issues. This was pre-PowerShell and .NET when any SMO access was a project in and of itself.

    These days SQL Server has done a great job of adding in the ability to pause or disable many objects. We’ve had the ability to lock out an account for many versions, which is a great way to setup a vendor tech support account when it’s needed. This is especially important for security these days, as we may want to be sure that we prevent access by any suspect accounts. Disabling them allows us to prevent their use, but keep all their rights and permissions in the even they are valid accounts.

    We can disable indexes, which can be useful as a precursor to deleting them at some point. We can disable triggers, which is incredibly useful when you are testing or debugging actions on a table. We can disable Extended Events, audits, and more. All of these are useful actions for a developer or administrator, if you use them.

    When things go wrong, we’re often stressed and pressed for time. If there are issues with a system, many of us make snap decisions, which might fix the problem or make the situation worse. Even in those cases where we fix an issue, deleting or dropping objects might cause is extra work later. My question for you is what’s your first reaction? When you need to make a quick change to remove something, security access, an index, etc., do you drop or disable?

    I would hope that you disable, as this removes the effects but keeps the object in the system with associated meta data. Rebuilding permissions or trying to get the old trigger code is a pain (since few people use a VCS, please start doing this). It’s possible that you won’t even be able to get things reset back up in the same manner. That might be fine, but it’s not ideal as new code should be tested, and in a crisis, it likely won’t be looked at too closely. At least the previous version of the code was tested in production.

    Build the habit to disable, not drop, and I think you’ll be glad you did.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Job Interviews: What is Normalization?

    This is part of a series that looks at interview questions that you might be asked.

    One of the cornerstones of a relational database is normalization. This is a database design technique that is used to decide how we might distribute data among tables and columns. If someone were to ask you “what is normalization” in your next job interview, what would you say? How do you answer this question?

    Depending on the position, and your experience, perhaps you’d talk about this in practical terms, with an example. Maybe you have a scenario you use to explain the concepts. I tend to lean towards an e-commerce type database, with orders, customers, and products. That’s an easy concept for people to grasp. I can then give examples of how normalization might change the way we store data.

    Do you there are various forms of normalization? We can talk about first, second, third, fourth, or other normal forms of the database. Perhaps you can explain these from memory? Maybe you know the academic definitions. Maybe you have your own description. I certainly think if the position may involve designing tables that you should be able to talk about the differences between these forms and determine what form a particular database is in.

    My answer would include quick explanations of how I would avoid repeating groups, columns not dependent on the PK, and how sometimes the third normal form starts to impact performance because of the number of joins required for simple queries. I would also be ready to expand on these topics, explaining what a PK is and how to choose one.

    This is a complex topic,and I doubt an interviewer would expect anyone to provide a thirty minute lecture, which wouldn’t completely cover the topic. Instead, I would think that if an interviewer is interested in this topic, they will ask probing questions to determine if you understand the concepts. This means you can’t just memorize the definition. You should ensure that if I have you a table, you could break it down into the entities for 1st, 2nd, and 3rd normal form. Perhaps you should read a few basic articles to get some understanding. Maybe you need to delve a little deeper into design and the anomalies that normalization attempts to mitigate.

    Prepping for an interview shouldn’t be a cram session to learn more, but really a review of concepts you understand. Make sure you have some examples to explain your thoughts and practice a few of these questions with a friend by giving them an answer and listening to their feedback.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.6MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Is This Data Science?

    In looking at the preliminary results of his salary survey, Brent Ozar noticed that female salaries seemed to be lower than males. The post focused on a simple analysis of data, the kind that many of us have done in our organizations. We will look at some data, notice some anomalies, and produce some report that allows others to look deeper or make a decision.

    The examination of the salary data was taken a bit further by Eugene Meidinger, who wrote a post that started the title with “Practicing Statistics“. His analysis was interesting, not trying to determine the reasons or causes, but just decide if there were some significant patterns in the data. Whether you think there are or aren’t issues with salary, I thought the statistical analysis was done well, and really a basis for what I would call data science.

    Data Science is a hot topic right now in many organizations. In fact, for the last year, quite a few organizations are trying to incorporate more data science into their applications, and the hiring of “data scientists” is rising, with higher salaries being paid. There are various definitions of the practice, with none being standardized. There are many curriculums out there from colleges, and even one from Microsoft. In fact, quite a few SQL Server people have completed that coursework.

    Data Science seems to mean many things, which is both good and bad. Like being a DBA, there is a lot of room for interpretation and quite a bit of variance in what we may do as a job. I’ve often made a good living as a DBA, being slightly out of the normal reporting structure, having autonomy at work, and usually able to make a difference to a variety of groups. However, I’ve also found that many places don’t want to hire a DBA or don’t think they need one, preferring to let some Windows admin or developer perform those duties. Microsoft doesn’t have DBAs as a job title inside the company, usually using IT Ops staff for those duties. That means it can be hard to find a job at times if many companies don’t think they need that position, which might be the case for data scientists as well.

    I prefer to think positively, that data science and data scientist positions are going to grow and be profitable for some of us. We’ll need to learn to have some statistical basis for our analysis, and certainly regularly improve our knowledge of the tools for things like machine learning, but we will find ways to perform data analysis that’s beyond what most business people would complete in Excel. I think we’ll find that those of us that work with data analysis will have lots of opportunities in the future, no matter what we’re called.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.3MB) podcast or subscribe to the feed at iTunes and Libsyn.