February 5, 2019 at 10:18 am
I have to support the vendor application upgrade on SQL Server 2014.
Here are some of the questions I was thinking about. Could you please add if I miss any?
How critical is this application? Does the upgrade will create any schemas and tables in SQL Server.
Does the SQL Server needs to have redundancy? Does there any DR requirement?
Please advise?
February 5, 2019 at 10:48 am
Have you tested this in another environment?
February 6, 2019 at 5:47 am
Please, don't upgrade anything to 2014. It's not that 2014 is bad. It's not. It's that 2014 introduced a potential problem and didn't supply good tools to deal with that problem.
In SQL Server 2014, a brand new cardinality estimation engine was introduced. For most queries this is a huge improvement over the old cardinality estimation engine. However, for some queries, it's either worse, or much worse. This results in, for some queries, a radical degradation in performance. To deal with this in 2014 you have three choices. One, never change the compatibility level of the database from the old version so the old cardinality engine is used. However, this means you don't get the benefits of all the other improvements made in 2014. Second, change the compatibility level, but put in a start-up traceflag that disables the new cardinality engine for the server. However, that means all the queries that could benefit across all your databases is limited by few bad actors. Third, modify the code of the badly performing queries to put the traceflag in there directly in order to get rid of the problem. However, that means modifying code which, frequently, just isn't an option for people.
We're looking at SQL Server 2019 being released relatively soon. To upgrade to a five year old version of SQL Server at this point is a bad choice right at the start. Add to it the issues with the cardinality estimation engine and it just doesn't make sense. A better choice is 2016 or 2017 (and I'd go for 2017) because they offer a much more viable solution for the cardinality issue, plan forcing through the Query Store.
I really strongly urge you to explore this in some detail. I promise you, you won't be happy upgrading to 2014 without having plan forcing to fall back on when some of your code behaves poorly.
As to your core question, it's a little all over the map. Are we upgrading or are we looking at DR requirements. They're two different things with different answers. I'd focus on one.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply