Re: Replication is failing due to use of FTS on my subscriber, what can I do?
- From: "Raymond Mak [MSFT]" <rmak@xxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 1 May 2007 09:54:35 -0700
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@xxxxxxxxxxxxxxxx> wrote in message
news:uAOtEPAjHHA.1272@xxxxxxxxxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: Replication is failing due to use of FTS on my subscriber, what can I do?
- From: Daniel Crichton
- Re: Replication is failing due to use of FTS on my subscriber, what can I do?
- References:
- Replication is failing due to use of FTS on my subscriber, what can I do?
- From: Daniel Crichton
- Replication is failing due to use of FTS on my subscriber, what can I do?
- Prev by Date: Snapshot Agent Updates statistics on all PK indexes even when there is NOTHING to do!!
- Next by Date: Re: Snapshot Agent Updates statistics on all PK indexes even when there is NOTHING to do!!
- Previous by thread: Replication is failing due to use of FTS on my subscriber, what can I do?
- Next by thread: Re: Replication is failing due to use of FTS on my subscriber, what can I do?
- Index(es):
Relevant Pages
|
