HELP! full text catalogs disapearing!

I have one publisher that pushes out a database to two subscribers, each with
Win 2003 server and SQL 2000 sp3. This Db has two tables who each have one
column full text indexed. The publisher is full text indexed so that QA can
test the data before replication happens.
my problem is that after the replication finishes sync'ing up the
subscribers, My tables are no longer full text enabled and my catalogue
disappears.
Can someone tell me how to avoid this? It takes about 5 ours to drop and
rebuild the catalog.
Thank you in advance!
Carl,
Unfortunately SQL Server 2000 FT Catalogs are not directly supported with
Replication. The one-time snapshot of your table does not re-create the FT
Catalog parameter for the FT-enable table and once the table is created on
your subscriber, you will need to manually re-create the FT Catalog and set
Change Tracking with Update Index in Background and this will run a Full
Population (if the FT Catalog is un-populated).
Could you provide more details on how your subscribers tables are setup?
Thanks,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
> I have one publisher that pushes out a database to two subscribers, each
with
> Win 2003 server and SQL 2000 sp3. This Db has two tables who each have one
> column full text indexed. The publisher is full text indexed so that QA
can
> test the data before replication happens.
> my problem is that after the replication finishes sync'ing up the
> subscribers, My tables are no longer full text enabled and my catalogue
> disappears.
> Can someone tell me how to avoid this? It takes about 5 ours to drop and
> rebuild the catalog.
> Thank you in advance!
|||I am using transactional replication to push the changes from my publisher to
the subscribers. Here is the schema for one subscriber/publisher table:
CREATE TABLE defdba (
UID bigint NOT NULL CONSTRAINT MSrepl_synctran_identity_default_1525580473
DEFAULT (0),
NAME varchar(256) NULL ,
DBAID int NULL ,
JUDGEMENTID int NULL ,
MERLINNAME varchar(300) NULL ,
ADDDATE varchar(8) NOT NULL ,
msrepl_tran_version uniqueidentifier NOT NULL CONSTRAINT
DF__defdba__msrepl_t__5C37ACAD DEFAULT (newid()),
CONSTRAINT PK_defdba PRIMARY KEY CLUSTERED
(
UID
) ON PRIMARY
)
I had set up the FTI to check for changes in the background, and then update
itself when replication push the data changes accross to the subscriber. From
what I am hearing you say, after I replicate the changes over, I must rebuild
the catalog from scratch each time. Is that correct?
"John Kane" wrote:

