Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Multiple Publishers to one Subscriber questions

Answered Multiple Publishers to one Subscriber questions

  • Friday, November 16, 2012 8:38 AM
     
     
    Sign In to Vote
    0

    I need to copy data from branch offices to a central server and it seems that "Multiple Publishers to one Subscriber" topology is the best solution. But I have several questions.

    Is it correct that Merge replication cannot be used in this case, only Transactional (with each branch office as a publisher and central server as a subscriber)?

    I plan to have distinct ranges of identity columns on every remote server. Do I need to add filters to every article (e.g. ID between a and b) in order to avoid deleting data on central server when replication on a new office server is configured?

    I'm not sure whether the servers will be connected all the time. I was thinking about transactional replication with queued updates. Is it a correct solution? How do I implement it? Does it allow the servers to be disconnected for some period of time and synchronize data when they are connected again?

    Is there a way to implement such replication on the central server as Merge replication with kind of "upload-only" articles (from branch office to the central server)? This would simplify the whole process but I'm not sure whether "upload-only" option is available - I've seen "download-only" option.

    Mark

     

All Replies

  • Friday, November 16, 2012 9:02 AM
     
     
    Sign In to Vote
    0

    I think the below article matches your exact requirement. After reading the below, you can post any further questions you have regarding this

    http://msdn.microsoft.com/en-us/library/ms151790(v=sql.105).aspx

     
  • Friday, November 16, 2012 10:16 AM
     
     
    Sign In to Vote
    0

    Thank you, I've seen this article. In fact, it matches my requirement. But it is also very confusing and answers maybe to one of my questions only.

    The article mentions common scenarios like "inventory data can be consolidated from a number of servers" (this is exactly my requirement) but then suddenly it switches to "inventory tables that are only updated at a central site" (this is not what I need) and never mention the first scenario again.

    The only answer that I got from this article is a strong suggestion that this can be done using Merge replication configured on the central server (but without giving any details).

    So just to recap; my requirement is to consolidate data from remote servers to a central server. The data is entered and updated on remote servers only. Theoretically it can be later updated on the central server but in such a case I don't need the data to be copied back to remote servers. The data is never created on the central server - this is something that is briefly mentioned in the article but later not explained.

    I have identity columns in the tables that need to be replicated but I can set separate ranges of IDs for each table on every remote server. I can add guid column to every table and create an unigue index on this column.

    So most of my questions are still valid:

    - how do I define articles with Marge replication that are "upload-only"? The article mentions "download-only" articles but this is not what I need.

    - how do I set filters for every office? I assume that the filter should be dynamic - based on the server name, right? Can someone show an example of such a filter?

    Mark 

     
  • Friday, November 16, 2012 12:22 PM
     
     
    Sign In to Vote
    0

    Transactional publication with updatable subscriptions:
    The Publisher streams transactions to SQL Server Subscribers after they receive an initial snapshot of the published data. Transactions originating at the Subscriber are applied at the Publisher.

    Merge publication:
    The Publisher and Subscribers can update the published data independently after the Subscribers receive an initial snapshot of the published data. Changes are merged periodically. Microsoft SQL Server Compact Edition can only subscribe to merge publications.

    Hope this helps ...

    Regards

    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

     
  • Friday, November 16, 2012 12:39 PM
     
     
    Sign In to Vote
    0

    Thanks for the replay but I'm not sure to which of my question it answers. I know what transactional replication with updatable subscriptions (have you noticed that I need exactly the oposite?) and merge replication is. I need answers to my questions, or recomendation how should I implement the replication in my case (data upload from remote servers to central server). Do you suggest transactional replication from remote servers to the central server - just the oposite what was suggested previously (in the article - see link above)? I would prefer to have everything configured on the central server to make the maintenace easier - that's why the idea with merge replication is OK for me. I just need some details (see questions in my previous post).

    Mark

     
  • Sunday, November 18, 2012 4:21 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Merge replication can be used. Set it up as the publisher and the remote branch offices as subscribers. Merge replication will manage the identity ranges for you. You can also use transactional replication with each branch office as a publisher and your central server as a subscriber. In this case you will need to add a location identifier to each branch office which will have the value of the branch office, and then set up row filters to filter on this location.

    For example you may want to add a column called location nvarchar(256) default @@ServerName to search remote branch office. If you are using identity ranges and your PK is on the identity column you will need to extend the PK to include this location column to preserver uniqueness in the central subscriber.

    You will also need to set your article Properties to set Action Name if in Use to "Delete data. If article has a row filter, delete only data that matches the filter."

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941