Voice of the DBA

Category: Editorial

  • Compatibility Level Confidence

    SQL Server databases have had a compatibility level in them for a long time. This is a number that essentially corresponds to a version. We had 70 for version 7.0, and then we went to 80 for SQL Server 2000, 90 for 2005, and we currently are at 150, which you just have to know maps to 2019. I really miss real version numbers.

    In any case, there is this statement on the Compatibility Certification page that says this: ” As long as the application does not need to leverage enhancements that are only available in a higher database compatibility level, it is a valid approach to upgrade the SQL Server Database Engine and maintain the previous database compatibility level, with no need to recertify an application. ”

    That’s a good statement, but it feels soft to me. This doesn’t really seem to me that Microsoft wants to stand behind the compatibility level as a guarantee that the way all code works on SQL Server 2017 with compat level 140, is the way it will work on SQL Server 2021 with compat level 140. That pages does say that the queries display the same behavior, and query shapes are protected, but what I, and most people, want to know is that our application will run the same.

    What about statistics behavior? What about encryption algorithms (that’s already been an issue)?  What about the Cardinality Estimator? In my mind, there are still lots of unknowns, and while I appreciate MS moving in this direction, I’m not sure they are as confident as they’d like you to be in their statement.

    I’m sure some of you have used the database compatibility level to upgrade an instance, but keep a database behaving as it would on the prior version. If that’s worked well, let us know. If that caused issues, what did you do or was there a workaround? Trace flags, perhaps some database or instance settings might help, and if they did, I’d like to know. In fact, I think Microsoft would like to know, so leave a comment on how you feel, with our without details.

    Moving forward, would you consider upgrading the engine, without much testing, and assume that your application will still work well? Do you think vendors would provide support with a different version, but the right compat level? I’m not sure they will.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

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