Compatibility Level Confidence

Posted on September 21, 2020 by way0utwest

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.

Posted in Editorial | Tagged , | Comments Off on Compatibility Level Confidence

Daily Coping 18 Sep 2020

Posted on September 18, 2020 by way0utwest

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

Today’s tip is to make time to do something you really enjoy.

I’ll be on holiday, but my plan is to spend some time playing guitar, sitting on the balcony of our condo and enjoying the afternoon.

If the weather is bad, I’ll see by the window.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 18 Sep 2020

Recharging and Cooling Off

Posted on September 17, 2020 by way0utwest

A couple days with my wife in the mountains is just what I need.  We’ll be out of touch the next few days, just us and the dogs, doing some hiking and relaxing.

I’m trying to meet the goal of having used up 60% of my vacation by the end of Sept. I think I’ll be a day short, but I’m busy enough that it’s tough to find enough places I can get away and still manage the workload.

Part of that’s my fault, as I was delaying booking days off, thinking we might go somewhere. Finally I decided to just book time away. Thur/Fri this week and next. I’m a little jammed up Mon-Wed, but it’s worth it to not take a laptop and get away.

Posted in Blog | Tagged | Comments Off on Recharging and Cooling Off

Daily Coping 17 Sep 2020

Posted on September 17, 2020 by way0utwest

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

Today’s tip is to let go of being busy. Allow yourself to take some breaks today.

I’m on holiday today, a welcome break as I try to comply with my 60% taken by the end of this month. As a result, I’m not trying to be busy. I asked my wife if we could take a couple days and just relax. No horses, no chores, no plans.

We’ll pack a few clothes, which is really the biggest stress item today. Then we’ll put the dogs in the car and drive to the mountains to stay for a few days.

A welcome break.

Posted in Blog | Tagged , , | 2 Comments