Voice of the DBA

Tag: sql server

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

  • Digging Into Internals

    Hugo Kornelis is a fellow Data Platform MVP and friend. He runs SQL Server Fast, and we recently put up an interesting page with this title: Debugging with debugger: investigating SQL Server’s internal structures. That’s a great title, and it is for a presentation that he is building and getting ready to present. I suspect it will be at a virtual conference soon, but we’ll see.

    Hugo digs deep into the details of how things work. If you read his blog regularly, you’ll learn a lot about the ways in which SQL Server processes queries. You should learn how to better tune your own queries and produce efficient code. At least that’s the goal.

    However, I wonder if most of you feel that way. While I see performance tuning sessions are often being the most popular topics at events, are you interested in internals? Do you actually use information from posts and presentations to dig in deeper to the way your queries are compiled and executed?

    I know Plan Explorer is a very popular tool among MVPs, and lots of people download it, but I still see so many basic questions on execution plans, I sometimes wonder if people are really using the tool to change they way they write code.

    If you want to learn more about query tuning, Hugo, Grant Fritchey, Aaron Bertrand, Paul White, Erik Darling, and many others will try to teach you. Just be sure you try and use the knowledge they impart in your daily work.

    Steve Jones

  • Adding Analysis Services (SSAS) to your SQL Server instance–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    This is a fairly simple task, but recently I needed to test something in SSAS. I hadn’t installed this on my machine, so I had to add it. It’s simple, but worth a quick post.

    The way to add SSAS (Analysis Services) to your instance is to run the setup program for SQL Server. When you do this, pick the Installation tab from the left menu.

    2020-04-30 11_08_06-SQL Server Installation Center

    From here, I want the first item (New SQL Server stand-along installation or add features to an existing installation). SSAS is a new feature.

    When this starts moving, be sure you stop on the Installation Type screen and move the radio button to the “Add features” item.

    2020-04-30 11_08_42-SQL Server 2019 Setup

    The next screen is where you will check the Analysis Services box.

    2020-04-30 11_09_26-SQL Server 2019 Setup

    You will then get an SSAS config screen. In my case, I was testing something with the Tabular model. If you don’t know which to choose, you need to research this. Here are a couple links:

    You also need an account here. I typically add my local account here for dev machines.

    Don’t forget to reset the data directory if you need to do this.

    2020-04-30 11_09_53-SQL Server 2019 Setup

    That’s it. Let the setup run and you’ll have SSAS installed.

    2020-04-30 11_12_38-SQL Server 2019 Setup

    How do you check? That’s another post.

    SQLNewBlogger

    This is a really simple task, but it’s something that you should be familiar with. I needed to do this, and just documented my task, this one simple thing.

    This can drive interviewers to ask about how I did this, why, what I tested, etc. This helps control the interview and the direction in which someone might query your knowledge.

    As you do small tasks, write about the specific task. If you want to cover what you did after this task, write a second post.

  • Encryption Libraries

    One of the ways we work to implement better security is through the use of encryption. I remember the early days of PGP, VPNs adding end to end encryption, IPSec becoming available, and more. I was excited to see column level encryption come in SQL Server 2005, but it was a bit complex to implement and had issues. The evolution over time with TDE and Always Encrypted were welcome additions, but we can do better.

    Handling encryption in the database can provide some protection, but really, we’d like to see an end to end solution, that ensures data is protected on the wire as well as in the db. Microsoft does a lot of research, and released a Simple Encrypted Arithmetic Library (SEAL) as open source code. This is a C++ library that developers can use to protect their communications. Last year they also released a .NET wrapper to make it easier for a wide variety of developers to use.

    There is a lot more to deal with when handling encrypted at scale. Indexing and search operations are difficult and complex, and can require lots of resources. I think that the Always Encrypted in SQL Server 2019 with Secure Enclaves is a good evolution, but it’s complex to set up, tooling is poor, and maybe more disconcerting, there are potential vulnerabilities with secure enclaves on Intel chips.

    Certainly a combination of developers being able to use something like SEAL and a linkage with a secure enclave on the server for set-based operations might be something we can look forward to in the future. I hope so, as security continues to be a challenge for many of us.

    I’d also like to see more software that is built to ensure users can’t access huge amounts of data, and perhaps easier ways to control the export of large amounts of data. We do need this capability, but we also want very few people, or no people and only a process, with rights to do so.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.