SQL 2005 and Replication

[Kevin Antel]

I am running into a problem between two SQL 2005 boxes. I've created a new
publication and enabled all the indexing on it to transfer. I've created a
subscription, with creation of a new DB.

None of the indexs are coming over nor is the full text catalog being built.
Even if I enable it, none of the indexes are coming over and the rest of the
replication is failing.

Any suggestions?

[Raymond Mak [MSFT]]

Hi Kevin,

Can you post the error that you are getting from the
distribution|logreader|snapshot agent so we have a better idea of what the
problem is? Did you configure your subscription to be initialized by a
snapshot? One possibility that I can think of is that the options to enable
indexes&fulltext indexes are enabled after the initial snapshot has been
generated. If that is the case then these indexes will not be copied over to
the subscriber unless you reinitialize the subscription, regenerate and
reapply the snapshot again.

Hope that helps.

-Raymond

"Kevin Antel" <kev...@cqlcorp.com> wrote in message
news:uvLQzckX...@TK2MSFTNGP05.phx.gbl...

[Kevin Antel]

I can try to get the error, basically, I manually created the indexes, and
the problem went away. I deleted the subscriber database, created a new
one, and reinitialized the distribution. It still would not create the
indexes. In fact, I had to first, on the subscription side,
sp_fulltext_database 'enable' before I could even begin the replication
process on the newly created snapshot.

Any other thoughts?

"Raymond Mak [MSFT]" <rm...@online.microsoft.com> wrote in message
news:OhWiHFlX...@TK2MSFTNGP04.phx.gbl...

[Raymond Mak [MSFT]]

Hi Kevin,

If possible, can you check the history of snapshot agent? It may point out
why the snapshot agent thinks certain fulltext indexes cannot be scripted
due to (what it thinks are) conflicting settings in the publication. I agree
that having to call sp_fulltext_database 'enable' ahead of time in the
subscriber database is unpleasant, but the initial thinking behind not doing
so automatically is driven by the off-by-default initiative so we wanted the
user to make the explicit decision for enabling fulltext index in the
subscriber database. That said, I am now leaning towards just enabling
fulltext indexing in the subscriber database automatically as not doing so
seems to yield very little security benefit while causing substantial user
pain.

-Raymond

"Kevin Antel" <kev...@cqlcorp.com> wrote in message

news:OvqjwVlX...@TK2MSFTNGP03.phx.gbl...

[Kevin Antel]

Sure, the first error I got was:

2006-04-12 15:34:28.046 Category:NULL
Source: Microsoft SQL Native Client
Number: 7616
Message: Full-Text Search is not enabled for the current database. Use
sp_fulltext_database to enable full-text search for the database. The
functionality to disable and enable full-text search for a database is
deprecated. Please change your application.

Which, performing the sp_fulltext_database 'enable' fixed, but then the
snapshot wasn't creating the indexes on the individual tables, so I started
to see the following error:

2006-04-12 15:36:02.421 Category:NULL
Source: Microsoft SQL Native Client
Number: 7601
Message: Cannot use a CONTAINS or FREETEXT predicate on table or indexed
view 'ItemFullText' because it is not full-text indexed.

Once I created the indexes and put them in the full text catalog,
replication started working.

"Raymond Mak [MSFT]" <rm...@online.microsoft.com> wrote in message

news:uSseVllX...@TK2MSFTNGP03.phx.gbl...

[Raymond Mak [MSFT]]

Are you replicating a view, function, or computed column referencing a
fulltext function such as CONTAINS() or FREETEXT()?

"Kevin Antel" <kev...@cqlcorp.com> wrote in message

news:OMmqjvlX...@TK2MSFTNGP05.phx.gbl...

[Kevin Antel]

Procedures.

/******************************************************************************
* Performs a full-text search using the supplied full-text CONTAINS clause.
*****************************************************************************/
CREATE PROCEDURE "dbo"."s_ItemSearch"
@FullTextDepartmentID INT,
@InventoryContextDepartmentID INT,
@FullTextContains VARCHAR(1000)
AS

