SQL 2005 and Replication -- sql server replication

sql server replication : SQL 2005 and Replication

Raymond Mak [MSFT]
4/12/2006 9:50:57 AM

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

[quoted text, click to view]

Raymond Mak [MSFT]
4/12/2006 10:48:37 AM

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

[quoted text, click to view]

Raymond Mak [MSFT]
4/12/2006 11:14:54 AM

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

[quoted text, click to view]

Kevin Antel
4/12/2006 11:38:50 AM

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?

Kevin Antel
4/12/2006 1:20:43 PM

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?

[quoted text, click to view]

Kevin Antel
4/12/2006 2:06:53 PM

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.

[quoted text, click to view]

Kevin Antel
4/13/2006 12:00:00 AM

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

[quoted text, click to view]

Raymond Mak [MSFT]
4/13/2006 10:50:50 AM

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.

[quoted text, click to view]

Raymond Mak [MSFT]
4/13/2006 10:55:44 AM

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.

[quoted text, click to view]

Raymond Mak [MSFT]
4/13/2006 2:40:46 PM

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
4/13/2006 9:31:27 PM

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)

Paul Ibison
4/14/2006 12:00:00 AM

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

[quoted text, click to view]

Raymond Mak [MSFT]
4/14/2006 9:59:04 AM

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

[quoted text, click to view]

Michael Hotek
4/14/2006 6:25:55 PM

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.

[quoted text, click to view]

Paul Ibison
4/15/2006 8:25:35 PM

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.Ibison@ReplicationAnswers.Com.
Regards,
Paul Ibison

Raymond Mak [MSFT]
4/15/2006 8:29:23 PM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

View Other Months
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
October 2008

home · blog · groups

Questions? Comments? Contact the dn