Voice of the DBA

Category: Editorial

  • Schema Security

    How many of you grant access to schemas in your databases? I’m curious because I slowly am starting to see more and more people using schemas in their designs and not defaulting every object to “dbo”. That was the default action for many developers for years, and it’s still a habit of mine. Without SQL Prompt to add the schema to my queries, I’d still be producing code that followed that practice.

    Schemas exist to allow some separation of different types of objects. In early versions of SQL Server, we didn’t have schemas, but considered the third of four part naming to be the owner. In other RDBMSes, the use of a schema was to allow grouping and separation of different objects. This was also, and still is, a security boundary that helps ensure that authorized users can be easily limited to data access for certain objects.

    This is also a nice organizational boundary for related objects that have the same schema, or even name. I have seen auditing or etl schemas that maintain the same named table that exists in a dbo schema, but is separated by placing this in a separate schema. I have even seen some archiving schemas that move data to a related table that exists in a different schema, making it easy for new administrators and developers to find (or remove) older data.

    This week, let us know if you manage objects and security with schemas or if you stick to granting access at a database/object level the two more common ways of working with databases. If you have never used schemas and stuck with the defaults, perhaps you want to learn a bit more about how schemas work and the ways in which they may make your work easier.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Pulling the Plug

    I’ve worked on some large scale software projects. For the purposes of this discussion, I’ll define a large scale project as one that takes more than a two man-years to develop. I’ve never spent two years on a project, but I certainly have been involved with some that took more than two people, all more than a year to complete to some level. Even while working in a DevOps style environment where we released software every week, I would count a project that followed this process across a year for multiple developers as a large scale project.

    When working on a large project, it’s hard to sometimes keep perspective on whether to keep going or stop and change directions. We often try to continue to improve and fix a project, even when it is not going well. There’s a term for this: Concorde fallacy. I’ve seen this similar situation occur pre-Concorde in other industries, who often have made an investment and continue to do so because of the weight of that previous investment on them. If I’ve spent $20 or $20mm on a project and I am evaluating whether to spent an equivalent amount moving forward, I can’t continue to worry about the money I’ve already spent.

    That money is gone whether I stop now or continue on. What I ought to do is look forward and decide if future spending is worth the investment. Certainly my reputation, and often some pain for switching or decommissioning existing work is to be considered, but that’s part of the value and too often we become afraid of abandoning something we ought to get rid of for a newer, better something else.

    This is discussed a little in the case of legacy technology in this piece about pulling the plug. If you start to avoid maintenance, especially in this day and age of additional regulation, you need to upgrade. If your staff or users are starting to pick other software because you can’t maintain your application, you ought to abandon it and move on. Does some application take too many resources to keep going? Look for something else. In fact, some portion of your staff ought to be making evaluations on alternatives. Not as a full time job, but with some framework to help them perform a cost benefit analysis.

    This doesn’t mean pick the shiny new thing or move to the cloud or make everything self-service, but it should be an evaluation of how well you can get software to your users. One of the best lessons of The Phoenix Project, to me, is that continuing to bet on a bad software project isn’t as good as building a plan to get replace it with something better, often something that is built with a more agile, DevOps process. This may not be the cheapest or quickest way to move, but if can often be the best way to change.

    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.

  • The RDBMS is Often the Right Tool

    I’ve spent most of my career, actually most of my life, working with relational database systems. I tend to agree with this piece on NoSQL systems being the wrong tool for many applications. I do think that’s the case, though to be fair, I haven’t done anywhere near the amount of work on key-value stores, document databases, graph databases, etc. as I have on relational systems. I admit I’m biased, but I think the author is as well. He works at MemSQL and used to work on SQL Server.

    The piece notes that relational databases didn’t deal well with certain problem domains and various NoSQL classes databases evolved to handle the challenges. The good things about this is that relational databases were forced to grow and adapt, incorporating new features and functions. I do truly wonder if that is the reason we’ve seen rapid changes in SQL Server, both in capabilities and the pace of new releases.

    Many of the non-relational systems are built to incorporate transactions, but usually within a very limited range. For example, many document databases only allow ACID compliant transactions within a document, not across documents. The same might be true in graph structures. Many of us working with relational systems know there are often a decent number of queries that need transactional consistency across entities, and sometimes across different databases. Our applications demand this, and while it might be overkill at times, it’s a capability that is definitely needed.

    There is also a discussion of schema-less features. I do understand the attraction for application developers. Adding or changing the way data is stored is much easier when there isn’t a set schema for writes. This truly does make development easier. What it doesn’t do, however, is remove the technical debt of having a schema. Instead, this debt is pushed to the reader, and often, means the application must maintain the ability to apply some schema for reads to make sense of the data. Does that mean everything needs a hard, strongly defined schema? No, but I think it means we need some schema.

    Ultimately I think that there are problem domains best solved by different platforms. In most cases, I’d argue a relational data store is the better platform, but I am certainly willing to entertain the discussion of where a graph, document, or other data platform may work better. Just be sure you argue for the problem being solved. Most of us aren’t operating at the Google/Facebook/Spotify/Netflix scale, and we have different problems to solve. We need something that works well for our problem, not the one another organization is facing.

    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.

  • Should the Data Lake be Immutable?

    There’s a concept in computer science of immutability. At a high level, this means once something is set, it isn’t changed. Various computer science languages do this with variables, where values don’t change, though variables can be destroyed and recreated.

    In the PASS keynote, Dr. Ramakrishnan pointed out that we have silos of data, often in disparate systems where we keep our information. We want to query this together, so we transfer this to a data warehouse or data lake (the future view) and that items in the data lake are immutable. They aren’t allowed to chang in the way that we update values in our relational databases. We should just read the most recent version of any data, and if there is an update, just add a new set of data.

    That’s an interesting concept, but not sure I agree. I think that while we might often want to use a simpler process, there are cases where we do need capabilities to edit. Imagine I had a large set of data, say GBs in a file, would I want to download this and change a few values before uploading it again? Do we want a large ETL load process to repeat? Could we repeat the process and reload a file again? I don’t think so, but it’s hard to decide. After all, the lake isn’t the source of data; that is some other system.

    Maybe that’s the simplest solution, and one that reduces complexity, downtime, or anything else that might be involved with locking and changing a file. After all, we wouldn’t want queries that could potentially read the data in between us deleting a value and adding back a new one.

    If you’re a data warehouse or analysis person, what do you think? Does it make sense to keep the data lake as immutable and reload data that might not be clean? Let us know today.

    Steve Jones

    The Voice of the DBA Podcast

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