This is a quick non-technical discussion (i.e. not a how to in any way) of some of the benefits of side-by-side vs in-place SQL server instance upgrades. First a couple of quick definitions.
- In-Place: Just what it sounds like. Upgrading the instance where it sits. There’s no need to move any databases, logins, jobs, etc. Just run the upgrade program.
- Side-by-side: You create a brand new instance of the new SQL Server version. You then copy and upgrade all of the databases, jobs, logins, etc.
Most discussions like this start with the fact that an in-place upgrade is far easier but riskier if anything goes wrong. Side-by-side is more work and you run the risk of forgetting something. But if something goes wrong you just move back to the old instance.
I thought about something that isn’t usually part of the discussion this week while working on a side-by-side migration. When we do these migrations, the first thing we have to do is find out who owns each database so that they can test the upgrades, modify the application to point to the new location etc. Unless it’s fairly obvious, which admittedly it sometimes is, this generally involves at least reviewing all of the connections for the database, possibly every job, SSIS package etc. that touches it. One of the side effects is that we frequently find databases and users that aren’t in use anymore. Then we have to figure out which logins, jobs, SSIS packages etc go with the applications involved and have to be copied over.
This has some very important effects. Just by it’s nature we are slowly removing obsolete/unused databases etc from the system. They get left behind on the old instances and go away when the instances are shut down. Over time this dramatically reduces the complexity on the new instance and can make life a lot easier. For example I just migrated a set of databases for the 4th time since I started with the company. There were a handful of databases I didn’t have to move with each upgrade. So conservatively we’d have an additional 10-20 old databases sitting on that instance that no one was using if we’d only ever done in-place upgrades. On top of that, for the stuff we keep, it improves our documentation. Which databases (and other objects) go with which application(s), which application(s) are managed by which developers, business owners, etc. This stuff is vital at times. For example, when one development team wants access to a database, but they aren’t the “owners” of that data.
I guess in the end this is just another reason I generally prefer a side-by-side upgrade.