September 21, 2020 at 12:00 am
Comments posted to this topic are about the item Compatibility Level Confidence
September 21, 2020 at 8:14 am
I feel the compatibility level facility is a technical fix for a human problem.
In the ideal world we would be able to install the latest SQL Server edition an have all our databases tested at the latest level.
In the real world our new development will be done at the latest level, those apps where an upgrade provided a solution to an existing problem will be ported. The other applications will be ported in their own good time as business priorities allow. The human problem is that fixing unbroken apps is always going to be at the bottom of the list of priorities.
I feel that compatibility levels are OK as a safety rope to allow you to upgrade your databases but, to use a climbing analogy, I like to have 3 points of contact with the rock face. I never quite trust that rope.
September 21, 2020 at 10:47 am
Is it viable to check for application performance regressions by running a workload on SQL Server 2019, recording metrics, and then running the same workload on the same app & DB with it's compatibility level set to a "known good" version such as 2016? (and then comparing metrics and query plans)
September 21, 2020 at 1:29 pm
For what it's worth: Even last year, I've seen vendors still installing SQL2005 Express with their "state of the art" stuff.
I've always been "the first with who made a fuss out of it".
I see many industry grade technical companies just sticking with old versions because their stuff works on it and they do not want to invest in newer versions because they feel their software doesn't take any advantage of it.
Strange thing is, that attitude is less when they use oracle db.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 21, 2020 at 2:54 pm
I feel the compatibility level facility is a technical fix for a human problem.
In the ideal world we would be able to install the latest SQL Server edition an have all our databases tested at the latest level.
Does this mean we need complete automated testing? Is that the issue?
I think there is something here, but with MS releasing on that 24-36mo cycle, that is a lot to ask across any minimal number of applications and databases. Plus there's patching involved. That brings me back to the testing issue, which is likely more necessary than ever since we have security issues and we know that those need to be dealt with, as unpatched systems are the main way that exploits from criminals occur, after social engineering.
September 21, 2020 at 4:42 pm
Is it viable to check for application performance regressions by running a workload on SQL Server 2019, recording metrics, and then running the same workload on the same app & DB with it's compatibility level set to a "known good" version such as 2016? (and then comparing metrics and query plans)
First, it likely is, but how would you do that? I don't know of a good tool to allow this.
In terms of an upgrade, this is more that you are on 2016, but then you upgrade to 2019 and need to run as 2016 compat. In that case, I think this would be helpful to determine the upgrade (or consolidation) is fine.
September 22, 2020 at 8:15 am
Jon 0x7f800000 wrote:Is it viable to check for application performance regressions by running a workload on SQL Server 2019, recording metrics, and then running the same workload on the same app & DB with it's compatibility level set to a "known good" version such as 2016? (and then comparing metrics and query plans)
First, it likely is, but how would you do that? I don't know of a good tool to allow this.
In terms of an upgrade, this is more that you are on 2016, but then you upgrade to 2019 and need to run as 2016 compat. In that case, I think this would be helpful to determine the upgrade (or consolidation) is fine.
Thanks, Steve. This would be performance testing a fresh instance of the application (so it's purely a test instance). We have automated performance testing tools, so the idea is to run those, flip compatibility levels, and then run them again. The goal is to support 2019, so we would do separate correctness testing to make sure everything is still working fine.
September 22, 2020 at 3:20 pm
Can I ask what tools you're using? I assume you're getting timing from third party tools. What I'd love is a way to check execution plan regressions.
September 23, 2020 at 3:48 pm
Can I ask what tools you're using? I assume you're getting timing from third party tools. What I'd love is a way to check execution plan regressions.
Sorry, I may have mistakenly implied that we're checking the execution plans automatically - unfortunately that is a manual process (an automated tool would be great though!). We know which queries are the most complex, so if we see a dip in any of the application functions, we'll have a good idea of where to look. The metrics collection and the workload are automated at least, so it makes the process not-too-painful.
October 13, 2020 at 12:18 pm
I have several servers on SQL Server 2019 but all the Compatibility Levels are on 110.
The sysadmin has been doing these "upgrades" since SQL Server 2012 I suppose.
And now we are in 2020 and I have to upgrade all the databases.
The cherry on the cake? All application hitting the servers are written in Access and the are basically legacy code from the '90. We have 3 developers which are 60, 66 and 74 years old.
I'm using 3 tools:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply