x

Replication and indexes

Hi Guys and Girls!

I am setting up a replication solution for a customer, replicating 7 tables, but not all columns in the tables. Publisher and distributor are on SQL 2005 Std, subscriber on SQL 2008 ent.

I have set in the article options for each of the published tables to replicate the clustered index, but not the non clustered ones. All columns involved in the clustered indexes are replicated in all tables.

It works fine on 6 tables, but on the 7:th I get a non clustered index on the subscriber (including the correct columns thou..) instead of the exepected clustered index.

I do not have any errors anywhere, and snapping again does not fix the problem.

Any ideas what might be causing this? And how to fix it?

more ▼

asked Mar 16, 2010 at 07:55 AM in Default

Steinar gravatar image

Steinar
1.7k 3 4 6

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

1 answer: sort voted first

Hm. Digging deeper into this it tured out that I was a bit fast in my judgement. While dubbelchecking, it turned out that the Index I thought was the Clustered PK on this table was in fact not. In reality the table has a unique index as PK, but but it is not clustered. And I have not choosen to replicate all the columns that was included in the clustered index. So now I just have to maintain a clustered index that will go away everytime we have to reinit the replication. But I guess that serves me right for not checking properly before posting to you guys :-)

more ▼

answered Mar 17, 2010 at 05:45 AM

Steinar gravatar image

Steinar
1.7k 3 4 6

Have you thought about making the index on the table clustered? Unless there is a reason not to, it is likely the best overall solution. Also, remember you can mark your own answer as accepted since you seem to have fully discovered the situation.
Mar 17, 2010 at 09:41 PM TimothyAWiseman
It is not so easy unfortunately, as that will upset the supplier of the system. Easier to make my own fix :-)
Mar 19, 2010 at 05:17 AM Steinar
(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:

x1951
x1850
x306
x131

asked: Mar 16, 2010 at 07:55 AM

Seen: 2685 times

Last Updated: Mar 16, 2010 at 07:55 AM