Reinitializing subscriptions not working

Hi,

I'm doing merge sync between SQL Compact on mobile devices and SQL Server 2005. I recently made a schema change on the server and noticed that it caused an error on the subscribers when syncing. I fixed the schema problem, and did a reinitialize all subscriptions with a new snapshot. On the mobile devices I forced a reinitialisation (upload changes first). I'm still getting the same error message, which relates to a schema change which is no longer relevant to the server db and snapshot. For some reason the subscriber dbs are trying to reapply the original problem schema change even though they're supposed to be reinitializing to a new snapshot. I've even tried dropping the affected tables from the publication, and reinitializing all subscriptions with a new snapshot, but still the same error on the subscribers. Does anyone know why the subscriber dbs are still trying to apply a now defunct schema change (on tables which no longer exist in the publication)? Is there any way to flush this from the subscribers so they correctly pick up the new snapshot?

Regards,

Greg McNamara

Some additional information on the above:

The "rogue" schema change appears to be coming from the publisher, and not cached on the subscriber. The basic problem is that I'm trying to reinitialize subscribers, but it's trying to make a schema change on the subscribers which was made before the current snapshot was created. My understanding of reinitialization was that it would upload subscriber changes and then basically rebuild the subscriber db from the snapshot. Instead it seems to be applying incremental, historical schema changes on the subscriber.

Hope someone can help me with this.

Regards,

Greg McNamara

|||

And more info:

The schema change causing the original problem was the addition of a foreign key constraint. Records in a table were deleted on the server db before adding the constraint but the subscriber dbs still contain records. The sync is failing because it's trying to apply a constraint against existing records and failing. Reinitializing the subscribers is not fixing the problem (as detailed above). I decided to try deleting the records on the subscriber db before re-syncing. I now get a different error message on sync:

"Either the cursor is not on a row or there are no rows left"

Apparently this is a SQL Compact engine internal error. The malfunctioning reinitialization function seems to have caused this. I tried a repair/compact on the db but still get the same error message on syncing.

Back to the reinitialize not using the current snapshot, is anyone aware of places I should look to see if an old snapshot is being cached and somehow used by the merge sync agent?

Regards,

Greg McNamara

Reinitializing Subscriptions

