February 25, 2020 at 11:34 pm
You'd think it's obvious why we need to upgrade from SQL 2012 to SQL 2019 (4 versions), but I have to justify the upgrade to non-technical executives. I'm looking for good blog posts/white papers/articles that enumerates reasons why staying on SQL 2012 is not viable, and why upgrading to SQL 2019 is necessary. Any suggestions?
February 26, 2020 at 1:53 am
Besides being out of support? (or is that 2008?) Kind of depends on how you use/what you use SQL Server for. Some of the new features may be part of Standard now, while they used to only be available in Enterprise.
February 26, 2020 at 3:40 am
Security improvements and the fact that 2012 is now 7 years old. The biggest advantage is that there are a fair number of features that were formally only available in the Enterprise Edition that became available in the Standard Edition as of 2016 SP1. That could be a fairly large money saver if you don't need more than 128GB of RAM.
There are several other features that have come out in 2019 not the least of which is the "Inlining of Scalar Functions" which is supposed to greatly improve the performance of even existing Scalar Functions IF they are "inlineable". I'm still on 2016 so I can't tell you how well that works... or not.
You do have to watch for the change in the Cardinality Estimator that came out in 2014. You can optionally go back to the old CE at the database level without having to reduce the compatibility level.
They also made it so that the equivalent of TF 1117 and 1118 is hardcoded in TempDB in 2016 and up. You have to be a bit careful there because of an issue they've not yet fixed and that is SET IDENTITY INSERT ON will cause a full sort in TempDB even if the related inserts qualify as no-sort-required minimal logging. That can be a problem because, if you work with a large amount of such data, all the files in TempDB will grow and possibly run you out of TempDB space like it did for me.
You also have to be careful of the "improvement" known as "Fast Inserts". If they're done in a bulk fashion, the system will create new extents without looking to see if space is already available. If you have an app, such as WhatsUp Gold (to name just one... a lot of 3rd party code follows suit) that uses INSERT BULK (not to be confused with BULK INSERT), it will create a brand new extent to hold just one row. This makes a huge amount of unused but allocated free space that can't be used by anything other than the table it has been assigned to. There is a server wide trace flat (TF 691) that you can turn that nonsense off with, though. Once you turn it off, the unused but allocated space will start to be used although you may have to do index rebuilds if you want it to recover more quickly.
As with all else, change is inevitable... change for the better is not BUT, updating from 7 year old software, getting the security enhancements that go with the upgrade, and a possible serious savings if you can change from Enterprise Edition to Standard Edition are well worth it. The other decent changes they made make it worth it to me even though it might require me to do this a bit differently... which is actually true for every freakin' upgrade they come out with. It'll also keep you up to date for all the fixes they might make.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2020 at 12:04 pm
How about positive changes to our Recovery Time Objective? That alone would do it for me when you add in all the other improvements that have occurred since 2012.
For a business, here's a simple, non-technical argument. The support lifecycle for the product ends in 2022. It's now February 2020. It's going to take us X months (let's say 15, big enterprise, lots of testing & backfill, 3rd party vendors, all the fun) to migrate to a new version. We need to start right now so that we are never out of support. It's all about avoiding serious risk for the business.
Small businesses may ignore this. Some bigger businesses that only have a few instances may ignore it as well. Any enterprise that is dependent on SQL Server being up, available, and ready to go, only ignores the support lifecycle if they're stupid (yeah, I know).
Seriously, the difference between 2012 and 2019 is just night and day. Let me toss one more technical reason in there: updateable columnstore indexes. And if you think the business won't get excited about columnstore indexes, then you haven't seen even a bad demo of them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 26, 2020 at 3:16 pm
Jeff,
Thanks for the input. I knew some of this, but not the issues with the Insert BULK, which is not a feature that I really cared about until now. I am also going to include in my justifications, virtual databases... especially for the TempDB. I was doing experiments with that in our test environment using vRAM disk carved out of excess RAM. The biggest reasons I'm promoting are the increased performance and security in SQL 2019. Ultimately, SQL 2012 is 4 versions behind and poses too many risks to stay on.
Cheers!
Brandon Forest
Senior SQL DBA
Purple Communications
February 26, 2020 at 3:29 pm
Grant,
Thanks for weighing in on this subject. It's stupid that I even have to justify the upgrade because we're 4 version out of date, but the bean-counters are not convinced that the upgrade is not worth the investment until absolutely the last minute.
We recently implemented a Cohesity Backup system, because we were hacked on Thanksgiving 2018 with the RYUK ransomware virus. We were using Iron Mountain for deep storage backups, but they couldn't get us our backups for two weeks! The only reason we're still in business is that I had instituted multiple AD service accounts to run our SQL environments. The hackers had tried to shut-down the SQL services, but they didn't have the passwords they had gleaned from other places. I saved the mdf/ldf files to an external drive and rebuilt the production servers from the ground up.
We're in the process of acquiring a new SAN and two VM Hosts to replace our aging system. That will take time, and we will build them out with Windows 2019 and SQL 2019... But in the meantime, I've got systems that I'm trying to rebuild now that should be upgraded for all these reasons. Thanks for all your blog posts. I've followed you for over a decade now.
Cheers!
Brandon Forest
Senior SQL DBA
Purple Communications
February 26, 2020 at 4:12 pm
Thanks! Appreciate the kind words.
I think it's a fine thing that you have to justify the move. I suffer from "new shiny" syndrome and would be upgrading constantly if someone wasn't tapping me on the shoulder. However, with less than two years of full support left, the decision to move off of 2012 is actually really simple.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 27, 2020 at 1:30 pm
You would be in the best position to make the justification by learning what are the new features and then seeing if you can make a business case for why an upgrade would be useful. For example, if you do a lot of work with time zones, the time zone improvements with 2016 are great. But from at least what I've seen so far, I wouldn't have a good business case for my business to move from 2016 to 2019.
The other points made about out of support are good, but it might be a risk someone is willing to make if it's also not cost effective.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply