My search for this question yielded some similar questions, but none that I could see exactly like this.
Question: In your experience, when doing a SQL Server upgrade, say from 2008 to 2017, or from 2012 SP1 to 2017, or whatever from/to (also depending on whether the direct upgrade path is available or not), is it better to start with a brand new installation, or with an in-place upgrade? What are the challenges you faced when doing one versus the other (SQL Server upgrade, in-place or new fresh install)? What poison do you pick? What is your own personal experience and advice?
Thank you in advance, Raphael.
Best Answer
I have been doing a ton of migrations at my current job and the one time we tried doing an in place upgrade we ended up losing a days worth of work. This is my normal process when moving from an older system to a new one.
IT Creates a new VM with SQL Server configuration (multiple drives for splitting data, temp, log, etc.), usually with the same specs as the old system.
I install SQL Server on the new server
Restore the latest backup from the old system onto the new one and assign basic permissions to allow users/developers to test.
Once they confirm compatibility I take advantage of DBATools and Powershell to migrate users, jobs, and everything else required to the new server. I will also make a fresh backup/restore of the old system databases.
Users/Developers test again and confirm system is operational.
Schedule a transition period, usually on weekends late at night. IT makes a snapshot of the VM. Set old databases as read only, backup/restore databases to new system, once the new system is ready users and developers test.
Only after they confirm it is working IT will shutdown the old system (they delete the VM after a month of no issues).
This is obviously fairly generic, sometimes if the system is small enough I will use DBATools migrate command and just move everything over at once. Sometimes the time between steps is days or weeks, even months if its a core system.