Index replication in SQL Server 2000

Hello.
I am using an MS SQL Server 2000 with service pack 3.
How can I replicate indexes with EM replication wizard?
Is it possible in snapshots , merges and transactionl replications?
Thank you so much.
Have a look at the snapshot tab of the article properties - there is a
checkbox for clustered and non-clustered indexes.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

index replication from DB2

Has any body done a replication of indexes or Primary keys from DB2 to SQL 2005 tables. Is that possible and how pls?

Thank you very much.

I believe by default primary keys are replicated. Is it not working for you?

http://msdn2.microsoft.com/en-us/library/ms152492.aspx

Gary

|||

is that stated in the msdn document u sent me?

or does it say so in any other document, so I can show it to the person who said it s not possible<

Thanks a lot for your help.

|||

If you take a look at sp_addarticle at its @.schema_option, there is option to set for primary key constraint.

http://msdn2.microsoft.com/en-us/library/ms173857.aspx

If you look at the default schema option for all the log based article type 0x80 are enabled for all of them. I think this article is the closest I can think of. I'm pretty sure, it's on by default.

Also a quick test probably won't hurt, just create a simple table with two integer columns and make the first column the primary key, then replicate to db2. Seeing is believing :-)

Gary

|||actually we re replicating from DB2 to SQL and not the other way? is it the same?
sorry but i never done replication before. Thanks again for your help
|||

I misread your posting... I think that's the question for DB2 Replication forum.

Regards,

Gary

index replication from DB2

Has any body done a replication of indexes or Primary keys from DB2 to SQL 2005 tables. Is that possible and how pls?

Thank you very much.

I believe by default primary keys are replicated. Is it not working for you?

http://msdn2.microsoft.com/en-us/library/ms152492.aspx

Gary

|||

is that stated in the msdn document u sent me?

or does it say so in any other document, so I can show it to the person who said it s not possible<

Thanks a lot for your help.

|||

If you take a look at sp_addarticle at its @.schema_option, there is option to set for primary key constraint.

http://msdn2.microsoft.com/en-us/library/ms173857.aspx

If you look at the default schema option for all the log based article type 0x80 are enabled for all of them. I think this article is the closest I can think of. I'm pretty sure, it's on by default.

Also a quick test probably won't hurt, just create a simple table with two integer columns and make the first column the primary key, then replicate to db2. Seeing is believing :-)

Gary

|||actually we re replicating from DB2 to SQL and not the other way? is it the same?
sorry but i never done replication before. Thanks again for your help
|||

I misread your posting... I think that's the question for DB2 Replication forum.

Regards,

Gary

index replication from DB2

Has any body done a replication of indexes or Primary keys from DB2 to SQL 2005 tables. Is that possible and how pls?

Thank you very much.

I believe by default primary keys are replicated. Is it not working for you?

http://msdn2.microsoft.com/en-us/library/ms152492.aspx

Gary

|||

is that stated in the msdn document u sent me?

or does it say so in any other document, so I can show it to the person who said it s not possible<

Thanks a lot for your help.

|||

If you take a look at sp_addarticle at its @.schema_option, there is option to set for primary key constraint.

http://msdn2.microsoft.com/en-us/library/ms173857.aspx

If you look at the default schema option for all the log based article type 0x80 are enabled for all of them. I think this article is the closest I can think of. I'm pretty sure, it's on by default.

Also a quick test probably won't hurt, just create a simple table with two integer columns and make the first column the primary key, then replicate to db2. Seeing is believing :-)

Gary

|||actually we re replicating from DB2 to SQL and not the other way? is it the same?
sorry but i never done replication before. Thanks again for your help
|||

I misread your posting... I think that's the question for DB2 Replication forum.

Regards,

Gary

sql

Index on IsRowGuidCol ?

