Hello,
I'm working on a replication topology that is completely merge. We have a single consolidated instance (SQL 2005 SP1 Standard) that holds all data and is a continuous push merge publication filtered by region to regional instances (SQL 2005 SP1 Standard). Then we have individual user instances (SQL Express SP1) that pulls from the republished regional instances which is filtered by user. Both publications have Replicate Schema Changes set to true.
I'm testing out changes to tables and sps on a test system I've been using this process:
1-Run Snapshot on the Consolidated instance
2-Verify all published articles have a status of 2 in sysmergearticles
3-Run Regional Snapshot
4-Verify all published articles have a status of 2 in sysmergearticles
5-Run alter table scripts
6-Once all three levels have the table changes, run the alter sp scripts
I've gotten to step 5 and and the changes get replicated to the regional instance just fine however only the existing column changes get replicated to the SQLExpress instance, not the new columns. Looking at the articles in the regional publication it shows the new columns, but they are not selected. I know I can manually select them (or probably write a script that adds them to the publication although sp_repladdcolumn has been depreciated), but isn't there a way to make this a completely automated process since it's just a republished database? Also is the process I'm using the correct one?
Thank you,
Aaron Lowe
Is your publication property replication_ddl set to true?|||I apologize for not being clearer in my original post. I had said that replicate schema changes was set to true, this is the replication_ddl property that I was referring to. Thanks, Aaron|||when you add a new column, the column should get replicated to all nodes in your topology. Is the new column not getting replicated at all? Where in your topology are you adding the new columns - publisher, republisher or subscriber?|||I'm adding the columns at my original publisher (the consolidated one). As I said it is pushed down to my subscribers that republish the data (the regional ones that are pushed from the consolidated one), it just doesn't get all the way down to my final subscribers (the individual sqlexpress ones that pull the data). Looking at the properties of the publication on the republisher it shows the columns in the publication but they are not selected.|||if replicate_ddl option is truly enabled at both the publisher and the republisher, then I'm not sure what the problem is. You verified the replicate_ddl column is set to 1 in sysmergepublications table in the published database at both the publisher and republisher?|||Well, I believe it's correct, here's what is in the sysmergepublications:
Consolidated database (original publisher)
publication name, replicate_ddl
Consolidated, 1
Region, 0
Regional database (republisher)
publication name, replicate_ddl
Consolidated, 1
Region, 1
SQL Express database (subscriber)
publication name, replicate_ddl
Consolidated, 0
Region, 1
Also the status in sysmergearticles in the consolidated db is 2 (active). There are two sets of articles in the sysmergearticles table in the regional db, one for each the consolidated and regional publication. The records in sysmergearticles for the consolidated publication has a status of 1 (Unsynced) while the records for the regional publication have a status of 2 (active). The status in the SQLExpress pull subscriptions is all 1 (Unsynced).
Thanks,
Aaron
|||Can you try your scenario with SP2? We fixed somewhat similar issue in SP2.sql