> Carl,
> Unfortunately SQL Server 2000 FT Catalogs are not directly supported with
> Replication. The one-time snapshot of your table does not re-create the FT
> Catalog parameter for the FT-enable table and once the table is created on
> your subscriber, you will need to manually re-create the FT Catalog and set
> Change Tracking with Update Index in Background and this will run a Full
> Population (if the FT Catalog is un-populated).
> Could you provide more details on how your subscribers tables are setup?
> Thanks,
> John
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
> with
> can
>
>
|||Carl,
No, it's the initial snapshot that is the problem as when the table schema
is created the on the subscribers, the FT Catalogs parameters (FT Catalog
name, FT-enabled columns) are not created and Change Tracking and Update
Index in background is not defined. Assuming that you do the snapshot only
once you will have to re-create this metadata only once, but when ever you
schedule another snapshot, you will have to re-do the FT Catalog metadata.
This may be the source of why your FT Catalogs are disappearing!
Furthermore, depending upon the amount (number of rows) and frequency
(batch/second or batch/hour), you should be able to use "Change Tracking"
and "Update Index in Background" on the Push Subscribers.
Regards,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:ECE0559F-C45A-4F14-A84F-9F5753BD1B1F@.microsoft.com...
> I am using transactional replication to push the changes from my publisher
to
> the subscribers. Here is the schema for one subscriber/publisher table:
> CREATE TABLE defdba (
> UID bigint NOT NULL CONSTRAINT MSrepl_synctran_identity_default_1525580473
> DEFAULT (0),
> NAME varchar(256) NULL ,
> DBAID int NULL ,
> JUDGEMENTID int NULL ,
> MERLINNAME varchar(300) NULL ,
> ADDDATE varchar(8) NOT NULL ,
> msrepl_tran_version uniqueidentifier NOT NULL CONSTRAINT
> DF__defdba__msrepl_t__5C37ACAD DEFAULT (newid()),
> CONSTRAINT PK_defdba PRIMARY KEY CLUSTERED
> (
> UID
> ) ON PRIMARY
> )
> I had set up the FTI to check for changes in the background, and then
update
> itself when replication push the data changes accross to the subscriber.
From
> what I am hearing you say, after I replicate the changes over, I must
rebuild[vbcol=seagreen]
> the catalog from scratch each time. Is that correct?
> "John Kane" wrote:
with[vbcol=seagreen]
FT[vbcol=seagreen]
on[vbcol=seagreen]
set[vbcol=seagreen]
message[vbcol=seagreen]
each[vbcol=seagreen]
one[vbcol=seagreen]
QA[vbcol=seagreen]
catalogue[vbcol=seagreen]
and[vbcol=seagreen]
|||I think your problem is with the way replication modifies tables for
replication.
It looks like you are complaining about the dropping of the full text
indexing on your publisher, correct? I think this is a consequence of using
queued updating which does modify both the publisher and the subscriber
tables.
I am curious as to why you are using queued? You might be able to get away
with using pure bi-directional transactional replication, depending on
certain factors.
Can you tell me exactly what you are trying to do, where do most of your
updates happen and how many subscribers you have? Also have you implemented
any partitioning scheme to avoid conflicts?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
>I have one publisher that pushes out a database to two subscribers, each
>with
> Win 2003 server and SQL 2000 sp3. This Db has two tables who each have one
> column full text indexed. The publisher is full text indexed so that QA
> can
> test the data before replication happens.
> my problem is that after the replication finishes sync'ing up the
> subscribers, My tables are no longer full text enabled and my catalogue
> disappears.
> Can someone tell me how to avoid this? It takes about 5 ours to drop and
> rebuild the catalog.
> Thank you in advance!
|||Hilary, thank you for your help!!!
My publisher server is where I process incoming raw files to update my
delivery servers (subscribers). There are six tables, two of which have one
column (MerlinName) that are FTI-enabled. Each table has between 4-6 million
rows in them.
I have Transactional Replication running once a week (The Publisher is only
updated 1-3 times a week.), and I rebuild the catalog three hours later in a
sql agent job. I rebuild that catalog each time because that only takes about
5 hours, where incremental rebuild can take up to 12 hours. I can run the sql
agent job seperately and it works fine.
Here is the sql agent job code:
EXEC sp_fulltext_catalog @.ftcat = 'db_cat', @.action = 'Rebuild'
EXEC sp_fulltext_catalog @.ftcat = 'db_cat', @.action = 'Start_Full'
When I come back the next day, the Subscribers data is sync'ed, but all FTI
configurations are gone. There is a catalog, but it is empty.
I dont understand how a transactional replication, which is just adding rows
to a table, can remove the FTI configurations.
"Hilary Cotter" wrote:

> I think your problem is with the way replication modifies tables for
> replication.
> It looks like you are complaining about the dropping of the full text
> indexing on your publisher, correct? I think this is a consequence of using
> queued updating which does modify both the publisher and the subscriber
> tables.
> I am curious as to why you are using queued? You might be able to get away
> with using pure bi-directional transactional replication, depending on
> certain factors.
> Can you tell me exactly what you are trying to do, where do most of your
> updates happen and how many subscribers you have? Also have you implemented
> any partitioning scheme to avoid conflicts?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
>
>
|||Were you able to find out anything about this?
"Hilary Cotter" wrote:

> I think your problem is with the way replication modifies tables for
> replication.
> It looks like you are complaining about the dropping of the full text
> indexing on your publisher, correct? I think this is a consequence of using
> queued updating which does modify both the publisher and the subscriber
> tables.
> I am curious as to why you are using queued? You might be able to get away
> with using pure bi-directional transactional replication, depending on
> certain factors.
> Can you tell me exactly what you are trying to do, where do most of your
> updates happen and how many subscribers you have? Also have you implemented
> any partitioning scheme to avoid conflicts?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
>
>
|||Carl,
As I said in my initial posting SQL Server 2000 FT Catalogs are not directly
supported with Replication. So, the replication of textual data & objects
(views, functions etc) containing full-text predicates such as CONTAINS* or
FREETEXT* during the initial snapshot is not supported. However, a
workaround is to manually create and maintain a post-snapshot script that
contains the FTS code as well as the referenced full-text indexes should be
placed in a post-snapshot script instead of being published as articles. You
can setup a SQLServerAgent job step to do this automatically and for some
T-SQL script examples , see KB article 240867 (Q240867) "INF: How to Move,
Copy, and Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867
Regards,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:2CC14822-F27F-44ED-9046-5C72714A8D79@.microsoft.com...[vbcol=seagreen]
> Were you able to find out anything about this?
> "Hilary Cotter" wrote:
using[vbcol=seagreen]
away[vbcol=seagreen]
implemented[vbcol=seagreen]
message[vbcol=seagreen]
each[vbcol=seagreen]
one[vbcol=seagreen]
QA[vbcol=seagreen]
catalogue[vbcol=seagreen]
and[vbcol=seagreen]