November 29, 2018 at 9:57 am
Quick question
restore backup of SQL server 2008 ENT edition backup on SQL 2016 STND edition directly? are there any issues involved?
November 29, 2018 at 10:03 am
Only where you have used enterprise features of 2008 that aren't in Standard Edition of 2016 and the breaking changes for SQL Server 2012-2016. And, of course, the cardinality estimator change in SQL Server 2014.
I suggest a good amount of testing first before you change your production environment to use the latest version.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 30, 2018 at 10:34 am
If you're upgrading from anything prior to 2014 to 2016 or greater (and don't ever upgrade to 2014), follow this simple set of steps:
Restore database
Leave compatibility level to old version of SQL Server
Turn on Query Store
Run your load for a period of time (days, weeks, months, you figure that part out)
Change the compatibility mode
Check the regressed queries report and do plan forcing as needed
This is the best way to deal with the 2014 cardinality estimation engine change.
"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
November 30, 2018 at 1:22 pm
Grant Fritchey - Friday, November 30, 2018 10:34 AMIf you're upgrading from anything prior to 2014 to 2016 or greater (and don't ever upgrade to 2014), follow this simple set of steps:Restore database
Leave compatibility level to old version of SQL Server
Turn on Query Store
Run your load for a period of time (days, weeks, months, you figure that part out)
Change the compatibility mode
Check the regressed queries report and do plan forcing as neededThis is the best way to deal with the 2014 cardinality estimation engine change.
If you are going to tell people to never upgrade to SQL Server 2014 don't you think you should tell them why?
December 1, 2018 at 12:17 pm
Lynn Pettis - Friday, November 30, 2018 1:22 PMGrant Fritchey - Friday, November 30, 2018 10:34 AMIf you're upgrading from anything prior to 2014 to 2016 or greater (and don't ever upgrade to 2014), follow this simple set of steps:Restore database
Leave compatibility level to old version of SQL Server
Turn on Query Store
Run your load for a period of time (days, weeks, months, you figure that part out)
Change the compatibility mode
Check the regressed queries report and do plan forcing as neededThis is the best way to deal with the 2014 cardinality estimation engine change.
If you are going to tell people to never upgrade to SQL Server 2014 don't you think you should tell them why?
Sure thing. There's nothing wrong with SQL Server 2014. However, it introduced the new cardinality estimation engine but did not introduce Query Store to help deal with the new CE. So, I always recommend people skip 2014 in favor of 2016 or better (and I really recommend 2017) so that they can more easily deal with the new CE.
"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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply