June 16, 2023 at 9:22 am
We're planning to start upgrading all of our 200+ SQL Server 2016 instances to SQL Server 2022 over the coming year or two. We have a Volume Licence Agreement and all of our instances run either Enterprise or Developer edition. We have one Azure instance for a chatbot. The rest is on-premises.
I'm combing through Microsoft Learn and SQL Server sites in general for changes to see if there are any reasons why we should possibly wait for another year until the Extended Support finally runs out.
I don't expect to find any reasons not to (and so far I haven't found any) but, in order to protect myself against my own biases, I am acting in devil's advocate mode. Upgrading seems like the obvious choice.
Are there any reasons not to start upgrading now rather than in a year's time? Is it so much more resource-intensive that we will need an immediate hardware upgrade or have less RAM/CPUs/bandwidth available?
I would be happy to have your experiences, both positive & negative.
June 17, 2023 at 10:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 17, 2023 at 8:12 pm
As I am sure you are aware, extensive testing, including stress testing, is key to any migration. Personally I would be loathed to put any critical production systems on a version of SQL Server which has been out for less than a year.
Maybe the Devil's Advocate should ask if all your systems need to continue using SQL Server or should some of them be moved to MySQL or PostgreSQL?
June 18, 2023 at 12:36 pm
My advise: read Brent's blog about SQL2022 to get a good idea what the product is (still) missing/having flaws of ...
SQL Server 2022 Keeps Getting Worse.
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
June 18, 2023 at 5:02 pm
Heh... this reminds me of every bloody release since and including 2005. The ones before that didn't seem nearly as bad. Maybe because there were fewer moving parts.
The spooky part is that they didn't get things right in 2012 until SP3, which is the equivalent of years worth of CUs.
Then, there are releases like 2016 where they changed some things that cause "silent failures producing incorrect rounding" in the DateTime datatype. Very fortunately, I never relied on the code that was affected.
I only recently discovered this while writing an article on how to properly combine DATE and TIME columns (write still in progress). The most common method people use used to work correctly prior to 2016 and hasn't since.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2023 at 5:47 am
Thank you everyone for your feedback.
As I am sure you are aware, extensive testing, including stress testing, is key to any migration. Personally I would be loathed to put any critical production systems on a version of SQL Server which has been out for less than a year.
Which is part of the reason that I am waiting until now and hoping to benefit from the experiences of early adopters. We will be putting our first application on SQL Server 2022 this month, a figurative dipping of the toe in the water. The devs will have it today and once the testers have approved it, it should be live by autumn.
Maybe the Devil's Advocate should ask if all your systems need to continue using SQL Server or should some of them be moved to MySQL or PostgreSQL?
We have had this discussion internally and the general consensus is that once SQL Server is no longer available on-premises (and Microsoft obliges us to migrate to Azure), we will strongly consider PostgreSQL. There is a lot of code to be re-written and lot of new learning to be done if we are to become proficient in PostgreSQL. The idea that a version of SQL Server would be so bad as to avoid it has not cropped up though.
My advise: read Brent's blog about SQL2022 to get a good idea what the product is (still) missing/having flaws of ...
Thanks for this. it is rather sobering and shows that I have a lot more to read.
Heh... this reminds me of every bloody release since and including 2005. The ones before that didn't seem nearly as bad. Maybe because there were fewer moving parts.
Then, there are releases like 2016 where they changed some things that cause "silent failures producing incorrect rounding" in the DateTime datatype.
And, for a change, I shall be thankful of our developers using DATETIME2...
June 19, 2023 at 3:25 pm
Jeff Moden wrote:Then, there are releases like 2016 where they changed some things that cause "silent failures producing incorrect rounding" in the DateTime datatype.
And, for a change, I shall be thankful of our developers using DATETIME2...
Agreed but, let me ask... do you have any tables where they have a separate DATE and TIME column and it sometimes needs to be recombined into a DATETIME2 value? If so and the use the most widely posted method to do the recombination, you're still affected.
And, do you have any place where an implicit conversion to DATETIME occurs, such as when people use the Date Serial Number of "0" to represent 1900-01-01? If so, you're still affected.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2023 at 5:33 am
Agreed but, let me ask... do you have any tables where they have a separate DATE and TIME column and it sometimes needs to be recombined into a DATETIME2 value? If so and the use the most widely posted method to do the recombination, you're still affected.
We have no columns that store time data only and since we are in one time-zone only (CET), we don't have to worry about time-offsets. For almost everything we do, the date is the lowest level of logical granularity. We store date & time data in a variety of datatypes (mostly DATETIME2(7) but also DATETIME2(0), DATETIME2(3) & SMALLDATETIME) for notification dates, timestamps & metadata.
June 20, 2023 at 2:25 pm
Sounds good. You could still be affected if you copy a DATETIME2(7) into any datatype that has a lower resolution.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2023 at 6:26 pm
Why plan on only jumping all the way to 2022? It seems that 2019 is pretty stable now and they may be done implementing breaking updates - there have been about 5-6 now with no new major issues. I am still waiting likely 2-3 more CU myself before I decide to trust MS for widespread deployment, but it seems like it is getting close.
June 23, 2023 at 6:04 am
Why plan on only jumping all the way to 2022? It seems that 2019 is pretty stable now and they may be done implementing breaking updates - there have been about 5-6 now with no new major issues. I am still waiting likely 2-3 more CU myself before I decide to trust MS for widespread deployment, but it seems like it is getting close.
It's what we've done in the past. We take large leaps forward every 6-7 years or so, usually to the latest version.
When I joined the company in 2008, we were primarily a DB/2 house with a few SQL Server 2000/2005 installations. In 2010, we migrated everything to 2008 and then again around 2017 we upgraded to 2016. Now 2022 is the obvious choice. We are starting our migration now. We have 200+ instances and almost all will go through the testing process that is a part of the software release process. This will take us the better part of 2 years, by which time the Extended Support will be drawing to a close.
SQL Server has been so reliable in the past that it was a strategy that has made (and still does make) sense. I'm not expecting any bumps with SQL Server 2022. But should anything serious enough crop up that causes us to drop 2022, then 2019 is there as a backup.
June 23, 2023 at 8:58 am
As everyone else has said, test everything.
Expect to find some breaking changes, be it functionality or performance. It always happens between major releases, so nothing new in this concept. The workload will be no less with SQL2019 so go for the latest with SQL2022.
Deal with the breaking changes for SQL2022 in the same way as any other issue. Scope it, prioritise it, and eventually fix it. (Add 'moan about it' if it helps...)
Overall you should find SQL2022 a better product than SQL2016. If you use AGs then the auto resync and Distributed AGs introduced in SQL2017 could be useful. The Contained AG new to SQL2022 adds useful stuff to explore. Inlining of functions in SQL2019 helped in performance, likewise the columnstore index improvements. Ordered columnstore in SQL2022 could be useful, it helped my previous old place when they started using the Redshift equivalent in 2014.
Ultimately there is lots to explore with SQL2022 that were not possible with SQL2016. One aspect is putting at least some of your SQL Server instances on Linux. This will help your teams get up to speed on how to do things with Linux, which will be vital if/when you use PostgreSQL.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply