Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Martyn123
Starting Member
29 Posts |
 Posted - 2010-09-04 : 04:04:19
|
| Hi,We have a two node (say NODE1 and NODE2)peer-to-peer replication set up on our system SQLServer 2008.I would like to change the metadata of one of the tables. As this is a high-availability system, I would like to stop all the replication activities, make the changes on one node(NODE1), verify that the changes are working, make the same changes on the other node(NODE2), then re-enable replication. The ddl changes to the table are extensive enough that I have to clear them before executing. So I will handle re-populating the tables in a later step.I could do the above changes using ALTER statements,if it was adding new column(s) or datatype changes, without stopping the replication activities as I have set the replicate_ddl=True, but I want to drop the primary key from one column and add primary key back on another column in this scenario I can't do that using Alter statement as replication requires a table to have a primary key (mandatory)on atleast one column so I am using the following approach: Right now I'm just testing the process of stopping, doing ddl, then restarting. Following process using the SSMS interface (note that each step is conducted on both nodes):1.stop sync agents 2.manually remove article: tblName1 with 'publications properties window' 3.close 'publications properties window' 4.make ddl changes to tblName1 (dropping primary key from one column and adding back primary key to another column) 5.manually add article: tblName1 with 'publications properties window' 6.re-enable agents But still I am getting the error:'Peer-To-Peer topologies require identical articles in publications at all nodes prior to synchronizing.' As I have made the same changes on both the nodes why am I getting this error ?Is there any other way of doing this please let me know.Please Please reply to this post as soon as possible.Thanks in advance.Martyn. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-09-07 : 14:38:54
|
| you dont need to stop the sync agents.. you can just drop the article from publishing using sp_droparticle.. make your DDL changes and add it back using sp_Addarticle. Also need to update the publisher using sp_addpublisher after you add the article using sp_Addarticle.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Martyn123
Starting Member
29 Posts |
Posted - 2010-09-08 : 22:30:37
|
| Thank you very much Dinakar for taking your precious time out and answering my query. Really it helped me a lot and I resolved the issue using sp_addarticle SP. Thanks again.Martyn. |
|
|
|
|
|
|
|
|
Altering tables in a peer-to-peer replication.