Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Unanswered: Transaction Replication Multiple Publication


    I have one Publisher! (SQL 2005)
    Two Subscribers. (SQL 2005)
    Each subscriber requires different row filter from the published tables.
    So, i need to create Two publications for each subscriber for the same db.
    When i setup first publication, and apply filters to published tables.
    All goes fine.
    As soon i create an other publication with same published tables but DIFFERENT row filter; the log reader agent fails!
    The process could not execute 'sp_replcmds' on 'DB1'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
    Get help: http://help/MSSQL_REPL20011
    Execution of filter stored procedure 2076702271 failed. See the SQL Server errorlog for more information. (Source: MSSQLServer, Error number: 18764)
    Get help: http://help/18764
    The Log Reader Agent failed to construct a replicated command from log sequence number (LSN) {0008443a:000003b5:003e}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
    I have tried to re-initialze the subscriptions and re-run snapshots but no luck.
    As i remove the row filter from the second publication; log reader starts working fine.

    Previously i was using Merge replication and all was going BUT, on very large DML on publisher, we use to receive deadlock errors on application.
    I decided to use Tran repl instead because log reader reads the changes from tran-log instead of locking the published tables; NOW I AM STUCK!

    Ideas please!!!!
    Cheers!





    !Direction is More Important than Speed!

    http://talatnauman.blogspot.com/

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am no replication expert but can you combine the two publications into one one publication by combining their logic with an OR statement perhaps.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Hi, thanks for the idea.
    But the solution doesn't seem to work in my case because the query that would accommodate both row filters will insert unwanted data on the subscriber tables.
    Eg, if i want one subscriber to get all employees with status = 1 and the other subscriber to get all employees with status = 2.
    One publication with a row filter on emp table
    select pulished columns where status = 1 or status =2)
    in this case both subscribers would get employes with status 1 and 2, but i want one subscriber with emp.status = 1 and the other with emp.status =2.

    Seems like since transaction replication allows one log reader agent per publisher, adding two publications with different row filters causes log-reader to fail!
    Looking for ideas!!
    Cheers!





    !Direction is More Important than Speed!

    http://talatnauman.blogspot.com/

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I'm pretty sure that when you set up the pub there is an option to use a separate thread (log reader) for that pub. Look at advanced options when creating the pub.

    Second way would be to modify the repl insert, update, and delete procs on the subscribers to filter for the status used on that instance.

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Hi.
    According to MS SQL Server Dev Team; Multiple Log Reader Agent for Same Publisher DB is not allowed:
    http://www.sqlmag.com/Article/Articl...ver_26853.html


    The second option you advised would be a manual effort each time i run the snapshot i guess!
    Thanks.
    Cheers!





    !Direction is More Important than Speed!

    http://talatnauman.blogspot.com/

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Manual! That is something I read or a spanish friend I have lunch with. It is not something I do.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •