Answered by:
HA - Transnational Replication issue

Question
-
Hi All,
I have an issue in Transnational replication. while configuring replication i have below issues,
- I've set the article properties Action if name is in use =Keep existing object unchanged, after completing setup above condition is resetting to default(Drop existing object and create a new one)
- For Statement delivery type I've made changes to different types for different articles not to deliver all types of statement(INSERT, UPDATE and DELETE), but after completion all configurations are resetting to default, it's replicating all statements.
- Also getting errors(The process could not bulk copy into table '"dbo"."Table"'.(Source: MSSQL_REPL, Error number:MSSQL_REPL20037) and Batch send failed Violation of PRIMARY KEY constraint 'PK_**'. Cannot insert duplicate key in object 'dbo.Table'. The duplicate key value is (2). (Source: MSSQLServer, Error number: 2627))
- Also setup for -SkipErrors with 2601, 2627, and 20598
I am not sure why and how this happening regularly all the time. some more info in below,
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)-Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600:)(Hypervisor)
OS - Microsoft Windows Server 2012 R2 Standard.
Please help me on this.
Syed Jakeer
- Moved by Tom Phillips Saturday, August 20, 2016 4:06 PM Replication question
Wednesday, August 17, 2016 11:16 PM
Answers
-
1) this setting means that existing data in the subscriber tables will remain there when you start a new snapshot and send it down. So if the table has 100 rows in in with the values of 1, 2, 3,...100, and you re-run a new snapshot it will attempt to add these rows again.
This should persist. Check the schema option to ensure it does. Are there other publications which contain this aeticle?
2) You need to write the word NONE in for all of these statements. There were some bugs which caused this to not "stick" so you will need to use sp_changearticle and sp_helparticle to verify that the changes are persisted.
3) This is by design if you have set action name if in use to Keep Existing Object unchanged. I think you want to delete all the data to drop the table
4) Skip error only work after the snapshot is applied.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
- Proposed as answer by Lin LengMicrosoft contingent staff Wednesday, August 24, 2016 9:38 AM
- Marked as answer by Lin LengMicrosoft contingent staff Friday, August 26, 2016 2:06 AM
Monday, August 22, 2016 1:45 PMAnswerer
All replies
-
:Please check below discussion if this can help you, it seems quite close to your issue.
Other links to check:
https://msdn.microsoft.com/en-us/library/ms151740.aspx?f=255&MSPPError=-2147217396
Please share update on how it goes for you.
Santosh Singh
Wednesday, August 17, 2016 11:25 PM -
Also please check this explained article for MSSQL Server Error number 2627: https://www.microsoft.com/technet/support/ee/transform.aspx?ProdName=SQL%20Server&ProdVer=10.0&EvtID=2627&EvtSrc=MSSQLServer&LCID=1033
Saturday, August 20, 2016 5:52 AM -
1) this setting means that existing data in the subscriber tables will remain there when you start a new snapshot and send it down. So if the table has 100 rows in in with the values of 1, 2, 3,...100, and you re-run a new snapshot it will attempt to add these rows again.
This should persist. Check the schema option to ensure it does. Are there other publications which contain this aeticle?
2) You need to write the word NONE in for all of these statements. There were some bugs which caused this to not "stick" so you will need to use sp_changearticle and sp_helparticle to verify that the changes are persisted.
3) This is by design if you have set action name if in use to Keep Existing Object unchanged. I think you want to delete all the data to drop the table
4) Skip error only work after the snapshot is applied.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
- Proposed as answer by Lin LengMicrosoft contingent staff Wednesday, August 24, 2016 9:38 AM
- Marked as answer by Lin LengMicrosoft contingent staff Friday, August 26, 2016 2:06 AM
Monday, August 22, 2016 1:45 PMAnswerer -
Hi Syed Jakeer,
I’m writing to follow up with you on this post. Was the issue resolved? If you issue has resolved, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.
If you have any other questions, please let me know.
Regards,
LinFriday, August 26, 2016 2:06 AM