We have set up a test scenario in Sql Server 2000 using merge replication
with:
A primary synchronization server (Publisher A)
An alternate synchronization partner (Publisher B)
Two subscribers to both publishers (Subscriber Y and Subscriber Z)
After some initial difficulties, we were able to get the alternate synch
partner running where both subscribers will successfully synch to either
machine without difficulty. (See thread “Alternate Synchronization Partner
-- Error”, originally posted on March 10.)
The next part of testing phase before going live is to test how schema
changes occur with alternate synch partners. We find NO documentation on
this subject anywhere, so if anyone has resources on how to do schema changes
where an alternate synchronization partner is involved, we would be very,
very grateful.
Our basic question: How are schema changes done where an alternate
synchronization partner is involved? We want to know exact procedures to do
the following:
-- Add a column
-- Drop a column
-- Add a table
We have tried to add a column as follows:
1.Used “sp_repladdcolumn” and added a column to one table in Publisher A,
the primary publisher. (See next paragraph on warning received.)
2.Synchronized the Publisher B, the alternate synch partner and it brought
in the column.
3.Synchronized Subscriber Y to Publisher A, and it brought in the column to
the subscriber.
At #1, when “sp_repladdcolumn” ran, I received the following warning:
“Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_D536588A75244907040F5997233E41C8_pal'.
The stored procedure will still be created.”
The Microsoft knowledge base article 811483 says this warning can be
ignored, but I wonder about being able to ignore this warning because of the
results we received described below.
Results from above process:
-- NO DATA UPDATES FROM PUBLISHER A WILL PROPAGATE TO EITHER PUBLISHER B,
THE ALTERNATE, OR TO SUBSCRIBER Y WHEN SYNCRHONIZING. New records added at
Publisher A do propagate, but no updates of existing data will. Inserts
propagate – updates do not propagate.
-- In addition, all data changes, inserts, and deletes made at either
Publisher B, the alternate synch partner, or at Subscriber Y, DO propagate
back to the primary Publisher A.
When I found that no data updates would propagate to the alternate synch
partner or the subscribers, I then
1.Used “sp_repldropcolumn” to drop the column I had added on Publisher A,
the primary.
2.Synchronized Publisher B, the alternate synch partner and it dropped the
column.
3.Synchronized Subscriber Y and it dropped the column.
I then tested data updates from Publisher A, the primary, and the updates
all began to propagate to Publisher B, the alternate and Subscriber Y once
again.
Summary:
-- A column was added to the Publisher A and the schema change propagated
to Publisher B and Subscriber Y.
-- After the schema change was propagated, any data UPDATES on Publisher A
would not propagate to the Publisher B or Subscriber Y. INSERTS however,
would propagate.
-- The column was dropped at Publisher A and that schema change propagated
to Publisher B and Subscriber Y.
-- Data updates from Publish A began to propagate again go Publisher B and
Subscriber Y.
What is wrong with the procedures I am using to make schema changes that are
causing the primary synchronization server to stop propagating data updates
after the column is added, but when the column is dropped, updates begin to
propagate again?
Any procedures for schema changes when an alternate synchronization partner
is involved would be very much appreciated.
Thank you.
Bill
Hi Bill,
From your descriptions, I understood that you would like to why
sp_repladdcolumn does not take effect. If I have misunderstood your
concern, please feel free to point it out.
First of all, please understand that replication issues tend to be very
complex and hard to troubleshoot in newsgroups. If you need detail and
prompt assistance, I recommend that you open a Support incident with
Microsoft Customer Service and Support (CSS) so that a dedicated Support
Professional can work with you in a more timely and efficient manner. If
you need any help in this regard, please let me know.
For a complete list of Microsoft Customer Service and Support phone
numbers, please go to the following address on the World Wide Web:
<http://support.microsoft.com/directory/overview.asp>
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Secondly, based on my knowledge, once the column is added at the original
publisher, the merge agent between the original publisher and the alternate
publisher has to be run so that the new schema is sent to the alternate
publisher. Also, the merge agent between the original publisher and the
subscriber needs to be run so that the schema definition is sent here. As
long as the schema of the replicating pairs match, there are no issues.
It seems your process is OK and it's weird that you are not able to
synchronize the data. Do you find any other error messages in Event Logs or
SQL Logs?
You may generate the file as following Knowledge Base article asked and
then send to me direcly v-mingqc@.online.microsoft.com (plase ensure to
remove 'online' in the email address as it's only for SPAM)
HOW TO: Enable Replication Agents for Logging to Output Files in SQL Server
http://support.microsoft.com/?id=312292
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Michael:
Thanks for the information. I was preparing to send you the logs when I
suddenly wondered if I needed to reinitialize the pull subscriptions. So I
decided to try that first, and it worked. After reinitializing all
subscriptions, all data changes began to synchronize with no errors or
problems.
I then ran several additional tests with adding and dropping new columns.
When I reinitialized the subscriptions, everything synchronized. When I did
not reinitialize, the schema would propagate, but data changes would not.
This is clearly different from when there is no alternate synch server. We
have added and deleted columns many times before with sp_repladdcolumn or
sp_repldropcolumn when no alternate synch server was involved. Data changes
always continued to synchronize properly in that circumstance. Perhaps some
note needs to be put in BOL about this difference when an alternate
synchronization server is involved? Just a suggestion that might save others
time.
The next test I am going to try is to add a new table (article) to our test
database and see if I can get it to propagate to the alternate synch server
and the two subscribers we have.
You have been a great help in pointing me the right direction. I really
appreciate it.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> From your descriptions, I understood that you would like to why
> sp_repladdcolumn does not take effect. If I have misunderstood your
> concern, please feel free to point it out.
> First of all, please understand that replication issues tend to be very
> complex and hard to troubleshoot in newsgroups. If you need detail and
> prompt assistance, I recommend that you open a Support incident with
> Microsoft Customer Service and Support (CSS) so that a dedicated Support
> Professional can work with you in a more timely and efficient manner. If
> you need any help in this regard, please let me know.
> For a complete list of Microsoft Customer Service and Support phone
> numbers, please go to the following address on the World Wide Web:
> <http://support.microsoft.com/directory/overview.asp>
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> Secondly, based on my knowledge, once the column is added at the original
> publisher, the merge agent between the original publisher and the alternate
> publisher has to be run so that the new schema is sent to the alternate
> publisher. Also, the merge agent between the original publisher and the
> subscriber needs to be run so that the schema definition is sent here. As
> long as the schema of the replicating pairs match, there are no issues.
> It seems your process is OK and it's weird that you are not able to
> synchronize the data. Do you find any other error messages in Event Logs or
> SQL Logs?
> You may generate the file as following Knowledge Base article asked and
> then send to me direcly v-mingqc@.online.microsoft.com (plase ensure to
> remove 'online' in the email address as it's only for SPAM)
> HOW TO: Enable Replication Agents for Logging to Output Files in SQL Server
> http://support.microsoft.com/?id=312292
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
I searched internal but limited resource on this topic about why
reinitializing make all things go smoothly as I cannot reproduce it on my
side as you described. If you want to fingure out the root cause of this
issue, you'd better send the logs to our PSS guys as Replication issues
might turn out to be very complicated.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael;
With the reinitialization of the subscriptions, the schema changes and data
changes synchronized perfectly on numerous tests. I put together a process
for us to use in these circumstances because it is a bit different between
add/drop a column and adding a new table. In any case, I did get them work
fine with the altternate synch server.
If I did not reinitialize the subscriptions, the schema would propagate, but
the data changes would not from the primary server. I tried this in several
tests and could not get it go, but since I have a procedure that works now,
my business partners and I are happy with it and will go live in the next
month or two with our alternate synchronization partner.
I appreciate all your help and suggestions.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> I searched internal but limited resource on this topic about why
> reinitializing make all things go smoothly as I cannot reproduce it on my
> side as you described. If you want to fingure out the root cause of this
> issue, you'd better send the logs to our PSS guys as Replication issues
> might turn out to be very complicated.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
Thanks for your perfect summary and I believe others will also benefit from
your great work
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
