About eight years ago, I wrote a (kind of long) answer to a DBA. Stackexchange question, “Are SQL Server In-Place upgrades as ill-advised as they used to be?” My summary answer on the top of my tome was:
Short Answer – In Place is okay. You can review your configuration afterward and implement the best practices for SQL Server 2012 (edit: Or SQL Server 2019/SQL Server 2016 or whatever version you are on. Remember I wrote this in 2012 😉 )
But then I spent the rest of the answer counting all the reasons why I, frankly, just used to hate doing a SQL Server upgrade in-place. Surely, though, eight years later, I’m here writing a post recanting of my awful advice, right? I mean, I was only 12 years into SQL then, I was a relatively new MVP, just trolling Stack Exchange bored on the Monday after Thanksgiving, right? No… I’m here to tell you that “back then Mike (the one with hair on his head)” was right. In fact. He was right.
Let me walk through my reasons with you. I’m going to steal some highlights from myself 8 years ago, but I’m giving attribution. Go check out my answer if you want even more words. . .
Why I Hate In-Place SQL Server Upgrades
And why you should hate upgrading a SQL Server in place, too.
Why your CFO should hate them- First a story. A few years ago a client had asked us our thoughts. I gave my standard answer NO! NO! NO! and explained that 95% maybe even 99% of the time they go fine with no issues. They were upgrading SQL Server 2014 to SQL Server 2016, I believe. So in-place upgrading didn’t sound -that- scary to them. I talked about how if things go bad, it’s ugly. And touted the benefits of a migration upgrade. Anyway “expediency won”. Things went bad. Then when those things went bad, other things went bad. The snapshot they thought they took before of the VM wasn’t right. There were MANY DBs there and a lot of (billable) time was spent first trying to fix the situation, then recovering the environment and all the databases.
That’s just one anecdote. And, sadly, we made some money on that one. But there are others just like it. Listen. If you want to try and perform an in-place upgrade of your production database server, it may work. In fact, it probably will work. But what if it doesn’t?
Some Reasons In Place SQL Server Upgrades stink
I’ll list some of the biggest reasons here. My answer is sort of all the questions you never knew you didn’t know you wanted to know answers to, so visit if you wish. But here are the big ones we use at Straight Path when planning an upgrade project with a client.
- Testing/Rollback – Going to a new server means you can test the process a couple of times before you do it. It means you can build up success criteria ahead of time of go-live night. You can verify that criteria, smoke-test (but don’t write new data) to the apps, and still have a failback option of aborting and going back to the old server. That’s huge!!!
- New New New!! When you chose to get the install media for the next version of SQL Server and just do an in-place upgrade, you’re saying you like all the hardware you are on, the OS, the setups, the mistakes made three years ago. Migration install means – you start fresh!
- Downtime Reduced – A migration upgrade can dramatically minimize downtime over an in place upgrade. Think about it. In place means you start. And once you start, you are committed. And your downtime is a bit unknown, and we already said you can’t rollback. And at some point, you’ll be done, and (hopefully) your users can get back in and work. Doing a migration based SQL Server upgrade means you can do a log shipping method to copy data over and dramatically minimize your downtime – and have a planned window for migration. How cool is that?
There are many more reasons. But those are my favorite and, frankly, the ones that should make in-place upgrade show stoppers. Do a migration upgrade and save yourself time and money!
Check out that answer from “Younger Mike With all the hair” if you want.
I also did a webinar series on SQL Server upgrades last year. You can see more about why I don’t like in place upgrades and how to plan an upgrade by checking that series out. It’s all there for you to watch, we have an upgrade checklist we use here, too.