October 27, 2015 at 8:11 am
We have a domain joined SQL 2008 R2 server performing a snapshot database replication to a non-domain joined SQL 2008 R2 server. The snapshot replication is working with one exception. Under one of the tables, there is a Key, Constraint and Index that are part of the database. The Key and Indexes is copying over. However,
the constraint is not. Anyone seen this issue and why would the Key and Index copy but not the Constraint?
Thx in advance for any assistance provided.
October 27, 2015 at 8:20 am
Check the article properties, generally its only primary keys and unique constraints that get copied over.
Someone may have changed the indexes setting, so just need to change the default constraint and other constraint settings to be included.
October 27, 2015 at 2:31 pm
Attached is screen shot of the settings. I tried to set the Copy Foreign Key Constraints to True but every time I save and close out, it goes back to False.
Thx in advance for any assistance provided.
October 27, 2015 at 2:58 pm
It's going to do that. I honestly do not know the real answer, but this is my take. I have seen this behavior before.
Replication is not "smart" enough to determine that Table A needs to be in place before Table B because of the parent-child constraint.
It generates the snapshot objects in no order, so it may actually try to add the child table before the parent table exists and is populated.
if this was transactional replication, I would strongly recommend removing the foreign keys as well as defaults and check constraints.
Since it's snapshot replication, simply create all of the keys in a post-snapshot script.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 7, 2018 at 8:14 am
We have had the same issue (SQL Server 2012, SP3) and we think it's because of this:
The NOT FOR REPLICATION option is specified by default for foreign key constraints and check constraints; the constraints are enforced for user operations but not agent operations.
Also some further information about IDENTITY columns in a replication scenario:
February 7, 2018 at 9:18 am
emailt@gmail.com - Wednesday, February 7, 2018 8:14 AMWe have had the same issue (SQL Server 2012, SP3) and we think it's because of this:The NOT FOR REPLICATION option is specified by default for foreign key constraints and check constraints; the constraints are enforced for user operations but not agent operations.
Also some further information about IDENTITY columns in a replication scenario:
Note: Three year old thread.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 7, 2018 at 2:59 pm
Yes I knew that when I posted a reply. However in the spirit of community driven content and the fact that I looked at this thread and found it relevant to the issue I was facing I thought I'd share further information that someone else might find useful. Maybe they might stumble across it in another three years or so.
But thank you for taking the time to let me know what I already knew. ????
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply