Re: replication settings

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Logical Records will prevent child records from being inserted before parent
records, which will cause a merge agent failure in SQL 2000, but not be
logged as a conflict.

The way it works in SQL 2000 is that if you get such a failure during a
sync, the offending row is put in a retry queue and at the end of the batch
the offending row(s) is tried again. If your generations per batch is large
enough and the parent has already gone it, it will succeed. If not your
agent will fail and chances are next time you start your agent the row will
sync correctly. Many dba's use the NFR clause on their constraints to
prevent this from occurring.

As you can imagine this has performance implications.

Logical records makes sure all rows are inserted in the correct order from
the start. But they should not cause more conflicts than in SQL 2000. With
the NFR clause in place it is possible that you will get orphaned child
records in your tables and the parent row will be logged in the conflict
tables.

I fail to understand why you think they might generate more conflicts. Can
you help me out here, I could be missing something again:)

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

"Troy Wolbrink" <wolbrink@xxxxxxxx> wrote in message
news:uMXm6tSKGHA.3856@xxxxxxxxxxxxxxxxxxxxxxx
With SQL 2005, related rows are grouped together in logical records and
inserted in the correct order ...

That's good to know. I will assume that exisitng constaints with that
property set will perform in the same way too.

I looked into it (logical records), and let's say you have a customer (for
example) with orders, and the customers table has a "TotalOrdersAmount"
column. You can tell SQL Server 2005 to treat a customer and all their
records as one big record. While this sounds good, it seems to make
conflicts much more likely. I think in a case like this I'd much rather
look into a way of keeping "TotalOrdersAmount" up to date without using
this feature. But perhaps it would have more use in cases where conflicts
are less likely.

--Troy



.

Relevant Pages

  • RE: Replication Error
    ... account that the SQL Agent runs under. ... When I setup replication two jobs are setup within sql server server. ... step of each job (Snapshot Agent startup message, ...
    (microsoft.public.sqlserver.replication)
  • Re: Ok, call me silly...
    ... out it will also use substantially fewer resources than Informix too. ... I in fact see SQLite matching Oracle and DB2 in load speed. ... SQL> CREATE TABLE parent ( ... SQL> CREATE TABLE child AS ...
    (comp.databases.informix)
  • Re: How to remove rows from a DataTable without deleting form the database
    ... However are you sure that that DataRow Array TargetRows is filled. ... You may have already answered the question: the JobDataTable is the parent ... It is during the database update ... Because of the way the 'user interface' works, I can't build up an SQL ...
    (microsoft.public.dotnet.general)
  • Re: permission problem?
    ... SQL Agent has an errorlog file, which you for instance can get to from EM, ... I suggest you read in Books Online about permissions. ... >> What error messages do you have in the SQL Server Agent errorlog? ...
    (microsoft.public.sqlserver.server)
  • Re: Data Normalization
    ... Given that SQL, the necessary data should exist in Currencies, Banks ... the parent table other than through a lookup field", ... I added an additional field to Tickets called ...
    (microsoft.public.access.gettingstarted)