I am new to SQL Server Replication so I still have a cloud over my
head.
Our target system for a new application is to have a central Sql
Server 2000 SP3 with at least 50 laptops running MSDE 2000 SP3. Mobile
workforce will be updating data offline that will then need to synch
to the master and back down throughout the other laptops (some of it
anyway, dynamic filtering is next on my list). We want to use
anonymous subscriptions.
For testing, I have the Northwind database setup on Windows 2000
running Sql Server 2000 and an XP machine running MSDE. I am using
Visual Studio .NET on the XP machine to go against the ActiveX
Replication objects.
I have successfully setup merge replication. The XP machine uses .NET
code against the SQLMerge ActiveX object to use an anonymous
subscription to pull down data from the publisher. I was able to see
changes made on both databases get merged correctly. I was also able
to see conflicts and see them get resolved correctly.
One area I still fail to understand (and there are many) is
Reinitializing Subscriptions. The scenario I am trying to test is how
we roll out a new release of the existing application -- and in this
case -- changes to the database. I have read and re-read the
Reinitializing Subscriptions in Books Online but I just don't get it.
I changed my publication on the Northwind database to exclude sending
the Picture column in the Categories table. EM told me that all
subscriptions would be marked for reinitialization and that subscriber
data not synchronized first would be lost. So it works as EM and Books
Online say as I lost my data I changed on the laptop (I changed a
description field in the Categories table).
One thing that did not seem to jive with the documentation was that I
tried to synchronize the laptop database before creating the new
snapshot but the SQLMerge.Initialize method received an error saying
the snapshot was not up to date. So I did not have any way of getting
my changed data from the laptop to the server once I had made the
change to the publication.
In our production environment, the laptops will trickle in over time
complicating matters.
But basically, how do I make a change to the database that needs to
get down to the laptops AND preserve the data that has been collecting
on the laptops for weeks?
Crossing my fingers for something built-in,
Gar
There is an option called upload changes to publisher before
reinitialization.
Have a look at this property
Sub ReinitializeSubscription([bUploadBeforeReinit As Boolean = False])
I think you want to set this to True.
What will happen is changes which are pooled on the subscriber are uploaded
to the publisher before the schema changes and the merged data are sent to
the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"post messages" <post.messages@.gmail.com> wrote in message
news:c45b744b.0502031406.5dbd12bc@.posting.google.c om...
> I am new to SQL Server Replication so I still have a cloud over my
> head.
> Our target system for a new application is to have a central Sql
> Server 2000 SP3 with at least 50 laptops running MSDE 2000 SP3. Mobile
> workforce will be updating data offline that will then need to synch
> to the master and back down throughout the other laptops (some of it
> anyway, dynamic filtering is next on my list). We want to use
> anonymous subscriptions.
> For testing, I have the Northwind database setup on Windows 2000
> running Sql Server 2000 and an XP machine running MSDE. I am using
> Visual Studio .NET on the XP machine to go against the ActiveX
> Replication objects.
> I have successfully setup merge replication. The XP machine uses .NET
> code against the SQLMerge ActiveX object to use an anonymous
> subscription to pull down data from the publisher. I was able to see
> changes made on both databases get merged correctly. I was also able
> to see conflicts and see them get resolved correctly.
> One area I still fail to understand (and there are many) is
> Reinitializing Subscriptions. The scenario I am trying to test is how
> we roll out a new release of the existing application -- and in this
> case -- changes to the database. I have read and re-read the
> Reinitializing Subscriptions in Books Online but I just don't get it.
> I changed my publication on the Northwind database to exclude sending
> the Picture column in the Categories table. EM told me that all
> subscriptions would be marked for reinitialization and that subscriber
> data not synchronized first would be lost. So it works as EM and Books
> Online say as I lost my data I changed on the laptop (I changed a
> description field in the Categories table).
> One thing that did not seem to jive with the documentation was that I
> tried to synchronize the laptop database before creating the new
> snapshot but the SQLMerge.Initialize method received an error saying
> the snapshot was not up to date. So I did not have any way of getting
> my changed data from the laptop to the server once I had made the
> change to the publication.
> In our production environment, the laptops will trickle in over time
> complicating matters.
> But basically, how do I make a change to the database that needs to
> get down to the laptops AND preserve the data that has been collecting
> on the laptops for weeks?
> Crossing my fingers for something built-in,
> Gar
|||Hi Hilary,
Thanks for the reply. Based on what you said I did more searching in
the newsgroup. I found a post that is a little dated, didn't get
promising answers and is still important to the success of trying to
send out database schema upgrades through replication.
Detect Reinitialization is required.
http://groups-beta.google.com/group/...public.sqlser
ver.replication%26q%3DReinitializeSubscription%26q t_g%3D1%26searchnow%3DSearch+this+group%26&_doneTi tle=Back+to+Search&&d#62baf38ab39170d6
Since the laptops are going to have compiled .NET code calling the
merge object, how should they detect that they need to call
ReinitializeSubscription(true) before calling the usual Initialize,
Run, Terminate methods? My understanding is it is wrong to always call
ReinitializeSubscription(true) -- it has to be called only when
necessary.
Thanks,
Gary

reinitializing subscriptions

