Skip to Content

Possible to have 2 repservers with same primary database?

I would like to replicate a table out to 2 repservers. I don't think this is possible since a RepAgent maps one primary database to one primary repserver. I see a capability for multi-path replication but this is not quite what I want (I think).

Does anyone know if it is possible to replicate a table to 2 repservers? Routes and some type of rep-to-rep replication maybe? Hopefully it does not have to be that complicated.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Jun 21, 2016 at 08:47 PM

    A repagent connects to one and only one repserver. (see 'sp_config_rep_agent' => no way to designate more than 1 repserver)

    Multi-path replication should allow you to configure multiple repagent threads in a single PDB, with each repagent thread having the ability to talk to a different repserver.

    So, out-of-the-box solutions for what you want to do:

    - can't be done

    or

    - use multi-path replication

    -----------------------------

    Alternative ('complicated') solutions:

    - IUD trigger on base table that writes to mirror table in another/separate PDB; each PDB has its own repagent which can talk to a different repserver; queue all the issues re: x-db transactional overhead, multi-db recovery (from dumps) issues, RDB sync issues, ...

    - route from PRS to another (P)RS; queue all the questions/issues about routes/repdefs/subs/etc

    -----------------------------

    Realistic solution:

    Use repserver's basic ability to allow multiple subscriptions against a single publication (aka repdef).

    PDB repagent sends txns to PRS; 2 (or more) downstream subscriptions receive the same stream of txns; downstream subscriptions could be hosted by the PRS or any downstream RS's (reachable by a route).

    -----------------------------

    If none of the above addresses your question ... consider posting back with a) a detailed explanation of why you want to connect a repagent to 2 different repservers and b) why none of the above solutions would apply in your scenario.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 21, 2016 at 08:34 PM

    Replication target is always a database.

    One or more replication servers can be used to send the data from source to target.

    If two replication servers are involved in the pipe line then you use routes between RS to RS.

    Also database can belong to one REPL domain (e..g. all prod or qa or dev, etc.)

    So please elaborate on "sending a table out to 2 repservers " ?

    Avinash

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 28, 2016 at 09:19 PM

    I wonder if it would be possible to setup a regular primary/replicate warm standby pair, and then setup a database subscription replicate off the logical connection (also called MSA). Then on that subscription replicate database, setup it's own warm standby replication via a second separate repserver.

    I think that subscription replicate database would have to have the repagent option "send maint xacts to replicate" enabled for the 2nd warm standby replication to work.

    This might not be what you're looking for, but could potentially be a way to prevent a single repserver hard/unrecoverable crash from taking out the whole replication system.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.