Transactional replication upgrade from SQL Server 2017

Question

We would like to inplace upgrade of transactional replication topology from SQL Server 2017 to SQL2019 with minimal downtime. The following is the setup. All the all current instances are on SQL Server 2017 enterprise

• Server A has 2 databases serving as publisher
• Server B has 2 databases serving as subscriber
• Server C is the remote distributor.

From inplace upgrade perspective, we have to first upgrade distributor --->publisher--->subscriber.

I would like to know the rollback steps for distributor database if inplace upgrade fails to SQL Server 2019 and we have to revert to SQL Server 2017 with minimal possible downtimes. Requesting some insights on procedure to do the same.

Kevin

Answer 1

Rolling back an in place upgrade on a distributor is pretty similar to any failed in place upgrade. Which is to say, it's not usually quick and simple.

If the failure is during the install, you'll need to uninstall, reinstall, and revert to backups. With replication, msdb is very important, in addition to the distribution database, in order to restore all of the jobs. VM-level snapshots can be very useful here, as that can make reverting easier, without having to do messy uninstall/reinstall. Depending on your VM configuration, VM snapshots might save you from having to restore databases, too.

If the failure/reason to rollback happens after upgrade, when you've re-enabled any of the agents, then restoring from backup won't be enough. Data that is being distributed to subscribers or read from the publisher logs after upgrade, before rollback would cause data inconsistencies. In that case, you'll also need to re-snapshot your publications on rollback.