none
One subscriber and two or more publisher

    Question

  • Is it posible have one subscriber and two or more publisher ?

    I lost data from Central Server (Subscriber) when I configure a new or second publisher Remote Server.

    I have one subscriber (Central Server) and one publisher (Remote Server 1), the replication works very well. I can see the data on the Central Server, but when I install the second publisher (Remote Server 2), I lose the data of the Remote Server 1 in Central Server (subscriber), after a few hrs, I can see only the new data in the Central Server of the publisher Remote Server 1, the history data was deleted after install the second Remote Server 2 publisher.

    If I install the third publisher (Remote Server 3), again, I lose all the data in the Central Server (Subscriber) of the Remote Server 1 and 2, a few hrs, I can see the only new data Server 1 and 2, but the history I lost.


    Can I avoid or prevent to lose the history data in Central Server (subscriber) when I install a new Remote Server or reinitialize subscription or drop/install the publisher/subscriber on old Remote Server ???

    Ernesto Ono

    Monday, February 20, 2012 7:49 PM

Answers

  • The reason the data is deleted is because the article property Action if name is in use is set to Drop existing object and create a new one.  The data will need to be horizontally partitioned and the article property Action if name is in use will need to be set to Delete data. If article has a row filter, delete only data that matches the filter.

    You can have a filter on an article and still replicate all of the data.  For instance, if we create a column LocationID int on the tables being replicated for Publication1, we can set LocationID = 1 for all rows.  Then Publication1's row filters would be LocationID = 1 which would replicate every row.

    Have a look at Central Subscriber for details on the Central Subscriber model.


    Brandon Williams (blog | twitter)

    Tuesday, February 21, 2012 9:53 PM

All replies

  • You probably want to take a look at the @pre_creation_cmd of sp_addarticle if this is Transactional or sp_addmergearticle if this is Merge.  In the Article Properties diaglog this property is Action if name is in use.  You probably have this set to Drop existing object and create a new one.

    What you'll want to do is create a location specific ID column which represents each publication, extend the primary key to this column, and horizontally filter the publications on this column.  Create seperate subscriptions for each publication but use the same subscription database.

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


    Brandon Williams (blog | twitter)

    Tuesday, February 21, 2012 3:05 AM
  • Thank you for reply, I was using the SQL Server Profile and
    I saw a little problem. When I reinitialize the subscription on any Remote
    Server, in the Central Server I saw that drop the table and create again the
    same table and after execute a stored procedure to recover the data from the
    Remote Server, but the rest of the Remote Server didn’t do the recover. I don’t
    want to delete all the data in the Central Server.

    I would like to ask, how does the sp_addarticle works ? (I
    read the article in Spanish in the follow link http://msdn.microsoft.com/es-mx/library/ms173857.aspx
    ) because I found that I have to execute on all the Remote Server, I planned to
    execute on 133 Remote Server.

    About the filter, I think it does not for me, because I want
    to replicate all the data in all the Remote Server.

    Similar to the follow link (English): http://msdn.microsoft.com/en-us/library/ms152761.aspx

    Thank you, I hope if it has other solutions


    Tuesday, February 21, 2012 8:01 PM
  • The reason the data is deleted is because the article property Action if name is in use is set to Drop existing object and create a new one.  The data will need to be horizontally partitioned and the article property Action if name is in use will need to be set to Delete data. If article has a row filter, delete only data that matches the filter.

    You can have a filter on an article and still replicate all of the data.  For instance, if we create a column LocationID int on the tables being replicated for Publication1, we can set LocationID = 1 for all rows.  Then Publication1's row filters would be LocationID = 1 which would replicate every row.

    Have a look at Central Subscriber for details on the Central Subscriber model.


    Brandon Williams (blog | twitter)

    Tuesday, February 21, 2012 9:53 PM
  • Thank you very much, It's working very well !!!!!!!!

    I tested in my virtual machine with 3 SQL Server (One Subscriber and 2 Publisher)

    I see the property of Action if name is in use is very important to configure but necesary to establish the filter

    Wednesday, February 22, 2012 12:29 AM