Question :
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.
Answer :
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
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.
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