Hi, We added a new column (bit not null default 0) to a published table. It was replicated to our two subscriber servers as expected. We discovered that the indexes were gone on the subscribers. I am not sure what else was lost. After some exploration, we discovered that that some column collations on the publisher were "case sensitive" and subscribers were 'case insensitive' . The DBA concluded that there is a schema mismatch and we now have to straighten out the schema so that the indexes on the subscribers are 'preserved'. The DBA is also talking about 'breaking the replication' and re-indexing. Is this correct? This is going to be a lot of work to just get the bit column in. The data in the column will get replicated too. We use merge replication and data is replicating fine over the years. TIA
If I understand correctly, the indexes were on the subscriber and now don't exist any more.
If that is the case then you have most probably re-initialized replication / or created a snapshot of the one table. This can drop the target object on the subscriber (including indexes). If you haven't setup the publication correctly, then the indexes will not be copied over to the subscriber.
You have two options here:
Using option 2 could be the better option if the subscriber has different query patterns compared with the publisher.
The collation issue is another option that is configurable on the publication (and requires you to manipulate it and create a snapshot). Either do that, or make sure each system in the replication topology has the same collation settings.
Unfortunately schema changes in a replication system can and will cause issues as you have described. You should build a set of scripts to fix these issues, as they will occur and sometimes in a seemingly random way.
answered Aug 24, 2012 at 07:21 PM