0

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.

  • 2
    If you have another server, always, always, always do a new install. There is no "undo" button for an in-place upgrade. There are tons of horror stories you can read about where pros take all the precautions and things still go sideways. Mike Walsh has a good checklist and some videos about this. – scsimon Aug 16 at 20:49
  • That's EXACTLY my opinion as well. But I am being largely slammed with opinions from my ITS department suggesting we do the opposite, that we should do in-place upgrades. :-/ – user1596979 Aug 16 at 21:02
  • 1
    I am also in favor of doing side by side. it is a little more complex (easy if using dbatools.io )but the rollback is much easier. Exception: if you are running on a VM, you could take a VM snapshot before you start in-place upgrade and if it fails, revert snapshot. – Bob Klimes Aug 16 at 21:06
  • @BobKlimes Yes, I failed to mention. They are VMs and yes, we would have the option of snapshots. But snapshots come with their own host of issues as well... The are not as reliable as I would want them to be. I've had some situations, for example, where we needed to expand the drive of the upgraded VM, and because there was a snapshot in place, we could not. Other issues as well, but I cannot remember off the top of my head. – user1596979 Aug 16 at 21:49
2

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.

  1. 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.

  2. I install SQL Server on the new server

  3. Restore the latest backup from the old system onto the new one and assign basic permissions to allow users/developers to test.

  4. 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.

  5. Users/Developers test again and confirm system is operational.

  6. 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.

  7. 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.

0

The basic trade-off on most systems when doing in-place vs new is that building a new thing requires more time to prepare due to increased complexity, more time (though not necessarily downtime) to execute (especially if you need to ship lots of data everywhere), and more time for testing.

The upside? Usually a more accessible fail-back plan (though watch out for potential writes orphaned on the upgraded system), cleaner / better control of the upgrade process, more thorough testing (you can do side-by-side performance comparisons, and typically you end up with less "cruft" in the system (especially if you can the build process for the new server repeatable).

Of course you can find ways to try to make those things available with an in-place upgrade as well, but it usually requires an intentional effort.

0

In an ideal world each new install would be on a nice shiny new server but in the real world it depends entirely on your setup, application, clients and timeframe for the upgrade. With the right planning and research, an in place upgrade can be a perfectly sensible option. The key for whichever method you choose is always having a rollback plan.

Whether you're moving or upgrading the first thing to do is read up on the changes between your versions and make sure that any deprecated features you now rely on aren't disappearing from the version you are moving to. They'll know of any caveats or things to watch out for. The Data Migration Assistant will help identify issues for you.

If you're hosting applications and data from other vendors give them a call and make sure the version you are running is supported on the version of SQL you are moving too. You may need to plan some 3rd party application updates before you can upgrade or you may not be able to upgrade to the latest SQL if not supported yet by the vendor.

I've used in place upgrades when the business were too worried that migrating every database, user, login and replication configs from a fairly old system with little documentation, without losing data or extending downtime, was too risky. An in place upgrade allowed us to keep these things exactly as they were originally with minimal effort. Over time we were able to move away from the old legacy databases and systems so future upgrades can be done via migration instead. (SQL2008 -> SQL2012)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.