Replicating data between SQL Server 2014 to SQL Server 2005 - temporary solution

I will be installing a new SQL Server 2014 database system, which is based on our current SQL Server 2005 database system. The new system will be on a completely new infrastructure when we go live, leaving the current infrastructure available for rollback should we encounter any issues.

The business requires that a rollback option be available for 2 weeks minimum. Therefore in the event of a disaster scenario we would be required to switch back to the SQL Server 2005 database system. So the challenge I have is replicating the data upon going live with the SQL Server 2014 database system.

I am looking for a way to replicate the data (not necessarily the schema) from the SQL Server 2014 system to the SQL Server 2005 system.

I am not really a fan of SQL Server Replication and my preference is not to use this. My understanding is that this is not an option for us anyway due to the versions being too far apart, and therefore not supported.

I want to reach out to the community to find out if any tools are available to achieve this type of replication. It may be possible for us to implement a schema change freeze if this helps in finding the right solution.

Please can you advise what options are available to us, if any? Or should we look to develop a bespoke system internally?

Are you aware of any Microsoft partners that might achieve this for us through a solution already developed?

Thanks in advance for any advice.

Joe

Reading your question and thinking a bit more about a possible solution leads me to recommend transactional replication.

Make sure you do implement a change freeze to your schema.

From BOL :


For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example, a SQL Server 2000 Publisher can have SQL Server 2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server 2000 Subscribers.

Based on above truth,

• Take backup of your current sql server 2005 database and make sure you have instant file initialization enabled on all servers.

• Freeze all the data and schema changes

• Migrate your database from sql server 2005 to 2014 and do all post restore steps.

• Repoint your application to the new server instance.

• Set up T-Rep from sql server 2014 to sql server 2008R2 (this will be an intermediate server or instance and can be on the same new server if you want).You care about this step since, you want to be on supported version. Make sure you initialize replication manually using tsql.

• Now have another layer of replication from sql server 2008 R2 (intermediate) to your sql server 2005 server. Make sure you initialize replication manually using tsql.

Note: If the servers are in same datacenter, then you can even do a snapshot which will be very fast.

Above exercise will be worth, since you want a supported rollback plan. Since your business wants to keep things around for 2 weeks, you should be good once you test above.

Note: I have not tried (as I have not been in a situation wherein I have to replicate data from sql server 2014 to 2005), but it should work (though not supported) theoretically. Give it a try for small database and you will easily be able to gauge the effort required.

StackExchange Database Administrators Q#125177, answer score: 4