Re: Snapshot Delivery - Violation of PRIMARY KEY constraint

Hi Richard, if you can come up with a deterministic repro for the PK
violation issue, you should file a bug\feedback item @
http://connect.microsoft.com. Perhaps there is something more to this than I
initially suspect. Thanks much.

-Raymond

"Richard T" <RichardT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6F168E41-044D-44D1-8D55-CC84C64D8BB6@xxxxxxxxxxxxxxxx
Hi Raymond,

Thanks for your description of what might cause this problem.

I still can't fathom why I'm getting the primary key collision as the
filter
isn't time dependent and the records in the publishing database have not
been
updated (there are only three of them!).

I've implemented a solution based on your previous "double buffering"
advice
and it's working very well.

Thanks again.

Richard

"Raymond Mak [MSFT]" wrote:

Hi Richard,

Given that I don't have much details about your scenario, one possibility
which I have mentioned previously is that your filter is time-dependent.
That is, the set of rows that qualify your filter when the snapshot was
generated wasn't necessarily the same as the set of rows returned when
you
evaluated the filter manually at the publisher (at a later time). Another
more esoteric possibility is that if you have a non-clustered primary key
(or unique indexes) on your published table, uniqueness on the primary
key
can be violated in the bcp data with the default concurrent snapshot
processing mechanism if there were updates to the published table that
move
rows to different locations in the clustered index during the bcp scan
since
we don't take shared table locks to disallow this from happening in the
first place. If this is the case, you will get uniqueness violation
regardless of whether you use truncate or delete as the article
pre-creation
command. In case you are wondering if this even works, we have elaborate
logic to create a non-unique version of the primary key during the
concurrent snapshot reconciliation phase while the "real" primary key
will
be created once the uniqueness violations have been compensated at the
end
of the reconciliation phase. *However*, this will only work only if the
destination table is created from scratch at the subscriber (meaning you
need to start with an empty subscriber database or use drop as the
pre-creation command) given that any existing unique non-clustered index
at
the subscriber that we can't touch will badly trip up the bulk load of
data
with potential uniqueness violation.

If you are using SQL2005 EE, you can use the new 'database snapshot'
sync_method which avoids most of these complexities.

-Raymond

"Richard T" <RichardT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A4AD0728-C82D-4BEB-9E28-E8E6AD00F922@xxxxxxxxxxxxxxxx
Thanks for the info Raymond, I think I'll pursue a solution based on
your
suggestion of "double-buffering", due to the disruptive nature of
snapshot
delivery.

I'm still a bit confused about the primary key error though. I've run
the
filter condition manually at the subscriber and it produces the same
records
as when the filter is run on the publisher. In these circumstances I'd
not
have expected a primary key error as the records being replicated
should
have
been removed. Have I misunderstood how this area of replication works?

Thanks for your help.

Richard

"Raymond Mak [MSFT]" wrote:

Hi Richard,

The simplest explanation is that the filter you use for deleting data
at
the
subscriber doesn't account for all the rows that will be replaced by
the
bcp
data delivered with the snapshot. This is likely to be the case if the
filter that you define has the potential to be evaluated differently
at
the
publisher and at the subscriber. One example will be a filter that
uses
the
getdate() function as the current date when the snapshot was generated
is
most likely different from the current date when the snapshot was
delivered.
Another example will be a filter that is based on data from another
table.

Delivering a snapshot is fundamentally a very disruptive process at
the
subscriber database although I have seen many attempts to keep the
subscriber data available while a snapshot is being delivered. At
best,
such
attempts will end up causing significant performance degradation in
the
snapshot delivery process as it would be nearly impossible to fulfill
the
minimal-logging requirements when the snapshot data is being
bulk-loaded.
At
worst, folks run into difficult to resolve issues such as the one that
you
encountered.

Perhaps a better strategy for making subscriber data available while
snapshot is being delivered is to implement some kind of
"double-buffering"
mechanism. That is, snapshot data can be first delivered to a staging
area
while keeping the existing subscriber data available which will then
be
brought online once the snapshot is fully delivered. One way to do
this
is
to simply setup a different subscriber database on the same server and
switch your application to point to the new subscriber database once
it
is
fully initialized. Another way is for me to implement the same at the
table
level using table rename and\or partition switching. However, given
the
current lack of resource on my end, you may want to log a feedback
item @
http://connect.microsoft.com if you ever want to see this implemented.
Note
that either way you pay for the higher data availability with extra
disk
space usage (up to 2x) but I guess this is typically how such things
go.

Hope that helps,

-Raymond
"Richard T" <RichardT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4FABE3E8-A8B7-43C6-A669-0B5AA6EF5105@xxxxxxxxxxxxxxxx
I'm having a problem with transactional replication. The problem
arises
when
applying a snapshot to a subscriber.

The 'Action if name is in use' property of the articles is set to
'Delete
data. If article has a row filter, delete only data that matches the
filter'

I filter the articles by use of a function which returns 1 or 0
depending
on
whether the record is to be replicated.

When the bcp process runs it errors with Violation of PRIMARY KEY
constraint
'PK_Article_ArticleMaster'. Cannot insert duplicate key in object
'dbo.ArticleMaster'.

If I change the 'Action if name is in use' property of the articles
to
'Truncate all data in the existing object' the snapshot is applied
successfully.

My problem here is that the database is used as the source of a
website
and
needs to be available 24/7, so if I truncate the tables when
applying
the
snapshot visitors of the website will be presented with an empty
screen.

I has assumed that the 'Delete data. If article has a row filter,
delete
only data that matches the filter' setting would remove those
records
which
are about to be bcp'd into the table, am I wrong?

Does anyone have an explanation of what's happening?

Thanks, Richard










.

Relevant Pages

  • Re: Snapshot Delivery - Violation of PRIMARY KEY constraint
    ... The simplest explanation is that the filter you use for deleting data at the ... data delivered with the snapshot. ... publisher and at the subscriber. ... Delivering a snapshot is fundamentally a very disruptive process at the ...
    (microsoft.public.sqlserver.replication)
  • Re: Snapshot Delivery - Violation of PRIMARY KEY constraint
    ... which I have mentioned previously is that your filter is time-dependent. ... the set of rows that qualify your filter when the snapshot was ... destination table is created from scratch at the subscriber (meaning you ... snapshot delivery process as it would be nearly impossible to fulfill the ...
    (microsoft.public.sqlserver.replication)
  • Re: Snapshot Delivery - Violation of PRIMARY KEY constraint
    ... I still can't fathom why I'm getting the primary key collision as the filter ... the set of rows that qualify your filter when the snapshot was ... destination table is created from scratch at the subscriber (meaning you ...
    (microsoft.public.sqlserver.replication)
  • Re: Creating Snapshot Subscription: Cant choose Dont Initialize Schema and Data
    ... pk's on this table and some of the data coming from the snapshot publication ... > replicated from the publisher). ... > want to Publisher to be able to delete data on the subscriber? ... >> the table that matches the row filter statement. ...
    (microsoft.public.sqlserver.replication)
  • Re: trans replication of large tables...
    ... Just because it is set to local admins doesn't mean that it can ... something as simple as a timeout during the application of the snapshot. ... subscriber, the subscription is created specifying that the subscriber ... > in the same publication - or if they are queried in the same procs they ...
    (microsoft.public.sqlserver.replication)
Loading