x

Lost indexing on subscribers after publishing new column

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
more ▼

asked Aug 24, 2012 at 05:14 PM in Default

Wai gravatar image

Wai
0 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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:

  1. Change the publication so that indexes are copied over
  2. Manually create the indexes on the subscriber.

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.
more ▼

answered Aug 24, 2012 at 07:21 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x127
x22
x22

asked: Aug 24, 2012 at 05:14 PM

Seen: 1061 times

Last Updated: Aug 24, 2012 at 07:21 PM