0

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

1 Answer 1

0

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.

1
  • Thank you. If the distributor is on physical servers, do we have a solution to do a snapshot?. We would like to save/revert to old sqlserver executables and database if the upgrade fails as this is a very critical server.Any thoughts on that please?.
    – kevin
    Oct 19, 2020 at 2:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.