Stop. Before you think about upgrading your existing SQL Server to the latest version, think it through.
Your hardware is old. Even if it’s just a couple of years old, the latest advances in processors, memory, and storage mean it’s practically an antique.
Your Windows is old, too. Over the years, you installed a bunch of crap that you probably shouldn’t have put on the production box. You could uninstall it, but even that will leave leftovers all over the place.
You’ve learned a lot since the first time. When you first installed it, you didn’t know that you shouldn’t install extra services you don’t need, or that you should use 4 TempDB data files, or that you should format the drives with 64K NTFS allocation units. Now that you know about really good setup checklists, you wish you could do it all over again – and now is your chance.
Patching takes forever. You need to bring both Windows and SQL Server completely up to date, but it can take minutes or hours of downtime to get all the right patches applied. And then there’s the BIOS and firmware, too. Instead of guessing how long the old box will take to shore up, just build a new one and get it perfect.
You need to test the new Cardinality Estimator. For several versions, your query plans haven’t changed when you upgraded, but those days are over. SQL Server 2014 brings dramatic changes to the Cardinality Estimator, and you need to check your queries against it to prevent a 100%-CPU-surprise on go-live day.
You need a plan B. Patching and upgrading has a serious potential for failure. In theory, you could restore the OS backups and try again, but have you ever tested that? What happens if it fails, and you have to try it all on the fly? What’s your recovery time objective?
Kendra says: It’s funny, in-place upgrades always worked fine for me, unless it was on a production server.
19 Comments. Leave new
I have never been a horror flick fan. “In Place Upgrade” sounds like a horror flick coming to a theater near you. No thanks.
I agree with Kendra, I wish I had a dollar for everything that went well in Dev, QA, and Staging, then caused problems during deployment or initial operations. 🙁
Even when the QA and Staging are restores from recent production backups and the hardware is almost identical.
Funny is when regular Service Packs fail on production servers.
or, you named your instance “SQL2K8R2” and will be forever explaining to people that is actually SQL 2014 now. 🙁
or, you are using a production instance with the naming convention of _TEST and “will be forever explaining to people that it’s actually the Production server.
Upgrade shmupgrade. I just install that shiz as a new instance. HIZZUH! I jest. I honestly prefer to view upgrades as migrations. Plus, who upgrades just your SQL Server and not your OS. Go big or go home.
Are you bidding on that awesome car?!
Ted – indeed I am. These jerks keep bidding it up – dagnabbit, I really want it.
I like to go against the grain. I upgrade my VM servers all the time. As SQL versions get higher, there are less and less difficulties. I don’t upgrade SSRS or SSAS, not yet. It looks like SSRS has “stabilized”, in that they don’t enhance it much, so I might upgrade my 2012 to 2014.
The fact that the optimizer can bust all my plans – well, I haven’t had time to figger out the ones that are running, so what’s the harm in upgrading? I know, bad DBA, but keeping current has kept my systems happier than building new VMs and migrating. You end up with old VMs floating around like ghosts.
I’ve upgraded SQL versions running ms replication, stand alone servers in production many times with no problem except in my DoD environment.. don’t ask about those issues. I simply snapshot the server prior to the upgrade and revert back to it if there any issues. I’ve had cases where the entire upgrade rolled back due to some issue and the instance remained in tact no problem with our me having to revert to a snapshot.. well a few times I didn’t have snapshot damn VM admins 🙂
I prefer to upgrade prior to the migration. This creates less breaking points to troubleshoot post migration. That way, app admins & devs can’t blame the sql versioning. Most common issues are: update alias to point to new server\instance, update app config or ini file to point to the right place & almost always ipconfig /flushdns. That is if there is an ip\server\instance\port change; e.g., moved off app server to DB, moved to standalone DB server, or naming convention delta.
There are many reasons for a migration, so the break point can be directly linked to the purpose of the migration. There are less with just moving to new HW with same name. Regardless, the less they can complain to the DB versioning being an issue, the better! And they all lived happily ever after…until the next upgrade\migration.
Don’t Upgrade, Get an Azure SQL Server.
Interesting. Do you abbreviate that as an A.S.S., I assume?
Originally we didn’t recommend upgrading to the Release to Manufacturer version of SQL Server 2016.
We’ve now changed our minds: it was a good launch and with Service Pack 1 out, we can only recommend upgrading. More here: [link redacted]
Richard – I understand your enthusiasm, but this isn’t a great way to promote your blog posts.
[…] has a SQL Server migration checklist in addition to a lot of other useful goodies. Brent also has an interesting post on why you shouldn’t upgrade an existing 2008 instance. Finally, DBATools, a set of […]
Ooof… Just discovered this blog after committing to a Windows & SQL in place upgrade… (Win2012/SQL2016 to WinSQL2019) .
Given that these are VM’s the hardware thing isn’t as much of an issue. And SQL has come a logn way. Is this Blog article still as valid today as it was when it was written in 2015?
Yep.
Pete,
I’ve had a lot of success with in-place upgrades from SQL 2016/2017 to SQL 2019. i run in a virtualized environment so I snapshot the VMs prior to the upgrade and thoroughly test post-upgrade, first in our dev/sandbox environment and then in prod for the same software. I’ve had one failure in recent memory that I rolled back and re-attempted at a later date by just building a new server with SQL 2019 and replicating everything over to it.
The post is still valid though.