2

We are planning for SQL Server 2019 upgrade from SQL Server 2014 version. One of the requirement from the business owners is that we should be able to fall back to earlier version without data loss for the first 30 days after the upgrade, if needed.

It is obvious that we can't restore SQL 2019 databases to SQL Server 2014. However, is there a way to accomplish this requirement using native or third party software and replicate data from the latest version to earlier version real-time? Did you come across similar need before?

Thank you in advance!!

1 Answer 1

1

You could achieve this with the native merge replication supplied by SQL Server. You would use a combination of backups, restores and replication setup.

1. Backup the Source Database

First you would back up the source database on the SQL Server 2014 and transport the backup to the new database server.

2. Restore the Source Database on Target Server

On the new SQL Server 2019, you would then restore the source database. After the restore and before the database is recovered, the restore process will internally upgrade the database to same internal version of the target database server.

3. Configure a Merge Replication

According to the compatibility matrix supplied in the article Replication Backward Compatibility configure a Merge Replication. A Merge Replication is explained in the article SQL Server Replication as follows:

Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing. Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include: exchanging data with mobile users; consumer point of sale (POS) applications; and integration of data from multiple sites. Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise. Replication to SQLCE 3.5 and SQLCE 4.0 is supported on both Windows Server 2012 and Windows 8.

Your new 2019 SQL Server would be the Publisher and the Distributor and the old 2014 SQL Server would be the Subscriber.

The article Merge Replication goes into slightly more details and might provide you with more information for you to make a decision whether this is a feasible solution for your requirements or not. Here a short extract:

[...] Merge replication is typically used in server-to-client environments. Merge replication is appropriate in any of the following situations:

  • Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers.

  • Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.

  • Each Subscriber requires a different partition of data.

  • Conflicts might occur and, when they do, you need the ability to detect and resolve them.

  • The application requires net data change rather than access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row will change only once at the Publisher to reflect the net data change (that is, the fifth value).

Merge replication allows various sites to work autonomously and later merge updates into a single, uniform result. Because updates are made at more than one node, the same data may have been updated by the Publisher and by more than one Subscriber. Therefore, conflicts can occur when updates are merged and merge replication provides a number of ways to handle conflicts. [...]

Replication in General

You can find additional information on the Types of Replication in the linked article.

Side Note

If your SQL Server versions had been only two apart (e.g. 2016 --> 2019), then you could have considered implementing a Transactional Replication. However, this can impact the database, as it introduces an additional column in tables in some cases. (see Transactional Replication for more details.)

3
  • Thank you John. I really appreciate your prompt reply with a detailed explanation.
    – Daniel
    Jun 15, 2022 at 16:59
  • If you think this is a viable solution then please consider upvoting my answer. If this is an actual solution to your question then please consider accepting the answer, by selecting the check mark. If this doesn't help then please add more details to your question and I or somebody else in the community might be able to assist. Thanks.
    – John K. N.
    Jun 15, 2022 at 17:07
  • Don't forget that Merge Replication likely requires a DDL change to the tables in the database in order to properly track row changes. This may be a deal breaker, especially in the case that it's a vendor database. Jun 27, 2022 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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