We're planning a move from SQL 2005 to SQL 2017. We're using new servers, standard edition. We plan to Backup/Restore databases to make the move. We've run the Data Migration Assessment tool from Microsoft. No 'Breaking' functionality was identified.
I see some posts about moving between other versions but not '05 to '16 or '17.
Is there any significant difference to only moving to Sql 2016? If we're moving doesn't it make sense to go ahead to Sql 2017?
Can you provide any pointers, suggestions or warnings? Very much appreciated.
Answer by Kev Riley ·
I would take the position that unless there is something in SQL 2017 that prevents you from using it, then I would migrate to it rather than 2016. It's the same due diligence process that would have to go through for 2016 anyway (test, test, test), but you get the added benefit of a newer, improved version that will stay supported for longer.
The biggest change from 2005 to anything beyond 2014 is the newer cardinality estimator. And there's likely (can't recall at the moment) more things in 2017 than 2016 that help you deal with that - Query Store improvements, trace flags, database level options etc