SQL Server - moving databases to an earlier version - options

Question

I have inherited a vendor product. It’s currently very old, having been installed in 2013 and never updated in the 11 years since. The database server it’s using for its 3 database is SQL Server 2008 R2. The databases are all required, and are of various sizes (currently 115GB, 67GB and 20GB approximately).

There is a daily backup-and-restore of each of the 3 vendor databases to a reporting database server, which is SQL Server 2016. Further copies of those database are made (again via backup and restore) to at least 3 other SQL Server 2016 servers and used in various offline processes. All of these reporting servers have many, many other databases on them and are used widely throughout the company by various groups.

All servers are on-premise. The daily / end-of-day backups are sufficient; i.e. there is no requirement for real-time synchronisation of the databases.

We are currently in the process of upgrading the vendor product. Doing so requires that the database server be upgraded to SQL Server 2019 (it could be a more recent version, but this is where we have landed).

I know that this will mean that the backup and restore to the SQL Server 2016 reporting server will no longer work, as it’s not possible to restore to an earlier version of SQL Server.

I was hoping to get some advice on other options. We have given consideration to a few, but haven’t tried anything or run any proofs-of-concept. I really wanted to see what the community advised or if we were missing anything,

1. Transactional replication - discounted since the vendor product has over 300 tables without primary keys, which I understand means that they can’t be replicated this way. Changing the underlying tables isn’t possible.
2. Snapshot replication - concerns here over the snapshot size and performance of this method, given the database sizes in question
3. Upgrading the target reporting server to SQL Server 2019 and continuing to use backup and restore. This option is currently our fallback but will involve several upgrades to the servers that the original database is subsequently restored to, and will introduce complexity and risk to the project that I’d prefer to avoid.

Thanks!

Answer 1

Short Answer

Option #3. Upgrade/replace the target(s) to SQL 2019.

Long Answer

We run into a similar issue whenever we first implement a new version of SQL Server. We have a number of secondaries, whether they are used for reporting, or analysis, or automated restoration of databases to run DBCC CheckDB against them. Sometimes the data gets to them via replication, sometimes log shipping, and sometimes old-fashioned backup/restore.

The way we handle it is to ensure that we upgrade -- or at least spin up a second server/instance in parallel -- our various secondaries at the beginning of our journey to a new SQL version. So, for example, we recently built a SQL 2022 instance to receive restores for offloaded DBCC CheckDB validation before we installed any other instances of SQL 2022.

In some cases, like our DBCC CheckDB instance, we just built a new SQL 2022 instance and moved everything over to it. And in others, like some of our data warehousing, we built a new SQL 2022 warehouse instance and ran it alongside our existing SQL 2017 warehouse instance until such time as we get everything moved over to it, and we can shut down the SQL 2017 warehouse instance.

You say that Option 3 is your fallback, and will introduce complexity and risk. I get that. But you're going to have to tackle that complexity and risk eventually. Maybe not for this upgrade, but for some other one in the near future. You're not really changing the amount of complexity and risk -- you're just moving it around a bit. And I maintain that getting it done now will not only make this upgrade go more smoothly, but it will also lay the groundwork for making the rest of your upgrades to SQL 2019 easier.