Voice of the DBA

Category: Editorial

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

  • It’s the Engineers

    There are all sorts of articles and blogs on the Internet that try and teach you how to use a particular technique or technology to solve a problem. Plenty of this information is well written, and showcases the knowledge of that individual (or team) and how they’ve built a fantastic system. Every vendor building software platforms has reference case studies that show how well their system has worked for some customer. These are all good models that might give you confidence why SQL Server or MongoDB or Entity Framework or GoLang are good choices for your company.

    In reality, it’s not as simple as just choosing a platform or framework or language to make your application perform better. You really need the staff that understands how to use your choice of X and has some skill in building a system in that manner. It’s why I think that the cost of the software is a pittance compared to the cost of your people. If you have to train them, or they need to learn how technology X works, then you’re going to pay more in salary than you’d save in software licenses.

    There’s an article that I think illustrates this well, and might be worth passing along to your developers. It’s called Why Amazon DynamoDB isn’t for everyone and it’s worth a few minutes of your time to read. The gist of the article is that a NoSQL system, like DynamoDB, isn’t as simple and easy as you expect. It also says that for many applications, a relational database is a better choice because it’s often better understood and will solve most problems at small scale. For most of us, we don’t really get past what I’d consider small to medium scale, and so we should stick with relational systems.

    This isn’t to say that DynamoDB will be a problem for you, but there will be a learning curve for your developers if they haven’t used it in production and at your scale. The same thing occurs if a development team decided to switch from MongoDB to SQL Server because they like automatic tuning. They won’t necessarily configure and use SQL Server efficiently, and might not have their application perform well. Switching your development paradigm or technology is hard, and it will take time for your staff to get up to speed.

    Ultimately it really comes down to having good engineers that can write good code and understands the ins and outs of administering that particular platform. The database is extremely important here as it is the central location of your information, but the same arguments apply to frameworks and languages. As much as we want to learn and try new technologies and incorporate them into our work, we need to realize that it takes time to learn to use them well. Make small experiments, try POCs and slowly build up skills before you decide that your mission critical system needs some new technology that you read about and are excited to try working with.

    Steve Jones

    The Voice of the DBA Podcast

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