We had some data become corrupt on our publisher and have to do a database
restore. Am I correct in thinking that after the restore I can just click
'reinitialize all subscriptions" and the snapshot and transactional
replication will clear out the old data and fill with the new data again at
the subscriber?
Partially. The reinitialze all subscriptions will mark the snapshots as
obsolete and the next time your snapshot agent runs the snapshot will be
generated and distributed to the subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"AW" <AW@.discussions.microsoft.com> wrote in message
news:6AA6434B-D32D-46DD-BCA2-8D22E0E7C0EA@.microsoft.com...
> We had some data become corrupt on our publisher and have to do a database
> restore. Am I correct in thinking that after the restore I can just click
> 'reinitialize all subscriptions" and the snapshot and transactional
> replication will clear out the old data and fill with the new data again
> at
> the subscriber?
|||On 2004-12-03, Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> Partially. The reinitialze all subscriptions will mark the snapshots as
> obsolete and the next time your snapshot agent runs the snapshot will be
> generated and distributed to the subscribers.
Does that mean, for merge replication, that I'll have all the data on the
publisher resent to the subscriber? DROP table on the subscriber, recreate
it, and then apply the initial snapshot? Or that happens only when first
setting up the replication?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr

Reinitialize subscriptions in transactional replication

If I have one table in one publication in transactional replication
replication between primary and replicate is broken because subscription is marked as inactive.

If there are 3 rows on replicate and 5 rows on primary , out of which 2 are added after replication is broken

If I do

Reinitialize subscriptions
start the snapshot agent
start the distribution agent

Does this mean that it will only transfer the new 2 rows to replicate sites ? or will it drop everything from replicate site and apply all rows from primary site on to replicate.

Any help is appreciatedIt will drop every article the subscriber subscribes and refresh the DDL and data from the publication.

reinitialization of subscriptions

If I have a subscription to a publication (transactional)
and the Distribution Agent tells me I need to
reinitialize.
What are the steps to do this?
Do I need to delete all the data on the subscriber? If
so, is there a way around this? If not, what is the best
practice of accomplishing this?
What if there are changes made at the subscriber do I
loose them if the subscription is reinitializatized?
Thanks...
Jimmy,
reinitialize means apply a new snapshot, so, you'll need to run the snapshot
agent first then run the distribution agent. When you say will it delete all
the data on the subscriber, I'm not sure why this is a concern. For
transactional replication, the subscriber data is treated as RO data. If
however someone has edited the data, then you could use binary checksums to
see what has changed and then do the same changes on the publisher. If the
subscriber is using updatable transactional replication then the changes are
already on the publisher. If you're using a queue, then be sure to run the
queue reader to send up the changes.
HTH,
Paul Ibison
|||expand your publishing database, right click on your publication, select
properties, click on your subscriber tab, highlight your subscriber, and
click the reinitialize button.
It will delete all the data of the replication objects and their schemas and
then replace them.
"Jimmy" <anonymous@.discussions.microsoft.com> wrote in message
news:9dc801c43447$43f3a5a0$a601280a@.phx.gbl...
> If I have a subscription to a publication (transactional)
> and the Distribution Agent tells me I need to
> reinitialize.
> What are the steps to do this?
> Do I need to delete all the data on the subscriber? If
> so, is there a way around this? If not, what is the best
> practice of accomplishing this?
> What if there are changes made at the subscriber do I
> loose them if the subscription is reinitializatized?
> Thanks...
|||Paul,
You said...
then you could use binary checksums to see what has
changed and then do the same changes on the publisher
How?
Larry...

>--Original Message--
>Jimmy,
>reinitialize means apply a new snapshot, so, you'll need
to run the snapshot
>agent first then run the distribution agent. When you
say will it delete all
>the data on the subscriber, I'm not sure why this is a
concern. For
>transactional replication, the subscriber data is
treated as RO data. If
>however someone has edited the data, then you could use
binary checksums to
>see what has changed and then do the same changes on the
publisher. If the
>subscriber is using updatable transactional replication
then the changes are
>already on the publisher. If you're using a queue, then
be sure to run the
>queue reader to send up the changes.
>HTH,
>Paul Ibison
>
>.
>
|||Larry,
there's a good explanation of BINARY_CHECKSUM in books online (BOL). If you
have any problems with the explanation, please post back.
Regards,
Paul Ibison