I am working on a setting up an alternate synchronization partner, and
getting an error. Bear with me as the explanation of what I have done so far
is long.
We have about 75 customers with about 100 pull subscriptions using about 20
publications in merge replication with Sql Server 2000. Our customers are
located where many have dial up connections and they need to set own
synchronization schedules. All are synchronizing with programs we wrote in
VB 6.0 using SQLDMO and the ActiveX merge control. All our customers have
been able to successfully synchronize now for many months, so we know our
programs are working as they should. They all synchronize over the internet
to a single publisher/distributor.
We would like to build some redundancy into our topology so that we have an
alternate synchronization server, or partner, as the BOL calls it, in case
the primary publisher fails. However, we want to locate the alternate synch
partner at a site other than where the primary publisher is located.
I have printed the MS KB article # 321176 on how to set up an alternate
synch partner. I also listened to the web cast from MS on this subject.
Following the KB #321176, I have set up two test databases, and a
subscriber. There is publisher A, and publisher B, with subscriber A.
Publisher A is the primary publisher and publisher B is to become our
alternate synch server. As the KB article and web cast said to do, I have
completed the following:
1.Created a global pull subscription on publisher B to publisher A.
2.Created a publication on publisher B which is identical to the database
on publisher A. The database names on both publisher A and publisher B are
the same name as are the publication names.
3.Generated a snapshot of the database on publisher B.
4.Enabled subscriber A on publisher B.
5.Set the alternate synch partners on both publisher A and publisher B so
each can use the other as a synch partner.
6.Created a pull subscription to publisher A from subscriber A which
synchronizes fine.
I then have tried to synchronize to the alternate publisher B from
subscriber A by changing the job commands as described in the KB article, but
get an error. In fact, the merge agent is able to connect to publisher B,
and initializes the process. In other words, I believe I have everything
set up correctly since the subscriber A connects and initializes to publisher
B. When it starts the actual synchronization, I get the following message:
“The process could not drop one or more tables because the tables are being
used by other publications.“ This is shown as error # -2147200976.
Apparently, subscriber A is trying to use the snapshot from publisher B,
which has scripts that tell it to drop tables. However, the tables trying to
be dropped are the subscription database as it was originally set up from the
pull subscription to publisher A. So, while the error make sense in a way
because the tables are replicated, subscriber A cannot synchronize to
publisher B because it is stopped by the error.
What am I doing wrong?
Do the database names and/or the publication names on the two publishers
need to be different? I had read one reference in BOL that said they should
be the same, but the KB article seems to imply otherwise.
I have found very little on any internet web site about Sql Server that
describes setting up and running alternate synch partners. Microsoft has the
most I have found on the subject, and I think I am following the MS set-up
correctly.
Any help would be gratefully appreciated.
Thanks!
Bill
Hi Bill,
From your descriptions, I understood that when applying alternate
synchronization partner, you encouter the error message "The process could
not drop one or more tables because the tables are being used by other
publications." Have I understood you? Correct me if I was wrong.
Based on my knowledge, this is because some entries does not match between
table sysmergepublications and sysmergesubscriptions on the publisher and
subscriber. A quick resolution is drop the publication completely and then
create it again.
How to manually remove a replication in SQL Server 2000
http://support.microsoft.com/kb/324401
I understood drop the publication may have huge business impact to your
business, as an option, please send the the result of following T-SQL
statement. I would like to check to see whether I could help further
select * from sysmerge_subscriptions
go
sp_helpserver
However, please understand that this replication issues tend to be very
complex and hard to troubleshoot in newsgroups. If you need further
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>
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.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael:
On a hunch before I sent you the requested the information, I changed the
database name on the alternate synch server (Publisher B) to a name that was
different from the original on Publisher A. Then the subscriber A was able
to sychronize with Publisher B, so I made it over that hurdle.
However, now, Subscriber A will not syncrhonize with Publisher A, but only
with Publsiher B. I get the following error:
"The Publisher has been restored from a backup whose schema change version
is different from the Subscriber. Rerun the Snapshot Agent and reinitialize"
The Publisher has NOT been restored from a backup. However, I went ahead as
the message said and reran the snapshot and reinitialized the subscription
from Subscriber A. Now the subscriber will not synch with Publisher B, and I
get the same error as above. So, on Publisher B, I do the snapshap again,
and reinitialize the subscription and then synch and it works. Then I try to
synch to Publisher A again, and get the error.
In other words, after I get a successful synch to either of the publishers,
I get the error on the other one the next time I try to synch to the other.
It is like an infinite loop. One works, the other doesn't. I run the
snapshot on the other and reinitialize, and it synchs, and then first one
doesn't, etc, etc. etc.
What is wrong now?
Thanks in advance for any help you can give!
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> From your descriptions, I understood that when applying alternate
> synchronization partner, you encouter the error message "The process could
> not drop one or more tables because the tables are being used by other
> publications." Have I understood you? Correct me if I was wrong.
> Based on my knowledge, this is because some entries does not match between
> table sysmergepublications and sysmergesubscriptions on the publisher and
> subscriber. A quick resolution is drop the publication completely and then
> create it again.
> How to manually remove a replication in SQL Server 2000
> http://support.microsoft.com/kb/324401
> I understood drop the publication may have huge business impact to your
> business, as an option, please send the the result of following T-SQL
> statement. I would like to check to see whether I could help further
> --
> select * from sysmerge_subscriptions
> go
> sp_helpserver
> --
> However, please understand that this replication issues tend to be very
> complex and hard to troubleshoot in newsgroups. If you need further
> 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>
>
> 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.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi,
I found you may encounter the problem described by KB article 814460
FIX: Merge Replication with Alternate Synchronization Partners May Not
Succeed After You Change the Retention Period
http://support.microsoft.com/kb/814460
Please feel free to ask this hotfix by contacting CSS. If you are simply
requesting a hotfix be sent to you and no other support then charges are
usually refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
NOTE that the hotfix is only intended to correct the problem that is
described in this article. Only apply it to systems that are experiencing
this specific problem. This hotfix may receive additional testing.
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.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thank you, Michael. This sounds like the issue I am having.
I looked at the "http://support.microsoft.com/kb/814460" as you suggested.
At the bottom of the page is says that the fix is also included in "MS03-031:
Security patch for SQL Server 2000 Service Pack 3" and gives a link to
download it, so I would prefer to do that rather than place a phone call as I
think it will save time.
However, in the middle of the KB # 814460, it says:
"The problem exists in SQL Server 2000 Service Pack 3, version 8.00.0760. To
fix this version, you must apply SQL Server 2000 Service Pack 3 rollup,
version 8.00.0765."
The versions we are running are 8.00.0760 on both of the affected servers,
so I tried to find the "SQL Server 2000 Service Pack 3 rollup, version
8.00.0765" on the Microsoft web site and cannot. When I search both
downloads and the entire site, I get no hits except back to the KB # 814460.
Can you tell me where I can get "SQL Server 2000 Service Pack 3 rollup,
version 8.00.0765"?
Thank you.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi,
> I found you may encounter the problem described by KB article 814460
> FIX: Merge Replication with Alternate Synchronization Partners May Not
> Succeed After You Change the Retention Period
> http://support.microsoft.com/kb/814460
> Please feel free to ask this hotfix by contacting CSS. If you are simply
> requesting a hotfix be sent to you and no other support then charges are
> usually refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default...S;PHONENUMBERS
> NOTE that the hotfix is only intended to correct the problem that is
> described in this article. Only apply it to systems that are experiencing
> this specific problem. This hotfix may receive additional testing.
> 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.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
Sorry that I should have clarified it more clearly.
Yes, You could download the patch available in the KB article 821277
MS03-031: Security patch for SQL Server 2000 Service Pack 3
http://support.microsoft.com/kb/821277
Let me know whether this security patch resolves your issue.
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:
Thanks for this reference. I have installed the hot fix on the alternate
synchronization server, but have not yet installed it on the primary server.
That will have to wait a couple of days as that server is very busy right now
with our customer's activity, so I won't be able to check the results on the
error I was getting for at least a couple of days. The hot fix requires Sql
Server to be stopped to install it, and we need to wait until a more
opportune time that has minimal impact on our customers.
I will be sure, however, to let you know the results when I have a chance to
test it. I just wanted you to know I won't be able to do my next testing on
this issue for a little while yet.
Thanks for your continuing help and I'll be sure to let you know what happens.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> Sorry that I should have clarified it more clearly.
> Yes, You could download the patch available in the KB article 821277
> MS03-031: Security patch for SQL Server 2000 Service Pack 3
> http://support.microsoft.com/kb/821277
> Let me know whether this security patch resolves your issue.
>
> 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 haven't heard back from you yet and I'm just writing in to see if you
have had an opportunity to perform the hotfix. If you could get back to me
at your earliest convenience, we will be able to go ahead
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 Michael:
Sorry it took me so long to respond back to you. I have been extremely busy
with customer issues and a family crisis of sorts, so I didn't get time unitl
yesterday to do some more testing.
I applied the hotfix to the three servers involved and it eliminated the
error! I was able to synchronize successfully numerous times between all the
machines. I was very pleased with the result.
However, I had my business partner add a another subscriber late yesterday
afternoon. He was able to create a suscription fine to the primary synch
server, but when trying the alternate, he started getting some errors. But
we both had other matters to attend to at that time and did not have a chance
yet to analyze the errors and if it was the way Sql Server is setup on his
machine or some other issue. We will work on that issue today, and may have
some additional questions for you. We did apply the hotfix to the computer
he is using for this test.
Thanks for you continuing help.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> I haven't heard back from you yet and I'm just writing in to see if you
> have had an opportunity to perform the hotfix. If you could get back to me
> at your earliest convenience, we will be able to go ahead
> 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:
We resolved all the initial errors and have the test alternate synch partner
running well with two subscribers. We have been able to run many, many
successful test syncrhonizations to both servers now without difficulty.
Part of testing plan, however, is to make some schema changes, and I have
run into another problem there. I will start a new thread for this issue.
Thanks for your help.
Bill
"Michael Cheng [MSFT]" wrote:
> Hi Bill,
> I haven't heard back from you yet and I'm just writing in to see if you
> have had an opportunity to perform the hotfix. If you could get back to me
> at your earliest convenience, we will be able to go ahead
> 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.
>
