I’ve had this conversation at least a 1000 times over the course of my career working with SQL Server and teaching classes. Is it better to upgrade in place or do a side by side upgrade.
There are pros and cons to each method, however I’ll say upfront that my preference is a side-by-side upgrade for production, and I think you’ll ultimately agree after reading this post. This goes for SQL Server on Virtual Machines such as Azure VMs as well as on-premises.
In-Place Upgrade
An in-place upgrade is exactly what you’d think it is. Simply upgrading an existing version of SQL Server by running the installer.
This option is often wanted due to the simplicity of the method.
- No application changes
- No DNS, firewall, or name changes
- Lower cost since there is no additional hardware or VMs needed
- Its fast, upgrades typically take less than an hour to complete
- No cutover/migration to another machine
However, this option has the highest risk
- No easy rollback – once the databases are upgraded to the new version, they can’t be restored back to a lower version
- Minimal testing can happen with the production workload
- Rollback consist of uninstalling SQL Server, installing the old version, restoring the system databases, and restoring the user databases.
- Once data starts changing in the user databases after upgrade, you’re likely past the point of rollback.
If you are on a VM, a VM snapshot before the upgrade should be able to be used as a recovery point. Otherwise, you’ll have to uninstall, reinstall, and restore everything. This will take some time.
In-place upgrades may make sense for lower environments; however, I’ve typically seen SQL Server upgrades also tied to OS level upgrades. Are you planning to in-place upgrade the OS as well? If the plan is in-place lower environments and side-by-side production, I’d argue strongly against that as you are not testing the side-by-side process.
Side-By-Side Upgrade
This option means to build a new server, install the upgraded version of SQL Server, and migrate your environment to the new server. This option is more labor intensive than the in-place upgrade and higher cost since you’ll be running two servers until just after the cutover. However, this method has some major advantages over the in-place upgrade.
- Failback/rollback is simple. You just point back to the old environment.
- Full end to end testing against the production dataset.
- Clean OS and SQL installation. Many times, this is the opportunity to get to your organization’s latest standard
- You get to upgrade the OS as well
- Possibility of moving to newer hardware, subnet, etc
- Provides a separate environment for testing to decrease some of the stress of the upgrade
The increase challenges of a side-by-side upgrade
- It takes more time overall
- It involves more teams – someone has to deploy the new server, update applications, etc
- You have to migrate logins and permissions – easy to do with dbatools.io
- You have to migrate SQL Agent jobs – easy to do with dbatools.io
- You have to migrate linked servers, credentials, dbmail, etc if they are used – easy to do with dbatools.io
- Applications and data sources need to be updated (however, if you were smart back then or doing things easier this time around, configure a DNS entry instead of using the server’s name, so future upgrades, you simply update DNS to point to the new server)
- There is a high chance that some process is hitting your old server that no one thought about that will fail after the upgrade. I look at this as a positive because now that process can be documented.
The Decision
The decision is up to you, however in my opinion, the side-by-side provides the least risk and greatest opportunity for testing and cleaning things up. In most cases, the failback is the key driving factor. During upgrades, you typically don’t find a ‘showstopper’ until testing, then if someone makes the decision to ‘fail back’, time is critical. How long will it take to uninstall, reinstall, restore the system databases, then restore the user databases. This could be hours and push way past the maintenance window for the upgrade. With a side-by-side, the application team simply points back to the old environment that was not touched other than stopping SQL Server Services.
The post SQL Server Upgrades appeared first on Tim Radney.