February 17, 2022 at 11:46 am
Hi
Restoring a backup of a 2012 database to 2017 instance and will copying over logins and Jobs using scripts, just wondering what I do with compatibility level of the database once its restored over to the 2017 instance.
thanks in Advance
Caz100
February 17, 2022 at 12:53 pm
You need to test everything in the new compatibility level before you attempt to migrate.
The cardinality estimator was changes in 2014 / compat > 120 in a huge way. Somethings will be faster, some will be the same, some will be slower. So you have a performance exercise to complete also post changing the compat level.
Most thing can be resolved by a statistics update using full scan after the switch but others won’t be that easy to resolve.
So you will need to investigate what problems remain performance wise after the fact.
February 17, 2022 at 12:55 pm
As another point to note.
Why SQL2017? This is soon dropping into extended support (October 2022) after which any product bugs will not be fixed unless absolutely critical.
If it was me I’d be looking to migrate to SQL2019 to keep full bug fix support going rather than extended support.
February 17, 2022 at 1:51 pm
You are fine with SQL Server 2017 -- https://docs.microsoft.com/en-us/lifecycle/products/sql-server-2017. October 2022 is end of mainstream support. Extended Support is October 12, 2027.
https://www.brentozar.com/archive/2019/01/which-version-of-sql-server-should-you-use/ is worth reading on determining which release to go to. My experience tells me that Microsoft tends to support the latest two versions that are subject to frequent Cumulative Updates for the latest release. I betting person may make the correlation that October 2022 will be SQL Server 2022 GTM. Then what you will see is Microsoft Resources focused on 1) SQL Server 2022 2) SQL Server 2019 and a distant 3) SQL Server 2017.
I call SQL Server 2019 the COVID release. SQL Server 2022 is post COVID. Has a lot to do with staffing. I believe SQL Server 2022 will be much better for than SQL Server 2019. SQL Server 2019 went live before COVID and then suffered in the CU cycle needed to gain maturity due to again staffing issues. With being at CU15 I think it is okay now, but a lot of new features/enhancements suffered due to COVID.
Lastly I would keep up with https://sqlserverupdates.com/ for the latest on patches and release notes.
I hope this helps.
February 17, 2022 at 3:34 pm
It just depends what level of support you want from Microsoft.
Yeah 2017 is good for another 5 years in extended support, but it is best endeavours and if it’s a bug, you’re out of luck getting it fixed unless it’s a CVE or something else MS would deem as critical.
Going through the pain of an upgrade I’d personally want to be on latest so I know I have the best support arrangements I can get.
Personal/Company preference at the end of the day.
February 17, 2022 at 6:26 pm
Hi
Restoring a backup of a 2012 database to 2017 instance and will copying over logins and Jobs using scripts, just wondering what I do with compatibility level of the database once its restored over to the 2017 instance.
thanks in Advance
Caz100
I would recommend changing it to 140 (2107) compatibility if you have performed adequate testing. Are you prepared to perform exhaustive performance testing, which is what you will need to do? If the results of the testing show that this makes sense, then go for it.
I suspect that you will find a combination of re-writing some procs, executing them under a lower compatibility level, setting the compatibility to a lower level, or all of the above will be what works best.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply