Upgrading SQL Server 2008 R2 to 2017

Question

Upgrading SQL Server 2008 R2 to 2017

stinger27

SSC Enthusiast

Points: 172
More actions
May 27, 2022 at 6:18 pm

#4040379

Do you guys recommend upgrading SQL Server 2008 R2 to 2017 by just running the disc and uprading? Or would it be better to create a whole new server, install SQL Server 2017, back up the database from the old server, and restore it onto the new server? I don't work with SQL much, so I want all the input I can get before I mess things up lol. From what I am reading online it seems like I can just do it through the disc upgrade wizard.
June 17, 2022 at 10:14 pm

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

First of all, what OS do you have? Not very many OSs supports both SQL 2008 R2 and SQL 2017, and if it supports SQL 2008 R2, the OS is quite old by now. So that alone could mandate moving to a new server. Or do you consider upgrading the OS in place as well?

Another factor is hardware - it may be time to move to new hardware. Then again, if SQL Server runs on a virtual machine, that's a not an issue, as you simply can move the VM to a new host any time you like.

I used to be in favour of moving to new an instance, as that permitted me to keep the old instance as a reference, in case of performance regressions which is not unheard of with upgrades. But this has changed with Query Store. Here is a good approach no matter which path you take: enable Query Store once you are SQL 2017. Keep the compat level at 100 for a week or two. Then flip the switch. If you get performance issues, you can force the old plan through Query Store and then investigate that query in more detail. There is also tooling for SSMS for this process.

There is one undeniable advantage with an in-place upgrade: no need to move jobs, logins etc.

So this is not a definite answer, but I leave it up to you. But I hope that there is some food for thought.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

ratbak Ten Centuries Points: 1381 More actions · Answer 2

SQL Server 2017 mainstream support ends in five months (extended support to October 2027). Why are you going to 2017 instead of 2019?

Create a new server. Backup, restore, update statistics, configure. And test!

Backup, restore, & point to the new server only after you're confident it will work well.

Why? Things can go wrong. You don't want your business to be down while you're trying to debug a broken install. You'll start with a clean OS & Sql install that doesn't have the clutter, overhead, and unknowns of years of updates.

Test not just for does everything work at all, but test for performance. Changes were made to the cardinality estimator that sometimes cause problems w/ legacy queries. Determine if you need to disable that before you go live in production (but consider later tuning queries and indexes to take advantage of the changes).

Johan Bijnens SSC Guru Points: 135374 More actions · Answer 3

Always ( indeed ) start with Data Migration Assistant
Fresh Hardware / fresh OS / Latest SQL Server version and CU over in-place upgrades. Hands down.
It will indeed be more work for you now, but will avoid having go through all the process in case of DRP, as in-place upgrades may keep some old settings in place.
Migrating to Fresh Hardware / fresh OS / Latest SQL Server version and CU may also give you the best opportunity to test it all up front.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

Johan Bijnens wrote:

Always ( indeed ) start with Data Migration Assistant
Fresh Hardware / fresh OS / Latest SQL Server version and CU over in-place upgrades. Hands down. It will indeed be more work for you now, but will avoid having go through all the process in case of DRP, as in-place upgrades may keep some old settings in place. Migrating to Fresh Hardware / fresh OS / Latest SQL Server version and CU may also give you the best opportunity to test it all up front.

+ 1 million!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rothj Hall of Fame Points: 3412 More actions · Answer 5

I agree with Johan's approach. Use Data Migration Assistant to check for deprecated code.

Go with a new server with all the latest. You build it with a different name like SQL01-new. At cutover time rename your server to existing name (SQL01) and IP after renaming the existing one to SQL01-old (and a different IP). I'd shut down the SQL services so nobody accidentally connects to it. You can keep the old server for a month or two until you ensure you have everything you need off of it. Also it's your rollback plan.

Also check out https://dbatools.io/. There's a database migration command that is awesome. I've successfully used it several times and does all the work for you. I think: Start-DbaMigration

Good luck!

Johan Bijnens SSC Guru Points: 135374 More actions · Answer 6

I would not rename the new server (to much a hassle ), but have an alias for the name of the old server point to the new server.