6

I am moving database to new server.

New server has SQL Server 2016 running, current server has SQL Server 2008.

I need to have backup/fallback plan in place for few weeks (in case something goes wrong - I can switch my application back to 2008 Server and continue working).

I am wondering is it possible to enable replication (or alternatives?) and have all transactions/merges be in sync on 2008 Instance so both databases are up to date in case I need to switch to old one?

Due to business requirements, we must be able to fall back to SQL Server 2008. This is non-negotiable.

New contributor
arleitiss is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
7

Converted from comments:

According to Replication Backward Compatibility, Transactional Replication will not work between a 2016 Publisher and a 2008 Subscriber, but Merge Replication would be supported. Check out Merge Replication to see if that will suit your needs. – Scott Hodgin

Or Upgrade to 2014 and it will be backward compatible to 2008 for transactional replication. Once You have a steady ship, you can move ahead to later versions if needed. – Ramakant Dadhichi

7

I am wondering is it possible to enable replication (or alternatives?) and have all transactions/merges be in sync on 2008 Instance so both databases are up to date in case I need to switch to old one?

It's a bit complex, but you could introduce a SQL Server 2014 server to serve as Transactional Replication Subscriber and Republisher.

But the only other option is to develop a set of scripts that move all the changes from the SQL 2016 database and apply them to the SQL 2008 database. Or use the database import/export wizard. If the probability of executing the fallback is low enough, and you have sufficient time to do this, this might be a good option.

0

Assuming you can have some downtime for a downgrade you can also export whole database as a dacpac and then import it into a lower version.
It is often a preferred method for moving databases between onprem and Azure.

Follow this instructions for an export: https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/export-a-data-tier-application?view=sql-server-ver15

Your Answer

arleitiss is a new contributor. Be nice, and check out our Code of Conduct.

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.