September 27, 2016 at 4:21 am
We have peer to peer replication configured in SQL 2014.
Often we are seeing that the replication is getting failed with an error that a particular column on subscriber database is invalid. when we compare it manually with the publisher database, its obviously present there.
Then we end up creating the column manually and replication catches up there after
Then after a few days, this happens for a different table and a column. we are unable to fix this totally at once.
This is a 2TB database and we are scared to re initiate entire replication process.
What could be the potential problems:
1. Could the new column be introduced in the publisher newly which isnt getting pushed to the subscriber. Creation/modification of an object is a DDL operation. will peer to peer replication push DDL operational changes?
2. Could some user/process be deleting these columns on the subscriber database? Is there a way to check that?
September 27, 2016 at 4:31 am
Benki Chendu (9/27/2016)
We have peer to peer replication configured in SQL 2014.1. Could the new column be introduced in the publisher newly which isnt getting pushed to the subscriber. Creation/modification of an object is a DDL operation. will peer to peer replication push DDL operational changes?
I'm not a great expert on replication, but I think that depends on how you set up your publication.
2. Could some user/process be deleting these columns on the subscriber database? Is there a way to check that?
You could look in the default trace, or run an extended events session to capture such occurrences.
John
September 27, 2016 at 7:30 am
if you want to fix it once off, select all the columns for all the tables and only show the tables and column names that do not exist in the subscriber.
select
s.name+'.'+o.name +'.'+ c.name columnname
from sys.columns C
inner join sys.objects o on o.object_id = c.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where o.is_ms_shipped =0
and type = 'u'
Do on both servers and do a vlookup in excel or do the equivalent in sql to find the missing.
September 27, 2016 at 7:38 am
MadAdmin (9/27/2016)
if you want to fix it once off, select all the columns for all the tables and only show the tables and column names that do not exist in the subscriber.
selects.name+'.'+o.name +'.'+ c.name columnname
from sys.columns C
inner join sys.objects o on o.object_id = c.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where o.is_ms_shipped =0
and type = 'u'
Do on both servers and do a vlookup in excel or do the equivalent in sql to find the missing.
ok. this should tell me which columns in which tables are missing. What next? How to fix them once for all?
September 27, 2016 at 8:36 am
You probably have the option to replicate schema changes set to false.
So this may have to be taken into account when the devs add columns in future as you may have to add the column both sides.
I think you can change the setting in the publication properties so that it will do schema changes both sides if added on publisher.
Do this in test first before putting on your shades and unholstering your magnum.
Decision as to what to do in the case of columns not existing comes after having a chat with the devs who might have an idea of what the environment supposed to look like.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply