question

jmccoy avatar image
jmccoy asked

Move from Sql 2005 to Sql 2017

SQL wizards,

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.

Thank you,

Jim

migrationsql server 2017sql server 2005moveplanning
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

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

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I second what @Kev Riley has said. Go to SQL 2017 and save yourself the hassle of upgrading later.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.