For merge replication I have only a few tables where the primary key spans
two columns. For the rest of the tables the primary key is one column and
is a uniqueidentifier.
For the fre tables where it spans two columns, I have to add an additional
uniqueidentifier column and mark it as the RowGuid Column for merge
replication.
My Question:
Will adding an index on this column speed merge replication?
--Pepto
Merge replication will add a unique index to the column that it uses for
merge replication. I believe in this case that it will create another
rowguid column to use.
Three conditions have to be met to precreate a rowguid column for merge
replication to use
1) it is called rowguid
2) its data type is uniqueidentifier ROWGUIDCOLUMN
3) it has a unique index on it.
If any of the three conditions are not met, merge replication will create
another column to use that meets these conditions although its name will be
slightly different - rowguidi where i increments.
"Pepto" <pepto@.hotmail.com> wrote in message
news:Ooh2BduPFHA.164@.TK2MSFTNGP12.phx.gbl...
> For merge replication I have only a few tables where the primary key spans
> two columns. For the rest of the tables the primary key is one column and
> is a uniqueidentifier.
> For the fre tables where it spans two columns, I have to add an additional
> uniqueidentifier column and mark it as the RowGuid Column for merge
> replication.
> My Question:
> Will adding an index on this column speed merge replication?
> --Pepto
>
>

Index not replicated to the device

Hi,

I have a table published on the SQL2K Merger Replication publisher. But when I add an index to the table it's not replicated to the device. I have checked the snapshot folder and the newly added index is already in the .dri file. Any ideas?

Cheers,
Justin
One more thing to add, the index is not replicated to the existing subscriber but it works with new subscibers. Does that mean I have to drop and re-add subscription everytime a new index is added on the publisher?

Justin|||

Justin,

Replication supports a wide range of schema changes to published objects. When you make those schema changes on the appropriate published object at a Microsoft SQL Server Publisher, that change is propagated by default to all SQL Server Subscribers. However, Explicitly adding, dropping, or altering indexes is not supported. That is why you see the index is not replicated to the existing subscriber but it works with new subscribers.

You don't need to drop and re-add subscription every time a new index is added on the publisher, you just need to reinitialize the subscribers. Or you can add the index manually at subscribers Or using sp_addscriptexec to populate the index creation script to all the subscribers.

Hope it helps.

Wanwen

|||Thx, Wanwen.|||One more thing...as there will be no notification from the publisher or exception generated from the device during the replication after a new index is add but not pulled down to the device, is there a way we could know when to reinitialize the subscription?

Index gone in merge replication

I have a merge replication and I inserted 5000 records in the main publisher
and they were inserted in the alternate publisher and in 1 subscriber, but
replicating in the other 2 subscribers is very slow is taking forever. And
sometimes it fails by timeout. I see the server and sql is consuming 99%
because he's trying to insert these records and goes 100 by 100.
I looked at the table structure and the only thing different I see is the
index_2109250569 for rowguid field. Is this causing the problem that
replicating the records is taking hours? May I create this missing index in
the table without any impact for replication?
Thanks in advance
Jennyfer
I am not so sure it wouldn't have an impact (it might have an impact when
you are reinitializing the replication or things like that).
Anyways, I would create it to see if that improves the performance.
The worst case scenario if that the agent tries in the future to create the
index and it can't (because it is already there) and then it fails saying
that (and the solution would be to drop the index).
Jos.
"Jennyfer Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:uG2bY4h5FHA.724@.TK2MSFTNGP14.phx.gbl...
> I have a merge replication and I inserted 5000 records in the main
publisher
> and they were inserted in the alternate publisher and in 1 subscriber, but
> replicating in the other 2 subscribers is very slow is taking forever. And
> sometimes it fails by timeout. I see the server and sql is consuming 99%
> because he's trying to insert these records and goes 100 by 100.
> I looked at the table structure and the only thing different I see is the
> index_2109250569 for rowguid field. Is this causing the problem that
> replicating the records is taking hours? May I create this missing index
in
> the table without any impact for replication?
> Thanks in advance
> Jennyfer
>