If anyone has run across this before, I'm having the same
issue with the same components.
If I figure anything out, I'll post findings here.
Thanks,
David
>--Original Message--
>Morning Guys,
>I really need you help, I am just about to seriously
pull my hair out. Here
>is the situation.
>I have 1 server running SQL Server 2000 sp3a with merge
replication setup,
>and 150 users on laptops running MSDE 2000 with the
latest service pack and
>dialling up to the server via a 56k modem and using a
Cisco VPN Client to
>gain access.
>If I communicate on the network, all the replication
works fine, if I
>communicate on a ADSL Line all the communicates is fine,
if I dialup,
>communication will just fail, it will process the
articles and then fail. I
>really need some help here. I have adjusted the MTU rate
on the server,
>Cisco router and the client. but I just can't get this
to work correctly and
>I have this working in many other companies.
>The dial up users are getting a good connection with 50k
>If you need anymore information give me a shout.
>Thanks Tim.
>
>.
>
I believe Paul Ibison has seen problems with this. If he doesn't chime in
here, contact him offline.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"David England" <anonymous@.discussions.microsoft.com> wrote in message
news:40e101c4900f$40544430$a301280a@.phx.gbl...[vbcol=seagreen]
> If anyone has run across this before, I'm having the same
> issue with the same components.
> If I figure anything out, I'll post findings here.
> Thanks,
> David
> pull my hair out. Here
> replication setup,
> latest service pack and
> Cisco VPN Client to
> works fine, if I
> if I dialup,
> articles and then fail. I
> on the server,
> to work correctly and
Showing posts with label components. Show all posts
Showing posts with label components. Show all posts
Tuesday, February 21, 2012
Replication is failing due to use of FTS on my subscriber, what can I do?
Hi Daniel,
If you are using push subscriptions from a SQL2000 distributor, you are
essentially using the SQL2000 replication components which, I am sorry to
say, are ill-equipped to handle things like full-text indexes during
snapshot processing. This also means that upgrading the subscriber to
SQL2005 SP2 will probably not do you any good. Alternatively, you can use a
SQL2005 SP2 instance as the distributor of your SQL2000 publisher so you can
enable the FulltextIndex (0x0000000001000000) article schema option via
sp_add|changearticle (both SQL2000 and SQL2005 GUI will not allow you to
specify that for a SQL2000 publisher) and let the SQL2005 replication
components handle the full-text index dependencies for you (ideally using
"drop" as the article pre-creation commands). That said, I can imagine that
moving the distributor can involve a substantial amount of work.
Hope that helps,
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:uAOtEPAjHHA.1272@.TK2MSFTNGP04.phx.gbl...
> I'm in the middle of setting up a non-updating transactional replication
> from SQL Server 2000 SP3 (can't put SP4 on as the main application we use
> hasn't been certified for SP4 yet) to SQL Server 2005 SP1 (I could put SP2
> on if it will fix the issue, but it would mean taking our e-commerce sites
> down while doing so and I'd need to schedule a maintenance window for
> this). At first it was working fine, but snapshots were set to delete the
> tables and so we'd lose the FTS setups. I've now changed the snapshots to
> not do this, and now the replications fails with the error:
> Cannot drop index 'PK_Product' because it enforces the full-text key for
> table or indexed view 'Product'.
> This occurs with both "Delete data. If article has a row filter, delete
> only data that matches the filter.", "Truncate all data in the existing
> object" (which is the preferred option), and "Keep existing object
> unchanged". It also doesn't matter if I have the clustered and/or
> nonclustered index copy set to true or false (the PK is the clustered
> index on this table).
> This is a push subscription, with the distribution agent running on the
> publisher.
> Any ideas how I can retain the table structure (so keeping the FTS index
> and not having to rebuild it each time the snapshot is applied) and have
> transaction replication working? Or is it not possible?
> Dan
>
Hi Daniel,
I probably don't understand all the details about your scenario but given
that applying a snapshot typically replaces most of the data at the
subscriber, I would think that a FTS rebuild is likely required (or at least
a good idea) after the snapshot has been applied. That said, we no longer
drop the primary key\unique constraints (which is an arguably bad fix for
some other issue) in a "mostly" SQL2005 environment (SQL2005 snapshot and
distribution agent) if the article pre-creation is 'delete'. However,
leaving any unique constraints\indexes (especially non-clustered) while the
snapshot is being applied runs the risk of slowing down the snapshot
delivery process significantly (no minimal logging) and may prevent a
concurrent snapshot from being delivered successfully since bcp data
generated for a concurrent snapshot can violate uniqueness constraints
(compensated later on during the reconciliation phase).
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eLx1McKjHHA.4904@.TK2MSFTNGP05.phx.gbl...
> Raymond wrote on Tue, 1 May 2007 09:54:35 -0700:
>
> It seems strange that all options require the PK to be dropped - I would
> have thought that the options to delete or truncate data in the
> destination table would leave the PK untouched (after all, it shouldn't be
> touching the schema). I don't have an FTS index on the SQL Server 2000
> source tables - the FTS is handled solely on the subscriber, so I'm not
> trying to replicate the FTS catalog, just the data in the tables so that
> the change tracking on the subscriber handles all the FTS work.
> Dan
>
|||Hi Daniel,
I probably don't understand all the details about your scenario but given
that applying a snapshot typically replaces most of the data at the
subscriber, I would think that a FTS rebuild is likely required (or at least
a good idea) after the snapshot has been applied. That said, we no longer
drop the primary key\unique constraints (which is an arguably bad fix for
some other issue) in a "mostly" SQL2005 environment (SQL2005 snapshot and
distribution agent) if the article pre-creation is 'delete'. However,
leaving any unique constraints\indexes (especially non-clustered) while the
snapshot is being applied runs the risk of slowing down the snapshot
delivery process significantly (no minimal logging) and may prevent a
concurrent snapshot from being delivered successfully since bcp data
generated for a concurrent snapshot can violate uniqueness constraints
(compensated later on during the reconciliation phase).
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eLx1McKjHHA.4904@.TK2MSFTNGP05.phx.gbl...
> Raymond wrote on Tue, 1 May 2007 09:54:35 -0700:
>
> It seems strange that all options require the PK to be dropped - I would
> have thought that the options to delete or truncate data in the
> destination table would leave the PK untouched (after all, it shouldn't be
> touching the schema). I don't have an FTS index on the SQL Server 2000
> source tables - the FTS is handled solely on the subscriber, so I'm not
> trying to replicate the FTS catalog, just the data in the tables so that
> the change tracking on the subscriber handles all the FTS work.
> Dan
>
If you are using push subscriptions from a SQL2000 distributor, you are
essentially using the SQL2000 replication components which, I am sorry to
say, are ill-equipped to handle things like full-text indexes during
snapshot processing. This also means that upgrading the subscriber to
SQL2005 SP2 will probably not do you any good. Alternatively, you can use a
SQL2005 SP2 instance as the distributor of your SQL2000 publisher so you can
enable the FulltextIndex (0x0000000001000000) article schema option via
sp_add|changearticle (both SQL2000 and SQL2005 GUI will not allow you to
specify that for a SQL2000 publisher) and let the SQL2005 replication
components handle the full-text index dependencies for you (ideally using
"drop" as the article pre-creation commands). That said, I can imagine that
moving the distributor can involve a substantial amount of work.
Hope that helps,
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:uAOtEPAjHHA.1272@.TK2MSFTNGP04.phx.gbl...
> I'm in the middle of setting up a non-updating transactional replication
> from SQL Server 2000 SP3 (can't put SP4 on as the main application we use
> hasn't been certified for SP4 yet) to SQL Server 2005 SP1 (I could put SP2
> on if it will fix the issue, but it would mean taking our e-commerce sites
> down while doing so and I'd need to schedule a maintenance window for
> this). At first it was working fine, but snapshots were set to delete the
> tables and so we'd lose the FTS setups. I've now changed the snapshots to
> not do this, and now the replications fails with the error:
> Cannot drop index 'PK_Product' because it enforces the full-text key for
> table or indexed view 'Product'.
> This occurs with both "Delete data. If article has a row filter, delete
> only data that matches the filter.", "Truncate all data in the existing
> object" (which is the preferred option), and "Keep existing object
> unchanged". It also doesn't matter if I have the clustered and/or
> nonclustered index copy set to true or false (the PK is the clustered
> index on this table).
> This is a push subscription, with the distribution agent running on the
> publisher.
> Any ideas how I can retain the table structure (so keeping the FTS index
> and not having to rebuild it each time the snapshot is applied) and have
> transaction replication working? Or is it not possible?
> Dan
>
Hi Daniel,
I probably don't understand all the details about your scenario but given
that applying a snapshot typically replaces most of the data at the
subscriber, I would think that a FTS rebuild is likely required (or at least
a good idea) after the snapshot has been applied. That said, we no longer
drop the primary key\unique constraints (which is an arguably bad fix for
some other issue) in a "mostly" SQL2005 environment (SQL2005 snapshot and
distribution agent) if the article pre-creation is 'delete'. However,
leaving any unique constraints\indexes (especially non-clustered) while the
snapshot is being applied runs the risk of slowing down the snapshot
delivery process significantly (no minimal logging) and may prevent a
concurrent snapshot from being delivered successfully since bcp data
generated for a concurrent snapshot can violate uniqueness constraints
(compensated later on during the reconciliation phase).
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eLx1McKjHHA.4904@.TK2MSFTNGP05.phx.gbl...
> Raymond wrote on Tue, 1 May 2007 09:54:35 -0700:
>
> It seems strange that all options require the PK to be dropped - I would
> have thought that the options to delete or truncate data in the
> destination table would leave the PK untouched (after all, it shouldn't be
> touching the schema). I don't have an FTS index on the SQL Server 2000
> source tables - the FTS is handled solely on the subscriber, so I'm not
> trying to replicate the FTS catalog, just the data in the tables so that
> the change tracking on the subscriber handles all the FTS work.
> Dan
>
|||Hi Daniel,
I probably don't understand all the details about your scenario but given
that applying a snapshot typically replaces most of the data at the
subscriber, I would think that a FTS rebuild is likely required (or at least
a good idea) after the snapshot has been applied. That said, we no longer
drop the primary key\unique constraints (which is an arguably bad fix for
some other issue) in a "mostly" SQL2005 environment (SQL2005 snapshot and
distribution agent) if the article pre-creation is 'delete'. However,
leaving any unique constraints\indexes (especially non-clustered) while the
snapshot is being applied runs the risk of slowing down the snapshot
delivery process significantly (no minimal logging) and may prevent a
concurrent snapshot from being delivered successfully since bcp data
generated for a concurrent snapshot can violate uniqueness constraints
(compensated later on during the reconciliation phase).
-Raymond
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eLx1McKjHHA.4904@.TK2MSFTNGP05.phx.gbl...
> Raymond wrote on Tue, 1 May 2007 09:54:35 -0700:
>
> It seems strange that all options require the PK to be dropped - I would
> have thought that the options to delete or truncate data in the
> destination table would leave the PK untouched (after all, it shouldn't be
> touching the schema). I don't have an FTS index on the SQL Server 2000
> source tables - the FTS is handled solely on the subscriber, so I'm not
> trying to replicate the FTS catalog, just the data in the tables so that
> the change tracking on the subscriber handles all the FTS work.
> Dan
>
Subscribe to:
Posts (Atom)