August 12, 2019 at 6:38 am
Hi, my company has a requirement to replicate a database on a 2014 source server to 3 2016 target servers. We would need the replication to take place routinely without existing target data being removed before being replicated so snapshot replication is out of the question. Essentially what we need is just the changes to be replicated routinely and to not interrupt the operations of any of the target databases.
What would be the best way to achieve this considering the data being replicated is missing unique row ids and preferrably we would not wish the source tables to change in anyway or for the source or targets to be taken down while replication occurs ?
If third party software is needed to achieve the desired result, could you please make some recommendations ?
Thanks in advance.
August 12, 2019 at 11:19 am
You could look at transactional replication. Since you're not talking about multi-way replication, you shouldn't need to modify the existing data structure to make it work. You'd only need to do that if you were looking at merge replication. This does support going from a 2014 source to 2016 targets.
You should also look at Availability Groups as a way of achieving the same thing. These are generally more stable than replication.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 12, 2019 at 7:41 pm
What would be the best way to achieve this considering the data being replicated is missing unique row ids and preferrably we would not wish the source tables to change in anyway or for the source or targets to be taken down while replication occurs ?
If the tables to be published don't have primary keys and you don't want to change that then transactional replication would not be an option. There are third party products that support that scenario but I haven't touched them for years. I can remember a couple (but can't vouch for) that have free trials if you wanted to test them:
BackOffice Associates Data Replication
Sue
August 12, 2019 at 11:13 pm
Thank you for your suggestions so far.
I would have thought transactional replication would be out of the question as well ? It seems out of the box sql replication will not work in this situation. Third party solutions might be the only option. Can anyone else confirm ?
Thanks again.
August 12, 2019 at 11:19 pm
It's in the documentation. One example in this link:
Frequently Asked Questions for Replication Administrators
How do I manage constraints on published tables?
There are a number of issues to consider regarding constraints on published tables:
Transactional replication requires a primary key constraint on each published table.
Sue
August 13, 2019 at 12:58 am
Fantastic. Thanks Sue.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply