April 21, 2019 at 6:27 pm
We are preparing for this migration, and wondering what are the worst issues and challenges we can anticipate and be prepared to resolve when migrating databases from 2008 R2 and 2005 versions to 2017 Standard edition.
Source 2008 R2 edition is Enterprise, and source 2005 server is Standard. Is there anything on the web (we have not found much...) like a list of worst problems to anticipate (in terms of the hardest to resolve) ?
thanks.
Likes to play Chess
April 22, 2019 at 1:16 pm
The single biggest issue you're going to hit on the database side of things is the new cardinality estimation engine that was introduced with SQL Server 2014. The new CE is great for most queries, benign for some queries, and downright evil for a few queries. The best bet to deal with this is to use the Query Store. When you migrate your databases into 2017, enable the Query Store on them, but leave them in the old compatibility mode. They'll use the old CE and performance should be largely the same (not counting bigger/faster machines,etc.). Run your load for an appropriate period of time (day, week, month, you figure it out). Then, after that period, with the data gathered by the Query Store, change the compatibility mode. If you see query regressions, you can use plan forcing to make them use the old plan until you come up with a more permanent solution (which usually entails rewriting code).
"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
April 22, 2019 at 2:12 pm
The single biggest issue you're going to hit on the database side of things is the new cardinality estimation engine that was introduced with SQL Server 2014. The new CE is great for most queries, benign for some queries, and downright evil for a few queries. The best bet to deal with this is to use the Query Store. When you migrate your databases into 2017, enable the Query Store on them, but leave them in the old compatibility mode. They'll use the old CE and performance should be largely the same (not counting bigger/faster machines,etc.). Run your load for an appropriate period of time (day, week, month, you figure it out). Then, after that period, with the data gathered by the Query Store, change the compatibility mode. If you see query regressions, you can use plan forcing to make them use the old plan until you come up with a more permanent solution (which usually entails rewriting code).
He he, are you trying to promote your performance tuning book again Grant 🙂
😎
You will need to "stage" the upgrade! My advice is to use an standard instance of 2014/2016 as "staging" and restore the backups from the sources there as 2017 will not go back to 2005. You'll also have to check for enterprise functionality being used on the sources. Apart from that, this should be a walk in the park.
I am a little surprised that you haven't found anything on the internet on this, would have thought that you should have, my first google foo turned up 31M answers .
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply