Logical Records in Merge Replication RRS feed

  • Question

  • Good Day All,

    We're currently running SQL Server 2008 R2 in a merge replication topology.  Publisher and distributor are on same machine.

    I've done some reading on the use of Logical Records to process merge changes as a unit across related tables.  However, I've discovered three key points (from BOL):

    1) Use of Logical Records has been deprecated and not recommended for use in future development work,
    2) Child tables can only have one parent table, and
    3) Custom conflict resolution with BLH or custom resolvers is not supported for articles forming a Logical Record.

    The concept of processing merge changes across related tables as a single transactional unit is ideal based on our changing business requirements.  To describe our environment, we have several shared tables in use in our replication scheme.  One would be similar to:

    CREATE TABLE [dbo].[Test]
        [TestID] [varchar](50) NOT NULL,
        [DocumentTypeID] [varchar](50) NULL,
        [DocumentID] [varchar](50) NULL,
        [TestDate] [datetime] NULL,
        [Remarks] [varchar](1048) NULL,
        [UserID] [varchar](50) NULL,
            [PK_Test_TestID] PRIMARY KEY CLUSTERED ([TestID] ASC)
            PAD_INDEX = OFF,
            IGNORE_DUP_KEY = OFF,
            ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON
        ON [PRIMARY]
    In our merge publication, [dbo].[Test] is configured as a joined table (unique key) to multiple filtered tables via the [DocumentID] column ([DocumentID] is unique across all filtered tables).  We'd much prefer to continue to keep the shared tables scheme we have in use.  However, we'd also like to pursue the logical records avenue.  If the feature is being deprecated, what are our options to achieve the same end goal?  Would there be a way to process merge changes as a unit using Business Logic Handlers?

    Any assistance in this regard is greatly appreciated!

    Best Regards
    Sunday, August 18, 2019 3:24 PM

All replies

  • It is deprecated and does not work in 2016.

    What the business Logic resolvers allow you to do is detect certain events, like a conflict or an error and then do something. This do something can be resolve the conflict, log the conflict or modify data. The business logic resolver can also be invoked on any change, for example it can be coded to only fire on a delete.

    What you can do is the business logic resolver running on the publisher can connect to the subscriber and get data from the subscriber, or write to the subscriber if you wish. These changes occurring as part of the resolver will not trigger subsequent merge replication.
    For example if you insert a row in a table in the subscriber by your business logic resolver, merge replication will not pick that row up and replicate it back to the publisher or other subscribers. 

    Sunday, August 18, 2019 5:17 PM
  • Hi Hilary,

    Thanks for your reply, although I'm not quite sure I understand what you mean.  If I have a filtered table in my publication, and that table has 5 join filters, I can use business logic handlers to intercept the change on the filtered parent table and do the manual inserts and/or updates for the related rows from there?  As those related child rows on the merge subscriber are already part of the merge changes to be processed how would I 'remove' them from the queue?  And all of this could be captured in a transaction, to ensure rollback if the connection were dropped (for example)?

    I have no idea why MS would plan to deprecate this feature, especially considering how handy it would be for (what I imagine would be) many organizations out there.

    Best Regards
    Tuesday, August 27, 2019 12:54 AM
  • You can do whatever you want using the business logic removed, BUT it works row by row. So if the parent comes through and you handle it via the blm, it knows nothing about child rows, if any.

    What I was suggesting what the if you do something via the BLM or a trigger, or a stored procedure resolved and the BLM, trigger, or stored procedure does something to another table that change in the other table will not be processed in subsequent merge replication sync's (unless you modify it by a user process).

    Tuesday, August 27, 2019 1:05 AM
  • Thanks Hilary.  Can you offer any suggestions as to how to accomplish applying changes across joined tables as a unit if logical records can't be used?
    Tuesday, August 27, 2019 11:24 PM