/******************************************************************************
* Don't allow peering into other department's
(Transaction sequence number: 0x0001608C000004BE018600000000, Command ID:
1298)

Error messages:

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view

'ItemFullText' because it is not full-text indexed. (Source: MSSQLServer,
Error number: 7601)
Get help: http://help/7601

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view

'ItemFullText' because it is not full-text indexed. (Source: MSSQLServer,
Error number: 7601)
Get help: http://help/7601

"Raymond Mak [MSFT]" <rm...@online.microsoft.com> wrote in message

news:%23VByB0l...@TK2MSFTNGP02.phx.gbl...

[Raymond Mak [MSFT]]

Kevin,

The cause of your problem is that with concurrent snapshot (default for
SQL2005) processing, unique constraints that full-text indexes depend upon
can not be created until the end of what we call the reconciliation phase
some time after all the snapshot files have been applied as uniqueness
constraints are not guaranteed to hold during the reconciliation phase. This
is indeed a bug in our product and we appreciate that you have taken the
effort to report this. It would be great if you can log a bug at
http://lab.msdn.microsoft.com/productfeedback/ although I have to say that
at this moment a general fix for the issue looks pretty impossible to me. In
the mean time, you can work around the problem by doing one of the
following:

1) If you know that your schema objects (VW|FN|SP) depend upon your tables
and not the other way around, you can separate your schema objects into a
different publication instead. (Or you simply can separate the ones using
fulltext functions)
2) You can disable concurrent snapshot processing by executing
sp_changepublication '<yourpublicationname>', 'sync_method', 'native' at the
publisher database although shared locks will be taken out on your tables
for relatively long period of time when snapshot generates.
3) At the risk of getting myself into trouble but given that nobody ever
wants locks to be taken out on their tables, you can also change the
sync_method of your publication to the undocumented|unofficial|unsupported
option of 'database snapshot' if you are running the *Enterprise* Edition of
SQL Server 2005 at your publisher. This will give you the low locking
overhead of concurrent snapshot processing without invoking the rather
complicated code path (including this bug) that concurrent snapshot
processing entails. Just to make you feel more comfortable with this option,
we do have a full pass of existing tests on it and I have written a rather
extensive test suite for the feature. And speaking strictly from the
perspective of a computer programmer, I definitely prefer the simplicity of
the database snapshot option over the trickier concurrent snapshot mechanism
that we have been offering.

Since I am probably in trouble anyway, I would appreciate if you can log a
DCR at http://lab.msdn.microsoft.com/productfeedback/ to get the 'database
snapshot' sync method option exposed officially if that works well for you.

-Raymond

This posting is provided "AS IS" with no warranties, and confers no rights.

"Kevin Antel" <kev...@cqlcorp.com> wrote in message

news:ucWBUvuX...@TK2MSFTNGP05.phx.gbl...

[Raymond Mak [MSFT]]

I forgot to mention that with the 'database snapshot' sync_method option,
you will also be subject to the restrictions described by the following
linked BOL topic:

http://msdn2.microsoft.com/en-us/library/ms190677(SQL.90).aspx

-Raymond

This posting is provided "AS IS" with no warranties, and confers no rights.

"Raymond Mak [MSFT]" <rm...@online.microsoft.com> wrote in message
news:uYgbMLyX...@TK2MSFTNGP05.phx.gbl...

[Paul Ibison]

Kevin,
another option would be to do the full-text indexing in a post-snapshot
script.
I'd generally go for Raymond's option (1) - it allows you to reinitialize
programming objects much more easily and leave the data intact, which is
usually the time-consuming part of initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

[Raymond Mak [MSFT]]

Thanks Paul for the additional insight\information although I just need to
add a slight bit of information regarding the use of a post-snapshot script
as a workaround since Kevin's scenario is actually more complicated. The
real issue here is not the fulltext indexes are not replicated at all, their
creation is simply deferred after the reconciliation phase which is
unfortunately *after* the distribution attempts to create the stored
procedure that requires them for reason that I have outlined in my previous
posting. So in order to use a post-snapshot as a workaround, it would be
necessary to put the definition of SP|VW|FN referencing fulltext functions
rather than the fulltext index definitions in the post-snapshot. The effect
will be similar to having a separate snapshot publication containing these
schema objects.

And to attempt to garner some sympathy from those who may feel angry that we
can't get our dependencies straight after all these years, let me say that
1) this is not a problem with non-concurrent snapshot processing as there is
no need to defer the creation of unique indexes, and 2) the problem was a
lot worse in the SQL2000 days as foreign keys referencing the deferred
unique indexes are broken for example. I can honestly say that I have spent
a lot of effort in SQL2005 to make these schema elements dependencies work
for transactional\snapshot replication so it won't be neccessary to resort
to using a post-snapshot script as a workaround, I can give the following
examples:

1) foreign keys depend on unique constraints that can get deferred in
concurrent snapshot processing
2) secondary xml indexes can depend on primary xml indexes which depend on
primary keys that can get deferred in concurrent snapshot processing
3) unique clustered indexes on indexed views actually work like
non-clustered indexes on the base tables and so they need to be deferred
during concurrent snapshot as well
4) On delete|update set default foreign keys depend on default constraints
that they reference
5) Extended properties can be defined on any schema elements that can get
deferred and so they need to be deferred as well

As you can imagine, the code has gotten quite hairy over the years just to
handle the cases I have outlined above and alas, it is not perfect by any
means. What makes Kevin's scenario so difficult is that an SP|VW|FN using a
fulltext function can in turn be depended upon by other article objects
including a computed column in a table and so a simple-minded deferral of
its creation will likely not suffice.

Ok, I should stop trying to post myself into deeper trouble and start
staring at the code in utter despair.

Hey, I am cheery :)

-Raymond

This posting is provided "AS IS" with no warranties, and confers no rights.

My opinions don't represent the official position of Microsft Corp., but I
am indeed a Microsoft employee.

[Paul Ibison]

Thanks raymond for the explanation. As a matter of interest, I'm wondering
why the reconciliation phase couldn't be done before the creation of
programattic objects. Presumably this is because some constraints can refer
to UDFs?
Rgds,
Paul Ibison

"Raymond Mak [MSFT]" <rm...@online.microsoft.com> wrote in message

news:%23SfhtL0...@TK2MSFTNGP05.phx.gbl...

[Raymond Mak [MSFT]]

The reconciliation phase is really coordinated at the publisher's
transaction log and lies mostly outside the scope of "normal" snapshot
processing, that is why we already have quite a bit of extra logic to defer
creation of programmatic objects (note: we can't really separate
programmatic objects such as views and data objects such as tables as they
can depend on each other) until the end of the reconciliation phase rather
than moving the reconciliation phase up front as all the distribution agent
has is just a signal to the end of the reconciliation phase. Another big
part of why we can't really "think outside of the box" and turn the problem
upside down is because the feature was originally designed to have the
reconciliation phase after snapshot files are applied and numerous tweaks
have subsequently been added based on that assumption. It is well nigh
impossible to introduce such a drastic change without breaking backward
compatibility with downlevel subscribers that our customers have come to
expect.

-Raymond

"Paul Ibison" <Paul....@Pygmalion.Com> wrote in message
news:OPAXef6X...@TK2MSFTNGP05.phx.gbl...

[Michael Hotek]

Not so fast. I would VERY strongly disagree with that. You have to create
a full text catalog before any full text indexes can be created. That full
text catalog has to be pointed to a specific directory. That directory does
not necessarily exist on the subscriber and it might also have to be created
in a completely different location on each subscriber. Then you also have
the fact that you have to test for specific editions of SQL Server as well
as specific versions before you even attempt something like this. I can
replicate from 2005 to 2000, but full text indexing does not transfer from
2000 to 2005 or vice versa and has to be completely rebuilt during the
upgrade process.

If you try to do this by default, you are going to have to literally rip
apart the replication engine and code a PILE of stuff to take care of how
you even go about doing this between different versions of SQL Server as
well as how do you even begin to handle the fact that the full text catalog
can reside in a different location on every single subscriber. Then, what
happens when you filter or republish..........

Just to add even more complexity into the problem... :)

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.

"Raymond Mak [MSFT]" <rm...@online.microsoft.com> wrote in message

news:uSseVllX...@TK2MSFTNGP03.phx.gbl...

[Paul Ibison]

Raymond - please could you email me offline as I'd like to ask you a private
question related to this thread. My email address is
Paul....@ReplicationAnswers.Com.
Regards,
Paul Ibison

[Raymond Mak [MSFT]]

Hi Mike,

As always, feedbacks are welcome. The situation with regard to the automatic
creation of fulltext catalog at the subscriber is probably not as dire as
you may fear. First of all, we are already doing that in SQL2005 today
without specifying an explicit phyical location so what will typically
happen is that the fulltext catalog will be created in the default location
(MSSQL.<instanceno>\FTData I think). But, if the user has created the
fulltext catalog at the subscriber using a non-default physical location
ahead of when the snapshot gets delivered, the user-defined fulltext catalog
will be used instead. In addition, we only replicate the definitions of the
full-text indexes during snapshot processing so the indexes will get rebuilt
anyway at the subscriber.

Now, the problem remains as to whether fulltext indexing should be enabled
automatically at the subscriber if the snapshot demands it. Like I mentioned
earlier, there is an initiative across the entire SQL Server product to
disable as many auxiliary features by default (fulltext indexing being one
of them) in an attempt to reduce the attack surface area so it didn't feel
right for me to be enabling fulltext indexing automatically. But looking at
the situation more pragmatically, there really isn't much gained
security-wise from not enabling fulltext indexing at the subscriber since
the user will almost certainly do it when the snapshot fails to deliver.
Believe it or not, the code for enabling fulltext indexing automatically is
downright trivial compared with some of the stuff that I need to do in order
to make concurrent snapshot works :) (That is not to say I am promising the
fix will show up in the near future.)

-Raymond

"Michael Hotek" <mi...@solidqualitylearning.com> wrote in message
news:ungwLsBY...@TK2MSFTNGP04.phx.gbl...