There are two ways to upgrade a SQL Server version, and the choice is really a choice about what your rollback looks like. Side-by-side gives you a rollback that consists of pointing the application back at a server which is still running. In-place gives you a rollback that consists of a restore.
Side-by-side is the recommended approach for anything that matters, and the runbook treats it as the default.
Network Pre-Flight
This applies to the side-by-side path, which is a migration underneath. An in-place upgrade keeps the host, the name and the firewall it already had. Run these days before the window, from the machines that will actually make the connections.
- Port 1433 to the target from the source server, from every application server, and from your own workstation:
Test-NetConnection new-server -Port 1433. A named instance needs its fixed port instead, plus UDP 1434 if clients rely on the Browser service. Walkthrough: testing remote port connectivity with PowerShell. - Port 445 to the backup share, from both sides. The source writes the backups and the target restores them, so test the share from each server, not just the one you happen to be logged into.
- Firewall rules in both directions on the new host. Inbound 1433 is the rule everyone remembers; outbound to the backup share, monitoring, and any linked servers is the one that turns up mid-window. Reference: SQL Server default ports.
- Line of sight from the applications, before cutover. Every app server and VM that will connect must reach the target before the window opens, tested from those machines rather than from the DBA workstation. A connection that fails at pre-flight is a task; the same failure after cutover is an incident.
- After cutover: DNS and SPNs. Repoint the CNAME and respect the TTL, because clients keep the old record for exactly as long as you told them to. Then re-register the SPNs for the new host so Kerberos keeps working; miss that and connections either fall back to NTLM quietly or fail with the pre-login handshake error.
Side-by-Side Upgrade
Deprecated features in use, the compatibility gap per database, and whether your source-to-target path is even a supported direct upgrade. Databases more than two versions behind the native compatibility level need testing rather than optimism.
A clean install on new hardware.
The move itself is an ordinary migration. That is the point of doing it this way.
This is the step people defer and then forget. A database restored to a newer instance keeps its old compatibility level, so the upgrade quietly has not happened yet. Enable Query Store before you raise the level; the plans it captures give you plan forcing as the rollback lever if the new cardinality estimator hurts something.
Restoring to a new version does not change a database compatibility level. The server is upgraded, the database is not, and the difference surfaces months later when somebody changes it and the plans change with it. Raise it deliberately, in the window, having tested it.
In-Place Upgrade
In-place is faster and occasionally the only option, for example where the hardware or the licence will not stretch to a second server. It needs a genuine pre-upgrade checklist and a backup you have verified, because the rollback is a restore.
Post-Upgrade Validation
First job on the new version: patch it to its latest Cumulative Update. An upgrade lands you on whatever build the media carried, which is rarely current.
- Every database online and at the compatibility level you intended
- Configuration settings flagged for review during the assessment actually reviewed
- Agent jobs running, and their history showing successful runs rather than an absence of failures
- Query performance sampled against a baseline taken before the upgrade, because a new cardinality estimator changes plans
The full version, with every command, is in the repo. Includes the supported direct upgrade paths and the full rollback procedure.
Frequently Asked Questions
Which upgrade path is supported directly?
It depends on both ends, and the runbook lists the matrix. The assessment flags whether your specific source-to-target path is direct or needs a hop, which is better than finding out from setup.exe.
Should I raise compatibility level immediately?
Raise it deliberately and test it, but do not leave it indefinitely. A database sitting two versions behind its host is a decision nobody made, and it gets harder to change the longer it sits there.
What changes most noticeably after an upgrade?
Query plans, through the cardinality estimator. This is why a performance baseline taken before the upgrade is worth more than any amount of reassurance afterwards.
Can I upgrade and move hardware at once?
That is exactly what side-by-side is. One window, one validation, and a rollback that does not involve a restore.
An upgrade is rarely the only thing changing in the window. These are the procedures either side of it.
- SQL Server Migration Runbook: Standalone, side-by-side is a migration, and this is that procedure end to end.
- Windows Server OS Upgrade Runbook for SQL Server, the operating system underneath often needs the same window.
- SQL Server Change Management, the change order, approvals and rollback statement this sits inside.
Leave a Reply