|
|
You're in the
sql server replication
group:Replication is failing due to use of FTS on my subscriber, what can I do?
sql server replication:
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 [quoted text, click to view] "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 >
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
Daniel,
I'd recommend having pre and post snapshot scripts to take care of the full-text index - one to drop it and one to readd it along with change-tracking. HTH, Paul Ibison
Paul wrote on Tue, 1 May 2007 21:41:25 +0100:
[quoted text, click to view] > Daniel, > I'd recommend having pre and post snapshot scripts to take care of the > full-text index - one to drop it and one to readd it along with > change-tracking. Unfortunately that is going to be a nightmare, as it takes hours to rebuild the FTS. I really need a solution that doesn't require the PK to be dropped, so that the FTS index will remain. Dan
Raymond wrote on Tue, 1 May 2007 09:54:35 -0700:
[quoted text, click to view] > 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. 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 [quoted text, click to view] "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: > >> 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. > > 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 [quoted text, click to view] "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: > >> 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. > > 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 >
I guess that makes sense. I'm hoping that snapshot application will be
rarely required in this case, so long as the transactional replication keeps going it shouldn't need a new snapshot applying too often, and in the cases it does it looks like I'll have to rebuild the FTS. Dan Raymond wrote on Wed, 2 May 2007 09:26:45 -0700: [quoted text, click to view] > 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: >> >>> 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. >> >> 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 >>
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |