May 8, 2019 at 9:50 pm
Hi,
Anyone upgraded their SQL to version 2017 from SQL 2008 SP2 using backup restore?
Is it mandatory to upgrade to SQL2008 SP4 then do backup restore on SQL2017?
Thanks
May 9, 2019 at 7:01 am
You need to be on 2008 SP4 to upgrade to 2017.
Just as an aside make sure you test thoroughly before going into production as performance may be very different in 2017.
Thanks
May 9, 2019 at 12:02 pm
When you jump the SQL Server 2014 gap (because no one should ever upgrade to 2014), you do need to be aware of something really important. In SQL Server 2014, a new cardinality estimation engine was introduced. This was the first upgrade to the CE since SQL Server 7.0 back in 1998. For most queries, the change in CE won't make any difference. For some queries, you'll see an improvement. For a few queries, usually already edge cases when it comes to performance and behavior, you'll see a massive degradation in performance. This is where Query Store (introduced in SQL Server 2016) and database scoped configuration (introduced in SQL Server 2016 sp1) come in to play.
Query store will let you run your load in the old compatibility mode while you capture data. This uses the old CE and optimizer, so behavior for the vast majority of your code should be as it used to be. Capture that data after upgrading before you switch the compatibility mode. Then, after a week, a month, whatever is reasonable for your systems, change the compatibility mode. The few queries that suffer a regression because of either the new CE or changes to the optimizer can then use plan forcing to go to the old execution plan until you do the necessary work of adjusting their code and/or structures so that they behave better.
The reason you never upgrade to 2014 is because while it introduced the new CE, it did not get Query Store. Therefore, it doesn't have a good way to deal with the regressions caused by 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
May 15, 2019 at 6:11 am
You can follow steps from https://datamigration.microsoft.com/scenario/sql-to-sqlserver?step=1 just using DMA to move logins and everything, not just database.
May 15, 2019 at 5:24 pm
As per this DMA document link, its showing backup and restore will work for SQL 2008 SP2 to SQL 2017 without updating SP4.
Backup and restore: A backup taken on SQL Server 2008 or SQL Server 2008 R2 can be restored to SQL Server 2017.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply